Solved

Shell Script for invoking SQL Loader

Posted on 2004-04-27
7
2,165 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: 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!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Expand macro to ask for filename column 8 40
notReplace  challenge 53 153
Prevent this page from creating additional dialogs. 3 791
MaxSpan challenge 9 140
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…
Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

751 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