[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 907
  • Last Modified:

How to insert values for BLOB, CLOB ..etc?

How to insert/update values for BLOB, CLOB ..etc
also need to know the usage for blob/clob,
need more examples for this.
How to check characters inside blob?
0
sakthikumar
Asked:
sakthikumar
  • 3
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
Insert them from what tool/language/???

You will need to convert the BLOB back into a clob to check for strings/characters.
0
 
BSSForestsCommented:
The following code is how I update a file into a Oracle Blob using Java:
As for insert a new blob, you need insert a new record, blob field with value "empty_blob()", then update this blob field using code below.


import oracle.sql.BLOB;


            //
            // update the contents of the file into the blob.
            //
            // Note: We must use ORACLE specific classes to do this for now (not
            // supported by JDBC).
            // Select the blob for update (locking it).
            //
            File file = new File(tmpPdf);
            if (file != null && file.exists() && file.canRead()) { // Select the blob for update (locking it).
                        String query = "select blob_field from table_name where id = 3" + " for update";
                  //System.out.println("query = " + query);
                  Connection conn =
                        ((JDBCDataObjectManager) objectMgr).getConnection();
                  //Get data connection using your way.

                  Statement stmt = conn.createStatement();
                  FileInputStream fis = new FileInputStream(file);
                  InputStream in = new BufferedInputStream(fis);
                  OutputStream out = null;
                  try {
                        ResultSet result = stmt.executeQuery(query);
                        if (result.next()) { //
                              // Use ORACLE specific classes to get the blob output stream.
                              //
                              oracle.sql.BLOB blob = (oracle.sql.BLOB) result.getBlob(1);
                              out =
                                    new BufferedOutputStream(blob.getBinaryOutputStream());
                              byte[] buf = new byte[blob.getBufferSize()];
                              int len;
                              while ((len = in.read(buf)) != -1) {
                                    out.write(buf, 0, len);
                              }
                              out.flush();
                        }
                  } finally {
                        in.close();
                        if (out != null) {
                              out.close();
                        }
                        stmt.close();
                  }
            
                  file.delete();
            }
0
 
BSSForestsCommented:
Replace the query with your own,

 String query = "select blob_field from table_name where id = 3"
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
sakthikumarAuthor Commented:
Inserting from oracle plsql?
0
 
sakthikumarAuthor Commented:
normally we have a table in oracle, which has a clob column, when we are inserting the data by
writing a procedure, how do we insert a value for a clob column.
0
 
slightwv (䄆 Netminder) Commented:
You originally asked several questions in the original post.  Then the followup post didn't add much information.

Inserting LOBS from pl/sql is easy.  The online docs have many examples.

If the LOB is on the database server it's a simple dbms_lob.loadClobfrom file or dbms_lob.loadBlobfromfile

If the LOB is coming from some app and passed to a stored procedure as input, still easy.  Just declare the input parameter as a clob or blob.

For searching a blob, need $ore about the requirements.  You might be able to index them with Oracle Text and search for 'strings'. But I need to know the complete requirements.
0
 
slightwv (䄆 Netminder) Commented:
I was typing when you posted the second post:  Clob or blob? Two different requirements.

Inserting to a clob is simple:

Insert into clob_column values('hello');
 
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now