Insert CLOB into Oracle database

Insert CLOB into Oracle database

insert clob data oracle sql developer
oracle insert clob more than 4000
how to insert clob data in oracle using java
how to insert xml data in clob column in oracle
update clob data in oracle
how to import clob data in oracle
how to insert clob data in teradata
oracle insert select clob

My question is: How do you get around the ORA-01704: string literal too long error when inserting (or doing anything in queries) with CLOBs?

I want to have a query like this:

INSERT ALL
   INTO mytable VALUES ('clob1')
   INTO mytable VALUES ('clob2') --some of these clobs are more than 4000 characters...
   INTO mytable VALUES ('clob3')
SELECT * FROM dual;

When I try it with actual values though I get ORA-01704: string literal too long back. This is pretty obvious, but how do I insert clobs (or execute any statement at all with a clob)?

I've tried looking at this question, but I don't think it has what I'm looking for. The clobs I have are in a List<String> and I iterate through them to make the statement. My code as it is follows:

private void insertQueries(String tempTableName) throws FileNotFoundException, DataException, SQLException, IOException {
String preQuery = "  into " + tempTableName + " values ('";
String postQuery = "')" + StringHelper.newline;
StringBuilder inserts = new StringBuilder("insert all" + StringHelper.newline);
List<String> readQueries = getDomoQueries();
for (String query : readQueries) {
  inserts.append(preQuery).append(query).append(postQuery);
}
inserts.append("select * from dual;");

DatabaseController.getInstance().executeQuery(databaseConnectionURL, inserts.toString());

}

public ResultSet executeQuery(String connection, String query) throws DataException, SQLException {
  Connection conn = ConnectionPool.getInstance().get(connection);
  Statement stmt = conn.createStatement();
  ResultSet rs = stmt.executeQuery(query);
  conn.commit();
  ConnectionPool.getInstance().release(conn);
  return rs;
}

You are making it way to complicated.

Use a PreparedStatement and addBatch() for each clob in your list:

String sql = "insert  into " + tempTableName + " values (?)";
PreparedStatement stmt = connection.prepareStatement(sql);
for (String query : readQueries) {
  stmt.setCharacterStream(1, new StringReader(query), query.lenght());
  stmt.addBatch();
}
stmt.exececuteBatch();

No messing around with escaping strings, no problem with the length of the literals, no need to create temporary clobs. And most probably just as fast as using a single INSERT ALL statement.

If you are using a current driver (> 10.2) then I think the setCharacterStream() call and the creation of the Reader is not necessary either. A simple setString(1, query) will most probably work as well.

Inserting a CLOB data - Ask Tom, c1 clob;. begin. c1 := {your text goes here};. insert into test_clob(t_col_clob) values(anon.c1);. commit;. end;. /. You may have to do something  Insert CLOB into Oracle database. 6. Java: How to insert JSON object to CLOB into oracle database. 0. ORA-01461 when inserting to a CLOB type from java String. 2.


You'll need to use bind variables rather than building a SQL statement using string concatenation. This will be beneficial from a security, performance, and robustness standpoint as well since it will reduce the risk of SQL injection attacks, decrease the amount of time Oracle has to spend doing hard parses of the SQL statement, and will eliminate the potential that there is a special character in the string that causes an invalid SQL statement to get generated (i.e. a single quote).

I would expect that you want something like

private void insertQueries(String tempTableName) throws FileNotFoundException, DataException, SQLException, IOException {
  String preQuery = "  into " + tempTableName + " values (?)" + StringHelper.newline;
  StringBuilder inserts = new StringBuilder("insert all" + StringHelper.newline);
  List<String> readQueries = getDomoQueries();
  for (String query : readQueries) {
    inserts.append(preQuery);
  }
  inserts.append("select * from dual");

  Connection conn = ConnectionPool.getInstance().get(connection);
  PreparedStatement pstmt = conn.prepareStatement(
        inserts);
  int i = 1;
  for (String query : readQueries) {
    Clob clob = CLOB.createTemporary(conn, false, oracle.sql.CLOB.DURATION_SESSION);
    clob.setString(i, query);
    pstmt.setClob(i, clob);
    i = i + 1;
  }
  pstmt.executeUpdate();
}

Insert data in clob, Database SQL Language Reference TO_CLOB converts NCLOB values in a LOB column or other character strings to CLOB values. char can be any of the  I just want to learn insert string to db as clob data type. – csjoseph May 17 '13 at 10:59 add a comment | 2 Answers 2


BLOB (Binary Large Objects ) and CLOB(Character large objects) are special datatypes and can hold the large chunks of data in form of objects or text. Blob and Clob objects persist the data of the objects into the database as a stream.

