PL/SQL-Cursor: iterate with FOR and IN

Question

I try to iterate thru a cursor. Its statement is dynamically created.

If I do so by LOOP and FETCH all is fine. If I try to do it by FOR and IN the syntax is bad ('CUR' is no procedure or undefined)

How can I do it with FOR and IN?

DECLARE
FUNCTION foo (pat VARCHAR) RETURN NUMBER IS
    sqlcmd VARCHAR (100);
    TYPE t_refcur IS REF CURSOR;
        cur t_refcur;
    str VARCHAR (200);
BEGIN
    sqlcmd := 'SELECT name FROM my_tab WHERE name LIKE :1';

    -- 1st loop ok
    OPEN cur FOR sqlcmd USING pat;
    LOOP
        FETCH cur INTO str;
        EXIT WHEN cur%NOTFOUND;
        dbms_output.put_line('C1  '|| str);
    END LOOP;
    CLOSE cur;

    -- 2nd loop with syntax error (no procedure or CUR not known)
    OPEN cur FOR sqlcmd USING pat;
    FOR str IN cur LOOP
        dbms_output.put_line('C2  '|| str);
    END LOOP;
    CLOSE cur;

    RETURN 1;
END foo;

BEGIN
    dbms_output.put_line (foo ('A%'));
END;

score 2 · Accepted Answer · answered Jun 30 '20 at 15:46

Unfortunally it is not available to use for in cycle with ref cursor because for in cycle requires column list defined at compile time. For example

begin
    for i in (select dummy, sysdate dt from dual) loop
       dbms_output.put_line(i.dummy || ': ' || i.dt);
    end loop;
end;
/

PL/SQL-Cursor: iterate with FOR and IN

1 Answers1