Oracle_Exceptions

Oracle Exception Handling

Concepts
General Syntax
Pre-Defined Exceptions
Non-predefined Oracle server exceptions
User-defined Exceptions
User-defined errors and EXCEPTION_INIT
Exception Trapping Functions: SQLCODE and SQLERRM , DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Raise-Application Errors
More Examples

Concepts
In PL/SQL, a warning or error condition is called an exception. Exceptions can be internally defined by the runtime system (pre-defined) or user defined. A runtime error such as stack overflow or division by zero, stops normal processing and returns control to the operating system. In PL/SQL, exception handling mechanism lets the user trap such conditions, so that it can continue operating in the presence of errors.
When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.

Exceptions can be trapped in the EXCEPTION section of a PL/SQL block. Oracle supports two ways to raise exception in a program, implicitly or explicitly.

Exceptions which are automatically raised by the oracle server fall under the category of implicit way raising an exception. PL/SQL runtime engine identifies the abnormal flow and raises the exception. For example, NO_DATA_FOUND or TOO_MANY_ROWS are the system defined exceptions which are raised by the server during program execution.

Exceptions which are trapped in executable section and handled in the EXCEPTION block by the programmer are explicit ways raising exceptions. In this category, a user can either explicitly raise an already existing system defined exception or create a new exception and invoke in the program.

General Syntax
EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
    statement1;
    statement2;
    . . .
[WHEN exception3 [OR exception4 . . .] THEN
    statement1;
    statement2;
    . . .]
[WHEN OTHERS THEN
    statement1;
    statement2;
    . . .]

In the syntax, a single WHEN-THEN statement is called as Exception Handler. Likewise, there can be multiple exception handlers in the EXCEPTION section of a PL/SQL block. An exception handler consists of exception name and set of statements, which would be executed once the exception has been raised. An exception handler can have more than one exception aligned using OR operator. So, the same action would be applicable for multiple exceptions.
Out of multiple exception handlers, only one can be executed at a time before the termination of the block.
One exception handler handles a single exception. Repetitive handling of an exception is not allowed in a single block.

Pre Defined Exceptions
Oracle maintains set of defined exceptions, which are implicitly raised by the server, if an abnormal situation occurs in the program. The table below lists some of the commonly occurring exceptions.

Oracle Exception Name	Oracle Error	Explanation
DUP_VAL_ON_INDEX	ORA-00001	Unique constraint violation. This means you've used constraints or indexes to restrict entry of duplicate records, but then gone and tried to insert a duplicate record, at least as far as the record's keys are concerned.
TIMEOUT_ON_RESOURCE	ORA-00051	A time-out occurred while waiting on a resource. This usually means that there was some sort of unusual termination of an Oracle instance.
TRANSACTION_BACKED_OUT	ORA-00061	The remote portion of a transaction has rolled back.
INVALID_CURSOR	ORA-01001	The cursor does not yet exist. The cursor must be OPENed before any FETCH cursor or CLOSE cursor operation.
NOT_LOGGED_ON	ORA-01012	You are not logged on.
LOGIN_DENIED	ORA-01017	Invalid username/password.
NO_DATA_FOUND	ORA-01403	This exception is returned when your select statement returned zero rows. NOTE that an update statement will not throw this exception. Instead, query the sql%notfound and the sql%rowcount variables to determine the result of your update statements
TOO_MANY_ROWS	ORA-01422	A SELECT ... INTO query matched more than one row. That is, a select that was supposed to return a single row returned more than one row.
ZERO_DIVIDE	ORA-01476	Divide by zero error.
INVALID_NUMBER	ORA-01722	This exception occurs when you try to convert a string to a number, and the string doesn't contain a valid number.
STORAGE_ERROR	ORA-06500	Occurs when PL/SQL itself has run out of memory, it needs more than is available, or if there was some sort of corruption error.
PROGRAM_ERROR	ORA-06501	This exception is thrown when the PL/SQL interpreter itself encounters an error while processing your code.
VALUE_ERROR	ORA-06502	You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.
ROWTYPE_MISMATCH	ORA-06504	You'll get this exception when a host cursor variable and the PL/SQL cursor variable you're fetching into have incompatible types.
CURSOR_ALREADY_OPEN	ORA-06511	You attempted to open a cursor that was already open
ACCESS_INTO_NULL	ORA-06530	Attempt to assign values to the attributes of a NULL object
COLLECTION_IS_NULL	ORA-06531	An attempt was made to apply collection methods other than EXISTS to a NULL PL/SQL table or varray
SELF_IS_NULL		Occurs when an attempt to call a MEMBER method is made on a null instance.
SUBSCRIPT_BEYOND_COUNT		Means you tried to index off the end of a varray or nexted table. The index you used was higher than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT		Reference to a nested table or varray index outside the declared range -- such as an index of -1.
SYS_INVALID_ROW		This occurs when the character string used to represent the rowID fails because the character string doesn't represent a valid row ID.

