How to return temporary CLOB instance from stored function in Pl/SQL?

Question

My stored function creates temporary LOB instance using: Dbms_Lob.CreateTemporary(BUFFER, TRUE, Dbms_Lob.SESSION); where BUFFER is a local CLOB variable. After that the function fills BUFFER with some data and returns it.

Duration parameter of Dbms_Lob.CreateTemporary in my case is Dbms_Lob.SESSION, but according to oracle documentation:

The duration parameter passed to dbms_lob.createtemporary() is a hint. The duration of the new temp LOB is the same as the duration of the locator variable in PL/SQL. For example, in the preceding program block, the program variable a has the duration of the residing frame. Therefore at the end of the block, memory of a will be freed at the end of the function.

So BUFFER CLOB may be destroyed by Oracle after leaving the function block. I can see that in some cases, when the BUFFER is more than 32K, I can’t read it’s value returned this way from Java (JDBC) side.

Is there any other way to return temporary CLOB instance from a function?

Answer 1

In a comment you said:

clob.getSubString(0, clob.length()) throws: java.sql.SQLException: Invalid argument(s) in call at oracle.sql.CLOB.getSubString(CLOB.java:236) while clob.length() returns actual length of my clob

The documentation of getSubString states that:

pos - the first character of the substring to be extracted. The first character is at position 1.

With a simple function to generate and return a CLOB, I can retrieve it over JDBC (ojdbc5 or ojdbc6) with no problems, either with getCLOB() or getString(). But if I try to assign the Oracle.sql.CLOB retrieved with getCLOB to a String using

String x = getSubString(0, clob.length());

then I also get the Invalid argument(s) in call error. Just changing that to:

String x = getSubString(1, clob.length());

works. So it seems to have nothing to do with the temporary allocation in the function, or the CLOB size. I don't understand why you didn't have a problem with smaller CLOBs - maybe your logic just didn't hit this if they were small?

In the meantime you've worked around this with clob.getCharacterStream().read(), so this may be a bit irrelevant now.