--Flat File Unload
create or replacepackage unloader
AUTHIDCURRENT_USER
as
/*Function run -- unloads data from any query into a file
and creates a control file to reload that
data into another table
p_query = SQL query to "unload". May be virtually any query.
p_tname = Table to load into. Will be put into control file.
p_mode = REPLACE|APPEND|TRUNCATE -- how to reload the data
p_dir = directory we will write the ctl and dat file to.
p_filename = name of file to write to. I will add .ctl and .dat
to this name
p_separator = field delimiter. I default this to a comma.
p_enclosure = what each field will be wrapped in
p_terminator = end of line character. We use this so we can unload
and reload data with newlines in it. I default to
"|\n" (a pipe and a newline together) and "|\r\n" on NT.
You need only to override this if you believe your
data will have that default sequence of characters in it.
I ALWAYS add the OS "end of line" marker to this sequence, you should not*/
function run( p_query in varchar2,
p_tnamein varchar2,
p_modein varchar2 default ‘REPLACE‘,
p_dirin varchar2,
p_filenamein varchar2,
p_separatorin varchar2 default ‘,‘,
p_enclosurein varchar2 default ‘"‘,
p_terminatorin varchar2 default ‘|‘)return number;end;/
create or replacepackage body unloaderasg_theCursorinteger defaultdbms_sql.open_cursor;
g_descTbl dbms_sql.desc_tab;
g_nlvarchar2(2) default chr(10);function to_hex( p_str in varchar2 ) return varchar2
is
begin
return to_char( ascii(p_str), ‘fm0x‘);end;function is_windows returnbooleanisl_cfilesvarchar2(4000);
l_dummynumber;begin
if (dbms_utility.get_parameter_value( ‘control_files‘, l_dummy, l_cfiles )>0)then
return instr( l_cfiles, ‘\‘ ) > 0;else
returnFALSE;end if;end;procedure dump_ctl( p_dir in varchar2,
p_filenamein varchar2,
p_tnamein varchar2,
p_modein varchar2,
p_separatorin varchar2,
p_enclosurein varchar2,
p_terminatorin varchar2)isl_output utl_file.file_type;
l_sepvarchar2(5);
l_strvarchar2(5) := chr(10);begin
if( is_windows )thenl_str := chr(13) || chr(10);end if;
l_output := utl_file.fopen( p_dir, p_filename || ‘.ctl‘, ‘w‘);
utl_file.put_line( l_output,‘load data‘);
utl_file.put_line( l_output,‘infile‘‘‘ ||p_filename|| ‘.dat‘‘"str x‘‘‘ ||utl_raw.cast_to_raw( p_terminator||l_str )|| ‘‘‘"‘);
utl_file.put_line( l_output,‘into table‘ ||p_tname );
utl_file.put_line( l_output, p_mode );
utl_file.put_line( l_output,‘fields terminated by X‘‘‘ ||to_hex(p_separator)||
‘‘‘enclosed by X‘‘‘ ||to_hex(p_enclosure)|| ‘‘‘ ‘);
utl_file.put_line( l_output,‘(‘);for i in 1 .. g_descTbl.countloopif ( g_descTbl(i).col_type = 12)thenutl_file.put( l_output, l_sep|| g_descTbl(i).col_name ||
‘date‘‘ddmmyyyyhh24miss‘‘ ‘);elseutl_file.put( l_output, l_sep|| g_descTbl(i).col_name ||
‘char(‘ ||to_char(g_descTbl(i).col_max_len*2) ||‘)‘);end if;
l_sep := ‘,‘||g_nl ;endloop;
utl_file.put_line( l_output, g_nl|| ‘)‘);
utl_file.fclose( l_output );end;function quote(p_str in varchar2, p_enclosure in varchar2)return varchar2
is
begin
return p_enclosure ||
replace( p_str, p_enclosure, p_enclosure||p_enclosure ) ||p_enclosure;end;function run( p_query in varchar2,
p_tnamein varchar2,
p_modein varchar2 default ‘REPLACE‘,
p_dirin varchar2,
p_filenamein varchar2,
p_separatorin varchar2 default ‘,‘,
p_enclosurein varchar2 default ‘"‘,
p_terminatorin varchar2 default ‘|‘ ) return number
isl_output utl_file.file_type;
l_columnValuevarchar2(4000);
l_colCntnumber default 0;
l_separatorvarchar2(10) default ‘‘;
l_cntnumber default 0;
l_linelong;
l_datefmtvarchar2(255);
l_descTbl dbms_sql.desc_tab;begin
selectvalueintol_datefmtfromnls_session_parameterswhere parameter = ‘NLS_DATE_FORMAT‘;/*Set the date format to a big numeric string. Avoids
all NLS issues and saves both the time and date.*/
executeimmediate‘alter session set nls_date_format=‘‘ddmmyyyyhh24miss‘‘ ‘;/*Set up an exception block so that in the event of any
error, we can at least reset the date format.*/
begin
/*Parse and describe the query. We reset the
descTbl to an empty table so .count on it
will be reliable.*/dbms_sql.parse( g_theCursor, p_query, dbms_sql.native );
g_descTbl :=l_descTbl;
dbms_sql.describe_columns( g_theCursor, l_colCnt, g_descTbl );/*Create a control file to reload this data
into the desired table.*/dump_ctl( p_dir, p_filename, p_tname, p_mode, p_separator,
p_enclosure, p_terminator );/*Bind every single column to a varchar2(4000). We don‘t care
if we are fetching a number or a date or whatever.
Everything can be a string.*/
for i in 1.. l_colCnt loop
dbms_sql.define_column( g_theCursor, i, l_columnValue,4000);endloop;/*Run the query - ignore the output of execute. It is only
valid when the DML is an insert/update or delete.*/l_cnt := dbms_sql.execute(g_theCursor);/*Open the file to write output to and then write the
delimited data to it.*/l_output := utl_file.fopen( p_dir, p_filename || ‘.dat‘, ‘w‘,32760);
loopexit when ( dbms_sql.fetch_rows(g_theCursor) <= 0);
l_separator := ‘‘;
l_line := null;for i in 1.. l_colCnt loop
dbms_sql.column_value( g_theCursor, i,
l_columnValue );
l_line := l_line || l_separator ||quote( l_columnValue, p_enclosure );
l_separator :=p_separator;endloop;
l_line := l_line ||p_terminator;
utl_file.put_line( l_output, l_line );
l_cnt := l_cnt+1;endloop;
utl_file.fclose( l_output );/*Now reset the date format and return the number of rows
written to the output file.*/
executeimmediate‘alter session set nls_date_format=‘‘‘ || l_datefmt || ‘‘‘‘;returnl_cnt;
exception/*In the event of ANY error, reset the date format and
re-raise the error.*/
when others then
executeimmediate‘alter session set nls_date_format=‘‘‘ || l_datefmt || ‘‘‘‘;
RAISE;end;endrun;endunloader;/