PL/SQL Example: Handling INVALID_CURSOR Exception

PL/SQL Exception Handling: Exercise-9 with Solution

Handle the INVALID_CURSOR exception when referencing an invalid or closed cursor.

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
  CURSOR c_employee IS
    SELECT employee_id, first_name, last_name FROM employees;
v_employee_idemployees.employee_id%TYPE;
v_first_nameemployees.first_name%TYPE;
v_last_nameemployees.last_name%TYPE;
BEGIN
  OPEN c_employee;
  FETCH c_employee INTO v_employee_id, v_first_name, v_last_name;
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_employee_id || ', ' || v_first_name || ' ' || v_last_name);

  CLOSE c_employee;
  FETCH c_employee INTO v_employee_id, v_first_name, v_last_name;
EXCEPTION
  WHEN INVALID_CURSOR THEN
    DBMS_OUTPUT.PUT_LINE('Invalid cursor.');
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No more data to fetch.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
  BEGIN
    IF c_employee%ISOPEN THEN
      CLOSE c_employee;
    END IF;
  END;
END;
/

Sample Output:

Employee: 100, Steven King 
Invalid cursor.

Explanation:

The said code in Oracle's PL/SQL that demonstrating the usage of a cursor to fetch employee data with specific attention given to handling the INVALID_CURSOR exception.

A cursor named "c_employee" that selects employee_id, first_name, and last_name from the 'employees' table is declares and also the variables v_employee_id, v_first_name, and v_last_name that stores the fetched values from the cursor are declares.

The cursor opens by using OPEN statement and fetches the first row of data from the cursor into the variables, and displaying the retrieved data using DBMS_OUTPUT.PUT_LINE.

After processing the first row, the cursor closed by using the CLOSE statement. After that when it attempts to fetch data from the cursor again, an INVALID_CURSOR exception raised. The EXCEPTION block handles this exception and displays an appropriate message using DBMS_OUTPUT.PUT_LINE.

An other exception handler NO_DATA_FOUND exception raised when there is no more data to fetch from the cursor. The handler displays a corresponding message.

For any other exception the WHEN OTHERS raised, displays an error message along with the error details retrieved from SQLERRM.

Flowchart:

Previous: Example of handling CURSOR_ALREADY_OPEN exception in PL/SQL cursors.
Next: PL/SQL program to display job titles of employees.