Exception in PL/SQL

Exception

Exception is a PL/SQL error that is raised during program execution.

Example of Exception

DECLARE
v_lname VARCHAR2(15);
BEGIN
SELECT last_name INTO v_lname
FROM employees
WHERE first_name='John';
DBMS_OUTPUT.PUT_LINE ('John ''s last name is :'||v_lname);
END;

Here query Returning Multiple rows but variable can hold single value at a time.

How to overcome this type of EXCEPTION


By writing EXCEPTION Section in Program

Example:

DECLARE
v_lname VARCHAR2 (15);
BEGIN
SELECT last_name INTO v_lname
FROM employees
WHERE first_name='John';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : '||v_lname);
EXCEPTION
WHEN TOO_MANY_ROWS then
dbms_output.put_line('Query is returning more than one rows');
END;

Output:

Handling Exceptions with PL/SQL


–An exception is a PL/SQL error that is raised during program execution.

An exception can be raised:


  1. Implicitly by the Oracle server
  2. Explicitly by the program

An exception can be handled:


  • By trapping it with a handler
  • By propagating it to the calling environment

Types of Exception


  • Predefined Oracle server
  • Non-predefined Oracle server
  • User-defined

Sample predefined exceptions:


  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • INVALID_CURSOR
  • ZERO_DIVIDE
  • DUP_VAL_ON_INDEX

Non-Predefined Error


DECLARE
e_insert_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_insert_excep, -01400);
BEGIN
INSERT INTO departments (department_id, department_name) VALUES (100, NULL);
EXCEPTION
WHEN e_insert_excep THEN
DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Functions for Trapping Exceptions


SQLCODE: Returns the numeric value for the error code.

SQLERRM: Returns the message associated with the error number.

Example of User-defined Exception:

DECLARE
v_deptno NUMBER := 200;
v_name VARCHAR2(20) := 'dev';
e_invalid_department EXCEPTION;
BEGIN
UPDATE departments
SET department_name = v_name
WHERE department_id = v_deptno;
IF SQL % NOTFOUND THEN
RAISE e_invalid_department;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

RAISE_APPLICATION_ERROR Procedure


Used in two different places:

  1. Executable section
  2. Exception section

–Returns error conditions to the user in a manner consistent with other Oracle server errors

RAISE_APPLICATION_ERROR Procedure

  1. Executable section:
  2. Exception section:
DECLARE
age INTEGER :=17 ;
BEGIN
IF (age < 18) THEN
Raise_Application_Error (-20555, 'candidate is not eligible for voting...');
END IF;
END;

Output:

Leave a Reply

Your email address will not be published. Required fields are marked *