Technical requirement (given by customer): Create statistic report X (comma separated values) and insert entire report into single row on table Y for later use. Report is requested once per month by dedicated single user and run as batch process.
Target column data type CLOB. Volume 500 000 rows per report.
Still i tried to implement one (not much experience on dbms_lob though). Excessive session waits exposed inefficiency on my approach. I faced heavy latch cache buffer chain or direct path temp read waits depending how i created temporary clob (experiment observations lead to this assumption).
dbms_lob.createtemporary( CsvClob, TRUE ) or dbms_lob.createtemporary( CsvClob, FALSE, 2 )
One solution is that i state requirement is not feasible.
Following over engineered code and concept has errors nor mistakes i should address differently?
create or replace
function CursorToCsvClob(
Rc in out sys_refcursor,
RowCount in out number,
IncludeHeaderRow boolean default true
) return clob
is
-- Cursor rows into single clob formatted as comma separated values (CSV)
Separator constant varchar2( 1 ) := ';';
procedure CloseCursor(
c in out number
) is
begin
if dbms_sql.is_open( c ) then
dbms_sql.close_cursor( c );
end if;
exception when others then
--logger.log( 'CloseCursor' );
raise;
end;
-- Cursor row into CSV format
procedure AddCsvDataRow(
CursorPointer number
,ColumnCount number
,DescTab in out dbms_sql.desc_tab
,CsvClob in out clob
) is
VarcharValue varchar2( 50 );
NumberValue number;
DateValue date;
ColumnValues varchar2(32767);
--
function AddQuotes( VarcharValue varchar2 )
return varchar2
is
begin
return '"' || VarcharValue || '"';
end;
-- AddCsvDataRow main
begin
for i in 1..ColumnCount loop
case DescTab(i).col_type
when 2 then
dbms_sql.column_value( CursorPointer, i, NumberValue );
ColumnValues :=
ltrim(
ColumnValues || Separator || to_char( NumberValue )
, Separator );
when 12 then
dbms_sql.column_value( CursorPointer, i, DateValue );
ColumnValues :=
ltrim( ColumnValues || Separator || AddQuotes( to_char( DateValue ) ), Separator );
else
dbms_sql.column_value( CursorPointer, i, VarcharValue );
ColumnValues :=
ltrim( ColumnValues || Separator || AddQuotes( VarcharValue ), Separator );
end case;
end loop;
ColumnValues := ColumnValues || chr( 10 );
dbms_lob.writeappend( CsvClob, length( ColumnValues ), ColumnValues );
exception when others then
--logger.log( 'AddCsvDataRow' );
raise;
end;
--
procedure DefineColsAndAddCvsHeaderRow(
CursorPointer number
,IncludeHeaderRow boolean
,ColumnCount in out number
,DescTab in out dbms_sql.desc_tab
,CsvClob in out clob
) is
-- Cursor column names to CSV header
procedure AddCsvHeaderRow(
CursorPointer number,
ColumnCount number,
DescTab in out dbms_sql.desc_tab,
CsvClob in out clob
) is
ColumnHeaders varchar2(32767);
begin
for i in 1..ColumnCount loop
ColumnHeaders := ltrim(
ColumnHeaders || Separator || '"' || DescTab(i).col_name || '"'
, Separator
);
end loop;
ColumnHeaders := ColumnHeaders || chr( 10 );
dbms_lob.writeappend( CsvClob, length( ColumnHeaders ), ColumnHeaders );
exception when others then
--logger.log( 'AddCsvHeaderRow' );
raise;
end;
-- DefineColsAndAddCvsHeaderRow main
begin
declare
VarcharColumn varchar2( 50 );
NumberColumn number;
DateColumn date;
begin
dbms_sql.describe_columns( CursorPointer, ColumnCount, DescTab );
if IncludeHeaderRow then
AddCsvHeaderRow( CursorPointer, ColumnCount, DescTab, CsvClob );
end if;
for i in 1..ColumnCount loop
case DescTab(i).col_type
when 2 then dbms_sql.define_column( CursorPointer, i, NumberColumn );
when 12 then dbms_sql.define_column( CursorPointer, i, DateColumn );
else dbms_sql.define_column( CursorPointer, i, VarcharColumn, 50 );
end case;
end loop;
exception when others then
--logger.log( 'DefineColsAndAddCvsHeaderRow' );
raise;
end;
end;
-- CursorToCsvClob main
begin
declare
CursorPointer number;
CsvClob clob;
ColumnCount number;
DescTab dbms_sql.desc_tab;
FetchCount number default 0;
begin
CursorPointer := dbms_sql.to_cursor_number( Rc );
dbms_lob.createtemporary( CsvClob, false );
DefineColsAndAddCvsHeaderRow( CursorPointer, IncludeHeaderRow, ColumnCount, DescTab, CsvClob );
loop
FetchCount := dbms_sql.fetch_rows( CursorPointer );
exit when FetchCount = 0;
AddCsvDataRow( CursorPointer, ColumnCount, DescTab, CsvClob );
end loop;
RowCount := dbms_sql.last_row_count;
CloseCursor( CursorPointer );
--
return CsvClob;
exception when others then
--logger.log( 'CursorToCsvClob' );
CloseCursor( CursorPointer );
raise;
end;
end;
/
-- test
set autoprint on
var Csv clob
var RowCount number
declare
Rc sys_refcursor;
IncludeHeaderRow boolean default true;
begin
open Rc for select dummy from dual;
:Csv := CursorToCsvClob( Rc, :RowCount, IncludeHeaderRow );
end;
/
drop function CursorToCsvClob
;
Function CURSORTOCSVCLOB compiled
PL/SQL procedure successfully completed.
CSV
------------
"DUMMY"
"X"
ROWCOUNT
-
1
Function CURSORTOCSVCLOB dropped.