• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2205
  • Last Modified:

Shell Script for invoking SQL Loader

Hi experts,

I need your help in writing a shell script(.sh file) to invoke SQl Loader, that will load the data(in .txt/.csv) using a control file(.ctl) to the database table
I have the password file for connecting to database instance that the script will read and invoke the SQL Loader.

Thanks.
0
arunec
Asked:
arunec
2 Solutions
 
stefan73Commented:
Hi arunec,
Do you have a working ctl file?

Cheers,
Stefan
0
 
HamdyHassanCommented:

#!/bin/sh
sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log
retcode=`echo $?`
case "$retcode" in
0) echo "SQL*Loader execution successful" ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
2) echo "SQL*Loader execution exited with EX_WARN, see logfile" ;;
3) echo "SQL*Loader execution encountered a fatal error" ;;
*) echo "unknown return code";;
esac
0
 
PerlKingCommented:
Assuming your password file (say filename is "password.conf") looks like this:

username=scott
password=tiger
dbhost=localhost

then you should modify HamdyHassan's script like this:
Source the password file like this at the begginning of the script:

#!/bin/sh
. password.conf

# HamdyHassan's code follows here ....
sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log
retcode=`echo $?`
case "$retcode" in
0) echo "SQL*Loader execution successful" ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
2) echo "SQL*Loader execution exited with EX_WARN, see logfile" ;;
3) echo "SQL*Loader execution encountered a fatal error" ;;
*) echo "unknown return code";;
esac
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PerlKingCommented:
Sorry, the code has to look like this:

#!/bin/sh
. password.conf

# HamdyHassan's code follows here ....
sqlldr $username/$password@$dbhost control=ulcase1.ctl log=ulcase1.log
retcode=`echo $?`
case "$retcode" in
0) echo "SQL*Loader execution successful" ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
2) echo "SQL*Loader execution exited with EX_WARN, see logfile" ;;
3) echo "SQL*Loader execution encountered a fatal error" ;;
*) echo "unknown return code";;
esac
0
 
arunecAuthor Commented:
Hi

Sorry for delay in replying..
The location of log n CTL file needs to be where the script runs right ?
or can it be changed to some other path?

Your comments were very helpful
Thanks everybody..

Regards,
arunec
0
 
ankhan100599Commented:
I am a newbie to Unix scripting and need a Unix script to do the following :

1. Take partition name as parameter and drop the partition.
2. Alter tablespace to  R/W
3. Take new partition name as parameter and create that partition
4. execute an analyze table on that partition
5. alter tablespace to R only.

Environment is Solaris with Oracle 8i.

There are two variations for this required. One is as described to manually input the partition names each time script is invoked. Second variation is to have the script read (partition names) from a parameter file and use those. Of course, partition names cannot be reused.

0
 
ankhan100599Commented:
Sorry wrong place to put a question !!!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now