raise exception ORA-00942
741034Jun 14 2010 — edited Jun 14 2010--How do I raise an exception for 6550 or 942? Here is the anonymous block that I'm running in SQL Developer which fails to raise an exception:
--(I might add that this is a self contained example; the table "image_masterr" does not exist)
--I'm running in 10.2.0.1
DECLARE
lcl_temp1 VARCHAR2(10);
v_error_code NUMBER;
v_error_message VARCHAR2(255);
no_table EXCEPTION;
no_table_942 EXCEPTION;
PRAGMA EXCEPTION_INIT(no_table, -06550);
PRAGMA EXCEPTION_INIT(no_table_942, -00942);
BEGIN
DECLARE
lcl_temp VARCHAR2(20);
BEGIN
v_error_code := SQLCODE;
v_error_message := SQLERRM;
SELECT count(1) INTO lcl_temp FROM image_masterr;
v_error_code := SQLCODE;
v_error_message := SQLERRM;
DBMS_OUTPUT.PUT_LINE('There is no problem '|| lcl_temp);
EXCEPTION
WHEN no_table OR no_table_942 THEN
DBMS_OUTPUT.PUT_LINE('There is a problem ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('There is a problem ');
END;
DBMS_OUTPUT.PUT_LINE('There is no problem :'||v_error_code ||v_error_message );
END;