| exiting PL/SQL block with error code [message #149668] |
Fri, 02 December 2005 15:34  |
alp0001
Messages: 29
Registered: September 2005
Location: Illinois, USA
|
Junior Member |
|
|
I have a sample small PL/SQL script that is called from a Unix KSH batch script. I always exit the PL/SQL script successfully. However, if the PL/SQL script goes into the WHEN OTHERS portion, I would like to exit with a different number, like exit $FAILURE:
...
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('Works fine');
EXCEPTION
WHEN OTHERS
then
DBMS_OUTPUT.PUT_LINE('SQL Error: '||SUBSTR(SQLERRM,1,200));
exit $FAILURE; < -- obviously doesn't work right now, but want something similar.
END;
/
exit $SUCCESS
...
[Updated on: Fri, 02 December 2005 15:47] Report message to a moderator |
|
|
|
| Re: exiting PL/SQL block with error code [message #149678 is a reply to message #149668] |
Fri, 02 December 2005 17:38  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
I think you want to exit/return with a user specified error number?
Check Oracle's password complexity example under $ORACLE_HOME/rdbms/admin/utlpwdmg.sql.
It shows you how to exit with an error code and description that you define.
|
|
|
|
| Re: exiting PL/SQL block with error code [message #149864 is a reply to message #149668] |
Mon, 05 December 2005 08:05 |
alp0001
Messages: 29
Registered: September 2005
Location: Illinois, USA
|
Junior Member |
|
|
Thanks for the suggestion, but the example appears to be using a function statement (RETURN <data type>).
I'll give more code to show what I need to do:
#!/bin/ksh
export FAILURE=4
export SUCCESS=3
sqlplus -s <<-%%
$DBLOG/$DBPWD
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('Works fine');
EXCEPTION
WHEN OTHERS
then
DBMS_OUTPUT.PUT_LINE('SQL Error: '||SUBSTR(SQLERRM,1,200));
exit $FAILURE; < -- obviously doesn't work right now, but want something similar.
END;
/
exit $SUCCESS
%%
check_status $?
|
|
|
|
| Re: exiting PL/SQL block with error code [message #149935 is a reply to message #149864] |
Mon, 05 December 2005 14:41 |
skgoel
Messages: 16
Registered: November 2005
|
Junior Member |
|
|
Since you are using SQL*Plus you can set the following command after you login
WHENEVER SQLERROR EXIT 1
#!/bin/ksh
export FAILURE=4
export SUCCESS=3
sqlplus -s <<-%%
$DBLOG/$DBPWD
SET SERVEROUTPUT ON SIZE 1000000
WHENEVER SQLERROR EXIT 1
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE('Works fine');
END;
/
%%
Also remove the EXCEPTION clause. If SQL*Plus finds that an error has occurred it will return with an exit code of 1
You can also try
WHENEVER SQLERROR EXIT SQL.SQLCODE
Shakti
http://www.impact-sol.com
Developers of Guggi Oracle - Tool for DBAs and Developers
|
|
|
|
| Re: exiting PL/SQL block with error code [message #150129 is a reply to message #149668] |
Tue, 06 December 2005 08:15 |
alp0001
Messages: 29
Registered: September 2005
Location: Illinois, USA
|
Junior Member |
|
|
|
Sorry, guess I should have stated that was already tried. The problem is, I'm going to be working with cursors and files, so I want to do some cleanup in the EXCEPTION part should it fail for whatever reason. And, I want to call specific batch functions should the PL/SQL part fail. So, adding the 'WHENEVER SQLERROR EXIT' clause isn't going to work. It seems like there is no easy way of both going into the EXCEPTION part and returning a specific error number. I guess a workaround is to create a temporary 'error file' should the EXCEPTION code get hit and then in the batch part see if the file exists.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
