******EXPortTable.sql*******
accept TableName prompt 'Table to export:'
set concat ~
prompt Data file - &TableName~.txt
prompt Control file - &TableName~.ctl
spool &TableName~.sql
start GetLoaderData &TableName
spool &TableName.txt
start &TableName
spool &TableName~.ctl
start GetLoaderControl &TableName
spool off
host del &TableName~.sql
rem host rm &TableName~.sql
set termout on
******GetLoaderControl.sql*******
--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.txt' from dual
union
select 3 ord,'truncate' from dual
union
select 4 ord,'into table &TableName' from dual
union
select 5 ord,'fields terminated by ' '''<>''' from dual
union
select 6 ord,'trailing nullcols' from dual
union
select 7 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;
******GetLoaderData.sql*********
--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 ,null , null, 'from &TableName order by 1;
'
from dual
order by ord;
|
