Is it possible to parameterise a SQLLDR .ctl file?

[EMC]

Hi,
Does anybody know of a way of passing parameters (variables) to a SQLLDR control file?

We will be using SQLLDR to load several sets of tables, and need to load a different identifying code with each row in each table for each set of data.

The only way of achieving this seems to be to manually edit each .ctl file and set the DataSet identifier as a CONSTANT for each set of data.

Any help much appreciated.

E

 

[raygg]

Create the entire parameter file at the time you need it with sqlplus. Pass the parameters to the file you are creating. This is an example of creating a parfile in NT at the time of execution, but you could do the same thing with a sqlldr ctl file.


connect system/&&1@&&2;
set serverout on
set feedback
spool H:\dba\SCRIPTS\fulldb.par
declare
pline varchar2(1000);

BEGIN
dbms_output.enable(10000);

pline := 'userid=system/&1@&2';
dbms_output.put_line(pline);
pline := 'file=' || 'l:\dba2\backup\' || TO_CHAR(SYSDATE,'MMDDYY') || '_fulldb.DMP ';
dbms_output.put_line(pline);
pline := 'log=l:\dba2\log\' || to_char(sysdate,'MMDDYY') || '_fulldb.LOG ';

dbms_output.put_line(pline);
pline := 'compress=yes';
dbms_output.put_line(pline);
pline := 'buffer=102400';
dbms_output.put_line(pline);
pline := 'grants=yes';
dbms_output.put_line(pline);
pline := 'indexes=yes';
dbms_output.put_line(pline);
pline := 'constraints=yes';
dbms_output.put_line(pline);
pline := 'full=yes';
dbms_output.put_line(pline);
pline := 'INCTYPE=COMPLETE';
dbms_output.put_line(pline);


end;
/
spool off;
host exp.exe parfile=H:\dba\SCRIPTS\fulldb.par
This file can then be executed by a bat file in NT - or the equivalent in Unix.

hope this helps