用sqlldr阿,生成控制文件:
SCRIPT TO GENERATE CONTROL FILE AND DATA FILE FOR USE WITH SQL*LOADER:
---------------------------------------------------------------------
/*
||
|| Description:
|| Given a tablename generates
|| 1. Flat ascii file with delimiters
|| 2. SQL*Load control file to load the data
||
*/
set echo off
set doc off
set pagesize 0
set feedback off
set verify off
set pause off
--
-- Set Line size to export data
--
select 'set linesize '||
sum(DECODE(data_type
,'DATE', 25
,'NUMBER', nvl(data_precision,45) + 5
, data_length + 5
)
)
from user_tab_columns
where table_name = UPPER('&&vtbl_name');
prompt set echo off
prompt set pagesize 0
prompt set space 0
prompt set feedback off
prompt set verify off
prompt set pause off
prompt set termout off
prompt spool &dataname
prompt select
select decode(column_id, 1, ' ', ',')
, column_name
, ',''&ColumnDelim.'''
from user_tab_columns
where table_name = upper('&&vtbl_name')
order by column_id
/
prompt from &&vtbl_name;;
prompt spool off
prompt set pagesize 15
prompt set feedback on
prompt set verify on
prompt set heading on
prompt set linesize 80
prompt set termout on
spool off
--
-- Create control file
--
prompt Generating control file for SQL*Load
set lines 100
spool &&vtbl_name..ctl
prompt
prompt load data
prompt replace
prompt into table &vTbl_Name
prompt (
select decode (column_id, 1, ' ', ',') ||
column_name ||
chr(9) ||
decode(data_type, 'DATE', ' DATE "&DateFormat"', ' CHAR ') ||
chr(9) ||
' terminated by ''&ColumnDelim.'' ' ||
' nullif ' ||
column_name ||
' = blanks '
from user_tab_columns
where table_name = upper('&vtbl_name')
order by column_id
/