Here is a ksh :
$ORACLE_HOME/bin/sqlplus id/psw@$ORACLE_SID @$P_SQL/mysql.sql $1
return_code=$?
echo return_code
Here is the sql :
set serverout on
var error number
...
declare
error number(1) := 0;
begin
.......
exception
when too_many_rows then
error := 2;
end;
/
exit
How to pass the PLSQL error value to SQLPLUS variable and then the SqlPlus variable to the ksh variable return_code.
Thank you
It is possible to pass a SQL error code from a PL/SQL script to the shell.
However, the shell will perform modulo 256 on the SQL error number. If you needed a specific number, you could use a user defined error with a custom error number.
SET SERVEROUT ON
WHENEVER SQLERROR EXIT SQL.SQLCODE;
DECLARE
l_result VARCHAR2(1);
BEGIN
SELECT result
INTO l_result
FROM (SELECT 'A' result
FROM dual
UNION
SELECT 'B' result
FROM dual);
EXCEPTION
WHEN too_many_rows THEN
Raise_application_error(-20226, 'Custom error ' || MOD(20226, 256) || ' for too many rows');
END;
/
exit
