PL/SQL: Generic function for converting arbitrary cursor to clob (delimited text). Take two

Addressing large dataset issue that was mentioned for previously developed cursor to clob conversion functions (see previous post) a new version of a function that converts any query into delimited text was born.
Not so elegant nevertheless it handles nulls (thanks to this StackOverflow topic) and large datasets (that still needs approval).
If you fill able, you can try to convert this procedure into highly optimized pipelined function making use of bulk operations, though I failed due to some bug producing ORA-24374 while working with xml and multisets (maybe I'll cover this bug in the following posts).
So by now the following procedure just inserts clob values into given table

Here are prerequisites:

/* ty_int_to_VCHAR2_row ty_int_to_VCHAR2_tbl type declarations ...*/
create or replace
type TY_INT_TO_VCHAR2_ROW force as object (
 int ingeger
 , vchar varchar2( 4000 char )
 , CONSTRUCTOR FUNCTION TY_INT_TO_VCHAR2_ROW( SELF IN OUT NOCOPY TY_INT_TO_VCHAR2_ROW, a_int in pls_integer, a_vchar in varchar2 ) 
  RETURN SELF AS RESULT
);
/

create or replace
type body TY_INT_TO_VCHAR2_ROW as

constructor function TY_INT_TO_VCHAR2_ROW ( SELF IN OUT NOCOPY TY_INT_TO_VCHAR2_ROW , a_int in pls_integer, a_vchar in varchar2 ) 
 return self as result
as
begin
 int := a_int;
 vchar := a_vchar;
   return;
end TY_INT_TO_VCHAR2_ROW ;

end;
/

create or replace type TY_INT_TO_VCHAR2_TBL force as table of TY_INT_TO_VCHAR2_ROW;
/

create global temporary table tmp_pk_utils_xml_result_set( 
  row_num integer, name_ varchar2( 30 char ), value_ varchar2( 4000 char )
) on commit preserve rows;
/

And here's the code:

procedure query_to_flat( 
  a_query in clob, a_delimiter in varchar2, a_line_break in varchar2, a_target_table in varchar2 
)
as
  v_prev_rn pls_integer := 0;
  v_clob clob;
  v_cur_desc sys_refcursor;
  v_desc_tab dbms_sql.desc_tab;
  v_count number;
  v_cur_handle NUMBER;
  
  v_cur sys_refcursor;
  
  v_column_names_tbl TY_INT_TO_VCHAR2_TBL := TY_INT_TO_VCHAR2_TBL();
  v_truncate_result pls_integer;
  v_truncate_message varchar2( 32767 char );
begin
$if ( pk_utils_cc.dbg = 1 ) $then
  dbms_output.enable( null );
$end  
  -- read column description into v_desc_tab
  open v_cur_desc for 'select * from ( ' || a_query || ' ) where 1=0';
  
  v_cur_handle := dbms_sql.to_cursor_number( v_cur_desc );
  -- we'll get all columns and their names
  dbms_sql.describe_columns( v_cur_handle, v_count, v_desc_tab );
  
  if v_cur_desc%ISOPEN then
    close v_cur_desc;
  end if;
-- move column descripition data into sql-level table type, so we can use it in queries
  v_column_names_tbl.extend( v_desc_tab.count );
  for v_column_idx in 1 .. v_desc_tab.count loop
    v_column_names_tbl( v_column_idx ) := 
      ty_int_to_vchar2_row( v_column_idx, v_desc_tab( v_column_idx ).col_name )
    ;
  end loop;
  
  open v_cur for a_query;
  
  -- just a truncate that retries if resource is busy, not necessary here, but it's an attitude
  pk_safe_ddl.safe_truncate( 'MY_SCHEMA', 'TMP_PK_UTILS_XML_RESULT_SET' );
  
  -- insert resultset into temporary table, here we'll got only not null columns
  insert into tmp_pk_utils_xml_result_set( row_num, name_, value_ )
  SELECT t.rn /*+ NO_XML_QUERY_REWRITE */
    , t2.COLUMN_VALUE.getrootelement() NAME
    , EXTRACTVALUE ( t2.COLUMN_VALUE, 'node()' ) VALUE
  FROM ( select /*+ NO_XML_QUERY_REWRITE */ rownum rn, column_value from TABLE ( XMLSEQUENCE ( v_cur ) ) ) t
    , TABLE ( XMLSEQUENCE ( EXTRACT ( t.COLUMN_VALUE, '/ROW/node()' ) ) ) t2
  ;
  -- first cross join to get all column names for every row in resulting dataset
  -- then join with temporary table that stores actual values
  for cur_val in (
    select a.row_num
      , a.name_
      , q.value_
    from ( 
      select r.row_num
        , c.int as col_idx
        , c.vchar as name_
      from ( 
        select row_num
        from tmp_pk_utils_xml_result_set
        group by row_num
      ) r
      cross join table( cast( v_column_names_tbl as TY_INT_TO_VCHAR2_TBL ) ) c
    ) a
    left outer join ( 
      select row_num
        , name_
        , value_
      from tmp_pk_utils_xml_result_set
      where name_ is not null
    ) q
      on a.row_num = q.row_num
      and a.name_ = q.name_
    order by a.row_num
      , a.col_idx
  ) loop
    -- some kind of listagg functionality but for potentially long lines (clobs)
    if ( 0 = v_prev_rn ) then
      v_clob := cur_val.value_; 
    else
      if ( v_prev_rn != cur_val.row_num ) then
        v_clob := v_clob || a_line_break;
        execute immediate 'insert into ' || a_target_table || ' values( :v_clob )'
        using v_clob
        ;
$if ( pk_utils_cc.dbg = 1 ) $then
        dbms_output.put_line( v_clob );
$end
        v_clob := cur_val.value_;
      else
        v_clob := v_clob || a_delimiter || cur_val.value_;
      end if;
    end if;
    v_prev_rn := cur_val.row_num;
  END LOOP;
  
  execute immediate 'insert into ' || a_target_table || ' values( :v_clob )'
  using v_clob
  ;
$if ( pk_utils_cc.dbg = 1 ) $then
  dbms_output.put_line( v_clob );
$end
  if v_cur%ISOPEN then
    close v_cur;
  end if;
  commit;
end;

PL/SQL: Generic function for converting arbitrary cursor to clob (delimited text)

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!