Cursor for loop in Oracle

database-cursor, for-loop, oracle, plsql, sql

Solution

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

Problem

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

Original source