Understanding OPEN FOR Statement In Oracle Database.

OPEN FOR statement is used to support multiple-row dynamic queries.

OPEN cursor_variable|host_cursor_variable} FOR sql_string USING bind_argument;

The bind_argument is always IN mode.

:host_cursor_variable : Is a cursor variable declared in a host environment.

When you execute an OPEN FOR statement, the PL/SQL runtime engine does the following :-

1) Associates a cursor variable or host cursor variable with the query found in the query string.
2) Evaluates any bind arguments and substitutes those values for the placeholders found in the query string.
3) Executes the query.
4) Identifies the result set.
5) Positions the cursor on the first row in the result set.
6) Set %ROWCOUNT is to 0.

SQL> DECLARE
  2     vename          emp.ename%TYPE;
  3     l_table         VARCHAR2 (30) := 'emp';
  4     l_cursor        SYS_REFCURSOR;
  5     l_column_list   VARCHAR2 (30) := 'sal';
  6     nsal            emp.sal%TYPE;
  7     nCNT            NUMBER := 0;
  8  BEGIN
  9     EXECUTE IMMEDIATE 'SELECT ename FROM emp WHERE empno = :empno' INTO vename USING 7369;
 10
 11     DBMS_OUTPUT.PUT_LINE ('Name : ' || vename);
 12
 13     EXECUTE IMMEDIATE 'CREATE INDEX DEPT_IND_3 on dept (loc)';
 14
 15     SELECT COUNT(*)
 16     INTO nCnt
 17     FROM all_indexes
 18     WHERE index_name = 'DEPT_IND_3';
 19
 20     DBMS_OUTPUT.PUT_LINE ('Index Created Successfully : '||nCnt);
 21
 22     EXECUTE IMMEDIATE 'UPDATE ' || l_table || ' SET sal = 10 WHERE empno = 7369';
 23
 24     DBMS_OUTPUT.PUT_LINE ('Rows Affected - 1 : ' || SQL%ROWCOUNT);
 25
 26     EXECUTE IMMEDIATE 'UPDATE ' || l_table || ' SET sal = :salary WHERE empno = :employee_id' USING 99, 7839;
 27
 28     DBMS_OUTPUT.PUT_LINE ('Rows Affected - 2 : ' || SQL%ROWCOUNT);
 29
 30     DBMS_OUTPUT.PUT_LINE ('OPEN FOR - 1!!!');
 31
 32     OPEN l_cursor FOR 'SELECT ' || l_column_list || ' FROM emp WHERE empno = 7369';
 33
 34     FETCH l_cursor INTO nsal;
 35
 36     DBMS_OUTPUT.PUT_LINE ('Salary : ' || nsal);
 37
 38     CLOSE l_cursor;
 39
 40     DBMS_OUTPUT.PUT_LINE ('OPEN FOR - 2!!!');
 41
 42     OPEN l_cursor FOR 'SELECT ' || l_column_list || ' FROM emp';
 43     LOOP
 44       FETCH l_cursor INTO nsal;
 45       EXIT WHEN l_cursor%NOTFOUND;
 46       DBMS_OUTPUT.PUT_LINE ('Salary : ' || nsal);
 47     END LOOP;
 48
 49     CLOSE l_cursor;
 50  END;
 51  /

--OUTPUT :::
Name : SMITH
Index Created Successfully : 1
Rows Affected - 1 : 1
Rows Affected - 2 : 1
OPEN FOR - 1!!!
Salary : 10
OPEN FOR - 2!!!
Salary : 99
Salary : 2850
Salary : 2450
Salary : 2975
Salary : 3000
Salary : 3000
Salary : 10
Salary : 1600
Salary : 1250
Salary : 1250
Salary : 1500
Salary : 1100
Salary : 950
Salary : 1300

