oracle 如何从 Pl/SQL 中的存储函数返回临时 CLOB 实例？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14586253/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return temporary CLOB instance from stored function in Pl/SQL?
提问by Volodymyr Frolov
My stored function creates temporary LOB instance using: Dbms_Lob.CreateTemporary(BUFFER, TRUE, Dbms_Lob.SESSION);where BUFFERis a local CLOBvariable. After that the function fills BUFFERwith some data and returns it.
我的存储函数使用以下方法创建临时 LOB 实例：Dbms_Lob.CreateTemporary(BUFFER, TRUE, Dbms_Lob.SESSION);whereBUFFER是局部CLOB变量。之后，该函数填充BUFFER一些数据并返回它。
Duration parameter of Dbms_Lob.CreateTemporaryin my case is Dbms_Lob.SESSION, but according to oracle documentation:
Dbms_Lob.CreateTemporary在我的情况下Dbms_Lob.SESSION，持续时间参数是，但根据oracle文档：
The duration parameterpassed 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.
传递给 dbms_lob.createtemporary()的持续时间参数是一个提示。新临时 LOB 的持续时间与 PL/SQL 中定位器变量的持续时间相同。例如，在前面的程序块中，程序变量 a 具有驻留帧的持续时间。因此，在块的末尾，a 的内存将在函数的末尾被释放。
So BUFFER CLOBmay 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.
所以BUFFER CLOB离开功能块后可能会被Oracle销毁。我可以看到，在某些情况下，当 BUFFER 超过 32K 时，我无法读取它从 Java (JDBC) 端以这种方式返回的值。
Is there any other way to return temporary CLOB instance from a function?
有没有其他方法可以从函数返回临时 CLOB 实例？
采纳答案by Alex Poole
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)whileclob.length()returns actual length of my clob
clob.getSubString(0, clob.length())throws:java.sql.SQLException: Invalid argument(s) in call at oracle.sql.CLOB.getSubString(CLOB.java:236)whileclob.length()返回我的clob的实际长度
The documentation of getSubStringstates that:
的文档getSubString指出：
pos - the first character of the substring to be extracted. The first character is at position 1.
pos - 要提取的子字符串的第一个字符。第一个字符位于位置 1。
With a simple function to generate and return a CLOB, I can retrieve it over JDBC (ojdbc5or ojdbc6) with no problems, either with getCLOB()or getString(). But if I try to assign the Oracle.sql.CLOBretrieved with getCLOBto a Stringusing
使用一个简单的函数来生成和返回 CLOB，我可以通过 JDBC（ojdbc5或ojdbc6）检索它，没有问题，无论是使用getCLOB()或getString()。但是，如果我尝试分配Oracle.sql.CLOB与检索getCLOB到String使用
String x = getSubString(0, clob.length());
then I also get the Invalid argument(s) in callerror. Just changing that to:
然后我也得到了Invalid argument(s) in call错误。只需将其更改为：
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?
作品。所以好像和函数中的临时分配，或者CLOB大小没有关系。我不明白为什么您对较小的 CLOB 没有问题——也许如果它们很小，您的逻辑就没有达到这一点？
In the meantime you've worked around this with clob.getCharacterStream().read(), so this may be a bit irrelevant now.
与此同时，您已经解决了这个问题clob.getCharacterStream().read()，所以现在这可能有点无关紧要。
回答by Alen Oblak
I created the function to return a clob, with random generated data, lenght is 200k characters.
我创建了返回一个clob的函数，随机生成的数据，长度为200k个字符。
create function f_clob
return clob is
l_clob CLOB := EMPTY_CLOB;
l_len BINARY_INTEGER;
l_content VARCHAR2(32000);
BEGIN
dbms_lob.createtemporary(l_clob, TRUE);
dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
--
for i in 1..100
loop
l_content := dbms_random.string('A', 2000);
l_len := length(l_content);
dbms_lob.writeappend(l_clob, l_len, l_content);
end loop;
dbms_lob.close(l_clob);
--
return l_clob;
end f_clob;
Then I call the function:
然后我调用函数：
select to_char(substr(f_clob, 1, 200)) clob_chunk
from (
select 1
from dual
union
select 2
from dual)
And I always get data out as a result. I wonder why your function isn't returning data.
结果我总是得到数据。我想知道为什么你的函数没有返回数据。
