Oracle Database Experts

Here’s what I think is an incredible piece of code to generate SQL*Loader control files for any table in the database.

cat generate_control.sql
def TAB='&1'
set head off feed off pages 0 trims on serveroutput on size 1000000 lines 2000 ver off
var v_tab VARCHAR2(100);

spool &TAB..control
declare
v_col VARCHAR2(255);

cursor c1 is
select column_name, data_type
from dba_tab_columns
where table_name=upper('&TAB');

v_counter NUMBER := 0;

BEGIN
:v_tab := '&TAB';
dbms_output.put_line('load data');
dbms_output.put_line('INFILE '||chr(39)||'data/'||:v_tab||'.txt'||chr(39)||' BADFILE '||chr(39)||:v_tab||'.bad'||chr(39)||' DISCARDFILE '||
                                chr(39)||:v_tab||'.dis'||chr(39));
dbms_output.put_line('INTO TABLE '||:v_tab);
dbms_output.put_line('APPEND');
dbms_output.put_line('FIELDS TERMINATED BY '||chr(39)||'|'||chr(39)||' trailing nullcols ');

FOR r1 in c1 LOOP

IF v_counter = 0 THEN
  IF substr(r1.data_type,0,9) = 'TIMESTAMP' THEN
    v_col := '( '||r1.column_name||' timestamp '||chr(34)||'YYYY-MM-DD HH24:MI:SS.FF'||chr(34);
    dbms_output.put_line(v_col);
  ELSE
    v_col := '( '||r1.column_name;
    dbms_output.put_line(v_col);
  END IF;
else
  IF substr(r1.data_type,0,9) = 'TIMESTAMP' THEN
    v_col := ','||r1.column_name||' timestamp '||chr(34)||'YYYY-MM-DD HH24:MI:SS.FF'||chr(34);
    dbms_output.put_line(v_col);
  ELSE
    v_col := ','||r1.column_name;
    dbms_output.put_line(v_col);
  END IF;
END IF;

-- CREATETIMESTAMP timestamp "YYYY-MM-DD HH24:MI:SS.FF",

v_counter := v_counter + 1;
END LOOP;
dbms_output.put_line(')');

END;
/

spool off
set lines 66

Posted by Charles Kim
Oracle ACE Director