Loop through an explicit cursor in Oracle

oracle, oracle11g, plsql, sql

Solution

Here's how to do it allowing for dynamic SQL. You can build the query string up in code however needed (usual warnings about SQL injection apply).

DECLARE      
  TYPE ref_cursor IS REF CURSOR;
  cur REF_CURSOR;

  d_item_no  items.item_no%TYPE;
  d_item_descr  items.item_descr%TYPE;

BEGIN
  OPEN cur FOR 'SELECT i.item_no, 
                       i.item_descr 
                  FROM ITEMS i 
                 WHERE i.item_no in (1,2,3)';
  LOOP
    FETCH cur INTO d_item_no, d_item_descr;
    EXIT WHEN cur%NOTFOUND;
    dbms_output.put_line( d_item_no||' '||d_item_descr );
  END LOOP;

  CLOSE cur;
END;
/

Problem

How can I loop through an implicit cursor which is created, for example, from a query? Here is the sample code: ``` SERVEROUTPUT on; DECLARE TYPE ref_cursor IS REF CURSOR; cur REF_CURSOR; BEGIN OPEN cur FOR 'SELECT i.item_no, i.item_descr FROM ITEMS i WHERE i.item_no in (1,2,3)'; ... loop statement to print all item descriptions? END; ```

Original source