syntaxhelp.com Your online cheat sheet!

Handling NO_DATA_FOUND Exception

-- table name: Employees
DECLARE
  name VARCHAR2(20);
BEGIN
  SELECT EmpName
  INTO name 
  FROM Employees
  WHERE EmpID = 10;
  dbms_output.put_line('Employee Exists');
EXCEPTION WHEN NO_DATA_FOUND THEN
  dbms_output.put_line('Invalid Employee ID');
END;

Handling unexpected exceptions

-- table name: Employees
DECLARE
  name VARCHAR2(20);
BEGIN
  SELECT EmpName
  INTO name
  FROM Employees
  WHERE EmpID =10;
  dbms_output.put_line('Employee Exists');
EXCEPTION WHEN OTHERS THEN
  dbms_output.put_line('Unknown Error!');
END;

Generating and Handling User Defined Exceptions

-- table name: Employees
DECLARE
  InvalidEmployeeException EXCEPTION;-- a user-defined exception
  cnt NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO cnt 
  FROM Employees
  WHERE EmpID =10;
  IF (cnt =0)THEN
    --explicitly raising the user-defined exception
    RAISE InvalidEmployeeException;
  END IF;
EXCEPTION
  --handling the user-defined exception
  WHEN InvalidEmployeeException THEN
    dbms_output.put_line('Invalid Employee ID!');
  WHEN OTHERS THEN
    dbms_output.put_line('Unknown Error!');
END;