Oracle PL/SQL - %ISOPEN Explicit Cursor Attribute

Introduction

The following code opens the explicit cursor c1 only if it is not open and closes it only if it is open.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w  w  w .j  a v a 2s  . c o  m

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-2000', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 10);
SQL> INSERT INTO emp VALUES( 200, 'Joe', 'Lee', 'abc', '123.123.9999', TO_DATE('17-JUN-2010', 'dd-MON-yyyy'), 'TESTER', 25000, NULL, NULL, 20);
SQL>
SQL> DECLARE
  2    CURSOR c1 IS
  3      SELECT last_name, salary FROM emp
  4      WHERE ROWNUM < 11;
  5
  6    the_name emp.last_name%TYPE;
  7    the_salary emp.salary%TYPE;
  8  BEGIN
  9    IF NOT c1%ISOPEN THEN
 10      OPEN c1;
 11    END IF;
 12
 13    FETCH c1 INTO the_name, the_salary;
 14
 15    IF c1%ISOPEN THEN
 16      CLOSE c1;
 17    END IF;
 18  END;
 19  /

PL/SQL procedure successfully completed.

SQL>

SQL>

Related Topic