PL/SQL Exception Handling

Exception Handling in PL/SQL refers to the concept of exception. The exception is an error or an warning message generated by the server or application.
Exceptions may be defined, enabled, treated at the level of each block in the program.

In PL/SQL there are two types of exceptions:
- internal exceptions that occur from the server
- external user-defined exceptions which are declared in declarative section


EXCEPTION
   WHEN No_Data_Found THEN
     statement1;
   WHEN Too_Many_Rows THEN
     statement2;
   WHEN Others THEN
     statement3;

For the processing of internal exceptions we can use WHEN OTHERS exceptions or pragma EXCEPTION_INIT.


DECLARE
  exception_name EXCEPTION;
  PRAGMA EXCEPTION_INIT (exception_name,-50);
BEGIN
  --plsql code;
EXCEPTION
  WHEN exception_name THEN
   --error processing
END;


ACCESS_INTO_NULL ORA-06530
CASE_NOT_FOUND ORA-06592
COLLECTION_IS_NULL ORA-06531
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
ROWTYPE_MISMATCH ORA-06504
SELF_IS_NULL ORA-30625
STORAGE_ERROR ORA-06500
SUBSCRIPT_BEYOND_COUNTORA-06533
SUBSCRIPT_OUTSIDE_LIMITORA-06532
SYS_INVALID_ROWIDORA-01410
TIMEOUT_ON_RESOURCEORA-00051
TOO_MANY_ROWSORA-01422
VALUE_ERRORORA-06502
ZERO_DIVIDEORA-01476

PL/SQL RAISE_APPLICATION_ERROR

The call is accomplished only in a subroutine that is cached.

CREATE PROCEDURE add_user(p_user_name VARCHAR2, p_user_email VARCHAR2)
  AS
BEGIN
   IF p_user_name IS NULL THEN
     RAISE_APPLICATION_ERROR (-20100, ' p_user_name IS NULL');
   ELSE
     INSERT INTO users(user_name, user_email)
     VALUES (p_user_name, p_user_email);
   END IF;
END add_user;


Online tutorials:  Transact-SQL,  Learn sqlplus