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 besql%rowcount
.