Table of Contents

Definition

The OPEN-FOR statement executes the query associated with a cursor variable. It's an important statement of the dynamic sql Management.

It allocates database resources to process the query and identifies the result set – the rows that meet the query conditions.

The OPEN-FOR statement permit the use of SQL dynamic :

You use three statements to control a cursor variable :

  • OPEN-FOR,

First, you OPEN a cursor variable FOR a multi-row query. The OPEN-FOR statement executes the query associated with a cursor variable. It allocates database resources to process the query and identifies the result set – the rows that meet the query conditions. The cursor variable is positioned before the first row in the result set.

  • FETCH,

Then, you FETCH rows from the result set.

  • and CLOSE.

When all the rows are processed, you CLOSE the cursor variable.

Fetching

into a record with a cursor

As the following example shows, you can fetch rows from the result set of a dynamic multi-row query into a record: 

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  emp%ROWTYPE;
   sql_stmt VARCHAR2(200);
   my_job   VARCHAR2(15) := 'CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM emp WHERE job = :j';
   OPEN emp_cv FOR sql_stmt USING my_job;
   LOOP
      FETCH emp_cv INTO emp_rec;
      EXIT WHEN emp_cv%NOTFOUND;
      -- process record
   END LOOP;
   CLOSE emp_cv;
END;
/

in a collection (BULK COLLECT INTO)

Bulk binding lets Oracle bind a variable in a SQL statement to a collection of values. The collection type can be any PL/SQL collection type (index-by table, nested table, or varray). The collection elements must have a SQL datatype such as CHAR, DATE, or NUMBER. Three statements support dynamic bulk binds: EXECUTE IMMEDIATE, FETCH, and FORALL.

See PL/SQL - Bulk Collect - Fetch collection of (records|Collection)

Example 1 

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   TYPE NumList IS TABLE OF NUMBER;
   TYPE NameList IS TABLE OF VARCHAR2(25);
   emp_cv EmpCurTyp;
   empids NumList;
   enames NameList;
   sals   NumList;
BEGIN
   OPEN emp_cv FOR 'SELECT employee_id, last_name FROM employees';
   FETCH emp_cv BULK COLLECT INTO empids, enames;
   CLOSE emp_cv;
END;
/

Example 2 

DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
  l_tab     t_bulk_collect_test_tab;
  l_cursor  SYS_REFCURSOR;
BEGIN
  -- With Open For Statement
  OPEN l_cursor FOR 'SELECT * FROM bulk_collect_test';
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor; 

  DBMS_OUTPUT.put_line('Dynamic FETCH  : ' || l_tab.count);

  -- With Execute Immediate Statement
  EXECUTE IMMEDIATE 'SELECT * FROM bulk_collect_test'
  BULK COLLECT INTO l_tab;
  DBMS_OUTPUT.put_line('Dynamic EXECUTE: ' || l_tab.count);
END;
/