PL/SQL Array to CLOB

Question

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

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