Infolinks

Saturday 12 May 2012

PLSQL-ERROR HANDLING(EXCEPTIONS)


ERROR HANDLING

PL/SQL implements error handling with exceptions and exception handlers. Exceptions can be associated with oracle errors or with your own user-defined errors. By using exceptions and exception handlers, you can make your PL/SQL programs robust and able to deal with both unexpected and expected errors during execution.

ERROR TYPES
Ø  Compile-time errors
Ø  Runtime errors

Errors that occur during the compilation phase are detected by the PL/SQL engine and reported back to the user, we have to correct them.

Runtime errors are detected by the PL/SQL runtime engine which can programmatically raise and caught by exception handlers.
Exceptions are designed for run-time error handling, rather than compile-time error handling.

HANDLING EXCEPTIONS

When exception is raised, control passes to the exception section of the block. The exception section consists of handlers for some or all of the exceptions. An exception handler contains the code that is executed when the error associated with the exception occurs, and the exception is raised.

Syntax:
            EXCEPTION
                        When exception_name then
                                    Sequence_of_statements;
                        When exception_name then
                                    Sequence_of_statements;
                        When others then
                                    Sequence_of_statements;
            END;
EXCEPTION TYPES

Ø  Predefined exceptions
Ø  User-defined exceptions

PREDEFINED EXCEPTIONS

Oracle has predefined several exceptions that corresponds to the most common oracle errors. Like the predefined types, the identifiers of these exceptions are defined in the STANDARD package. Because of this, they are already available to the program, it is not necessary to declare them in the declarative secion.

Ex1:
DECLARE
        a number;
        b varchar(2);
        v_marks number;
        cursor c is select * from student;
        type t is varray(3) of varchar(2);
        va t := t('a','b');
        va1 t;
