PL/SQL Array to CLOB

Question

Hi,

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.

Answer 1

Hi guigui42,

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

Answer 2

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