Thursday, June 25, 2015

Oracle EXCEPTION_INIT PRAGMA

EXCEPTION_INIT PRAGMA
Using this you can associate a named exception with a particular oracle error. This gives you the ability to trap this error specifically, rather than via an OTHERS handler.
Syntax:
          PRAGMA EXCEPTION_INIT(exception_name, oracle_error_number);
Ex:
DECLARE
        e exception;
        pragma exception_init(e,-1476);
        c number;
BEGIN
        c := 5/0;
EXCEPTION
        when e then
                  dbms_output.put_line('Invalid Operation');
END;
Output:
Invalid Operation
RAISE_APPLICATION_ERROR
You can use this built-in function to create your own error messages, which can be more descriptive than named exceptions.
Syntax:
          RAISE_APPLICATION_ERROR(error_number, error_message,, [keep_errors_flag]);
The Boolean parameter keep_errors_flag is optional. If it is TRUE, the new error is added to the list of errors already raised. If it is FALSE, which is default, the new error will replace the current list of errors.
Ex:
DECLARE
        c number;
BEGIN
        c := 5/0;
EXCEPTION
        when zero_divide then
                  raise_application_error(-20222,'Invalid Operation');
END;
Output:
DECLARE
*
ERROR at line 1:
ORA-20222: Invalid Operation
ORA-06512: at line 7
EXCEPTION PROPAGATION
Exceptions can occur in the declarative, the executable, or the exception section of a PL/SQL block.
EXCEPTION RAISED IN THE EXECUATABLE SECTION
Exceptions raised in execuatable section can be handled in current block or outer block.
Ex1:
DECLARE
      e exception;
BEGIN
       BEGIN
          raise e;
       END;         
       EXCEPTION
          when e then
                    dbms_output.put_line('e is raised');
END;
Output:
e is raised
Ex2:
DECLARE
      e exception;
BEGIN
       BEGIN
          raise e;
        END;
END;
Output:
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 5
EXCEPTION RAISED IN THE DECLARATIVE SECTION
Exceptions raised in the declarative secion must be handled in the outer block.
Ex1:
DECLARE
      c number(3) := 'abcd';
BEGIN
      dbms_output.put_line('Hello');
EXCEPTION
      when others then
                dbms_output.put_line('Invalid string length');
END;
Output:
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
Ex2:
BEGIN
      DECLARE
           c number(3) := 'abcd';
      BEGIN
           dbms_output.put_line('Hello');
      EXCEPTION
           when others then
                     dbms_output.put_line('Invalid string length');
      END;
EXCEPTION
     when others then
               dbms_output.put_line('From outer block: Invalid string length');
END;
Output:
From outer block: Invalid string length
EXCEPTION RAISED IN THE EXCEPTION SECTION
Exceptions raised in the declarative secion must be handled in the outer block.
Ex1:
DECLARE
       e1 exception;
       e2 exception;
BEGIN
       raise e1;
EXCEPTION
       when e1 then
                 dbms_output.put_line('e1 is raised');
                 raise e2;
       when e2 then
                 dbms_output.put_line('e2 is raised');
END;
Output:
e1 is raised
DECLARE
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 9
ORA-06510: PL/SQL: unhandled user-defined exception
Ex2:
DECLARE
      e1 exception;
      e2 exception;
BEGIN
       BEGIN
           raise e1;
       EXCEPTION
           when e1 then
                     dbms_output.put_line('e1 is raised');
                     raise e2;
           when e2 then
                     dbms_output.put_line('e2 is raised');
        END;
EXCEPTION
       when e2 then
                 dbms_output.put_line('From outer block: e2 is raised');
END;
Output:
e1 is raised
From outer block: e2 is raised
Ex3:
DECLARE
       e exception;
BEGIN
       raise e;
EXCEPTION
       when e then
                 dbms_output.put_line('e is raised');
                 raise e;
END;
Output:
e is raised
DECLARE
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at line 8
ORA-06510: PL/SQL: unhandled user-defined exception
RESTRICTIONS
You can not pass exception as an argument to a subprogram

No comments:

Post a Comment