Discover PL/SQL: Handling PL/SQL Errors

These are my notes from the Oracle documentation on Handling PL/SQL Errors.

1 Overview of PL/SQL Runtime Error Handling
- 1.1 Avoiding and Handling Errors
2 Advantages of PL/SQL Exceptions
3 Summary of Predefined PL/SQL Exceptions
4 Defining Your Own PL/SQL Exceptions
5 How PL/SQL Exceptions Are Raised
- 5.1 Raising Exceptions with the RAISE Statement
6 How PL/SQL Exceptions Propagate
7 Reraising a PL/SQL Exception
8 Handling Raised PL/SQL Exceptions
9 Overview of PL/SQL Compile-Time Warnings

1 Overview of PL/SQL Runtime Error Handling

	Predefined (by the system)	User-defined
Named	Already named (ZERO_DIVIDE) or Unnamed, but can be named	Must be named
Raised	Automatically by the system or with RAISE	With RAISE

Top

1.1 Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions

Look for errors here:

string manipulation

memory error

database operations

math

bad/null input data

query returns > 1 row

query returns 0 rows

disk storage

a table was changed and %[ROW]TYPE was not used

Decide whether to commit or rollback

Always have WHEN OTHERS, but use as a last resort

/* Write out debugging information in your exception handlers. You might store such information in a separate table. If so, do it by making a call to a procedure declared with the PRAGMA AUTONOMOUS_TRANSACTION, so that you can commit your debugging information, even if you rollback the work that the main procedure was doing. -- (Investigate & study this) this */
Top

2 Advantage of PL/SQL Exceptions

Handles errors from many statements with a single exception handlerTop

3 Summary of Predefined PL/SQL Exceptions

Exception Name	SQLCode (ORA- if different)	Description
Access_Into_Null	-6530	A program attempts to assign values to the attributes of an uninitialized object.
Case_Not_Found	-6592	No Else and no When works
Collection_Is_Null	-6531	Collection methods other than EXISTS are applied, or values assigned to the elements of an uninitialized nested table or varray.
Cursor_Already _Open	-6511	An attempt to open an opened cursor or a cursor in a FOR loop.
Dup_Val_On_Index	1	Attempts to store duplicate values constrained by a unique index.
Invalid_Cursor	-1001	i.e., closing an unopened cursor, etc.
Invalid_Number	-1722	Raised from within SQL. (In PL/Sql, Value_Error is raised.) or when a bulk FETCH's LIMIT-clause < 0
Login_Denied	-1017	Invalid username or password
No_Data_Found	+100 (-01403)	A SELECT INTO returns no rows, or a reference to a nested tables' deleted element or an uninitialized element in an index-by table. This exception is used internally by some SQL functions to signal that they are finished so don't rely on this exception being propagated if you raise it within a function that is called as part of a query.
Not_Logged_On	-1012	A database call issued without being connected to Oracle.
Program_Error	-6501	(contact Oracle support) PL/SQL has an internal problem.
Rowtype_Mismatch	-6504	The host and PL/SQL cursor variables involved in an assignment have incompatible return types. i.e., when a variable is passed to a subprogram.
Self_Is_Null	-30625	A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBER method.
Storage_Error	-6500	(ran out of memory or memory corrupted)
Subscript_ Beyond_Count	-6533	A nested table is referenced or index number larger > collection.
Subscript_ Outside_Limit	-6532	A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
Sys_Invalid _Rowid	-1410	The character string does not represent a valid rowid.
Timeout_On_ Resource	-51	A time-out occurs while Oracle is waiting for a resource.
Too_Many_Rows	-1422	A SELECT INTO statement returns more than one row.
Value_Error	-6502	An arithmetic, conversion, truncation, or size-constraint error occurs. (For SQL see Invalid_Number)
Zero_Divide	-1476	A program attempts to divide a number by zero.

Top

4 Defining Your Own PL/SQL Exceptions

4.1 Declaring PL/SQL Exceptions

DECLARE
Error1 EXCEPTION;

Top

4.2 Scope Rules for PL/SQL Exceptions