An example piece of code:

public class TestDB { 
    public static void main(String[] args) { 
        try { 
            /** Loading the driver */ 
            Class.forName("com.oracle.jdbc.Driver"); 

            /** Getting Connection */ 
            Connection con = DriverManager.getConnection("Driver URL","test","test"); 

            PreparedStatement pstmt = con.prepareStatement("insert into Emp(id,name,description)values(?,?,?)"); 
            pstmt.setInt(1,5); 
            pstmt.setString(2,"Das"); 

            // Create a big CLOB value...AND inserting as a CLOB 
            StringBuffer sb = new StringBuffer(400000); 

            sb.append("This is the Example of CLOB .."); 
            String clobValue = sb.toString(); 

            pstmt.setString(3, clobValue); 
            int i = pstmt.executeUpdate(); 
            System.out.println("Done Inserted"); 
            pstmt.close(); 
            con.close(); 

            // Retrive CLOB values 
            Connection con = DriverManager.getConnection("Driver URL","test","test"); 
            PreparedStatement pstmt = con.prepareStatement("select * from Emp where id=5"); 
            ResultSet rs = pstmt.executeQuery(); 
            Reader instream = null; 

            int chunkSize; 
            if (rs.next()) { 
                String name = rs.getString("name"); 
                java.sql.Clob clob = result.getClob("description") 
                StringBuffer sb1 = new StringBuffer(); 

                chunkSize = ((oracle.sql.CLOB)clob).getChunkSize(); 
                instream = clob.getCharacterStream(); 
                BufferedReader in = new BufferedReader(instream); 
                String line = null; 
                while ((line = in.readLine()) != null) { 
                    sb1.append(line); 
                } 

                if (in != null) { 
                    in.close(); 
                } 

                // this is the clob data converted into string
                String clobdata = sb1.toString();  
            } 
        } catch (Exception e) { 
            e.printStackTrace(); 
        } 
    } 
} 

TO_CLOB, Keep in mind that SQL strings can not be larger than 4000 bytes, while Pl/SQL can have strings as large as 32767 bytes. see below for an  2.If we insert into a clob using a bind variable( in sqlplus or through a procedure) then the max we can insert is 32000. tkyte@TKYTE816> exec p(1, rpad('*',32000,'*') ); 3.If we want to insert into a clob more than 32000( i.e. more than the max length of a varchar2 pl/sql variable) then we have to use dbms_lob or dbms_lob.writeappend. 1 create


From Oracle document

You must bear in mind the following automatic switching of the input mode for large data. There are three input modes as follows: Direct binding, Stream binding, and LOB binding.

For PL/SQL statements

The setBytes and setBinary stream methods use direct binding for data less than 32767 bytes.

The setBytes and setBinaryStream methods use LOB binding for data larger than 32766 bytes.

The setString, setCharacterStream, and setAsciiStream methods use direct binding for data smaller than 32767 bytes in the database character set.

The setString, setCharacterStream, and setAsciiStream methods use LOB binding for data larger than 32766 bytes in the database character set.

The setBytesForBlob and setStringForClob methods, present in the oracle.jdbc.OraclePreparedStatement interface, use LOB binding for any data size.

Follow is a example for put a file content into a input CLOB parameter of a PLSQL procedure:

  public int fileToClob( FileItem uploadFileItem ) throws SQLException, IOException
  {
    //for using stmt.setStringForClob method, turn the file to a big String 
    FileItem item = uploadFileItem;
    InputStream inputStream = item.getInputStream();
    InputStreamReader inputStreamReader = new InputStreamReader( inputStream ); 
    BufferedReader bufferedReader = new BufferedReader( inputStreamReader );    
    StringBuffer stringBuffer = new StringBuffer();
    String line = null;

    while((line = bufferedReader.readLine()) != null) {  //Read till end
        stringBuffer.append(line);
        stringBuffer.append("\n");
    }

    String fileString = stringBuffer.toString();

    bufferedReader.close();         
    inputStreamReader.close();
    inputStream.close();
    item.delete();

    OracleCallableStatement stmt;

    String strFunction = "{ call p_file_to_clob( p_in_clob => ? )}";  

    stmt= (OracleCallableStatement)conn.prepareCall(strFunction);    

    try{    
      SasUtility servletUtility = sas.SasUtility.getInstance();

      stmt.setStringForClob(1, fileString );

      stmt.execute();

    } finally {      
      stmt.close();
    }
  }

