oracle导入平面文件,oracle平面文件卸载

--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;/