SQL Script to check status of last command

vikas027 · #1 (**permalink**) 08-07-09, 13:27

Hi All,

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

Code:

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 <==

LKBrwn_DBA · #2 (**permalink**) 08-07-09, 14:58

Try this:

Code:

sqlplus vikas/vikas <<END
WHENEVER SQLERROR EXIT 13;
spool /oracle/vikas/output.txt
command 1
command 2
...
....
comman N
spool off;
commit;
EXIT 0;
END
if [ $? -ne 0 ]; then
  echo "ERROR! SQL*Plus failed..."
  exit 1
fi

vikas027 · #3 (**permalink**) 08-15-09, 19:26

Hi,

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

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

LKBrwn_DBA · #4 (**permalink**) 08-18-09, 13:25

You may want to FORCE a numeric error upon exit:

Code:

sqlplus vikas/vikas <<END
WHENEVER SQLERROR EXIT 41;
WHENEVER OSERROR  EXIT 99;
spool /oracle/vikas/output.txt
select SYSDATE from dual;
exit 0
END
case $? in
   0) echo "\n\nALL SUCCESSFUL!";;
  41) echo "ERROR! SQL*Plus failed..."; exit 1;;
  99) echo "ERROR! OS failure..."; exit 2;;
   *) echo "Warning! Unexplained exit code: $?;; 
fi

vikas027 · #5 (**permalink**) 08-19-09, 16:10

Quote:

Originally Posted by LKBrwn_DBA

You may want to FORCE a numeric error upon exit:

Code:

sqlplus vikas/vikas <<END
WHENEVER SQLERROR EXIT 41;
WHENEVER OSERROR  EXIT 99;
spool /oracle/vikas/output.txt
select SYSDATE from dual;
exit 0
END
case $? in
   0) echo "\n\nALL SUCCESSFUL!";;
  41) echo "ERROR! SQL*Plus failed..."; exit 1;;
  99) echo "ERROR! OS failure..."; exit 2;;
   *) echo "Warning! Unexplained exit code: $?;; 
fi

Thanks a lot my friend. Just one question (might be stupid).
Are these error codes (41,99) system error codes OR we can use any number to identify error.

I am asking, because in Unix we have a fixed error code 0 when a command is executed successfully else it is 1.

Thanks a ton again for you help. Will include it in my SQL scripts. I am pretty okay with shell scripting But still learning SQL commands and use them in SQL scripts.

LKBrwn_DBA · #6 (**permalink**) 08-22-09, 11:43

Quote:

Originally Posted by vikas027

Thanks a lot my friend. Just one question (might be stupid).
Are these error codes (41,99) system error codes OR we can use any number to identify error.

I am asking, because in Unix we have a fixed error code 0 when a command is executed successfully else it is 1.

Thanks a ton again for you help. Will include it in my SQL scripts. I am pretty okay with shell scripting But still learning SQL commands and use them in SQL scripts.

No, these error numbers are arbitrary (made up) you can use any number between 0 and 255.

Internet Services

Web Development

Digital Marketing

Consumer Tech