Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Sending a very long string to PL/SQL via bind Pro*C (bind variable) in 10.2

562018Feb 26 2007 — edited Feb 26 2007
I have to call a stored procedure with a Very Long String (100,000 characters) as an input parameter, from pro*c.

So I do

EXEC SQL EXECUTE SQL_STATEMENT USING
:b1, :b2, :b3;

where b1 is a char * that points to this long string.

My procedure accepts parameters like

myprocedure( longString IN OUT CLOB, outNumber OUT NUMBER,
outString1 OUT VARCHAR2, outString2 OUT VARCHAR2)

Now, if longString is say 400 characters, there is no problem.
But if longString is 100,000 characters, I get the error -
ORA-01460: unimplemented or unreasonable conversion requested

So I gather that the problem is - longString, which is a char array in C, gets converted to CLOB if it's 400 characters but if it's 100,000 characters, it doesn't automatically convert it inot CLOB.
How can I make this procedure accept this long string?
One way to do this would be to pass 30,000 characters at a time and keep appending them, storing them in a clob package variable, and when done, start the processing - but this involves multiple trips to the database.
Is there any other way? In other words, I want the sp to "think" that it's getting a CLOB, not a varchar2
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 26 2007
Added on Feb 26 2007
1 comment
642 views