Error ORA-22828 while passing clob data into oracle stored proc using JDBC

Question

I am trying to send CLOB data into a stored proc using JDBC. I am using oracle database 11g and ojdbc6.jar for driver. I am not able to send the data as it is greater than 32kb. I had tried various ways to send the data:

Using Clob object
Using characterStream

These all did not work for me. I got the following error: ORA-22828: input pattern or replacement parameters exceed 32K size limit

Is there some way to pass large data into oracle stored proc using jdbc(java), which may scale to 1MB. Code used is as follows:

CallableStatement cstmt = null;

String formedStr = "{CALL MAIL_PROC(?)}";
//Preparing statement
cstmt = con.prepareCall(formedStr);
cstmt.setCharacterStream(1, new StringReader(info.getContent()), info.getContent().length());
cstmt.execute();

CLOB can be up to 2,147,483,647 characters, given your error stating "32K size limit" - talk to your DBA & ask that the table is redesigned and the length limit is increased. Or truncate your data. — Dave
– Dave, Commented Jan 14, 2015 at 12:45
I am not at all inserting data into a table. I am just passing data to stored proc which have only one input variable of CLOB type. — john0609
– john0609, Commented Jan 14, 2015 at 12:48
@Dave 32k is the limit of a varchar in PL/SQL. I doubt that the table is the limiting factor. — Klas Lindbäck
– Klas Lindbäck, Commented Jan 14, 2015 at 12:48
Try setString() - more recent driver versions (11.x - you didn't specify your version) should handle that correctly. Another possibility is that the stored procedure somehow converts the CLOB to a VARCHAR and while doing that, the error is thrown inside the procedure, not when passing the value. — user330315
– user330315, Commented Jan 14, 2015 at 12:49

john0609 · Accepted Answer · 2015-01-14 14:08:42Z

2

There was issue with the String object that I passed in as clob. It had a next line (\r\n) in it. When I removed it, call was going well. I had used three types of calls to pass clob object and all are working fine now:

CallableStatement cstmt = null;

String formedStr = "{CALL MAIL_PROC(?)}";

cstmt = con.prepareCall(formedStr);

//Option 1:
cstmt.setCharacterStream(1, new StringReader(info.getContent()), info.getContent().length());

//Option 2:
cstmt.setString(1, info.getContent());

//Option 3:
Clob stmtClob = con.createClob();
stmtClon.setString(1,info.getContent())
cstmt.setClob(1,stmtClob);

cstmt.execute();

answered Jan 14, 2015 at 14:08

john0609

1671 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error ORA-22828 while passing clob data into oracle stored proc using JDBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related