Is it impossible to iterate table rows in Oracle PL/SQL without using a cursor

oracle-10gplsql

I wanted to figure out if it was possible to avoid using cursor to iterate the table rows in PL/SQL and tried the following script:

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0; -- number(10,0)
    v2 tbl_plant.name%type := ''; -- varchar2(50)
begin
    while true
    loop
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        exit when sql%rownum = 0;

        dbms_output.put_line(v1 || v2);
    end loop;
end;

So I figured that compiler optimizes this into an implicit cursor as while loop is used, and tried to circumvent:

set serveroutput on;

declare
    v1 tbl_plant.plant_id%type := 0;
    v2 tbl_plant.name%type := '';
begin
<<divein>>
        select plant_id, name
        into v1, v2
        from tbl_plant
        where rownum = 1
            and plant_id > v1
        order by plant_id;

        if sql%rownum = 0 then
          goto bailout;
        end if;

        dbms_output.put_line(v1 || v2);
        goto divein;
<<bailout>>
    null;
end;

But in both cases I got exactly the same error:

PLS-00207: identifier 'ROWNUM', applied to implicit cursor SQL, is not a legal cursor attribute

But if I commented out either while or goto and the labels, eliminating either type of loop, the same select worked fine and returned the first id/name in tbl_plant by plant ID.

Is it at all possible to iterate w/o using a cursor?

Best Answer

Valid cursor attribute name is one of %NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN. There is no %rownum attribute . In your question you use implicit cursors, so it should be sql%rowcount.