oracle PL/SQL 数组到 CLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2297672/
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
PL/SQL Array to CLOB
提问by guigui42
i m using Oracle 9i.
我使用 Oracle 9i。
I m fetching data from a cursor into an array :
我正在从游标中获取数据到一个数组中：
FETCH contract_cur
BULK COLLECT INTO l_contract ;
But now i want to "convert" this l_contractinto a CLOB variable l_clob
但是现在我想将此l_contract“转换”为 CLOB 变量l_clob
Is there an easy way to do that?
有没有简单的方法来做到这一点？
Or otherwise, how do i convertthe rows from a SELECT statement into one single CLOB Variable ?
否则，如何将 SELECT 语句中的行转换为一个 CLOB 变量？
thanks
谢谢
EDIT : i forgot to mention its an array of %ROWTYPE, not just one column.
编辑：我忘了提到它的 %ROWTYPE 数组，而不仅仅是一列。
采纳答案by Gary Myers
What an ugly thing to do.
这是一件多么丑陋的事情。
Is it all character data, or do you have numeric and/or date/time values in there too ? If so what format do you want to use for those datatypes when you convert them to strings.
是所有字符数据，还是那里也有数字和/或日期/时间值？如果是这样，当您将它们转换为字符串时，您希望对这些数据类型使用什么格式。
You also may need to think about field and record delimiters.
您可能还需要考虑字段和记录分隔符。
Have you considered XML ?
你考虑过 XML 吗？
declare
v_clob clob;
v_xml xmltype;
begin
select xmlagg(XMLELEMENT("test",xmlforest(id,val)))
into v_xml
from test;
select v_xml.getclobval
into v_clob
from dual;
dbms_output.put_line(v_clob);
end;
/
回答by Vincent Malgrat
you can loop through your array and build the CLOB as you go:
您可以遍历您的数组并随时构建 CLOB：
SQL> DECLARE
2 TYPE tab_vc IS TABLE OF VARCHAR2(4000);
3 l_contract tab_vc;
4 l_clob CLOB;
5 BEGIN
6 dbms_lob.createtemporary (l_clob, TRUE);
7 SELECT to_char(dbms_random.STRING('a', 1000)) BULK COLLECT
8 INTO l_contract
9 FROM dual
10 CONNECT BY LEVEL <= 100;
11 FOR i IN 1..l_contract.count LOOP
12 dbms_lob.writeappend(l_clob,
13 length(l_contract(i)),
14 l_contract(i));
15 END LOOP;
16 -- your code here
17 dbms_lob.freetemporary(l_clob);
18 END;
19 /
PL/SQL procedure successfully completed
If you don't use l_contractfor anything else you can build the CLOB directly from the cursor loop without the array step, it will save memory and will probably be faster:
如果您不l_contract用于任何其他用途，则可以直接从游标循环构建 CLOB 而无需数组步骤，它将节省内存并且可能会更快：
SQL> DECLARE
2 l_clob CLOB;
3 BEGIN
4 dbms_lob.createtemporary (l_clob, TRUE);
5 FOR cc IN ( SELECT to_char(dbms_random.STRING('a', 1000)) txt
6 FROM dual
7 CONNECT BY LEVEL <= 100) LOOP
8 dbms_lob.writeappend(l_clob,
9 length(cc.txt),
10 cc.txt);
11 END LOOP;
12 -- your code here
13 dbms_lob.freetemporary(l_clob);
14 END;
15 /
PL/SQL procedure successfully completed
