Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Exception Handling in PL/SQL

In any procedural language, it is critical to remember that the programs are a complete and separate entity from the database.  Hence, whenever the program requests rows from the database, the programmer must make sure that the request completed successfully.

In relational databases, the database will always pass a special variable called SQLCODE back to any calling program.  The SQLCODE returned from reach call from the program to the database is translated by Oracle into a named Boolean variable (See table below).

PL/SQL Exception Variable 

Oracle Error 

SQLCODE Value 

ACCESS_INTO_NULL 

ORA-06530 

-6530 

CASE_NOT_FOUND 

ORA-06592 

-6592 

COLLECTION_IS_NULL 

ORA-06531 

-6531 

CURSOR_ALREADY_OPEN 

ORA-06511 

-6511 

DUP_VAL_ON_INDEX 

ORA-00001 

-1 

INVALID_CURSOR 

ORA-01001 

-1001 

INVALID_NUMBER 

ORA-01722 

-1722 

LOGIN_DENIED 

ORA-01017 

-1017 

NO_DATA_FOUND 

ORA-01403 

+100 

NOT_LOGGED_ON 

ORA-01012 

-1012 

PROGRAM_ERROR 

ORA-06501 

-6501 

ROWTYPE_MISMATCH 

ORA-06504 

-6504 

SELF_IS_NULL 

ORA-30625 

-30625 

STORAGE_ERROR 

ORA-06500 

-6500 

SUBSCRIPT_BEYOND_COUNT 

ORA-06533 

-6533 

SUBSCRIPT_OUTSIDE_LIMIT 

ORA-06532 

-6532 

SYS_INVALID_ROWID 

ORA-01410 

-1410 

TIMEOUT_ON_RESOURCE 

ORA-00051 

-51 

TOO_MANY_ROWS 

ORA-01422 

-1422 

VALUE_ERROR 

ORA-06502 

-6502 

ZERO_DIVIDE 

ORA-01476 

-1476 

For example, if the database returns a SQLCODE=100, the PL/SQL variable NO_DATA_FOUND will be set to TRUE.

Without exception, all PL/SQL programs should be made to abort whenever an unexpected SQLCODE is returned by the Oracle database.  There have been many cases where an improperly configured PL/SQL program failed to abort when the Oracle database returns a non-zero SQL code.  This mistake causes the program to think that it has successfully accessed the database, when in reality, the operation never completed. 

This can have a disastrous effect on the database, especially when the PL/SQL loads data into tables based upon false premises.  To prevent this tragedy, Oracle provides a WHEN OTHERS variable, which is set to TRUE if any unexpected SQLCODE is returned from the Oracle database.

For example, consider the following code:

DECLARE
   err_num NUMBER;
   err_msg VARCHAR2(100);
BEGIN
   ...
EXCEPTION
   ...
   WHEN OTHERS THEN
      err_num := SQLCODE;
      err_msg := SUBSTR(SQLERRM, 1, 100);
      INSERT INTO errors VALUES (err_num, err_msg);
END;

Here we see that our EXCEPTIONS area tests WHEN OTHERS.  If the WHEN OTHERS Boolean variable is TRUE, the PL/SQL code captures the SQLCODE and the associated error message (SQLERRM), and stores these values into a special Oracle errors table.


For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.