CODEWITHSUNDEEP
sqloracle-databasefor-loopplsqldatabase-cursor
Anton Barycheuski
Anton Barycheuski

Reputation: 720

Cursor for loop in Oracle

Please, explain me how to use cursor for loop in oracle.

If I use next code, all is fine.

for rec in (select id, name from students) loop
    -- do anything
end loop;

But if I define variable for this sql statement, it doesn't work.

v_sql := 'select id, name from students';

for rec in v_sql loop
    -- do anything
end loop;

Error: PLS-00103

Upvotes: 10

Views: 68151

Answers (4)

Nikhil
Nikhil

Reputation: 163

You're not executing that sql string anywhere. Simply do this

v_sql := 'select id, name from students';
open cur for v_sql;
for rec in cur loop
    -- do anything
end loop;

Or you can do this

cursor cur is select id, name from students;
open cur;
for rec in cur loop
        -- do anything
end loop;

Or you can do this

for rec in (select id, name from students) loop
    -- do anything
end loop

Upvotes: 2

Harshit
Harshit

Reputation: 560

You have to use Refcursor if you are making the query at runtime. Actually refcursors are pointers to the query they wont take up any space for the rows fetched. Normal Cursors will not work for it.

declare 
v_sql varchar2(200);
rec sys_refcursor;
BEGIN
v_sql := 'select id, name from students';

open rec for v_sql 
loop
fetch
exit when....
-- do anything
end loop;

Upvotes: 0

Nicolas Constant Brix
Nicolas Constant Brix

Reputation: 121

try this :

cursor v_sql is
select id, name from students;

for rec in v_sql 
loop
    -- do anything
end loop;

then no need to open, fetch or close the cursor.

Upvotes: 7

Nick Krasnov
Nick Krasnov

Reputation: 27251

To address issues associated with the second approach in your question you need to use

cursor variable and explicit way of opening a cursor and fetching data. It is not

allowed to use cursor variables in the FOR loop:

declare
  l_sql varchar2(123);        -- variable that contains a query
  l_c   sys_refcursor;        -- cursor variable(weak cursor). 
  l_res your_table%rowtype;   -- variable containing fetching data  
begin
  l_sql := 'select * from your_table';

  -- Open the cursor and fetching data explicitly 
  -- in the LOOP.

  open l_c for l_sql;

  loop
    fetch l_c into l_res;
    exit when l_c%notfound;   -- Exit the loop if there is nothing to fetch.

     -- process fetched data 
  end loop;

  close l_c; -- close the cursor
end;

Find out more

Upvotes: 13

Related Questions