Error handling techniques

[zircon06]

Hello everyone

I'm looking code samples for doing error handling in below script

spool ORA_Test_Upgrade_$ORACLE_SID.log

ORACLE_HOME=/u05/app/oracle/product/9.2.0; export ORACLE_HOME
PATH= $PATH:$ORACLE_HOME/bin:/usr/lbin; export PATH
ORACLE_SID=TestDB; export ORACLE_SID
ORACLE_OWNER=system; export ORACLE_OWNER
sqlplus << END
sys/sys
@table.sql
@view.sql
@procedure.sql
@Trigger.sql
quit
End
spool off

exit;

What I'm looking solution for if @table.sql run successfully print to my log file @table.sql completed without errors otherwise failed to run @table.sql file next @trigger.sql file print errors to spool log file.

Thanks in advance

 

[Ogzilal]

Hi,
You can use something like this :

sqlplus /nolog << END
connect / as sysdba
WHENEVER SQLERROR  exit 1
@table.sql
prompt table.sql : OK
WHENEVER SQLERROR  exit 2
@view.sql
prompt view.sql : OK
WHENEVER SQLERROR  exit 3
@procedure.sql
prompt procedure.sql : OK
WHENEVER SQLERROR  exit 4
@Trigger.sql
prompt trigger.sql : OK
spool off
quit
END
RC=$?
case $RC in
0)
    echo "Everything is OK" ;;
1)
   echo "table.sql : failed" ;;
2)
   echo "view.sql : failed" ;;
3)
   echo "procedure.sql : failed" ;;
4)
   echo "trigger.sql : failed" ;;
*)
   echo "some errors happened" ;;
esac

exit;

 

[khalidaaa]

Good solution auu.

Or you can put the whole thing inside a script and redirect errors to a file

script 2> error.log

Regards,
Khalid

 

[zircon06]

I'm getting following error any idea

SQL> @table.sql
SP2-0310: unable to open file "table.sql"
SQL> prompt table.sql : OK
table.sql : OK
SQL> WHENEVER SQLERROR exit 2
SQL> @view.sql
SP2-0310: unable to open file "view.sql"
SQL> prompt view.sql : OK
view.sql : OK
SQL> WHENEVER SQLERROR exit 3
SQL> @procedure.sql
SP2-0310: unable to open file "procedure.sql"
SQL> prompt procedure.sql : OK
procedure.sql : OK
SQL> WHENEVER SQLERROR exit 4
SQL> @trigger.sql
SP2-0310: unable to open file "trigger.sql"
SQL> prompt trigger.sql : OK
trigger.sql : OK

MY sql are located in u01/app/oracle directory how to tell in script about this location

 

[p5wizard]

by cd-ing to that dir perhaps?

...
ORACLE_SID=TestDB; export ORACLE_SID
ORACLE_OWNER=system; export ORACLE_OWNER
cd /path/to/u01/app/oracle
sqlplus << END
sys/sys
@table.sql
...

or providing a full path name to the @ commands:

...
ORACLE_SID=TestDB; export ORACLE_SID
ORACLE_OWNER=system; export ORACLE_OWNER
sqlplus << END
sys/sys
@/path/to/u01/app/oracle/table.sql
...


HTH,

p5wizard

 

[theprogrammer]

I need to do similar error handling only using the windows commands instead of unix. Can anyone give me an example?

 

[khalidaaa]

This link will definitly help:

http://www.oracle-base.com/articles/misc/OracleShellScripting.php

Regards,
Khalid