Home » Infrastructure » Unix » How to start if sqlloader fails inside shell script (Unix,1Oracle 2c)
How to start if sqlloader fails inside shell script [message #680002] Fri, 17 April 2020 00:10 Go to next message
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 messageGo to next message
Michel Cadot
Messages: 68624
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 #680005 is a reply to message #680004] Fri, 17 April 2020 00:44 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
Thank You. Can u pls guide me as per my requirement how I will declare the fucntion and calling it for multiple sqlloader cmd.
Re: How to start if sqlloader fails inside shell script [message #680006 is a reply to message #680005] Fri, 17 April 2020 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can declare a function like this:
handle_retcode() {
  if [ $1 -eq 0 ]
  then
    echo "SQL*Loader execution successful"
  else
    echo "There is error while running the sqlloader"
    echo "Errors found in $2:"
    error_code=`grep ORA- $2 | sed 's/^/ /'`
    echo "$error_code"
  fi  
}
and call it like this:
retcode=999
while [ $retcode -ne 0 ]
do
  sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log
  retcode=$?
  echo "$retcode"
  handle_retcode $retcode ulcase1.log
done
Re: How to start if sqlloader fails inside shell script [message #680007 is a reply to message #680006] Fri, 17 April 2020 02:07 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
thank You sir
I will relook as I have 100 CTL files and loop should work for all
Re: How to start if sqlloader fails inside shell script [message #680008 is a reply to message #680007] Fri, 17 April 2020 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If your names are really all like ulcase1, ulcase2, ulcase3... then a single "for" loop can do the trick.

Re: How to start if sqlloader fails inside shell script [message #680009 is a reply to message #680008] Fri, 17 April 2020 02:14 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
No, names are different.

like msg,hist,text,act,num etc
Re: How to start if sqlloader fails inside shell script [message #680010 is a reply to message #680008] Fri, 17 April 2020 02:16 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
And more ever, if sqlloader will fail for 2nd table then it should restart from 2nd table. It should not start from first table.

looks like this is tricky
Re: How to start if sqlloader fails inside shell script [message #680011 is a reply to message #680010] Fri, 17 April 2020 04:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, you have to enclose EACH sqlldr with the code I gave.

If names are all like, for a sqlldr session <name>, <name>.ctl and <name>.log, you can still do it in a single loop:
for name in msg hist text act num 
do
  retcode=99999
  while [ $retcode -ne 0 ]
  do
    sqlldr scott/tiger control=${name}.ctl log=${name}.log
    retcode=$?
    echo "$retcode"
    handle_retcode $retcode ${name}.log
  done
done
Re: How to start if sqlloader fails inside shell script [message #680012 is a reply to message #680011] Fri, 17 April 2020 04:49 Go to previous messageGo to next message
bkbora
Messages: 21
Registered: April 2020
Junior Member
yeah..got it..thank you
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 messageGo to next message
Michel Cadot
Messages: 68624
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

Re: How to start if sqlloader fails inside shell script [message #683175 is a reply to message #680004] Thu, 10 December 2020 05:33 Go to previous message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
retcode=999
Good tip, Michel. I'd never thought of that and I consider myself a reasonable script writer Smile
Previous Topic: [REQ] Oracle Database 7i for sparc
Next Topic: View creation script is copying data to log file (merged)
Goto Forum:
  


Current Time: Thu Mar 28 08:58:24 CDT 2024