How to Use SQL ISOPEN and What is %ISOPEN?

In Oracle SQL ISOPEN attribute checks whether currently a cursor is open or not. cursor_name% %ISOPEN returns TRUE if its cursor is open.  As you know Cursors are used to fetch records from tables using query. Then if you want to check the status of the cursor ISOPEN attribute is used to find cursor is open or not.

Overview of SQL ISOPEN works:

• Open Cursor: When the cursor is open by an OPEN statement. Then cursor is always open until the CLOSE
• Closed Cursor: CLOSE statement is used to CLOSE the Cursor then mapping system resources will close and release. You must close all the opened cursors.
• ISOPEN Attribute: The SQL ISOPEN attribute returns whether the cursor is open or closed. If the cursor is open then return TRUE else FALSE

Below is the simple SYSTAX and a usage example. But we can use the ISOPEN attribute differently according to the situation. You can find them in the below examples.

All the explicit cursor and cursor variables in these four attributes %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. Other that this SQL the cursor has another two attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, use with the FORALL statement

--
-- Syntax....
DECLARE 
CURSOR cursor_data IS (SELECT columns FROM table ); 
var_col_ cursor_data%ROWTYPE; 
BEGIN OPEN cursor_data; 
LOOP 
FETCH cursor_data INTO var_col_ 
EXIT WHEN cursor_data%NOTFOUND; 
END LOOP; 
CLOSE cur_data; END;

--Example:

DECLARE
  CURSOR cur_emp IS
    SELECT first_name, last_name FROM employee_table;
BEGIN
  OPEN cur_emp; -- Open the cursor
  IF cur_emp%ISOPEN THEN -- Check if cursor is open
    DBMS_OUTPUT.PUT_LINE('Cursor cur_emp is open');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Cursor cur_emp is closed');
  END IF;
  CLOSE cur_emp; -- Close the cursor
END;
/
--

Where to ISOPEN Oracle is Used:

• Cursor Management: Cursors are handled inside the PL/SQL block. Able to check its already open or not
• Exception Handling: SQL ISOPEN is used to close the cursors when an exception is raised from the code.
• Resource Optimization: When the cursor is no longer used, we can release resources by them closing. Therefore, we need to check whether the cursor is still open.

SQL ISOPEN Example

1: Using ISOPEN with a Cursor

--
DECLARE
  CURSOR cur_emp IS
    SELECT employee_id, name FROM employee_table WHERE department_id =’HR’;
BEGIN
  OPEN cur_emp;
IF (cur_emp%FOUND) THEN
	Your Code block   
       END IF;
  IF cur_emp %ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Cursor cur_emp is open');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Cursor cur_emp is closed');
  END IF;
  CLOSE cur_emp;
END;
--

This is similar to earlier examples but additionally tested whether the record was found by using cur_emp%FOUND this example declares a cursor cur_emp and it is fetching employees. We testing the cursor is open or not by running %ISOPEN. Finally must close the cursor by executing the Close statement.

2: Using ISOPEN with Implicit Cursor

--
DECLARE
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employee_table;
  IF SQL%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Implicit cursor SQL is open');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Implicit cursor SQL is closed');
  END IF;
END;

Output: Implicit cursor SQL is closed
--

This is simple and different from earlier cases. We use an implicit cursor to fetch data. This is the COUNT number of rows from employee_table. %ISOPEN(SQL ISOPEN) Check whether the implicit cursor is open.

3: Using ISOPEN in a Cursor FOR Loop

--
DECLARE
  v_emp_id employee_table.employee_id%TYPE;
CURSOR emp_rec IS SELECT employee_id FROM employee_table

BEGIN
FOR empl_ IN emp_rec LOOP
            v_emp_id:= empl_.employee_id  Your Code
END LOOP;
IF emp_rec%ISOPEN THEN
      		DBMS_OUTPUT.PUT_LINE('Cursor emp_rec is open for employee ID: ' || v_emp_id);
    	END IF;


END;
--

This FOR LOOP example shows how to loop and test SQL ISOPEN inside the block. this example uses a cursor and iterates employee after fetching data into the emp_rec. It then checks if emp_rec is open after the iteration (ITERATOR DESIGN PATTERN) using %ISOPEN.

4: Using ISOPEN with Dynamic SQL

--
DECLARE
  v_sql VARCHAR2(100) := 'SELECT * FROM employee_table';
  v_emp_id employee_table.employee_id%TYPE;
  CURSOR c_emp IS
    EXECUTE IMMEDIATE v_sql;
BEGIN
  OPEN c_emp;
  IF c_emp%ISOPEN THEN
    DBMS_OUTPUT.PUT_LINE('Dynamic cursor c_emp is open');
  END IF;
  CLOSE c_emp;
END;
--

We can execute dynamic SQL using  EXECUTE IMMEDIATE This is similar to the first example. After execution complete open the cursor and then test: SQL ISOPEN using %ISOPEN.

5: Using ISOPEN with EXCEPTION Handling

--
DECLARE
  CURSOR c_emp IS
    SELECT employee_id, first_name, last_name FROM employee_table WHERE department_id = HR;
  v_emp c_emp%ROWTYPE;
BEGIN
  OPEN c_emp;
  FETCH c_emp INTO v_emp;
  IF c_emp%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'No employees found for department Human resource);
  END IF;
  IF c_emp%ISOPEN THEN
    CLOSE c_emp;
  END IF;
END;

//In the later part of the code you can handle above exeption like below. 
// You have to raise error again after closing all the opened cursors.

EXCEPTION
  WHEN no_data_found
  THEN
    close_open_cursors;
    <<do something meaningful>>
  WHEN too_many_rows
  THEN
    close_open_cursors;
    <<do something meaningful>>
  WHEN others
  THEN
    close_open_cursors;
    raise;
END;
--

We can have exceptions in the middle, or we can raise application errors in the middle of the code but later we need to close the cursor we can test it like above.

Summary

If you are in a PL/SQL function or procedure, then you can check cursor is open by like below

--
IF NOT (emp_cur%ISOPEN) THEN
   OPEN emp_cur;
END IF;
FETCH emp_cur INTO emp_rec;
--

In Summary, this helps to check whether the cursor is open or not. If it is not open yet you can open and do the needful and then close it. As we discussed in the example you can use it in exception handling as well.

REF https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/13_elems12.htm