Regarding PLSQL Exit Command return status

I have the test.sql script which is called from unix shell script test.sh. Problem is in test.sql script when i have a exception i do the rollback and exit. When it comes to shell script it will have exit status($?) as 0 and it considered successful. How i can exit with a error status from test.sql?

test.sh

sql_script=sql/test.sql

$ORACLE_HOME/bin/sqlplus -s << ENDSQL

${DATABASE_LOGIN}@${DATABASE_NAMES[${DMIN_DB}]}

WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK

SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET TRIMSPOOL ON
SET SERVEROUTPUT ON

@${sql_script} $PROC_DATE

EXIT
ENDSQL

  if [ $? -ne 0 ]; then
    echo "Error in executing the开发者_开发问答 insertBclErrorReport function."
  fi

test.sql

WHILE i IS NOT NULL LOOP
BEGIN

  insert into test1(field1) values(error(i).amount);

  EXCEPTION
    WHEN others THEN rollback;
  exit;

  END;
END LOOP;

commit;

END;

Your PL/SQL block will catch every exception and handle it by simply doing a rollback. If you want the caller to be aware that there was an error, you most likely want to simply remove the exception handler.

You could also include an explicit RAISE or RAISE_APPLICATION_ERROR in your exception handler but that wouldn't seem to be particularly useful. Since the SQL*Plus script is already reacting to an error by rolling back the transaction, catching and re-throwing your exception in the PL/SQL loop doesn't change any behavior. All it accomplishes is to obscure the actual line number and call stack that the error relates to making it more difficult to debug errors.

In general, if you cannot actually handle an error, there is no sense in catching it. By the same token, if you need a WHEN OTHERS, then you almost certainly cannot handle the error and shouldn't be catching it in the first place. Now, if you are actually catching the error, logging the error and the entire call stack to an error table, and re-throwing a custom error that is more meaningful than the Oracle error, a WHEN OTHERS can be reasonable.

I was having the same issue, but after tweaking a while, this appears to work on my system:

WHENEVER SQLERROR EXIT SQL.SQLCODE;
variable recordId number;
begin
::::::
exception
  WHEN OTHERS then
       :::::::;
      :recordId:=SQLCODE;
end;
/
exit :recordId;

---on SQL*Plus: Release 11.2.0.1.0 Production