PL/SQL 数组到 CLOB

发布于 2024-08-21 19:49:12 字数 318 浏览 9 评论 0原文

我使用的是 Oracle 9i。

我正在将数据从游标提取到数组中：

FETCH contract_cur 
        BULK COLLECT INTO l_contract ;

但现在我想将这个 l_contract 转换为 CLOB 变量 l_clob

有没有一种简单的方法可以做到这一点？

或者，我如何将 SELECT 语句中的行转换为一个 CLOB 变量？

谢谢

编辑：我忘了提及它是一组 %ROWTYPE，而不仅仅是一列。

原文

i m using 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_contract into a CLOB variable 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 ?

thanks

EDIT : i forgot to mention its an array of %ROWTYPE, not just one column.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

黯然#的苍凉 2024-08-28 19:49:12

这是多么丑陋的事情啊。

是所有字符数据，还是其中也有数字和/或日期/时间值？如果是这样，当您将这些数据类型转换为字符串时，您希望使用什么格式。

您可能还需要考虑字段和记录分隔符。

您考虑过 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;
/

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 ?

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;
/

回复收藏 0 原文

鲜血染红嫁衣 2024-08-28 19:49:12

您可以循环遍历数组并构建 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

如果您不使用 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

you can loop through your array and build the CLOB as you go:

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_contract for 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:

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

回复收藏 0 原文

~没有更多了~