Passing CHAR literal containing more than 4000 char in function parameter [message #365253] |
Thu, 11 December 2008 04:16  |
 |
delna.sexy
Messages: 941 Registered: December 2008 Location: Surat, The Diamond City
|
Senior Member |
|
|
Hello friends,
I am having problem in passing CHAR type literal into function as an argument.
This happens because literal contains more than 4000 char.
I think, I am required to pass string as CLOB type.
But dont know to pass it as CLOB.
I tried CAST() function. but failed.
Please help in this matter.
Regards,
Delna
|
|
|
|
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365268 is a reply to message #365265] |
Thu, 11 December 2008 04:57   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
If you're passing a string in Pl/Sql then it can be up to 32767 chrs long.
In SQL, it may be a maximum of 4000 chrs.
If you to build up a CLOB to longer than these limits, then you have to append clobs to each other.
This works because no string of more than 4000 chrs exists at any point:SQL> select length(to_clob(rpad('A',4000,'B'))||to_clob(rpad('C',4000,'D'))) from dual;
LENGTH(TO_CLOB(RPAD('A',4000,'B'))||TO_CLOB(RPAD('C',4000,'D')))
----------------------------------------------------------------
8000
This fails because two strings are appended together to make a string of > 4000 chrs:SQL> select length(to_clob(rpad('A',4000,'B')||rpad('C',4000,'D'))) from dual;
select length(to_clob(rpad('A',4000,'B')||rpad('C',4000,'D'))) from dual
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
|
|
|
|
|
|
|
|
|
|
Re: Passing CHAR literal containing more than 4000 char in function parameter [message #365307 is a reply to message #365299] |
Thu, 11 December 2008 06:31   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Change your String_to_table function to accept a CLOB.
Change your Java wrapper to return a CLOB
Just replace the bit where it says 'VARCHAR2' in the parameter definitions with 'CLOB'.
You may have to do a bit of tinkering with string_to_table if you want it to handle strings of > 32K, but less than that should work transparantly.
|
|
|
|
|