Exception handling in Oracle

Here is sample code you can use in Oracle Procedure, Package, Function or Trigger.

SQLERRM is a oracle keyword and contains the error message, you need to store this message for debugging purpose. SQLCODE is also a keyword and used to find out error details. There are number of ways you can store error message.

1. Store in Global Temporary Table
2. Return Values either Through Cursor or String.
3. use dbms_output.put_line( v_Error)

As per my Experience, Global Temporary table works well.

 

Code Snippet

1. DECLARE
2.     N_COUNT  number := 0;
3.     v_Error varchar2(4000) := '';
4.     BEGIN
5.  
6.        --Body Part Logic
7.  
8.     EXCEPTION
9.        WHEN OTHERS THEN
10.            v_Error := sqlerrm;  
11.            v_code := SQLCODE;     
12.            
13.     END;

 

Handling Specific Exceptions:

 

Code Snippet

1. BEGIN
2.  
3.     EXCEPTION
4.       WHEN ZERO_DIVIDE THEN
5.         dbms_output.put_line('Failure : ' || sqlerrm);
6.     END;

 

Predefined (Named) PL/SQL Exceptions
Exception Name	Error	Description
ACCESS_INTO_NULL	ORA-06530	Attempted to assign values to the attributes of an uninitialized (NULL) object
CASE_NOT_FOUND	ORA-06592	None of the choices in the WHEN clauses of a CASE statement is selected and there is no ELSE clause
COLLECTION_IS_NULL	ORA-06531	Attempt to apply collection methods other than EXISTS to an uninitialized (NULL) PL/SQL table or varray
CURSOR_ALREADY_OPEN	ORA-06511	Exactly what it seems to be. Tried to open a cursor that was already open
DUP_VAL_ON_INDEX	ORA-00001	An attempt to insert or update a record in violation of a primary key or unique constraint
INVALID_CURSOR	ORA-01001	The cursor is not open or not valid in the context in which it is being called
INVALID_NUMBER	ORA-01722	It isn't a number even though you are treating it like one to trying to turn it into one
LOGIN_DENIED	ORA-01017	Invalid name and/or password for the instance
NO_DATA_FOUND	ORA-01403	The SELECT statement returned no rows or referenced a deleted element in a nested table or referenced an initialized element in an Index-By table
NOT_LOGGED_ON	ORA-01012	Database connection lost
PROGRAM_ERROR	ORA-06501	Internal PL/SQL error
ROWTYPE_MISMATCH	ORA-06504	The rowtype does not match the values being fetched, or assigned, to it
SELF_IS_NULL	ORA-30625	Program attempted to call a MEMBER method, but the instance of the object type has not been intialized. The built-in parameter SELF points to the object, and is always the first parameterpassed to a MEMBER method
STORAGE_ERROR	ORA-06500	A hardware problem: Either RAM or disk drive
SUBSCRIPT_BEYOND_COUNT	ORA-06533	Reference to a nested table or varray index higher than the number of elements in the collection
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	Reference to a nested table or varray index outside the declared range (such as -1)
SYS_INVALID_ROWID	ORA-01410	The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid
TIMEOUT_ON_RESOURCE	ORA-00051	The activity took too long and timed out
TOO_MANY_ROWS	ORA-01422	The SQL INTO statement brought back more than one value or row (only one is allowed)
USERENV_COMMITSCN_ERROR	ORA-01725	Added for USERENV enhancement, bug 1622213
VALUE_ERROR	ORA-06502	An arithmetic, conversion, truncation, or size-constraint error. Usually raised by trying to cram a 6 character string into a VARCHAR2(5) variable
ZERO_DIVIDE	ORA-01476	Not only would your math teacher not let you do it. Computer's won't either. Who said you didn't learn anything useful in primary school