SQL> DECLARE
  2     TYPE query_curtype IS REF CURSOR;
  3
  4     dyncur1    query_curtype;
  5     dyncur2    query_curtype;
  6     emp_rec1   emp%ROWTYPE;
  7     nempno1    emp.empno%TYPE;
  8     cv         SYS_REFCURSOR;
  9     vename1    emp.ename%TYPE;
 10     nempno2    emp.empno%TYPE;
 11     vename2    emp.ename%TYPE;
 12     nempno3    emp.empno%TYPE;
 13     nsal1      emp.sal%TYPE;
 14     nsal2      emp.sal%TYPE;
 15  BEGIN
 16     DBMS_OUTPUT.PUT_LINE ('OPEN-FOR Into Record Without USING Clause');
 17
 18     IF dyncur1%ISOPEN
 19     THEN
 20        CLOSE dyncur1;
 21     END IF;
 22
 23     OPEN dyncur1 FOR 'SELECT * FROM emp';
 24     LOOP
 25        FETCH dyncur1 INTO emp_rec1;
 26        EXIT WHEN dyncur1%NOTFOUND;
 27
 28        IF dyncur1%FOUND
 29        THEN
 30           DBMS_OUTPUT.PUT_LINE ('ENAME ::: '|| emp_rec1.ename|| ' ROWCOUNT ::: '|| dyncur1%ROWCOUNT);
 31        END IF;
 32     END LOOP;
 33
 34     CLOSE dyncur1;
 35
 36     DBMS_OUTPUT.PUT_LINE ('OPEN-FOR Into Variable Without USING Clause');
 37
 38     OPEN cv FOR 'SELECT empno FROM emp WHERE ename=''SMITH''';
 39     FETCH cv INTO nempno1; --We can FETCH into a sequence of variables as well.
 40
 41     DBMS_OUTPUT.PUT_LINE ('EMPNO ::: ' || nempno1);
 42
 43     CLOSE cv;
 44
 45     vename1 := 'KING';
 46     nempno2 := 7839;
 47
 48     vename2 := 'FORD';
 49     nempno3 := 7902;
 50
 51     DBMS_OUTPUT.PUT_LINE ('OPEN-FOR With USING Clause - 1');
 52
 53     OPEN dyncur2 FOR 'SELECT sal FROM emp WHERE ename = :1 AND empno = :2' USING vename1, nempno2;
 54     FETCH dyncur2 INTO nsal1;
 55     DBMS_OUTPUT.PUT_LINE ('Employee Salary ::: ' || nsal1);
 56
 57     CLOSE dyncur2;
 58
 59     DBMS_OUTPUT.PUT_LINE ('OPEN-FOR With USING Clause - 2');
 60
 61     OPEN dyncur2 FOR 'SELECT sal FROM emp WHERE ename = :1 AND empno = :2' USING vename2, nempno3;
 62     FETCH dyncur2 INTO nsal2;
 63     DBMS_OUTPUT.PUT_LINE ('Employee Salary ::: ' || nsal2);
 64
 65     CLOSE dyncur2;
 66  END;
 67  /

--OUTPUT :::
OPEN-FOR Into Record Without USING Clause
ENAME ::: KING ROWCOUNT ::: 1
ENAME ::: BLAKE ROWCOUNT ::: 2
ENAME ::: CLARK ROWCOUNT ::: 3
ENAME ::: JONES ROWCOUNT ::: 4
ENAME ::: SCOTT ROWCOUNT ::: 5
ENAME ::: FORD ROWCOUNT ::: 6
ENAME ::: SMITH ROWCOUNT ::: 7
ENAME ::: ALLEN ROWCOUNT ::: 8
ENAME ::: WARD ROWCOUNT ::: 9
ENAME ::: MARTIN ROWCOUNT ::: 10
ENAME ::: TURNER ROWCOUNT ::: 11
ENAME ::: ADAMS ROWCOUNT ::: 12
ENAME ::: JAMES ROWCOUNT ::: 13
ENAME ::: MILLER ROWCOUNT ::: 14
OPEN-FOR Into Variable Without USING Clause
EMPNO ::: 7369
OPEN-FOR With USING Clause - 1
Employee Salary ::: 5000
OPEN-FOR With USING Clause - 2
Employee Salary ::: 3000

SQL> CREATE OR REPLACE PROCEDURE showcol1 (tab IN VARCHAR2,col IN VARCHAR2,whr IN VARCHAR2 := NULL)
  2  IS
  3     cv    SYS_REFCURSOR;
  4     val   VARCHAR2 (32767);
  5  BEGIN
  6     OPEN cv FOR 'SELECT ' || col || ' FROM ' || tab || ' WHERE ' || NVL (whr, '1 = 1');
  7     LOOP
  8        FETCH cv INTO val;
  9        EXIT WHEN cv%NOTFOUND;
 10
 11        IF cv%ROWCOUNT = 1
 12        THEN
 13           DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
 14           DBMS_OUTPUT.PUT_LINE ('Contents of ' || UPPER (tab) || '.' || UPPER (col));
 15           DBMS_OUTPUT.PUT_LINE (RPAD ('-', 60, '-'));
 16        END IF;
 17
 18        DBMS_OUTPUT.PUT_LINE (val);
 19     END LOOP;
 20
 21     CLOSE cv;
 22  END;
 23  /

Procedure created.

SQL> EXEC showcol1 ('emp','ename');
------------------------------------------------------------
Contents of EMP.ENAME
------------------------------------------------------------
KING
BLAKE
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER

SQL> EXEC showcol1 ('emp','ename','deptno=10');
------------------------------------------------------------
Contents of EMP.ENAME
------------------------------------------------------------
KING
CLARK
MILLER

SQL> EXEC showcol1 ('emp','ename',NULL);
------------------------------------------------------------
Contents of EMP.ENAME
------------------------------------------------------------
KING
BLAKE
CLARK
JONES
SCOTT
FORD
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER

SQL> BEGIN
  2     showcol1 ('emp','ename || ''-$'' || sal','comm IS NOT NULL');
  3  END;
  4  /

--OUTPUT :::
------------------------------------------------------------
Contents of EMP.ENAME || '-$' || SAL
------------------------------------------------------------
ALLEN-$1600
WARD-$1250
MARTIN-$1250
TURNER-$1500

SQL> BEGIN
  2     showcol1 ('emp','ename || ''-$'' || sal','1=2');
  3  END;
  4  /

PL/SQL procedure successfully completed.