How to write oracle insert script with one field as CLOB?, How do I import CLOB data into Oracle SQL Developer? My table has one clob column and one blob column. I need to insert data into it through insert statements. The length of data to be inserted is more than 4000 chars. When I do export of insert statements is sql navigator I get empty values. INSERT INTO mytable VALUES('REQUEST',EMPTY_CLOB(),EMPTY_BLOB());


Me, I like to use the classes from java.sql.* package, not oracle.* stuff. For me the simple approach

Connection con = ...;
try (PreparedStatement pst = con.prepareStatement(
     "insert into tbl (other_fld, clob_fld) values (?,?)", new String[]{"tbl_id"});
     ) {
        Clob clob = con.createClob();
        readIntoClob(clob, inputStream);
        pst.setString(1, "other");
        pst.setClob(2, clob);
        pst.executeUpdate();
        try (ResultSet rst = pst.getGeneratedKeys()) {
            if (rst == null || !rst.next()) {
                throw new Exception("error with getting auto-generated key");
            }
            id = rst.getBigDecimal(1);
        }  

stopped working when testing (current tomcat, jdbc) moved into production (stuck in Tomcat6 for stupid reasons). con.createClob() returns null for reasons unknown in that version, so I had to do this double-take (It took me ages to figure out so I'm sharing here...)

try (PreparedStatement pst = con.prepareStatement(
         "insert into tbl (other_fld) values (?)", new String[]{"tbl_id"});
     PreparedStatement getClob= con.prepareStatement(
         "select clob_fld from tbl where tbl_id = ? for update");
     ) {
        Clob clob = con.createClob();
        readIntoClob(clob, inputStream);
        pst.setString(1, "other");
        pst.executeUpdate();
        try (ResultSet rst = pst.getGeneratedKeys()) {
            if (rst == null || !rst.next()) {
                throw new Exception("error with getting auto-generated key");
            }
            id = rst.getBigDecimal(1);
        }  

        //  fetch back fresh record, with the Clob
        getClob.setBigDecimal(1, id);
        getClob.execute();
        try (ResultSet rst = getClob.getResultSet()) {
            if (rst == null || !rst.next()) {
                throw new Exception("error with fetching back clob");
            }
            Clob c = rst.getClob(1);
            // Fill in data
            readIntoClob(c, stream);
            // that's all 
        }

    } catch (SQLException) {
       ...
    }

for completeness here's

// Read data from an input stream and insert it in to the clob column
private static void readIntoClob(Clob clob, InputStream stream) {
    try (BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(stream))) {
        char[] buffer = new char[CHUNK_BUFFER_SIZE];
        int charsRead;
        try (Writer wr = clob.setCharacterStream(1L)) {
            // Loop for reading of chunk of data and then write into the clob.
            while ((charsRead = bufferedReader.read(buffer)) != -1) {
                wr.write(buffer, 0, charsRead);
            }
        } catch (SQLException | IOException ex) {
            ...
        }

    }
}

which is from elsewhere on SO, thanks.

CLOB (Character Large Object) Definition, data may be very large, some database management systems do not store the text directly in the table. If you are not using SQL Loader, you *will* need a directory object, eg, Here's a code snippet for you - see how this goes SQL> declare 2 l_blob blob; 3 l_clob clob; 4 l_bfile bfile; 5 begin 6 insert into demo values ( 1, empty_blob(), empty_clob() ) 7 returning theBlob, theClob into l_blob, l_clob; 8 9 l_bfile := bfilename( 'MY_FILES', 'asktom.rtf' ); 10 dbms_lob.fileopen( l_bfile ); 11 dbms


Oracle for Absolute Beginners: Data Types, VARCHAR2 can hold look as tiny as a two year old girl crying in the rain. There are 2 steps for inserting data to clob field, 1. Insert the record 2. Open the same row with FOR UPDATE clause and execute the above line of code The problem is while inserting record don't ignore CLOB field, instead use EMPTY_CLOB() in Insert statement as value for CLOB field.


Working with BLOB and CLOB Data with dotConnect for Oracle, stores single-byte and multibyte character data. Both fixed-width and variable-width character sets are supported, and both use the database character set. I am trying to insert a xml that is extracted from another Clob column into clob column of a temporary table. The Length of the extracted xml is around 8000. i also saw your post related to this


Inserting data into a table with one value as clob and blob , In SQL, the limit is 4,000 characters. Using straight SQL like that, without a bind variable, you'll be limited to 4,000 characters. Steps to insert lob  I am copying XML data from Operating System file into CLOB and then casting as XMLTYPE for extracting information into relational database. This solution is working for years but I received a business requirement to process big XML files ranging from 80-500MB.