Home » Infrastructure » Unix » Passing arguments to Sql Loader (merged)
Passing arguments to Sql Loader (merged) [message #296653] Mon, 28 January 2008 09:35 Go to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
Hi,

I want to pass an argument from the ksh script to the sqlldr. How can i do this?

Below are the ksh script and ctl file

Ksh Script:

#!/bin/ksh -x

SQLLOAD="C:/saurabh/proj/scripts/ctlfiles/xrx_data.ctl"

cd c:/saurabh/proj/data

for filename in *.csv
do
filepath="c:/saurabh/proj/data/"$filename
export filepath

extractdt=`date +%m%d%y`
export extractdt

lines=`cat $filepath | wc -l `
lines=`expr $lines "-" 13`

logxrx="C:/saurabh/proj/scripts/logfiles/XRX_LOG.log"
export logxrx

. $SQLLOAD $filepath > $logxrx
done


data file:

sqlldr saurbh/saurbh01 control="c:/saurabh/proj/scripts/posfile/xrxpos.ctl" log=="C:/saurabh/proj/scripts/logfiles/XRX_LOG.log" data=$1 skip="5" load="$lines"


ctl file:

LOAD DATA
INFILE *
discardfile "C:/saurabh/proj/scripts/logfiles/XRX_LOG.log"
APPEND
INTO TABLE rpt
WHEN(NBR != '') AND (NBR != 'TOTAL')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
NBR "xrx_nbr(:NBR)",
BATCH FILLER,
Description FILLER,
EXTRACT_DT $extractdt
)


I wish to use the variable extractdt in the ksh script, while loading the data. Please help me with your suggestions

Regards
Saurabh
Re: Passing arguments to Sqlldr [message #296657 is a reply to message #296653] Mon, 28 January 2008 09:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.

Always post your Oracle version (4 decimals).

Post in the correct forum.

Generate the control file inside your script.

Regards
Michel
Passing arguments to Sql Loader [message #296668 is a reply to message #296653] Mon, 28 January 2008 10:41 Go to previous messageGo to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
Hi,
I want to pass an argument from the ksh script to the sqlldr. How can i do this?
Below are the ksh script and ctl file

Ksh Script:
#!/bin/ksh -x
SQLLOAD="C:/saurabh/proj/scripts/ctlfiles/xrx_data.ctl"
cd c:/saurabh/proj/data
for filename in *.csv
do
	filepath="c:/saurabh/proj/data/"$filename
	export filepath
	
	extractdt=`date +%m%d%y`
        export extractdt
	
	lines=`cat $filepath | wc -l `
	lines=`expr $lines "-" 13`

        logxrx="C:/saurabh/proj/scripts/logfiles/XRX_LOG.log"
        export logxrx

	. $SQLLOAD $filepath > $logxrx
done


data file:
sqlldr saurbh/saurbh01  control="c:/saurabh/proj/scripts/posfile/xrxpos.ctl" \
log="C:/saurabh/proj/scripts/logfiles/XRX_LOG.log" data=$1 skip="5" load="$lines"


ctl file:
LOAD DATA
INFILE *
discardfile  "C:/saurabh/proj/scripts/logfiles/XRX_LOG.log"
APPEND
INTO TABLE rpt
WHEN(NBR != '') AND (NBR != 'TOTAL')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
	NBR "xrx_nbr(:NBR)", 
	BATCH FILLER, 
	Description FILLER, 
	EXTRACT_DT $extractdt
)	

I wish to use the variable extractdt in the ksh script, while loading the data. Please help me with your suggestions
Regards
Saurabh

[Updated on: Mon, 28 January 2008 11:26] by Moderator

Report message to a moderator

Re: Passing arguments to Sql Loader [message #296678 is a reply to message #296668] Mon, 28 January 2008 11:27 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Generate the control file inside your script.

Regards
Michel
Previous Topic: Very urgent please
Next Topic: Appending a value at the end of each line
Goto Forum:
  


Current Time: Thu Mar 28 19:51:43 CDT 2024