Total Pageviews

February 7, 2015

2/07/2015 02:16:00 PM


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 section.

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
          -
          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;
 
Related Posts Plugin for WordPress, Blogger...