ldctrl.sql

/**********************************************************************

      Name     : LOADCTL.sql
      Function : Generates Load Control Script for a table
      Author   : Ramesh Meda 

***********************************************************************/

/* Customise Environment */

set head        off
set pages       0
set lines       132
set echo        off
set feedback    off
set message     off
set verify      off


/* Accept Parameters  */

accept mTabName prompt 'Table Name      '
accept mOwnName prompt 'Owner Name      '
accept mInpFile prompt 'Data File Name  '


/* This is optional this is for optimising the space */

column SUNNY noprint new_value mMaxLen
select max(length(column_name)) + 2 "SUNNY"
from   all_tab_columns
where  table_name = upper('&mTabName')
and    owner      = upper('&mOwnName');
column SUNNY clear


/* Spool Control File */
spool &mTabName..ctl

/* Setup Title */

prompt load data infile '&mInpFile'
prompt into table &mOwnName..&mTabName
prompt (

select decode (column_id, 1, '  ', ' ,') ||
       rpad   (column_name, &mMaxLen, ' ') ||
       ' position ( : ) '
,      decode (data_type, 'NUMBER', 'INTEGER EXTERNAL', data_type)
,      decode (data_type,
               'CHAR',     'nullif (' || rtrim(column_name) || '=blanks)',
               'VARCHAR2', 'nullif (' || rtrim(column_name) || '=blanks)',
               'DATE',     '''YYMMDD'' nullif (' || rtrim(column_name) ||
                           '=blanks)',
               'NUMBER',   '"nvl(:' || rtrim(column_name) || ', 0) / ' ||
                                 power(10, data_scale) || '"')
from  all_tab_columns
where  table_name = upper('&mTabName')
and    owner      = upper('&mOwnName')
order  by column_id;

prompt )
spool off