Home » SQL & PL/SQL » Client Tools » SQL Script to check status of last command
SQL Script to check status of last command [message #417368] Fri, 07 August 2009 11:27 Go to next message
vikas027
Messages: 11
Registered: September 2008
Junior Member
Hi All,

I need to make a SQL script and call it from Unix (bash) shell.


==================================
sqlplus vikas/vikas <<END
spool /oracle/vikas/output.txt
command 1
command 2
...
....
comman N
spool off;
commit;
END
==================================


This runs perfectly, but my problem is that if a command fails, the other commands keeps on running. However, I need to keep a check that when a command fails the script is exited.

In unix we have an option of echo $? to check the status of previous command, But how to accomplish this SQL.

Pls help !!
__________________
==> VIKAS <==
Re: SQL Script to check status of last command [message #417369 is a reply to message #417368] Fri, 07 August 2009 11:31 Go to previous messageGo to next message
BlackSwan
Messages: 22924
Registered: January 2009
Senior Member
WHENEVER SQLERROR .......
Research above in SQL*Plus manual

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: SQL Script to check status of last command [message #417371 is a reply to message #417369] Fri, 07 August 2009 11:43 Go to previous messageGo to next message
vikas027
Messages: 11
Registered: September 2008
Junior Member
I guess I made a mistake by writing "Pls". I will keep this in mind for future.
Re: SQL Script to check status of last command [message #418465 is a reply to message #417369] Sat, 15 August 2009 17:26 Go to previous messageGo to next message
vikas027
Messages: 11
Registered: September 2008
Junior Member
Hi,

Sorry for reply late. The lines in bold helped me. Here is my final code.

sqlplus vikas/vikas << END
WHENEVER SQLERROR EXIT;
WHENEVER OSERROR EXIT;

spool /oracle/vikas/output.txt
desc WHITELIST;
desc USERS;
desc TRANSACTION;
desc computer;
spool off;
!echo ALL SUCCESSFUL
END
if [ $? -ne 0 ];
then
echo "ERROR! SQL*Plus failed..."; exit 1
else
echo "\n\nOne or more query has failed if --- ALL SUCCESSFUL -- is not echoed !!!"
fi


Here,
WHENEVER SQLERROR EXIT -- Exits if any error is encounter in query
WHENEVER OSERROR EXIT -- Exits if any error encountered in OS
desc computer; -- Wrong Line
Re: SQL Script to check status of last command [message #418473 is a reply to message #418465] Sat, 15 August 2009 23:52 Go to previous message
Michel Cadot
Messages: 59496
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Thanks for the feedback.

Regards
Michel
Previous Topic: sqlplus &1 not working???
Next Topic: SQL PLUS set echo off command
Goto Forum:
  


Current Time: Thu Oct 30 07:19:10 CDT 2014

Total time taken to generate the page: 0.08858 seconds