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

Insert them from what tool/language/???

You will need to convert the BLOB back into a clob to check for strings/characters.

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();
            }

Replace the query with your own,

 String query = "select blob_field from table_name where id = 3"

Inserting from oracle plsql?

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.

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');