Sending a very long string to PL/SQL via bind Pro*C (bind variable) in 10.2
562018Feb 26 2007 — edited Feb 26 2007I 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