如何从Pl/SQL中的存储函数返回临时CLOB实例? [英] How to return temporary CLOB instance from stored function in Pl/SQL?

问题描述

我的存储函数使用以下方法创建临时LOB实例:Dbms_Lob.CreateTemporary(BUFFER, TRUE, Dbms_Lob.SESSION);其中，BUFFER是局部CLOB变量.之后，该函数用一些数据填充BUFFER并返回它.

在我的情况下， Dbms_Lob.CreateTemporary的持续时间参数是Dbms_Lob.SESSION，但根据解决方案

在评论中您说:

clob.getSubString(0, clob.length())引发:java.sql.SQLException: Invalid argument(s) in call at oracle.sql.CLOB.getSubString(CLOB.java:236)而clob.length() 返回我的Clob的实际长度

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?

解决方案

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.

这篇关于如何从Pl/SQL中的存储函数返回临时CLOB实例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！