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


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Oracle Unexpected Errors
Oracle Tips by Burleson
 

If an error occurs during the execution of an object, Oracle raises an exception and generates the most appropriate error text.

If the error message corresponds to one of the predefined exceptions that PL/SQL uses, the error can be handled using an exception handler for the predefined exception. Table 4.2 lists these predefined exceptions and their associated Oracle error message numbers.

Table 4.2 Predefined exceptions in PL/SQL.

Exception

Associated Oracle Error

CURSOR_ALREADY_OPEN

ORA-06511

DUP_VAL_ON_INDEX

ORA-00001

INVALID_CURSOR

ORA-01001

INVALID_NUMBER

ORA-01722

LOGIN_DENIED

ORA-01017

NO_DATA_FOUND

ORA-01403

NOT_LOGGED_ON

ORA-01012

PROGRAM_ERROR

ORA-06501

STORAGE_ERROR

ORA-06500

TIMEOUT_ON_RESOURCE

ORA-00051

TOO_MANY_ROWS

ORA-01422

TRANSACTION_BACKED_OUT

ORA-00061

VALUE_ERROR

ORA-06502

ZERO_DIVIDE

ORA-01476

You can re-declare these predefined exceptions and create custom handlers for your new exceptions, but Oracle will not recognize your new exception when it attempts to raise one of the predefined exceptions. Your best bet is to leave the predefined exceptions alone and create your own user-defined exceptions.

While the predefined exceptions are quite useful and deal with the most commonly encountered error conditions quite well, itís not uncommon for other errors to be encountered as well. To allow you to handle specified errors that donít have a predefined exception, Oracle has provided the OTHERS exception handler.

Listing 4.21 illustrates the use of an OTHERS exception handler.

Listing 4.21 Using the OTHERS exception handler.

BEGIN
  <statements>
 
EXCEPTION
  WHEN OTHERS THEN
      <error handling code>
END;

The code that follows the OTHERS exception handler is written to handle errors that you donít expect. Often, this code is a call to another procedure that logs a message to a table containing specific information about the error (the text of the Oracle error, the parameters of the procedure or function in which the error occurred, and any other relevant information).

You can also use the OTHERS exception handler in conjunction with the SQLERRM() and SQLCODE() functions that PL/SQL provides, as shown in Listing 4.22.

Listing 4.22 Using SQLCODE() and SQLERRM() in an OTHERS exception handler.

BEGIN
  <statements>
 
EXCEPTION
  WHEN OTHERS THEN
      IF (SQLCODE = -942) THEN
        DBMS_Output.Put_Line (SQLERRM);
 
      ELSE
        RAISE;
      END IF;
END;

This example calls the SQLCODE() function to determine what error is occurring and SQLERRM() to record the text of the error message.

 

         
This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

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

 

   

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

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter