vemul
asked on
How to pass variables into SQL loader control file?
Hello,
I have a control file test.ctl like this:
LOAD DATA
INSERT NTO TABLE TEST
FIELDS TERMINATED BY '|'
(
COL1,
COL2,
COL3 "1234"
)
and I load the data by
sqloader control = test.ctl data=test.data ...
I have to keep changing the value for COL3 based on the requirements, so I would like to pass this value from outside instead of always modifying the control file. Is that possible?
Thanks,
vemul
I have a control file test.ctl like this:
LOAD DATA
INSERT NTO TABLE TEST
FIELDS TERMINATED BY '|'
(
COL1,
COL2,
COL3 "1234"
)
and I load the data by
sqloader control = test.ctl data=test.data ...
I have to keep changing the value for COL3 based on the requirements, so I would like to pass this value from outside instead of always modifying the control file. Is that possible?
Thanks,
vemul
No, not directly, since SQL*loader and the control files for SQL*Loader do not accept parameters.
Now that is likely not the answer you were hoping for, but there are some alternatives.
One option is to fill this third column with a pre-insert trigger on the table instead of filling it via SQL*Loader. Then you could adjust the trigger when necessary and not have to change the control file. This may not be a huge improvement, since it still requires a DDL statement.
A variation of this would involve writing a PL\SQL function that the pre-insert trigger could call to get the value. This function could select a value from a table, so you only have to add or modify a record in a control table before each run.
Now that is likely not the answer you were hoping for, but there are some alternatives.
One option is to fill this third column with a pre-insert trigger on the table instead of filling it via SQL*Loader. Then you could adjust the trigger when necessary and not have to change the control file. This may not be a huge improvement, since it still requires a DDL statement.
A variation of this would involve writing a PL\SQL function that the pre-insert trigger could call to get the value. This function could select a value from a table, so you only have to add or modify a record in a control table before each run.
ASKER
Hi riazpk and markgeer,
Thank you for your comments. The problem is that I do not have permissions to modify the DDL. Also, I have to keep changing the value every week. So I don't see any benefit out of this. I was wondering if there was any way one could simply pass variables as command line parameters (like the one we can do with sqlplus. I can't understand why they wouldn't allow the same functionality with sqlloader also). I have searched everywhere and couldnt find any solution.
Thanks,
vemul
Thank you for your comments. The problem is that I do not have permissions to modify the DDL. Also, I have to keep changing the value every week. So I don't see any benefit out of this. I was wondering if there was any way one could simply pass variables as command line parameters (like the one we can do with sqlplus. I can't understand why they wouldn't allow the same functionality with sqlloader also). I have searched everywhere and couldnt find any solution.
Thanks,
vemul
It may be possible to call a PL\SQL function in the control file, something like the syntax that riazpk suggested with a procedure. I am quite sure that a procedure will not work that way, but a function may (I haven't tried). If so, this would give you a way to supply a different value with DDL changes each time. You would just need a one-time DDL command to create the function and a table that the function could select the value from.
Hi,
write a batch file and pass this parameter to batch file. Batch file should first create sqlloader control file (test.ctl) with desired parameter and then start the sqlloader.
this is an example of ms-dos batch (unix shell script will be similar)
loadtest.bat
------------------------
Set myctl=test.ctl
echo LOAD DATA > %myctl%
echo INSERT NTO TABLE TEST >> %myctl%
echo FIELDS TERMINATED BY '|' >> %myctl%
echo ( >> %myctl%
echo COL1, >> %myctl%
echo COL2, >> %myctl%
echo COL3 "%1" >> %myctl%
echo ) >> %myctl%
rem ** call sqlloader **
sqloader control = test.ctl data=test.data ...
you can execute your batch file like below;
loadtest.bat 1234
regards.
write a batch file and pass this parameter to batch file. Batch file should first create sqlloader control file (test.ctl) with desired parameter and then start the sqlloader.
this is an example of ms-dos batch (unix shell script will be similar)
loadtest.bat
------------------------
Set myctl=test.ctl
echo LOAD DATA > %myctl%
echo INSERT NTO TABLE TEST >> %myctl%
echo FIELDS TERMINATED BY '|' >> %myctl%
echo ( >> %myctl%
echo COL1, >> %myctl%
echo COL2, >> %myctl%
echo COL3 "%1" >> %myctl%
echo ) >> %myctl%
rem ** call sqlloader **
sqloader control = test.ctl data=test.data ...
you can execute your batch file like below;
loadtest.bat 1234
regards.
@vemul : You should able to do...
$sqlldr userid=scott/tiger data=test.dat \
control=`sed 's/<Variable_name>/<New_va le>/g' test_tempate.ctl > test.ctl ; echo test.ctl `
Check-out below for demo ...
SQL> desc test
Name Null? Type
----------------- -------- ------------
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER
SQL>
SQL> select count(*) from test;
COUNT(*)
----------
0
SQL>!
$ cat test.dat
100|555
200|999
300|444
$ cat test_template.ctl
LOAD DATA
APPEND INTO TABLE TEST
FIELDS TERMINATED BY '|' trailing NULLCOLS
(
COL1,
COL2,
COL3 "COL3_VALUE"
)
$
$ sqlldr userid=scott/tiger \
> data=test.dat \
> control=`sed 's/COL3_VALUE/1234/g' test_template.ctl > /tmp/test.ctl; echo "/tmp/test.ctl"`
SQL*Loader: Release 9.2.0.1.0 - Production on Fri Apr 16 7:01:34 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
$ cat /tmp/test.ctl
LOAD DATA
APPEND INTO TABLE TEST
FIELDS TERMINATED BY '|' trailing NULLCOLS
(
COL1,
COL2,
COL3 "1234"
)
$
SQL> select * from test;
COL1 COL2 COL3
---------- ---------- ----------
100 555 1234
200 999 1234
300 444 1234
SQL>
$ sed 's/COL3_VALUE/1234/g' test_template.ctl
LOAD DATA
APPEND INTO TABLE TEST
FIELDS TERMINATED BY '|' trailing NULLCOLS
(
COL1,
COL2,
COL3 "1234"
)
$
-R
$sqlldr userid=scott/tiger data=test.dat \
control=`sed 's/<Variable_name>/<New_va
Check-out below for demo ...
SQL> desc test
Name Null? Type
----------------- -------- ------------
COL1 NUMBER
COL2 NUMBER
COL3 NUMBER
SQL>
SQL> select count(*) from test;
COUNT(*)
----------
0
SQL>!
$ cat test.dat
100|555
200|999
300|444
$ cat test_template.ctl
LOAD DATA
APPEND INTO TABLE TEST
FIELDS TERMINATED BY '|' trailing NULLCOLS
(
COL1,
COL2,
COL3 "COL3_VALUE"
)
$
$ sqlldr userid=scott/tiger \
> data=test.dat \
> control=`sed 's/COL3_VALUE/1234/g' test_template.ctl > /tmp/test.ctl; echo "/tmp/test.ctl"`
SQL*Loader: Release 9.2.0.1.0 - Production on Fri Apr 16 7:01:34 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Commit point reached - logical record count 3
$ cat /tmp/test.ctl
LOAD DATA
APPEND INTO TABLE TEST
FIELDS TERMINATED BY '|' trailing NULLCOLS
(
COL1,
COL2,
COL3 "1234"
)
$
SQL> select * from test;
COL1 COL2 COL3
---------- ---------- ----------
100 555 1234
200 999 1234
300 444 1234
SQL>
$ sed 's/COL3_VALUE/1234/g' test_template.ctl
LOAD DATA
APPEND INTO TABLE TEST
FIELDS TERMINATED BY '|' trailing NULLCOLS
(
COL1,
COL2,
COL3 "1234"
)
$
-R
ASKER
Hi rajnadimpalli,
Your suggestion is something what I was really looking for. Let me try it out over the week and shall let you know.
musdu, I liked even your idea though it did appear a bit strange initially. Shall try this out too and let you know.
Thanks,
vemul
Your suggestion is something what I was really looking for. Let me try it out over the week and shall let you know.
musdu, I liked even your idea though it did appear a bit strange initially. Shall try this out too and let you know.
Thanks,
vemul
ASKER
Hi,
I am pretty close to achieving what I wanted but am kind of stuck here. I wrote a sample shell script:
test.ksh
#!/bin/ksh
OUTFILE="output.txt"
echo > $OUTFILE
while read LINE
do
echo ${LINE} >> $OUTFILE
done < test.txt
exit $?
-------------------------- ---------- ---------- ---------- --
and the test.txt is
OPTIONS (SKIP_UNUSABLE_INDEXES=YES )
LOAD DATA APPEND
INTO TABLE OSE_RPST_TEMP
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
COL1 FILLER,
COL2 DATE 'MMDDYYYYHH24MISS',
COL3 "$1"
)
-------------------------- ---------- ---------- ---------- --------
it is the value of COL3 that I want to give as input parameter but when I run
./test.ksh VAL3
it does not replace the $1 value. Can you please tell me what am I doing wrong?
Thanks,
vemul
I am pretty close to achieving what I wanted but am kind of stuck here. I wrote a sample shell script:
test.ksh
#!/bin/ksh
OUTFILE="output.txt"
echo > $OUTFILE
while read LINE
do
echo ${LINE} >> $OUTFILE
done < test.txt
exit $?
--------------------------
and the test.txt is
OPTIONS (SKIP_UNUSABLE_INDEXES=YES
LOAD DATA APPEND
INTO TABLE OSE_RPST_TEMP
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
COL1 FILLER,
COL2 DATE 'MMDDYYYYHH24MISS',
COL3 "$1"
)
--------------------------
it is the value of COL3 that I want to give as input parameter but when I run
./test.ksh VAL3
it does not replace the $1 value. Can you please tell me what am I doing wrong?
Thanks,
vemul
Similar question has just been asked
https://www.experts-exchange.com/questions/21049777/Can-a-SQL-Loader-CTL-file-accept-parameter-input.html
https://www.experts-exchange.com/questions/21049777/Can-a-SQL-Loader-CTL-file-accept-parameter-input.html
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if <Your conditions go here> then
:new.col3=YourValue
else
......
....
..
end if;
No need to mention it in the control file.
Alternatively if you have defined some procedure (in which you do some processing and return value for col3) then u can do something like:
LOAD DATA
INSERT NTO TABLE TEST
FIELDS TERMINATED BY '|'
(
COL1,
COL2,
COL3 "ProcedureName(Param1,Para
)
I am quite doubtfull about my 2nd option ...you will have to try it out.