BEGIN
          -- NO_DATA_FOUND
          BEGIN
                        select smarks into v_marks from student where sno = 50;
                        EXCEPTION
                        when no_data_found then
                                   dbms_output.put_line('Invalid student number');
                        END;
                        -- CURSOR_ALREADY_OPEN
                        BEGIN
                         open c;
                         open c;
                        EXCEPTION
                         when cursor_already_open then
                                   dbms_output.put_line('Cursor is already opened');
                        END;

                        -- INVALID_CURSOR
                        BEGIN
                         close c;
                         open c;
                         close c;
                         close c;
                         EXCEPTION
                         when invalid_cursor then
                                   dbms_output.put_line('Cursor is already closed');
                         END;
                         -- TOO_MANY_ROWS
                         BEGIN
                         select smarks into v_marks from student where sno > 1;
                         EXCEPTION
                         when too_many_rows then
                                   dbms_output.put_line('Too many values are coming to marks
                                                                             variable');
                          END;
                          -- ZERO_DIVIDE
                          BEGIN
                           a := 5/0;
                          EXCEPTION
                           when zero_divide then
                                     dbms_output.put_line('Divided by zero - invalid operation');
                          END;
                          -- VALUE_ERROR
                          BEGIN
                           b := 'saketh';
                          EXCEPTION
                           when value_error then
                                     dbms_output.put_line('Invalid string length');
                          END;
                          -- INVALID_NUMBER
                          BEGIN
                           insert into student values('a','srinu',100);
                          EXCEPTION
                           when invalid_number then
                                     dbms_output.put_line('Invalid number');
                          END;

                          -- SUBSCRIPT_OUTSIDE_LIMIT
                          BEGIN
                           va(4) := 'c';
                          EXCEPTION
                           when subscript_outside_limit then
                                     dbms_output.put_line('Index is greater than the limit');
                          END;
                          -- SUBSCRIPT_BEYOND_COUNT
                          BEGIN
                           va(3) := 'c';
                          EXCEPTION
                           when subscript_beyond_count then
                                     dbms_output.put_line('Index is greater than the count');
                          END;
                          -- COLLECTION_IS_NULL
                          BEGIN
                           va1(1) := 'a';
                          EXCEPTION
                           when collection_is_null then
                                     dbms_output.put_line('Collection is empty');
                          END;
                     --
           END;

Output:
Invalid student number
Cursor is already opened
Cursor is already closed
Too many values are coming to marks variable
Divided by zero - invalid operation
Invalid string length
Invalid number
Index is greater than the limit
Index is greater than the count
Collection is empty



Ex2:
DECLARE
        c number;
BEGIN
         c := 5/0;
EXCEPTION
         when zero_divide then
                   dbms_output.put_line('Invalid Operation');
         when others then
                   dbms_output.put_line('From OTHERS handler: Invalid
                                                              Operation');
END;

Output:
Invalid Operation

USER-DEFINED EXCEPTIONS

A user-defined exception is an error that is defined by the programmer. User-defined exceptions are declared in the declarative secion of a PL/SQL block. Just like variables, exeptions have a type EXCEPTION and scope.

RAISING EXCEPTIONS

User-defined exceptions are raised explicitly via the RAISE statement.

Ex:
DECLARE
       e exception;
BEGIN
       raise e;
EXCEPTION
       when e then
                 dbms_output.put_line('e is raised');
END;
Output:
e is raised
BULIT-IN ERROR FUNCTIONS

SQLCODE AND SQLERRM

Ø  SQLCODE returns the current error code, and SQLERRM returns the current error message text;
Ø  For user-defined exception SQLCODE returns 1 and SQLERRM returns “user-deifned exception”.
Ø  SQLERRM wiil take only negative value except 100. If any positive value other than 100 returns non-oracle exception.

Ex1:
DECLARE
         e exception;
         v_dname varchar(10);
BEGIN
           -- USER-DEFINED EXCEPTION
           BEGIN
               raise e;
           EXCEPTION
               when e then
                         dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
           END;

           -- PREDEFINED EXCEPTION
          BEGIN
              select dname into v_dname from dept where deptno = 50;
          EXCEPTION
              when no_data_found then
                        dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
          END;
END;

Output:
1 User-Defined Exception
100 ORA-01403: no data found


Ex2:
BEGIN
       dbms_output.put_line(SQLERRM(100));
       dbms_output.put_line(SQLERRM(0));
       dbms_output.put_line(SQLERRM(1));
       dbms_output.put_line(SQLERRM(-100));
       dbms_output.put_line(SQLERRM(-500));
       dbms_output.put_line(SQLERRM(200));
       dbms_output.put_line(SQLERRM(-900));
END;
Output:
ORA-01403: no data found
ORA-0000: normal, successful completion
User-Defined Exception
ORA-00100: no data found
ORA-00500: Message 500 not found;  product=RDBMS; facility=ORA
-200: non-ORACLE exception
ORA-00900: invalid SQL statement

DBMS_UTILITY.FORMAT_ERROR_STACK

Ø  The built-in function, like SQLERRM, returns the message associated with the current error.
Ø  It differs from SQLERRM in two ways:
Ø  Its length is not restricted; it will return the full error message string.
Ø  You can not pass an error code number to this function; it cannot be used to return the message for a random error code.

Ex:
DECLARE
     v number := 'ab';
BEGIN
     null;
EXCEPTION
     when others then
               dbms_output.put_line(dbms_utility.format_error_stack);
END;
Output:
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2

DBMS_UTILITY.FORMAT_CALL_STACK

This function returns a formatted string showing the execution call stack inside your PL/SQL application. Its usefulness is not restricted to error management; you will also find its handy for tracing the exectution of your code. You may not use this function in exception block.

Ex:
BEGIN
     dbms_output.put_line(dbms_utility.format_call_stack);
END;

Output:
----- PL/SQL Call Stack -----
  Object_handle      line_number  object_name
       69760478                 2           anonymous block

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

It displays the execution stack at the point where an exception was raised. Thus , you can call this function with an exception section at the top level of your stack and still find out where the error was raised deep within the call stack.

Ex:
CREATE OR REPLACE PROCEDURE P1 IS
BEGIN
     dbms_output.put_line('from procedure 1');
     raise value_error;
END P1;
CREATE OR REPLACE PROCEDURE P2 IS
BEGIN
     dbms_output.put_line('from procedure 2');
     p1;
END P2;

CREATE OR REPLACE PROCEDURE P3 IS
BEGIN
     dbms_output.put_line('from procedure 3');
     p2;
EXCEPTION
     when others then
               dbms_output.put_line(dbms_utility.format_error_backtrace);
END P3;

Output:
SQL> exec p3

from procedure 3
from procedure 2
from procedure 1
ORA-06512: at "SAKETH.P1", line 4
ORA-06512: at "SAKETH.P2", line 4
ORA-06512: at "SAKETH.P3", line 4

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 section 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.
Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)