要把数据从一个系统移到另一个系统,可以使用EXP/IMP , EXPDP/IMPDP,也可以把数据卸载到一个平面文件,即以可理解的格式卸载数据
下面tom kyte写的一个PL/SQL程序,可以将数据表以SQL LDR有好的格式卸载数据
create or replace package unloader
AUTHID CURRENT_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 sequence in it. I ALWAYS add the
OS "end of line" marker to this sequence, you should not
*/
function run( p_query in varchar2,
p_tname in varchar2,
p_mode in varchar2 default 'REPLACE',
p_dir in varchar2,
p_filename in varchar2,
p_separator in varchar2 default ',',
p_enclosure in varchar2 default '"',
p_terminator in varchar2 default '|' )
return number;
end;
/
create or replace package body unloader
as
g_theCursor integer default dbms_sql.open_cursor;
g_descTbl dbms_sql.desc_tab;
g_nl varchar2(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 return boolean
is
l_cfiles varchar2(4000);
l_dummy number;
begin
if (dbms_utility.get_parameter_value( 'control_files', l_dummy, l_cfiles )>0)
then
return instr( l_cfiles, '\' ) > 0;
else
return FALSE;
end if;
end;
procedure dump_ctl( p_dir in varchar2,
p_filename in varchar2,
p_tname in varchar2,
p_mode in varchar2,
p_separator in varchar2,
p_enclosure in varchar2,
p_terminator in varchar2 )
is
l_output utl_file.file_type;
l_sep varchar2(5);
l_str varchar2(5) := chr(10);
begin
if ( is_windows )
then
l_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.count
loop
if ( g_descTbl(i).col_type = 12 )
then
utl_file.put( l_output, l_sep || g_descTbl(i).col_name ||
' date ''ddmmyyyyhh24miss'' ');
else
utl_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 ;
end loop;
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_tname in varchar2,
p_mode in varchar2 default 'REPLACE',
p_dir in varchar2,
p_filename in varchar2,
p_separator in varchar2 default ',',
p_enclosure in varchar2 default '"',
p_terminator in varchar2 default '|' ) return number
is
l_output utl_file.file_type;
l_columnValue varchar2(4000);
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
l_line long;
l_datefmt varchar2(255);
l_descTbl dbms_sql.desc_tab;
begin
select value
into l_datefmt
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT';
/*
Set the date format to a big numeric string. Avoids
all NLS issues and saves both the time and date.
*/
execute immediate
'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);
end loop;
/*
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 );
loop
exit 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;
end loop;
l_line := l_line || p_terminator;
utl_file.put_line( l_output, l_line );
l_cnt := l_cnt+1;
end loop;
utl_file.fclose( l_output );
/*
Now reset the date format and return the number of rows
written to the output file.
*/
execute immediate
'alter session set nls_date_format=''' || l_datefmt || '''';
return l_cnt;
exception
/*
In the event of ANY error, reset the data format and
re-raise the error.
*/
when others then
execute immediate
'alter session set nls_date_format=''' || l_datefmt || '''';
RAISE;
end;
end run;
end unloader;
/
set serveroutput on
create or replace directory my_dir as '/tmp';
declare
l_rows number;
begin
l_rows := unloader.run
( p_query => 'select * from scott.emp order by empno',
p_tname => 'emp',
p_mode => 'replace',
p_dir => 'MY_DIR',
p_filename => 'emp',
p_separator => ',',
p_enclosure => '"',
p_terminator => '~' );
dbms_output.put_line( to_char(l_rows) ||
' rows extracted to ascii file' );
end;
/
记得要给你模式授权执行 utl_file
scott@ORCL> conn system/viszl@orcl as sysdba
已连接。
sys@ORCL> alter system set utl_file_dir='d:\utl' scope=spfile;
系统已更改。
sys@ORCL> GRANT EXECUTE ON utl_file TO scott;
授权成功。
执行成功后在你选择的目录里生成一个数据文件和一个控制文件按,可以直接使用sqlldr或者外部表来通过这个控制文件加载数据
生成的控制文件emp.ctl如下
load data
infile 'emp.dat' "str x'7E0D0A'"
into table emp
replace
fields terminated by X'2c' enclosed by X'22'
(
EMPNO char(44 ),
ENAME char(20 ),
JOB char(18 ),
MGR char(44 ),
HIREDATE date 'ddmmyyyyhh24miss' ,
SAL char(44 ),
COMM char(44 ),
DEPTNO char(44 )
)
生成的数据文件emp.dat如下
"7369","SMITH","CLERK","7902","17121980000000","800","","20"~
"7499","ALLEN","SALESMAN","7698","20021981000000","1600","300","30"~
"7521","WARD","SALESMAN","7698","22021981000000","1250","500","30"~
"7566","JONES","MANAGER","7839","02041981000000","2975","","20"~
"7654","MARTIN","SALESMAN","7698","28091981000000","1250","1400","30"~
"7698","BLAKE","MANAGER","7839","01051981000000","2850","","30"~
"7782","CLARK","MANAGER","7839","09061981000000","2450","","10"~
"7788","SCOTT","ANALYST","7566","19041987000000","3000","","20"~
"7839","KING","PRESIDENT","","17111981000000","5000","","10"~
"7844","TURNER","SALESMAN","7698","08091981000000","1500","0","30"~
"7876","ADAMS","CLERK","7788","23051987000000","1100","","20"~
"7900","JAMES","CLERK","7698","03121981000000","950","","30"~
"7902","FORD","ANALYST","7566","03121981000000","3000","","20"~
"7934","MILLER","CLERK","7782","23011982000000","1300","","10"~