|
摘自：不详
Exporting Table Data to SQL*Loader Files
下面是一个如何把指定table里的数据通过写好的script来导到一个SQL*LOADERL里的方法。包括3个脚本：
ExportTable.sql
GetLoaderControl.sql
GetLoaderData.sql (can be joined)
通过这些脚本把指定的table数据按照ascii存成CSV文件格式(可在MS Excel里读取)。同时也产生一个控制文件，在SQL*Loader导入CSV文件时用到。
先用文本编辑器把下面的3个脚本存成3个.sql文件，然后
SQL>@ExportTable
下面是这3个文件
--文件1:ExportTable.sql
--执行者:用户
--目的: Export table data to SQLLoader files
accept TableName prompt 'Table to export:'
set concat ~
prompt Data file - &TableName~.csv
prompt Control file - &TableName~.ctl
spool &TableName~.sql
start GetLoaderData &TableName
spool &TableName.csv
start &TableName
spool &TableName~.ctl
start GetLoaderControl &TableName
spool off
host del &TableName~.sql
rem host rm &TableName~.sql
set termout on
--------------------------------------------------------------------------------------------------------------
--文件2:GetLoaderControl.sql
--执行者: ExportTable.sql
--目的: Create SQLLoader control file
set pause off
set newpage none
set heading off
set concat ~
set feedback off
set verify off
set linesize 80
set trimspool on
set trimout off
set termout off
column ord noprint
select 1 ord,'load data' from dual
union
select 2 ord,'infile &&TableName.csv' from dual
union
select 3 ord,'append' from dual
union
select 4 ord,'into table &TableName' from dual
union
select 5 ord,'fields terminated by ' || ''';''' from dual
union
select 6 ord,'(' from dual
union
select 10*column_id ord, column_name || ' ' ||
decode(data_type,
'NUMBER','decimal external',
'VARCHAR2','char',
'CHAR','char',
'DATE','date') || ','
from user_tab_columns
where table_name = upper('&TableName')
and column_id not in ( select max(column_id)
from user_tab_columns
where table_name = upper('&TableName') )
union
select 1000*column_id ord,column_name || ' ' ||
decode(data_type,
'NUMBER','decimal external',
'VARCHAR2','char',
'CHAR','char',
'DATE','date') || ')'
from user_tab_columns
where table_name=upper('&TableName')
and column_id in ( select max(column_id)
from user_tab_columns
where table_name = upper('&TableName') )
order by ord;
--------------------------------------------------------------------------------------------------------------
--文件3:GetLoaderData.sql
--执行者: ExportTable.sql
--目的: Create script extracting data from table
set pause off
set newpage none
set heading off
set concat ~
set feedback off
set verify off
set linesize 1000
set trimspool on
set trimout on
set termout off
column ord noprint
select 0 ord, 'select',null,null,'rtrim('||column_name ||')'
from user_tab_columns
where table_name = upper('&&TableName')
and column_id=1
union
select column_id ord, '||' , ''';''' , '||' ,'rtrim('||column_name ||')'
from user_tab_columns
where table_name = upper('&TableName')
and column_id > 1
union
select 1000 ord, '||' , ''';''' , null, 'from &TableName order by 1;'
from dual
order by ord;
|