You can declare the same exception in two different blocks.

They are local to the block in which they are declared and global to all its sub-blocks

The sub-block cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label: block_label.exception_name

DECLARE
   Error1 EXCEPTION;
BEGIN
   ---------- sub-block begins--------------------------
   DECLARE
      Error1    EXCEPTION;  -- this overrides previous
   BEGIN
      IF 2 < 1 THEN RAISE Error1;
      END IF;
   END;  
   ------------- sub-block ends ------------------------
EXCEPTION
  /*----------------------------------------------*/
  /* Enclosing blocks cannot reference exceptions */
  /* declared in a sub-block                      */
  /*----------------------------------------------*/
  WHEN Error1 
  THEN       DBMS_OUTPUT.PUT_LINE('Cannot handle');
  WHEN OTHERS  
  THEN       DBMS_OUTPUT.PUT_LINE('Error1 not seen');
END;
/

Top

4.3 Associating a PL/SQL Exception with a Number

To handle unnamed ORA- messages, use
PRAGMA EXCEPTION_INIT(defined_exception , -Oracle_error#);

DECLARE
   Error1 EXCEPTION; 
   PRAGMA EXCEPTION_INIT(Error1, -60);
BEGIN
   -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN Error1 THEN
      -- handle the error
END;
/

Top

4.4 Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR( -20nnn, message);

DECLARE
  num_tables  NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO num_tables 
    FROM USER_TABLES;
     
  IF num_tables < 1000 THEN
     Raise_Application_Error(-20101,'Need 1000 tables');
  END IF;
END;
/
DECLARE
*
ERROR at line 1:
ORA-20101: need 1000 tables
ORA-06512: at line 9

Top

4.5 Redeclaring Predefined Exceptions

-- Don't do it

Top

5 How PL/SQL Exceptions Are Raised

Implicitly
- Internal (Ora-)
- User-defined exceptions set up through EXCEPTION_INIT
Explicitly ('RAISE')
- If user-defined & not associated with an error #
- If a predefined should process other errors

Top

5.1 Raising Exceptions with the RAISE Statement

DECLARE
   Error1 EXCEPTION;  
BEGIN
   IF 0 < 1 THEN RAISE Error1; /* user-defined */
   END IF; 
 
   IF 7 > 9 THEN RAISE INVALID_NUMBER;                        /* predefined  */
   END IF; 
EXCEPTION
   WHEN Error1        
   THEN               DBMS_OUTPUT.PUT_LINE('Error 1'  );

   WHEN INVALID_NUMBER
   THEN               DBMS_OUTPUT.PUT_LINE('Invalid #');
                      ROLLBACK;
END;
/

Top

6 How PL/SQL Exceptions Propagate

If a handler can't be found in the current block, the exception propagates*, but the enclosing block may not be able to reference the name based on where it was declared.

*reproduces itself in successive enclosing blocks until a handler is found, and finally to the host environment if no handler found.

BEGIN
   ---------- sub-block begins-------------------------
   DECLARE  
     sub_err EXCEPTION;
   BEGIN
     IF 1 < 2 THEN RAISE sub_err;
     END IF;
   END;  
   ------------- sub-block ends------------------------
EXCEPTION
   WHEN OTHERS THEN ROLLBACK;
END;
/

Top

7. Reraising* a PL/SQL Exception

* handle locally & then pass along

DECLARE
  ErrNum EXCEPTION;
BEGIN
  ---------- sub-block begins -------------------------
  BEGIN  
    IF 1 < 2 THEN RAISE ErrNum; END IF;
  EXCEPTION
    WHEN ErrNum THEN DBMS_Output.Put_Line('Some Stuff');
                     /*--------------------------*/
                     /* Raise to enclosing block */
                     /*--------------------------*/
                     RAISE;  
  END;
  ------------ sub-block ends -------------------------
EXCEPTION
  WHEN ErrNum THEN DBMS_Output.Put_Line('More Stuff');
END;
/
Some Stuff
More Stuff

PL/SQL procedure successfully completed.

Top

8 Handling Raised PL/SQL Exceptions

Control does not return to where the exception was raised so processing is not resumed.

OTHERS is always the last handler in a block, and handles all exceptions not specifically named.

EXCEPTION
  WHEN exception1 
    OR exception2 
    OR VALUE_ERROR THEN
                       -- handle the error

Top

8.1 Exceptions Raised in Declarations

Exceptions raised in a declaration propagates immediately to the enclosing block.

Top

8.2 Handling Exceptions Raised in Handlers

Exceptions raised in a handler propagates immediately to the enclosing block.

Top

8.3 Branching to or from an Exception Handler

You can use a GOTO only from a handler into an enclosing block.

Top

8.4 Retrieving the Error Code and Error Message

SQLERRM: maximum length is 512 (includes code, message, and table/column name inserts)

You cannot use SQLCODE or SQLERRM directly in a SQL statement.

Create Table Errors ( Code     Number
                    , Message  Varchar2(64)
                    , Happened Timestamp
                    ) ;
DECLARE
   vSqlCode NUMBER;
   vSqlErrM VARCHAR2(64);
BEGIN
   some executable statements
EXCEPTION
   WHEN OTHERS 
   THEN       vSqlCode := SqlCode;
              vSqlErrM := Substr(SqlErrM, 1 ,64);
              Dbms_Output.Put_Line('Code '||vSqlCode 
                                || ': '   ||vSqlErrM);
--??---------------------------------------------------
--?? Normally we would call another procedure, declared
--?? with PRAGMA AUTONOMOUS_TRANSACTION, to insert 
--?? information about errors. -- to be investigated
--??---------------------------------------------------
                    Insert Into Errors Values 
                    ( vSqlCode
                    , vSqlErrM
                    , SysTimestamp
                    );
End;
/

Top

8.5 Catching Unhandled Exceptions

Avoid by always coding OTHERS at the topmost level.
Top

8.6 Tips for Handling PL/SQL Errors

To continue with the next statement, place a statement in a sub-block with an exception handler.

Top

8.6.1 Continuing after an Exception Is Raised

The statement 'Print this 2nd' is never reached as an error is implicitly raised by dividing by zero.

Declare
   Calc Number(8);
Begin
   Dbms_Output.Put_Line('Print this 1st.');
   Calc := 200/0;
   Dbms_Output.Put_Line('Print this 2nd.');
Exception
   When Zero_Divide 
   Then           Dbms_Output.Put_Line('0 Divide Err');
End;
/
Print this 1st.
0 Divide Err

PL/SQL procedure successfully completed.

This is solved by putting an error-prone statement in a sub-block with an exception handler. If it fails, then it passes back out to the enclosing block, which continues with the next statement.

Declare
   Calc Number(8);
Begin
   Dbms_Output.Put_Line('Print this 1st.');
   ----- sub-block begins -----------------------------
   Begin
      Calc := 200/0;
   Exception
      When Zero_Divide 
      Then            Dbms_Output.Put_Line
                         ('Handle here then continue');
    End;
    ----- sub-block ends ------------------------------
    Dbms_Output.Put_Line('Print this 2nd.');
Exception
   When Zero_Divide 
   Then           Dbms_Output.Put_Line('0 Divide Err');
End;
/
Print this 1st.
Handle here then continue
Print this 2nd.

PL/SQL procedure successfully completed.

/* ?? You can also perform a sequence of DML operations where some might fail, and process the exceptions only after the entire operation is complete, as described in "Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute". -- Investigate */

Top

8.6.2 Retrying a Transaction

Encase the transaction in a sub-block.
Place the sub-block inside a For/While loop to limit tries
First, SAVEPOINT.
If transaction...
1. succeeds: commit and exit loop.
2. fails: in handler rollback to savepoint, then fix the problem.

Drop Table Names Purge;
Create Table Names ( Name Varchar2(8) Primary Key );

Declare
   vName    Varchar2(20) := '&Name';
   vCount   Number;
Begin
   For I In 1..5 Loop                            /*#2*/
      -- sub-block begins------------------------/*#1*/
      Begin  
         Savepoint StartHere;                    /*#3*/
         /*-----------------------------------------*/
         /* Raises Dup_Val_On_Index Since Same Name */
         /*-----------------------------------------*/
         Insert Into Names Values (vName); 
         Commit;                               /*#4.1*/
         Exit;                                 /*#4.1*/
      Exception
         When Dup_Val_On_Index                 /*#4.2*/
         Then                 Rollback To StartHere;
                              vCount := vCount + 1;
                              vName  := vName || 
                                       To_Char(vCount);
      End;  
      -- sub-block ends--------------------------------
   End Loop;
End;
/

Top

8.6.3 Using Locator Variables to Identify Exception Locations

If you need to know which statement failed:

DECLARE
   stmt_no NUMBER;
   name    VARCHAR2(100);
BEGIN
   stmt_no := 1;
   -- some executable statement -- 
   stmt_no := 2;
   -- some executable statement -- 
EXCEPTION
   WHEN NO_DATA_FOUND 
   THEN              DBMS_OUTPUT.PUT_LINE(
                                'Error on '||stmt_no);
END;
/

Top

9 Overview of PL/SQL Compile-Time Warnings

See the USER/DBA/ALL_PLSQL_OBJECT_SETTINGS views.
Top

9.1 PL/SQL Warning Categories

SEVERE:: something could cause unexpected behavior or wrong results
PERFORMANCE:: i.e., passing character to number column while inserting
INFORMATIONAL:: Things you might want to change (i.e., unreachable code)
ALL

Top

9.2 Controlling PL/SQL Warning Messages

This parameter can be set

at the system level

or the session level

a single compilation (ALTER PROCEDURE...COMPILE)

Alter Session Set PLSql_Warnings='Disable:All';        
Alter Session Set PLSql_Warnings='Enable:All';         
Alter Session Set PLSql_Warnings='Enable:Performance'; 
Alter Session Set PLSql_Warnings='Enable:Severe'
                    , 'Disable:Performance'
                    , 'Error:06002'; -- PLW-06002 will halt compilation

Alter Procedure Loc_Var Compile 
        PLSql_Warnings='Enable:Performance' 
        Reuse Settings; -- Recompile With Extra Checking

Yes: PL/SQL subprograms
No: anonymous blocks

View with SHOW ERRORS or Select * from USER_ERRORS.
These have a PLW prefix.
Top

9.3 Using the DBMS_WARNING Package

CREATE OR REPLACE PROCEDURE unreachable_code AS
  x Number := 1;
BEGIN
  IF x = 1 THEN DBMS_OUTPUT.PUT_LINE(1);
  END IF;
END unreachable_code;
/
Procedure created.

SQL> /*---------------------------------------------------*/
SQL> /* enable all warning messages for this session      */
SQL> /*---------------------------------------------------*/
SQL> CALL DBMS_WARNING.set_warning_setting_string(
                              'ENABLE:ALL' ,'SESSION');

Call completed.

SQL> /*---------------------------------------------------*/
SQL> /* Check the current warning setting                 */
SQL> /*---------------------------------------------------*/
SQL> SELECT DBMS_WARNING.get_warning_setting_string()
       FROM DUAL;

DBMS_WARNING.GET_WARNING_SETTING_STRING()
---------------------------------------------------------------------------
ENABLE:ALL

SQL> /*---------------------------------------------------*/
SQL> /* Recompile the procedure and a warning about       */
SQL> /* unreachable code displays                         */
SQL> /*---------------------------------------------------*/
SQL> ALTER PROCEDURE unreachable_code COMPILE;

SP2-0805: Procedure altered with compilation warnings

SQL> SHOW ERRORS;
Errors for PROCEDURE UNREACHABLE_CODE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/8      PLW-06002: Unreachable code

SQL> Alter Procedure Unreachable_Code Compile
          Plsql_Warnings = 'Enable:All' Reuse Settings;

Top

Discover PL/SQL

Sunday, April 12, 2009

Handling PL/SQL Errors