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;

PL/SQL RAISE_APPLICATION_ERROR

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;