Handling NO_DATA_NEEDED exception in PL/SQL

PL/SQL Exception Handling: Exercise-16 with Solution

Handle the NO_DATA_NEEDED exception when a query retrieves more data than required.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_employee_idemployees.employee_id%TYPE;
v_first_nameemployees.first_name%TYPE;
v_last_nameemployees.last_name%TYPE;
  CURSOR employee_cursor IS
    SELECT employee_id, first_name, last_name
    FROM employees;
BEGIN
  OPEN employee_cursor;
  LOOP
    FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name;
    DBMS_OUTPUT.PUT_LINE(v_employee_id || ' - ' || v_first_name || ' ' || v_last_name);
    IF employee_cursor%NOTFOUND THEN
      RAISE NO_DATA_NEEDED;
    END IF;
  END LOOP;
EXCEPTION
  WHEN NO_DATA_NEEDED THEN
    DBMS_OUTPUT.PUT_LINE('Caught NO_DATA_NEEDED exception: More data retrieved than required.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
/;

Sample Output:

……….
……….
201 - Michael Hartstein
202 - Pat Fay 
203 - Susan Mavris
204 - Hermann Baer 
205 - Shelley Higgins 
206 - William Gietz
206 - William Gietz
Caught NO_DATA_NEEDED exception: More data retrieved than required.

Explanation:

The said code in Oracle's PL/SQL that demonstrates how to handle the NO_DATA_NEEDED exception in the context of a cursor, specifically when more data is retrieved than necessary.

The three variables v_employee_id, v_first_name, and v_last_name of type employee_id, first_name, and last_name respectively in the employees table are declared and also declares a cursor employee_cursor that selects the employee_id, first_name, and last_name from the employees table.

Inside the block, the cursor opens and enters a loop, and each iteration of the loop, it fetches data from the cursor into the declared variables and outputs the employee ID, first name, and last name using DBMS_OUTPUT.PUT_LINE.

If the cursor reaches the end of the result set and no more data is available, in that cases, the code raises the NO_DATA_NEEDED exception, and the exception handling section, outputs a message indicating that more data was retrieved than required.

Flowchart:

Previous: Handling ACCESS_INTO_NULL exception in PL/SQL.