Create CLOB from a long string using JDBC

Question

I have the following query:

select id from table1 where some_func(?) = 1; 

where some_func is a function that allows its arguments to be VARCHAR2 or CLOB, and ? this is some kind of string that can be very long.

I am trying to use the following code to bind variables:

 stmt.setObject(i+1, obj); 

but in case of string.length() > 4000 I get the following error:

 java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested 

for obvious reason: the VARCHAR2 size limit is 4000 characters.

Then I tried to use the following code:

 if(obj instanceof String && ((String) obj).length() >= 4000) { String s = (String) obj; StringReader stringReader = new StringReader(s); stmt.setClob(i+1, stringReader, s.length()); } else { stmt.setObject(i+1, obj); } 

which gave another error:

 ORA-22922: nonexistent LOB value 

The last idea I tried was to create a CLOB using oracle.sql.CLOB.createTemporary() but this failed due to the following exception:

 java.lang.ClassCastException: org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection 

What am I doing wrong? Are there any other options to do this?

Answer 1

CLOB can be created in a simple way:

 if(obj instanceof String && ((String) obj).length() >= 4000) { Clob clob = connection.createClob(); clob.setString(1, (String) obj); stmt.setClob(i+1, clob); } 

Then these goats should be freed, of course.

Answer 2

From my experience, setCharacterStream () is much more reliable than setClob ()

  String s = (String) obj;
 StringReader stringReader = new StringReader (s);
 stmt.setCharacterStream (i + 1, stringReader, s.length ());

and it works without having to create CLOB objects

Answer 3

Have you tried the setCharacterStream method in PreparedStatement?