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;
