Create a .ctl file? with DDL?

[wolves]

Can I create a ctl file using the DDL? I used to have a control file generator, but no longer have access to it. Is it possible to create the ctl file with the DDL or would I have to revert to a manual ctl file generation.

Thanks in advance.

 

[LKBrwnDBA]

Specify which control file? sql loader?


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[wolves]

forgot that. yes, sql loader control file.

 

[LKBrwnDBA]

Ok, then what you really mean is DML!

Try this script:

-- Begin Script: --
-- dump_pipe.sql - M. Ortiz-Mena
-- mortiz1@beethoven.com
-- Modified from dump.sql by Jared Still
-- jkstill@cybcon.com
--
-- dump a table to a comma delimited ascii file
-- only drawback is line length is likely to be padded with
-- quite a few spaces if the 'set trimspool on' option is
-- not in your version of SQLPLUS
--
-- also builds a control file and a parameter file for SQL*LOADER
--
-- 08/28/2000 - use defined variables for quotes and commas
-- 10/18/2002 - Changed from csv to pipe delimited.


set trimspool on
set serverout on
clear buffer

-- Here you set the path of the directory where you want
-- the files created:
def tmpdir=/tmp
-- Here you define the separator character:
-- (44=comma,124=pipe'|')and the quotes:
--define comma=chr(44)
define comma=chr(124)
define squote=chr(39)
define dquote=chr(34)

var maxcol number
var linelen number
var dumpfile char(40)

col column_id noprint

set pages 0 feed off termout on echo off verify off

accept dumpowner char prompt 'Owner of table to dump: '
accept dumptable char prompt 'Table to dump: '
prompt 'Replace single quotes in the where condition with ~'
accept wherecond char prompt 'Where condition: '

set term off feed off
col cdumpowner noprint new_value dumpowner
select '&&dumpowner' cdumpowner from dual;

col cdumptable noprint new_value dumptable
select '&&dumptable' cdumptable from dual;
set term on feed off embedded on

begin
  select max(column_id) into :maxcol
    from all_tab_columns
   where table_name = rtrim(upper('&dumptable'))
     and owner = rtrim(upper('&dumpowner'));
  select sum(data_length) + ( :maxcol * 3 ) into :linelen
    from all_tab_columns
   where table_name = rtrim(upper('&dumptable'))
     and owner = rtrim(upper('&dumpowner'));
end;
/
print linelen
print maxcol
spool &tmpdir/_dump.sql

prompt set trimspool on termout off pages 0
prompt set heading off echo off ver off feed off
select 'set line ' || :linelen from dual;
select 'spool &tmpdir/' || lower('&dumptable') || '.txt' from dual;

select 'select'|| chr(10) from dual;

select
  decode(data_type,'DATE','TO_CHAR('||column_name||','
         ||&&squote ||'YYYY-MM-DD'|| &&squote ||')',
  decode(data_type,'VARCHAR2','RTRIM(','')
         ||'replace(' || column_name ||','|| &&squote ||&&comma|| &&squote
         ||','||&&squote ||'/'|| &&squote || ') ' ||
  decode(data_type,'VARCHAR2',')','')) || ' ||' ||
  decode(column_id,:maxcol, &&squote || &&comma || &&squote
                          , &&squote || &&comma || &&squote || ' || '),
  column_id
from all_tab_columns
where table_name = upper('&dumptable')
and owner = upper('&dumpowner')
order by 2
/
select 'from &dumpowner..&dumptable' from dual;
select Decode('&&wherecond',Null,'/'
             ,'Where '||replace('&&wherecond','~',chr(39))
             ||chr(10)||'/')
  from dual;

select 'spool off' from dual;

spool off

@@&tmpdir/_dump

set line 79
-- build a basic control file
spool &tmpdir/_dtmp.sql
select 'spool &tmpdir/' || lower('&dumptable') || '.par' from dual;
spool off
@@&tmpdir/_dtmp

select 'userid = ' ||upper('&dumpowner')  || chr(10) ||
       'control = ' || lower('&dumptable') || '.ctl' || chr(10) ||
       'log = ' || lower('&dumptable') || '.log' || chr(10) ||
       'bad = ' || lower('&dumptable')|| '.bad' || chr(10)
from dual;

spool &tmpdir/_dtmp.sql
select 'spool &tmpdir/' || lower('&dumptable') || '.ctl' from dual;
spool off
@@&tmpdir/_dtmp
select 'load data' || chr(10) ||
       'infile '||&&squote||lower('&dumptable')||'.txt'||&&squote||chr(10)||
       'append into table &dumpowner..&dumptable' || chr(10) ||
       'fields terminated by ' || &&squote || &&comma || &&squote ||
       ' optionally enclosed by ' || &&squote || &&dquote || &&squote  || chr(10
)
from dual;

select '(' from dual;

select '   ' || column_name ||
  decode(data_type,'DATE',' Date '||&&squote||'YYYY-MM-DD'||&&squote,'') ||
  decode(column_id,:maxcol, '', ','),
  column_id
from all_tab_columns
where table_name = upper('&dumptable')
and owner = upper('&dumpowner')
order by 2
/

select ')' from dual;

spool off
--exit

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb