CODEWITHSUNDEEP
oracle-databaseplsql
user251287
user251287

Reputation: 81

PL/SQL - pass query in for loop stored in variable

i have a scenario where i need to create a dynamic query with some if/else statements, i have prepared the query but unable to use this inside loop below is the snippet of what i am trying to acheive.

Query :

select user_name into username from table1 where user ='user1';

for(query)  
Loop  
  Dbms_output.put_line('user name ' || user_name);  
END Loop;

is this possible to use the vaiable in the for loop ?

Upvotes: 0

Views: 1260

Answers (1)

William Robertson
William Robertson

Reputation: 16001

Not really. Notice in the 19c PL/SQL Reference it says:

Cursor FOR Loop syntax diagram

cursor

Name of an explicit cursor (not a cursor variable) that is not open when the cursor FOR LOOP is entered.

You have to code this the long way, by explicitly fetching the cursor into a record until you hit cursorname%notfound, e.g.

create or replace procedure cheese_report
    ( cheese_cur in sys_refcursor )
as
    type cheese_detail is record
        ( name   varchar2(15)
        , region varchar2(30) );

    cheese cheese_detail;
begin
    loop
        fetch cheese_cur into cheese;
        dbms_output.put_line(cheese.name || ', ' || cheese.region);
        exit when cheese_cur%notfound;
    end loop;
    
    close cheese_cur;
end;

Test:

declare
    cheese sys_refcursor;
begin
    open cheese for
        select 'Cheddar',     'UK' from dual union all
        select 'Gruyere',     'France' from dual union all
        select 'Ossau Iraty', 'Spain' from dual union all
        select 'Yarg',        'UK' from dual;

    cheese_report(cheese);
end;
/

Cheddar, UK
Gruyere, France
Ossau Iraty, Spain
Yarg, UK

In Oracle 21c you can simplify this somewhat, though you still have to know the structure of the result set:

create or replace procedure cheese_report
    ( cheese_cur in sys_refcursor )
as
    type cheese_detail is record
        ( cheese varchar2(15)
        , region varchar2(30) );
begin
    for r cheese_detail in values of cheese_cur
    loop
        dbms_output.put_line(r.cheese || ', ' || r.region);
    end loop;
end;

You could parse an unknown ref cursor using dbms_sql to find the column names and types, but it's not straightforward as you have to do every processing step yourself. For an example of something similar, see www.williamrobertson.net/documents/refcursor-to-csv.shtml.

Upvotes: 1

Related Questions