set echo off
--------------------------------------------------------------------------------
-- gen_ctl.sql
--
-- Generate part of an SQL/Loader .ctl file for a specific table in current schema
--
-- Usage: @gen_ctl table_name
--
--------------------------------------------------------------------------------
define psize=0
define lsize=80
define workfile=gen_ctl.ddl
set verify off
set doc off
set feedback off
set linesize &&lsize
set pagesize &&psize
spool &&workfile
--
define crlf=chr(10)
set heading off
select '--------------------------------&1'||&&crlf
||'unrecoverable'||&&crlf
||'load file'||&&crlf
||'infile        &1..txt'||&&crlf
||'badfile       &1..bad'||&&crlf
||'discardfile   &1..dsc'||&&crlf
||'truncate into table &1'||&&crlf
||'('
from dual
;
select column_name || ' char terminated by '''||','||''','
from user_tab_columns
where table_name = upper('&1')
;
--
spool off
set echo on
set verify on
set doc on
set feedback on
set pagesize 30
set linesize 80
--start &&workfile
undefine psize
undefine lsize
undefine workfile
undefine 1
undefine 2
-- exit