Example of handling CURSOR_ALREADY_OPEN exception in PL/SQL cursors

PL/SQL Exception Handling: Exercise-8 with Solution

Handle the CURSOR_ALREADY_OPEN exception when trying to open a cursor that is already open.

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);
  OPEN c_employee;
EXCEPTION
  WHEN CURSOR_ALREADY_OPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor is already open.');
  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 
Cursor is already open.

Explanation:

The said code in Oracle's PL/SQL demonstrates the handling of the CURSOR_ALREADY_OPEN exception and properly closing the cursor, the code ensures the correct and efficient management of the cursor and avoids any potential issues caused by attempting to open an already open cursor.

The cursor named c_employee that selects employee IDs, first names, and last names from a table called employees is declared.

The three variables v_employee_id, v_first_name, v_last_name are declared to hold the fetched values from the cursor.

Then the cursor c_employee starts to fetch the first row of data into the declared variables and the fetched information displays by using the DBMS_OUTPUT.PUT_LINE procedure.

The CURSOR_ALREADY_OPEN exception raised if the cursor is already open and in such a case, the code handeles exception by displaying a message: 'Cursor is already open.'

An other exception the NO_DATA_FOUND raised when there are no more rows to fetch from the cursor. It displays a message indicating 'No more data to fetch.'

The OTHERS exception if raised during the execution of the code, displays a generic error message along with the error message provided by SQLERRM.

A block that checks whether the cursor c_employee is open using the %ISOPEN attribute and if so the nested block closes the cursor using the CLOSE statement to ensure proper cursor management.

Flowchart:

Previous: PL/SQL Program Handling Internal Error.
Next: Handling INVALID_CURSOR Exception.