由 webmaster 发布于: 2001-02-05 11:31

下面是一个如何把指定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;