Create CLOB from long string using JDBC

Question

I have the following query:

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

where some_func is a function which allows its arguments to be either VARCHAR2 or CLOB, and ? is some string, which could be really 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 an obvious reason: the VARCHAR2 size limit is 4000 characters.

I then 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 a different error:

ORA-22922: nonexistent LOB value

The last idea I tried was to create a CLOB using oracle.sql.CLOB.createTemporary() method but it failed because of 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 possibilities to do this?

Have you tried the setCharacterStream method in PreparedStatement? — JOTN, Feb 21 '11 at 15:10
Yes, please look at my previous answer (a_horse_with_no_name was a bit faster) — taurus, Feb 21 '11 at 15:30

score 13 · Accepted Answer · answered Feb 22 '11 at 08:03

13

The CLOB could be created in a simple manner:

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

Then these clobs should be freed of course.

answered Feb 22 '11 at 08:03

taurus

213
1
3
9

When to use setString instead of setCharacterStream? – Gaurav Apr 15 '19 at 12:18

score 1 · Answer 2 · answered Feb 21 '11 at 15:09

1

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 the need to create CLOB objects

answered Feb 21 '11 at 15:09

Unluckily, I get the same error as with strings: `java.sql.SQLException: ORA-01460: unimplemented or unreasonable conversion requested` – taurus Feb 21 '11 at 15:26
What datatype is the underlying column? I have no problem using this with CLOB columns. – Feb 21 '11 at 15:34
This is just a parameter for the some_func function, which is 3rd-party software, so I do know only that it accepts CLOB or varchar2 as parameter. – taurus Feb 21 '11 at 16:13
You might be getting the VARCHAR2 version of the function. Use named parameters to make sure you're getting the one you expect. – JOTN Feb 21 '11 at 17:29
I couldn't find a way to use named parameters with such a function so I tried to create a CLOB and succeeded. See my answer if you want to. – taurus Feb 22 '11 at 08:03
A named parameter works like this: `select id from table1 where some_func(parameter_name => ?) = 1;` – Feb 22 '11 at 09:44
Thank you, +1. You mentioned from your experience, ' setCharacterStream() is much more reliable than setClob()'. But can you please share more details on what you experienced or why you prefer it? – spiderman Aug 13 '14 at 19:07
@prash: some drivers don't implement `setClob()` at all (at least not 3 years ago when I wrote this. And some drivers would work correctly using `setClob()`. I don't recall the details. Maybe today's drivers are better regarding this. – Aug 13 '14 at 19:10
Thanks for revisiting and replying to my query. Ok, I understand your point. – spiderman Aug 13 '14 at 20:02
Why to use setCharacterStream instead of setString? – Gaurav Apr 15 '19 at 11:59

Create CLOB from long string using JDBC

2 Answers2

Linked