Regarding sqlldr

ancys · 08-04-2006, 07:10 AM

hi
I wrote a shell script(bash) for reading the text file and store into database
sample.sh
#!bin/sh
var="test.txt"
sqlldr uname/pwd@dbname control=crl.ctl

crl.ct
Load data infile '/sai/test.txt' into table employee fields terminated by "#" trailing nullcols(Col1,col2,col3)

here in control file i am hardcoding the filename i.e test.txt and pathname but i dont want to hard code it.

I need to transfer the filename and path stored in variable in shell program(sample.sh) to control file

I created the control.template as mentioned below but it replace only filename

how to replace both
the solution is given for replacing file name how to replace the path

Help me

Tinkster · 08-05-2006, 01:15 AM

You could have a file crl.template like so

Code:

Load data infile '/sai/REPLACE_ME' into table employee fields terminated by "#" trailingn ullcols(Col1,col2,col3)

And in the script do

Code:

#!bin/sh
var="test.txt"
sed "s/REPLACE_ME/$var/" crl.template > crl.ctl
sqlldr uname/pwd@dbname control=crl.ctl

Cheers,
Tink

ancys · 08-05-2006, 04:11 AM

Thanks For the solution

ancys · 08-07-2006, 02:55 AM

but how can include the path name stored in a variable

Tinkster · 08-07-2006, 04:39 AM

Almost exactly like that. You just use something that's not in a file- or
pathname as the separator, e.g. @

Code:

#!bin/sh
var="/path/to/test.txt"
sed "s@REPLACE_ME@$var@" crl.template > crl.ctl
sqlldr uname/pwd@dbname control=crl.ctl

Cheers,
Tink

ancys · 08-07-2006, 05:44 AM

Ihanks the script works successfully

ancys · 08-10-2006, 08:02 AM

Hi,
Even the delimiter is stored i an variable called delimter='#'

how to place in control.template