| SQL Script to check status of last command [message #417368] |
Fri, 07 August 2009 11:27  |
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 #418465 is a reply to message #417369] |
Sat, 15 August 2009 17:26  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
