OraFAQ Forum: Server Utilities » Passing parameter to control file in SQLLDR (SQL LOADER)

Home » RDBMS Server » Server Utilities » Passing parameter to control file in SQLLDR (SQL LOADER)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Switch to threaded view of this topic

Create a new topic

Submit Reply

Passing parameter to control file in SQLLDR (SQL LOADER) [message #605513]

Fri, 10 January 2014 02:45

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?

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605537 is a reply to message #605513]

Fri, 10 January 2014 06:50

Littlefoot
Messages: 21648
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.

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605587 is a reply to message #605537]

Sat, 11 January 2014 07:17

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

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605588 is a reply to message #605587]

Sat, 11 January 2014 07:36

Michel Cadot
Messages: 67667
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?

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605589 is a reply to message #605588]

Sat, 11 January 2014 08:21

BlackSwan
Messages: 26757
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

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605597 is a reply to message #605587]

Sat, 11 January 2014 13:12

Barbara Boehmer
Messages: 8922
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.

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605726 is a reply to message #605597]

Mon, 13 January 2014 23:41

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.

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605729 is a reply to message #605726]

Tue, 14 January 2014 00:10

Michel Cadot
Messages: 67667
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?

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #605731 is a reply to message #605729]

Tue, 14 January 2014 00:42

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

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672683 is a reply to message #605513]

Wed, 24 October 2018 01:08

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

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672685 is a reply to message #672683]

Wed, 24 October 2018 02:08

Michel Cadot
Messages: 67667
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).

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672688 is a reply to message #672683]

Wed, 24 October 2018 06:26

EdStevens
Messages: 1293
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?

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672692 is a reply to message #672688]

Wed, 24 October 2018 08:21

Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Not the same guy. Wink

Wink

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672708 is a reply to message #672692]

Thu, 25 October 2018 06:15

EdStevens
Messages: 1293
Registered: September 2013

Senior Member

Michel Cadot wrote on Wed, 24 October 2018 08:21

Not the same guy.

DOH!

Embarassed

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672745 is a reply to message #672708]

Fri, 26 October 2018 04:57

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.

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672746 is a reply to message #672745]

Fri, 26 October 2018 05:28

Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

kindly do what I suggested.

Report message to a moderator

Send a private message to this user

Re: Passing parameter to control file in SQLLDR (SQL LOADER) [message #672783 is a reply to message #672745]

Fri, 26 October 2018 09:18

BlackSwan
Messages: 26757
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.

Report message to a moderator

Send a private message to this user

Switch to threaded view of this topic

Create a new topic

Submit Reply

Previous Topic:	service name how to get the database name
Next Topic:	Reading data fom file using External Loader

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 27 11:54:40 CST 2021