SQL & PL/SQL

Dear Members,

How can i make sql plus exit with an error code.

We have a Host concurrent program. The executable is a shell script for this program. This Shell script calls a sql script which in turn gives call to a database custom package which i built.

So the flow is :


SHELL SCRIPT -----> SQL SCRIPT -----> CUSTOM PACKAGE

Custom Package has two OUT parameters for error flag and error message. If some thing fails in the package these two OUT parameters are returned with a value.

So my sql script code looks like this:

WHENEVER SQLERROR EXIT 251
WHENEVER OSERROR EXIT 251
set serveroutput on size 1000000
set linesize 32767
set verify off
set heading off
set feedback off

DECLARE
x_error_message VARCHAR2(1000);
x_sql_message VARCHAR2(1000);
x_exception EXCEPTION;
e_value NUMBER;

BEGIN
dbms_output.put_line('Inside sql file');


XXpackage.xxproc(x_error_message,x_sql_message);


IF (x_error_message IS NOT NULL) AND (x_sql_message IS NOT NULL) THEN
RAISE x_exception;
END IF;


EXCEPTION
WHEN X_EXCEPTION THEN
e_value := 1;
DBMS_OUTPUT.PUT_LINE('Exit Code : '||e_value);
DBMS_OUTPUT.PUT_LINE('X_EXCEPTION: EXCEPTION IN SCRIPT xpoext04a.sql :'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('x_error_message is : '||x_error_message);
DBMS_OUTPUT.PUT_LINE('x_sql_message is : '||x_sql_message);
DBMS_OUTPUT.PUT_LINE('ERROR IS : '||SQLERRM);
WHEN OTHERS THEN
e_value := 252;
DBMS_OUTPUT.PUT_LINE('Exit Code : '||e_value);
DBMS_OUTPUT.PUT_LINE('OTHERS: EXCEPTION IN SCRIPT xpoext04a.sql :'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('x_error_message is : '||x_error_message);
DBMS_OUTPUT.PUT_LINE('x_sql_message is : '||x_sql_message);
DBMS_OUTPUT.PUT_LINE('ERROR IS : '||SQLERRM);


END;
*/*
exit
*/*


As you can see from above if both OUT parameters have a value then it goes to an exception.

My Problem is, if this goes to X_EXCEPTION exception, the sql plus is not exiting with an error. I mean sql plus is not treating this case as an sqlerror.


How can we make sql plus exit with an sqlerror so that the host program should fail.


Thanks
Sandeep

Edited by: user340 on Jul 28, 2009 12:51 PM

Edited by: user340 on Jul 28, 2009 12:57 PM