Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Shell Script for invoking SQL Loader

Posted on 2004-04-27
7
Medium Priority
?
2,194 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 1200 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 3

Accepted Solution

by:
PerlKing earned 800 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article, I'll describe -- and show pictures of -- some of the significant additions that have been made available to programmers in the MFC Feature Pack for Visual C++ 2008.  These same feature are in the MFC libraries that come with Visual …
Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
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.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses

886 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