Non-predefined Oracle server exceptions
These exceptions are system exceptions, Oracle will raise the exception if an error occurs.
Since the exception is not named, you cannot raise it explicitly, and must reference it by error code in the exception handler.
The most common examples of an unnamed system exception are:

Oracle Error	Explanation
ORA-02291	Integrity constraint violation. Occurs when attempting to insert a child record for which no parent record exists.
ORA-00292	Integrity constraint violation. Occurs when attempting to delete a parent record for which children records exist.

Example:
BEGIN
DELETE FROM dept
    WHERE deptno = 20;
EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    IF SQLCODE=-2292 THEN
      DBMS_OUTPUT.PUT_LINE('ORA Error Code: ' || SQLCODE );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || SQLERRM );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || DBMS_UTILITY.FORMAT_ERROR_STACK );
      DBMS_OUTPUT.PUT_LINE('Line containing Error: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

      DBMS_OUTPUT.PUT_LINE( 'Can not delete Department.');
      DBMS_OUTPUT.PUT_LINE( 'Delete Employees for Department first.');
    END IF;
END;
/

ORA Error Code: -2292
ORA Error Message: ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated -
child record found
ORA Error Message: ORA-02292: integrity constraint (SCOTT.FK_DEPTNO) violated -
child record found

Line containing Error: ORA-06512: at line 2

Can not delete department.
Delete employees for department first.

User-Defined Exceptions
Because this type of exception is one that is defined by you, you must detect the error condition and raise the exception explicitly.
Since the exception is named, you can reference it by name.
You can define any number of exceptions relevant to the block. The scope of user-defined exceptions is limited to the block.
An example PL/SQL block using user-defined exception:

DECLARE
e_security EXCEPTION;
v_ename emp.ename%TYPE:='KING';
v_emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO v_emp_rec
    FROM emp
    WHERE UPPER(ename)=UPPER(v_ename);

IF v_emp_rec.job='PRESIDENT' THEN
     RAISE e_security;
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE( 'Invalid employee name ['||v_ename||']');

WHEN e_security THEN
     DBMS_OUTPUT.PUT_LINE('Cannot Access President''s salary ['||v_ename||']');
END;
/

Cannot access president's salary [KING]

User-defined errors and EXCEPTION_INIT
It is possible to associate an non-predefined Oracle server exception with a defined user exception.
If you are expecting Oracle will raise a non-predefined Oracle server exception, you can associate this error code with an identifier that you declare. This enables you to handle the error by name, and can make your code easier to debug.
There are four steps in this process:
- Declare the exception
- Associate the exception with an Oracle error code (using PRAGMA EXCEPTION_INIT)
- Raise the exception (and Oracle will raise this exception implicitly if it occurs)
- Handle the exception.

DECLARE
e_integrity_violation EXCEPTION; --First declare the exception

--Associate the exception with an error code
PRAGMA EXCEPTION_INIT(e_integrity_violation, -2292);

/* From this point on, if Oracle raises error code -2292, it is raising the exception named 'e_integrity_violation'.
   * Likewise, if you explicitly raise the exception, the error code associated with the exception will be -2292.
   */

v_deptno dept.deptno%TYPE:=20;
BEGIN
--Oracle will implicitly raise a -2292 error if integrity is violated.
DELETE FROM dept
    WHERE deptno=v_deptno;

EXCEPTION
WHEN e_integrity_violation THEN
    --Because you declared this exception and associated it with
    --Oracle error -2292, you can handle it by name
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('Can not delete department.');
    DBMS_OUTPUT.PUT_LINE('Delete employees for department first.');
END;
/

Can not delete department.
Delete employees for department first.

Exception Trapping Functions:
SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
Oracle uses two built in functions for catching exceptions and getting its information, SQLCODE and SQLERRM.
SQLCODE returns the error number for the latest occurred exception in the PL/SQL block.
SQLERRM returns the error message associated with the latter error number.
The DBMS_UTILITY.FORMAT_ERROR_STACK provides details about the error message (similar to SQLERRM)
The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE provides details on the line containing the error

BEGIN
   EXECUTE IMMEDIATE 'garbage';
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ORA Error Code: ' || SQLCODE );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || SQLERRM );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || DBMS_UTILITY.FORMAT_ERROR_STACK );
      DBMS_OUTPUT.PUT_LINE('Line containing Error: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
END;
/

ORA Error Code: -900
ORA Error Message: ORA-00900: invalid SQL statement
ORA Error Message: ORA-00900: invalid SQL statement
Line containing Error: ORA-06512: at line 2

raise_application_error:
RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.
Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).
RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.

The General Syntax to use this procedure is:
RAISE_APPLICATION_ERROR (error_number, error_message);

where error_number is a negative integer in the range -20000 and -20999 and error_message is a character string up to 2048 bytes long.

Steps to be folowed to use RAISE_APPLICATION_ERROR procedure:
1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution section.
3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLICATION_ERROR.

declare
   Value_to_Check NUMBER := &Value_to_Check;
   evenno EXCEPTION;
   oddno EXCEPTION;
BEGIN
IF MOD(Value_to_Check, 2) = 1 THEN
    RAISE oddno;
ELSE
    RAISE evenno;
END IF;
EXCEPTION
WHEN evenno THEN
    RAISE_APPLICATION_ERROR(-20001, 'Even Number Entered');
WHEN oddno THEN
    RAISE_APPLICATION_ERROR(-20999, 'Odd Number Entered');
END;
/

ORA-20999: Odd Number Entered
ORA-06512: at line 15

More examples

1. ZERO_DIVIDE:
declare
   k int;
begin
   k:=1/0;
   dbms_output.put_line(k);
exception
   when ZERO_DIVIDE then
      DBMS_OUTPUT.PUT_LINE('ORA Error Code: ' || SQLCODE );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || SQLERRM );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || DBMS_UTILITY.FORMAT_ERROR_STACK );
      DBMS_OUTPUT.PUT_LINE('Line containing Error: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
      raise_application_error(-20002,'cant divide by zero.....!');
   when OTHERS then
      raise_application_error(-20003,'some other error........!');
end;
/

2. NO_DATA_FOUND:
declare
   name varchar2(20);
   Emp_Num int:=1;
begin
   select ename into name
      from emp where empNo = Emp_Num;
   dbms_output.put_line(name);
exception
   when NO_DATA_FOUND then
      DBMS_OUTPUT.PUT_LINE('ORA Error Code: ' || SQLCODE );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || SQLERRM );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || DBMS_UTILITY.FORMAT_ERROR_STACK );
      DBMS_OUTPUT.PUT_LINE('Line containing Error: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
      raise_application_error(-20002,'No data is found for this record.....!');
   when OTHERS then
      raise_application_error(-20003,'Some other error........!');
end;
/

3. DUP_VAL_ON_INDEX:
begin
   insert into dept(deptno,dname) values (10,'Duplicated');
   commit;
   dbms_output.put_line('One row inserted...!');
exception
   when DUP_VAL_ON_INDEX then
      DBMS_OUTPUT.PUT_LINE('ORA Error Code: ' || SQLCODE );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || SQLERRM );
      DBMS_OUTPUT.PUT_LINE('ORA Error Message: ' || DBMS_UTILITY.FORMAT_ERROR_STACK );
      DBMS_OUTPUT.PUT_LINE('Line containing Error: ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );
      raise_application_error(-20001,'duplicate entry...!');
   when OTHERS then
      raise_application_error(-20002,'some other error occured...!');
end;
/

4. TOO_MANY_ROWS:
declare
   name varchar2(20);
begin
   select ename into name from emp where deptno=20;
   dbms_output.put_line(name);
exception
   when TOO_MANY_ROWS then
      raise_application_error(-20002,'more than one matching record found...!');
   when NO_DATA_FOUND then
      raise_application_error(-20001,'no such data found...1');
   when OTHERS then
      raise_application_error(-20003,'some unexpected error occured...!');
end;
/

5. INVALID_CURSOR:
In this example, the program will raise the exception because the program tries to access the cursor variable %ROWCOUNT after the cursor is closed.

declare
   cursor c1 is select ename from emp where rownum < 11;
   name emp.ename%type;
begin
   open c1;
   loop
      fetch c1 into name;
      exit when c1%notfound;
      dbms_output.put_line(c1%rowcount || '. ' || name);
   end loop;
   close c1;
   dbms_output.put_line(' And here is the error');
   dbms_output.put_line(c1%rowcount);
exception
   when INVALID_CURSOR then
      raise_application_error(-20001,'invalid operation in cursor');
end;
/

6. Use Defined Exceptions:
declare
   salary emp.sal%type;
   name emp.ename%type;
   no number:=&no;
   greater exception;
   lesser exception;
begin
   select ename,sal into name,salary from emp where empNo=no;
   if salary>2000 then
      raise greater;
   else
      raise lesser;
   end if;
exception
   when GREATER then
      raise_application_error(-20001,'Your salary is more than 2000');
   when LESSER then
      raise_application_error(-20002,'Your salary is less than 2000');
   when NO_DATA_FOUND then
      raise_application_error(-20003,'Please Enter a valid empNo (like 7369 or 7902) ');
end;
/

7. User defined exception for oracle defined number
declare
   exp1 exception;
   pragma exception_init(exp1,-00001);
begin
   insert into dept values(&dno,'&dname','&loc');
   dbms_output.put_line('one record inserted');
exception
   when EXP1 then
      dbms_output.put_line('ERROR!!!! Duplicate Value');
end;
/

8. Pragma
A pragma is a compiler directive that is processed at compile time, not at run time.
The pragma must appear somewhere after the exception declaration in the same declarative section. In below example the user tries to enter null values to a not-null field (empNo field of emp table) which is restricted.
declare
   empty exception;
   pragma exception_init(empty, -01400);
begin
   insert into emp(empNo) values (null);
   commit;
exception
   when EMPTY then
      dbms_output.put_line('error: trapped fields left null');
   when OTHERS then
      dbms_output.put_line(sqlerrm);
end ;
/

More information here:
http://psoug.org/reference/exception_handling.html