Insert CLOB into Oracle Database

Question

My question is: How to get around error ORA-01704: string literal too long when pasting (or doing something in queries) using CLOB s?

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 to use it with actual values, I get ORA-01704: string literal too long back. This is pretty obvious, but how can I embed clobs (or even execute some kind of statement with clob)?

I tried looking at the question , but I do not think that I have what I am looking for. The bugs that I have are in the List<String> , and I repeat them to make a statement. My code is as 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; } 

Answer 1

You make it difficult.

Use 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 need to bother with escaping strings, no problem with the length of literals, no need to create temporary folds. And, most likely, as fast as using one INSERT ALL statement.

If you are using the current driver (> 10.2), then I think calling setCharacterStream () and creating a Reader are not needed either. A simple setString(1, query) is likely to work too.

Answer 2

You will need to use bind variables, rather than building an SQL statement using string concatenation. This will be useful from the point of view of safety, performance and reliability, as this will reduce the risk of SQL injection attacks, reduce the time spent by Oracle to conduct complex analyzes of the SQL query, and eliminate the potential, which there is a special character in the string, which leads to to the fact that an invalid SQL statement is 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(); } 

Answer 3

BLOB (binary large objects) and CLOB (large character objects) are special data types and can contain large chunks of data in the form of objects or text. Blob and Clob objects save object data in the database as a stream.

Example code snippet:

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

Answer 4

From Oracle Document

You should remember the following automatic switching of input mode for big data. There are three input modes: 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 in size.

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 that is less 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.

The following is an example of placing the contents of a file in the CLOB input 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(); } }