OraFAQ Forum: Unix » Passing arguments to Sql Loader (merged)

Home » Infrastructure » Unix » Passing arguments to Sql Loader (merged)

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

Passing arguments to Sql Loader (merged) [message #296653]

Mon, 28 January 2008 09:35

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

Report message to a moderator

Re: Passing arguments to Sqlldr [message #296657 is a reply to message #296653]

Mon, 28 January 2008 09:39

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Passing arguments to Sql Loader [message #296668 is a reply to message #296653]

Mon, 28 January 2008 10:41

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

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Generate the control file inside your script.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Nov 25 00:08:58 CST 2024