Solved

Shell Script for invoking SQL Loader

Posted on 2004-04-27
7
2,134 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
not able to insert into temp table 68 150
isEverywhere  challenge 19 64
either24  challenge 19 84
how to split multiple lines delimiter : 8 54
Introduction: Finishing the grid – keyboard support for arrow keys to manoeuvre, entering the numbers.  The PreTranslateMessage function is to be used to intercept and respond to keyboard events. Continuing from the fourth article about sudoku. …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now