Home » SQL & PL/SQL » SQL & PL/SQL » managing errors from script
managing errors from script [message #201224] Fri, 03 November 2006 03:33 Go to next message
istruttorenuoto
Messages: 5
Registered: October 2006
Junior Member
hi to everybody!!
i wrote i script from Unix's Bourne shell that connects to an Oracle DataBase and calls a Stored Procedure. Now i'd like to insert some more commands for error managing.
here's my scritp:

stty istrip
stty erase ^H
export ORACLE_BASE=/product/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.6
export PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/local/bin:/usr/ucb:/tibcosv/dtsgvemi/S2T/scripts
sqlplus USER/PASSWORD@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = IP_ADDRESS)(PORT = PORT_number)))(CONNECT_DATA =(SID = sid_name))) @chiamata_errore
exit

and here's the chiamata_errore.sql:

spool PROVA_3.log
exec PROVA_3;
whenever sqlerror exit 1
whenever oserror exit 1
spool off
exit

What can i add more on these 2 scripts in case of errors?
connection errors
stored procedure calling errors
etc

And how can i creat a log file for all these errors?
thank you
Re: managing errors from script [message #201231 is a reply to message #201224] Fri, 03 November 2006 04:01 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I'm not sure why you need all that stuff for the database address. It should be possible to get something a bit shorter using the alias in the tnsnames.ora file.

Also, you need your "whenever" statements before the command is issued, rather than after. That aside, I think you've largely covered it. Obviously, you will need to check the $? status after the sqlplus call to determine if it has been set to 1.

It might be better to move the connect inside the script in order to better capture the error message. You could do something like:

sqlplus /nolog << EOF
spool PROVA_3.log
whenever sqlerror exit 1
whenever oserror exit 1
connect USER/PASSWORD@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = IP_ADDRESS)(PORT = PORT_number)))(CONNECT_DATA =(SID = sid_name))) 
exec PROVA_3;
spool off
exit
EOF

if [ $? -ne 0 ]
then
...


This also enables you to get rid of the .sql script, so the additional error you could get because the script can't be located is removed.

[Updated on: Fri, 03 November 2006 04:03]

Report message to a moderator

Re: managing errors from script [message #201240 is a reply to message #201224] Fri, 03 November 2006 04:19 Go to previous messageGo to next message
istruttorenuoto
Messages: 5
Registered: October 2006
Junior Member
thank you!!!
i will try now. can i ask you more if i have any problems?
Re: managing errors from script [message #201241 is a reply to message #201224] Fri, 03 November 2006 04:22 Go to previous message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Just post any more questions in the thread and I or someone else will probably be able to answer them.
Previous Topic: table join
Next Topic: Diff between count(1) and count(*)
Goto Forum:
  


Current Time: Fri Dec 02 20:40:48 CST 2016

Total time taken to generate the page: 0.11912 seconds