How to start if sqlloader fails inside shell script [message #680002] |
Fri, 17 April 2020 00:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/de1b2f5b0b36153288a60ab7023a4667?s=64&d=mm&r=g) |
bkbora
Messages: 21 Registered: April 2020
|
Junior Member |
|
|
Hi Team,
I am writing a shell script which run 10 sql loader command, if anyone will fail then it should start again. Suppose if return code other than zero then the particular sqlloader will be restarted. How I will proceed, can someone pls guide me. Actually while the DB server is failing over to DR side that time our sqlloader is failing. So need to start it again from that point only.
Please help.
sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log
sqlldr scott/tiger control=ulcase2.ctl log=ulcase2.log
sqlldr scott/tiger control=ulcase3.ctl log=ulcase3.log
sqlldr scott/tiger control=ulcase4.ctl log=ulcase4.log
retcode=`echo $?`
echo "$retcode"
if [ $retcode -eq 0 ]
then
echo "SQL*Loader execution successful"
else
echo "There is error while running the sqlloader"
echo "Errors found in ulcase1.log:"
error_code=`grep ORA- ulcase1.log | sed 's/^/ /'`
echo "$error_code"
exit
fi
|
|
|
Re: How to start if sqlloader fails inside shell script [message #680004 is a reply to message #680002] |
Fri, 17 April 2020 00:32 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
And for question like the OS and shell version.
This is not a real Oracle question but a shell one so solution depends on your shell.
You can do, for each sqlldr, something like:
retcode=999
while [ $retcode -ne 0 ]
do
sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log
retcode=`echo $?`
echo "$retcode"
if [ $retcode -eq 0 ]
then
echo "SQL*Loader execution successful"
else
echo "There is error while running the sqlloader"
echo "Errors found in ulcase1.log:"
error_code=`grep ORA- ulcase1.log | sed 's/^/ /'`
echo "$error_code"
fi
done
Of course the retcode treatment part can be put in a function to prevent from repeating the same code...
[Updated on: Fri, 17 April 2020 00:34] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: How to start if sqlloader fails inside shell script [message #680014 is a reply to message #680012] |
Fri, 17 April 2020 07:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I talked only about the scripting but there are some changes to make in the content.
In some cases, you can get a return code 0 but there were some errors/important warnings.
You can also get other errors than ORA- like SQL*Loader- ones or even OCI- ones.
So I'd change the handler for something like this:
handle_retcode() {
# Parameters:
# #1: sqlldr return code
# #2: sqlldr log file
rc=$1
log=$2
if [ $rc -eq 0 && `egrep -c '(ORA|SQL\*Loader|OCI|UL)-' $log` -eq 0 ]
then
echo "SQL*Loader execution successful"
else
echo "There were errors while running SQL*Loader (return code $rc, log $log)."
egrep '(ORA|SQL\*Loader|OCI|UL)-' $log
fi
}
[Updated on: Sat, 18 April 2020 03:17] Report message to a moderator
|
|
|
|