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: 19476
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: 58861
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: 22690
Registered: January 2009
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: 7969
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: 58861
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 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
Previous Topic: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified
Next Topic: Table reorganization
Goto Forum:
  


Current Time: Fri Aug 22 18:48:45 CDT 2014

Total time taken to generate the page: 0.09630 seconds