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?
sakthikumarAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor 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:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
BSSForestsCommented:
Replace the query with your own,

 String query = "select blob_field from table_name where id = 3"
0
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.