Thanks to a couple of posts (namely TO_CHAR of an Oracle PL/SQL TABLE type and SQL*Plus tips. #2) I was able to develop very convenient and what’s more important generic function for converting arbitrary cursor to clob lines or say any result set to clob lines.

Such function will be very useful for unloading results of an arbitrary query to delimited flat files.

function cursor_to_flat( a_cur in sys_refcursor, a_delimiter in varchar2, a_line_break in varchar2 )

return ty_clob_tbl pipelined

as

v_prev_rn pls_integer := 0;

v_clob clob;

begin

FOR cur_val IN (

SELECT t.rn

, EXTRACTVALUE ( t2.COLUMN_VALUE, 'node()' ) VALUE

FROM ( select rownum rn, column_value from TABLE ( XMLSEQUENCE ( a_cur ) ) ) t

, TABLE (XMLSEQUENCE ( EXTRACT ( t.COLUMN_VALUE, '/ROW/node()' ) ) ) t2

order by 1

) LOOP

if ( 0 = v_prev_rn ) then

v_clob := cur_val.value;

else

if ( v_prev_rn != cur_val.rn ) then

v_clob := v_clob || a_line_break;

pipe row ( v_clob );

v_clob := cur_val.value;

else

v_clob := v_clob || a_delimiter || cur_val.value;

end if;

end if;

v_prev_rn := cur_val.rn;

END LOOP;

pipe row ( v_clob );

end;

Unit test code:

select *

from table(

pk_utils.cursor_to_flat(

cursor(

select 1, 2, 3 from dual

union all

select 4, 5, 6 from dual

)

)

);

**NB!** One problem with this approach is that if you have NULLs in your result set, then they will be completely skipped (not wrapped with delimiters). Now I’m trying to solve it.

**Edit**

The author of SQL*Plus tips. #2 was totally awesome pointing out how to handle NULLs. Resulting function takes query as clob, but one can easily rollout version with sys_refcursor, as dbms_xmlgen.newcontext will accept it.

function query_to_flat( a_query in clob, a_delimiter in varchar2, a_line_break in varchar2 )

return ty_clob_tbl pipelined

as

v_prev_rn pls_integer := 0;

v_clob clob;

v_cur sys_refcursor;

v_xml clob;

v_context dbms_xmlgen.ctxtype;

begin

v_context := dbms_xmlgen.newcontext( a_query );

dbms_xmlgen.setnullhandling( v_context, dbms_xmlgen.empty_tag );

v_xml := dbms_xmlgen.getxml( v_context );

dbms_xmlgen.closecontext( v_context );

for cur_val in (

select row_num

, col_value

from xmltable( --<<<ML

'(#ora:view_on_null empty #){

for $a at $i in /ROWSET/ROW

, $r in $a/*

return element ROW{

element ROW_NUM{$i}

, element COL_VALUE{$r/text()}

}

}'

--ML;

passing xmltype(v_xml)

columns

row_num int

, col_value varchar2(100)

)

) loop

if ( 0 = v_prev_rn ) then

v_clob := cur_val.col_value;

else

if ( v_prev_rn != cur_val.row_num ) then

v_clob := v_clob || a_line_break;

pipe row ( v_clob );

v_clob := cur_val.col_value;

else

v_clob := v_clob || a_delimiter || cur_val.col_value;

end if;

end if;

v_prev_rn := cur_val.row_num;

END LOOP;

pipe row ( v_clob );

end;

**NB!**This solution is still has some drawbacks for large dataset as all data is placed into variable, consuming PGA.

I’ll try to address this issue in the following post.

**Edit**

And here’s the followup post!