如何从Pl/SQL中的存储函数返回临时CLOB实例?

问题描述

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

在我的情况下， Dbms_Lob.CreateTemporary的持续时间参数是Dbms_Lob.SESSION，但根据 Oracle文档:

因此，离开功能块后，Oracle可能会破坏BUFFER CLOB.我看到在某些情况下，当BUFFER大于32K时，我无法读取以这种方式从Java(JDBC)端返回的值.

还有其他方法可以从函数中返回临时CLOB实例吗?

解决方案

在评论中您说:

getSubString 指出:

使用一个简单的函数来生成和返回CLOB，我可以通过JDBC(ojdbc5或ojdbc6)通过getCLOB()或getString()毫无问题地检索它.但是，如果我尝试将使用getCLOB检索的Oracle.sql.CLOB分配给使用的String

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

然后我也收到Invalid argument(s) in call错误.只需将其更改为:

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

有效.因此，这似乎与函数中的临时分配或CLOB大小无关.我不明白为什么您没有遇到较小的CLOB的问题-也许如果您的逻辑很小，您的逻辑就不会实现这一目标?

与此同时，您已经使用clob.getCharacterStream().read()解决了这个问题，所以现在这可能已经无关紧要了.

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:

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:

The documentation of getSubString states that:

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实例?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！