Page History

CREATE OR REPLACE PROCEDURE PROC1
AS 
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
DECLARE ---------- sub-block begins
past_due EXCEPTION; -- this declaration prevails
acct_num NUMBER;
due_date DATE := SYSDATE - 1;
todays_date DATE := SYSDATE;
BEGIN
IF due_date < todays_date THEN
RAISE past_due; -- this is not handled
END IF;
END; ------------- sub-block ends
EXCEPTION
WHEN past_due THEN -- does not handle raised exception
DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS
past_due EXCEPTION;
acct_num NUMBER;
BEGIN
DECLARE ---------- sub-block begins
past_due EXCEPTION; -- this declaration prevails
acct_num NUMBER;
due_date DATE := SYSDATE - 1;
todays_date DATE := SYSDATE;
BEGIN
IF due_date < todays_date THEN
RAISE past_due; -- this is not handled
END IF;
END; ------------- sub-block ends
EXCEPTION
WHEN past_due THEN -- does not handle raised exception
SYSTEM_.PRINTLN('Handling PAST_DUE exception.');
WHEN OTHERS THEN
SYSTEM_.PRINTLN('Could not recognize PAST_DUE_EXCEPTION in this scope.');
END;
/

Declaring and raising Exceptions.

CREATE OR REPLACE PROCEDURE PROC2
AS
 E1 EXCEPTION;
BEGIN
 RAISE E1;
PROC1;
EXCEPTION
WHEN E1 THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION FROM PROC1 CATCHED.');
DBMS_OUTPUT.PUT_LINE('SQLCODE : '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
/

CREATE OR REPLACE PROCEDURE PROC2
AS
 E1 EXCEPTION;
BEGIN
 RAISE E1;
PROC1;
EXCEPTION
WHEN E1 THEN
PRINTLN('EXCEPTION FROM PROC1 CATCHED.');
PRINTLN('SQLCODE : '||SQLCODE);
PRINTLN('SQLERRM: ' || SQLERRM);
END;
/

Acquiring Error Code and Error Message from Altibase and Oracle.
Handling user-defined exceptions in Altibase, the error code is always 201232, which can be verified by
checking the value of SQLCODE.

CREATE OR REPLACE PROCEDURE PROC1
AS
 CODE INTEGER;
 ERRM VARCHAR(1000);
 
BEGIN
 INSERT INTO T1 VALUES(NULL, SYSDATE);
EXCEPTION
WHEN OTHERS THEN
 CODE := SQLCODE;
 ERRM := SUBSTR(ERRM, 1, 1000);
 
 INSERT INTO T2 VALUES (CODE, ERRM);
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS
 CODE INTEGER;
 ERRM VARCHAR(1000);
 
BEGIN
 INSERT INTO T1 VALUES(NULL, SYSDATE);
EXCEPTION
WHEN OTHERS THEN
 CODE := SQLCODE;
 ERRM := SUBSTR(ERRM, 1, 1000);
 
 INSERT INTO T2 VALUES (CODE, ERRM);
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS
salary_too_high EXCEPTION;
current_salary NUMBER := 20000;
max_salary NUMBER := 10000;
erroneous_salary NUMBER;
BEGIN
BEGIN ---------- sub-block begins
IF current_salary > max_salary THEN
RAISE salary_too_high; -- raise the exception
END IF;
EXCEPTION
WHEN salary_too_high THEN
-- first step in handling the error
DBMS_OUTPUT.PUT_LINE('Salary ' || erroneous_salary || ' is out of range.');
DBMS_OUTPUT.PUT_LINE('Maximum salary is ' || max_salary || '.');
RAISE; -- reraise the current exception
END; ------------ sub-block ends
EXCEPTION
WHEN salary_too_high THEN
-- handle the error more thoroughly
erroneous_salary := current_salary;
current_salary := max_salary;
DBMS_OUTPUT.PUT_LINE('Revising salary from ' || erroneous_salary ||
' to ' || current_salary || '.');
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS
salary_too_high EXCEPTION;
current_salary NUMBER := 20000;
max_salary NUMBER := 10000;
erroneous_salary NUMBER;
BEGIN
BEGIN ---------- sub-block begins
IF current_salary > max_salary THEN
RAISE salary_too_high; -- raise the exception
END IF;
EXCEPTION
WHEN salary_too_high THEN
-- first step in handling the error
SYSTEM_.PRINTLN('Salary ' || erroneous_salary || ' is out of range.');
SYSTEM_.PRINTLN('Maximum salary is ' || max_salary || '.');
RAISE; -- reraise the current exception
END; ------------ sub-block ends
EXCEPTION
WHEN salary_too_high THEN
-- handle the error more thoroughly
erroneous_salary := current_salary;
current_salary := max_salary;
SYSTEM_.PRINTLN('Revising salary from ' || erroneous_salary ||
' to ' || current_salary || '.');
END;
/

This example has a different result.
Discrepancy resulted from the difference of scope in those DBMSs.

ORACLE:
Salary  is out of range.
Maximum salary is 10000.
Revising salary from 20000 to 10000.
PL/SQL procedure successfully completed.

ALTIBASE:
Salary  is out of range.
Maximum salary is 10000.
[ERR-31157 : Unhandled exception : Unable to find exception name]

CREATE OR REPLACE PROCEDURE PROC1
AS
name employees.e_lastname%TYPE;
v_code NUMBER;
v_errm VARCHAR2(64);
BEGIN
SELECT e_lastname INTO name FROM employees
WHERE eno = -1;
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1 , 64);
DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
-- Normally we would call another procedure, declared with PRAGMA
-- AUTONOMOUS_TRANSACTION, to insert information about errors.
INSERT INTO errors VALUES (v_code, v_errm, SYSTIMESTAMP);
END;
/

CREATE OR REPLACE PROCEDURE PROC1
AS
name employees.e_lastname%TYPE;
v_code NUMBER;
v_errm VARCHAR2(64);
BEGIN
SELECT e_lastname INTO name FROM employees
WHERE eno = -1;
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1 , 64);
SYSTEM_.PRINTLN('Error code ' || v_code || ': ' || v_errm);
-- Normally we would call another procedure, declared with PRAGMA
-- AUTONOMOUS_TRANSACTION, to insert information about errors.
INSERT INTO errors VALUES (v_code, v_errm, SYSTIMESTAMP);
END;
/