Solved

Shell Script for invoking SQL Loader

Posted on 2004-04-27
7
2,156 Views
Last Modified: 2013-12-26
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
Comment
Question by:arunec
7 Comments
 
LVL 12

Expert Comment

by:stefan73
ID: 10927682
Hi arunec,
Do you have a working ctl file?

Cheers,
Stefan
0
 
LVL 9

Assisted Solution

by:HamdyHassan
HamdyHassan earned 300 total points
ID: 10927762

#!/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
 
LVL 3

Expert Comment

by:PerlKing
ID: 10935623
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 3

Accepted Solution

by:
PerlKing earned 200 total points
ID: 10935627
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
 
LVL 1

Author Comment

by:arunec
ID: 11013123
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
 

Expert Comment

by:ankhan100599
ID: 11202636
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
 

Expert Comment

by:ankhan100599
ID: 11202645
Sorry wrong place to put a question !!!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here is how to use MFC's automatic Radio Button handling in your dialog boxes and forms.  Beginner programmers usually start with a OnClick handler for each radio button and that's just not the right way to go.  MFC has a very cool system for handli…
Introduction: Dynamic window placements and drawing on a form, simple usage of windows registry as a storage place for information. Continuing from the first article about sudoku.  There we have designed the application and put a lot of user int…
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question