Home » RDBMS Server » Server Utilities » Passing parameter to control file in SQLLDR (SQL LOADER)
Passing parameter to control file in SQLLDR (SQL LOADER) [message #605513] Fri, 10 January 2014 02:45 Go to next message
Vnarang
Messages: 4
Registered: January 2014
Junior Member
I have a requirement where in I have to call SQLLDR from a batch file.
Control File will have few parameter which needs to be passed at runtime when the SQLLDR command is executed. Can you please clarify on below doubts:

1. How to pass the varibale to the Control file being given in control parameter of the sqlldr command ?
2. how to capture the variable in control file, so that it can be used for further processing?

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605537 is a reply to message #605513] Fri, 10 January 2014 06:50 Go to previous messageGo to next message
Littlefoot
Messages: 21514
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't think that you can pass parameters to control file. You could/should create a control file on-the-fly, based on that parameter's value. Have a look at example provided by Mahesh Rajendran.
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605587 is a reply to message #605537] Sat, 11 January 2014 07:17 Go to previous messageGo to next message
Vnarang
Messages: 4
Registered: January 2014
Junior Member
Thanks a lot for the reply. Using the given reference I was able to create control file on the fly and the use that in control parameter of the SQL Loader command.

This is How my control file and batch file look likes:

--CONTROL FILE--

LOAD DATA
INFILE '${FILENAME}'
APPEND
INTO TABLE SQLLDR_TEST
FIELDS TERMINATED by '|'
(
NAME CHAR,
ID
)

${FILENAME} is the Variable that I wanted to pass to the control file

----Batch file----

Replace ${FILENAME} in the control file and then use it in control parameter of the sql loader command as below

sqlldr control=D:\CONTROLFILE1.CTL log=D:\DATA_LOG.log

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605588 is a reply to message #605587] Sat, 11 January 2014 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
---Batch file----


Why don't you post the actual batch so others can profit of it?

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605589 is a reply to message #605588] Sat, 11 January 2014 08:21 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
>${FILENAME} is the Variable that I wanted to pass to the control file
any SQLLDR control file is a static text which knows nothing about anything; including OS environmental variable
simply put, your idea has no basis in reality
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605597 is a reply to message #605587] Sat, 11 January 2014 13:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
Instead of putting the filename in the control file, you can put it in the command line:

sqlldr control=D:\CONTROLFILE1.CTL log=D:\DATA_LOG.log data=filename

If the filename is on your server, not your client, another option would be to use an external table and alter the location.
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605726 is a reply to message #605597] Mon, 13 January 2014 23:41 Go to previous messageGo to next message
Vnarang
Messages: 4
Registered: January 2014
Junior Member
The given control file is just a example file and not the actual file that I am creating. I have to pass around 6 variables in control file.

--CONTROL FILE (temp.txt)--

LOAD DATA
INFILE '${FILENAME}'
APPEND
INTO TABLE SQLLDR_TEST
FIELDS TERMINATED by '|'
(
NAME CHAR,
ID
)

${FILENAME} is the Variable that I wanted to pass to the control file

----Batch file----
SET /P FILE=Enter Data File
CALL Sub.BAT ${FILENAME} %FILE% E:\temp.txt>E:\temp1.txt

sqlldr control=E:\temp1.txt

Sub.BAT contains the logic to replace ${FILENAME} with the actual file name passed by the user. Thus I am passing control file created on the fly to SQLLDR utility.
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605729 is a reply to message #605726] Tue, 14 January 2014 00:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Sub.BAT contains the logic to replace ${FILENAME} with the actual file name passed by the user.


Once again why don't you post it?

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605731 is a reply to message #605729] Tue, 14 January 2014 00:42 Go to previous messageGo to next message
Vnarang
Messages: 4
Registered: January 2014
Junior Member
This is the Link for the Code for The Find and Replace utitlity that I have used.

http://www.dostips.com/DtCodeBatchFiles.php#Batch.FindAndReplace
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672683 is a reply to message #605513] Wed, 24 October 2018 01:08 Go to previous messageGo to next message
raghava9247
Messages: 2
Registered: October 2018
Junior Member
Hi Team, kindly let me know how to pass direct values from .cmd file to .CTL file
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672685 is a reply to message #672683] Wed, 24 October 2018 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Use sqlldr parameters (see Barbara's post above) or dynamically create the CTL file (see example).

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672688 is a reply to message #672683] Wed, 24 October 2018 06:26 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
raghava9247 wrote on Wed, 24 October 2018 01:08
Hi Team, kindly let me know how to pass direct values from .cmd file to .CTL file
You said earlier:

Quote:
Thanks a lot for the reply. Using the given reference I was able to create control file on the fly and the use that in control parameter of the SQL Loader command.
So you are able to create the control file on the fly and use it. So what's the problem now?
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672692 is a reply to message #672688] Wed, 24 October 2018 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not the same guy. Wink

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672708 is a reply to message #672692] Thu, 25 October 2018 06:15 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
Michel Cadot wrote on Wed, 24 October 2018 08:21

Not the same guy. Wink

DOH!
Embarassed
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672745 is a reply to message #672708] Fri, 26 October 2018 04:57 Go to previous messageGo to next message
raghava9247
Messages: 2
Registered: October 2018
Junior Member
Yes, I am new person. I want to pass values from batch file to .ctl file dynamically. Kindly help me.
Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672746 is a reply to message #672745] Fri, 26 October 2018 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66715
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

kindly do what I suggested.

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672783 is a reply to message #672745] Fri, 26 October 2018 09:18 Go to previous message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
raghava9247 wrote on Fri, 26 October 2018 02:57
Yes, I am new person. I want to pass values from batch file to .ctl file dynamically. Kindly help me.
any "control file" contains static text; which has NO ability to do variable substitution.
If you have decent shell script ability, YOU can produce control file with different values by applying a Small Amount Of Programming.

A possible alternative to SQLLDR is to use EXTERNAL TABLE instead.
Previous Topic: service name how to get the database name
Next Topic: Reading data fom file using External Loader
Goto Forum:
  


Current Time: Sun Dec 08 07:24:23 CST 2019