Is ref cursor can be used in for loop like cursors? i.e. like
for i in cur_name
loop
dbms_output.put_line(i.column_name)
end loop;
If not possible then why?
-
What happened when you tried it? By the way, yes it is possible. But it really makes no sense. See community.oracle.com/thread/2451436?start=0&tstart=0Lalit Kumar B– Lalit Kumar B2015-12-15 12:41:58 +00:00Commented Dec 15, 2015 at 12:41
Add a comment
|
1 Answer
You can not use for loop just as you do against an implicit/explicit cursors
declare
ref_cur sys_refcursor;
BEGIN
OPEN ref_cur FOR SELECT table_name FROM all_tables WHERE ROWNUM < 5;
for i in ref_cur loop
dbms_output.put_line(i.table_name);
end loop;
END;
/
you will get an error
PLS-00221: 'REF_CUR' is not a procedure or is undefined
A ref cursor being a pointer to an open cursor used to send an open cursor as an out argument to the client app to loop through the record.
If you want to loop through then,
declare
ref_cur sys_refcursor;
v_name all_tables.table_name%TYPE;
BEGIN
OPEN ref_cur FOR SELECT table_name FROM all_tables WHERE ROWNUM < 5;
LOOP
FETCH ref_cur INTO v_name;
exit when ref_cur%notfound;
dbms_output.put_line(v_name);
end loop;
END;
/
You can use implicit/explicit cursors if the life time of the cursor is with in the *block*
declare
cursor cur IS SELECT table_name FROM all_tables WHERE ROWNUM < 5;
BEGIN
for i in cur loop
dbms_output.put_line(i.table_name);
end loop;
END;
/
or
BEGIN
for i in (SELECT table_name FROM all_tables WHERE ROWNUM < 5) loop
dbms_output.put_line(i.table_name);
end loop;
END;
/
