PL/SQL Procedure Use String Select Statement in for loop

Question

I am building a procedure, where I`m first creating a select statement and store it in an VARCAHR variable. I now want to execute that query and store the whole result set in an variable to loop through it or use directly in a for loop. I only find examples where the Select is hard written in the for loop definition. How do i exchange the Select statement with my variable that holds my select statement?

for r IN (SELECT ... FROM ...)
loop
   --do sth;
end loop;

how i want to use it :

statement := 'SELECT .... FROM ...';
for r IN (statement) -- HOW TO DO THIS
    loop
       --do sth;
    end loop;

What do you mean exactly? for r in EXECUTE IMMEDIATE(statement) dows not work — Mattizin
– Mattizin, Commented Aug 7, 2017 at 13:10
check this: docs.oracle.com/cd/B28359_01/appdev.111/b28370/… — LoztInSpace
– LoztInSpace, Commented Aug 7, 2017 at 13:11

William Robertson · Accepted Answer · 2017-08-07 15:08:40Z

3

For a dynamic ref cursor, you need to define everything explicitly:

declare 
    sqlstring long := 'select 123 as id, ''demo'' as somevalue from dual where dummy = :b1';
    resultset sys_refcursor;

    type demo_rectype is record
        ( id integer
        , somevalue varchar2(30) );

    demorec demo_rectype;
begin
    open resultset for sqlstring using 'X';

    loop
        fetch resultset into demorec;
        exit when resultset%notfound;
        dbms_output.put_line('id=' || demorec.id || ' somevalue=' || demorec.somevalue);
    end loop;

    close resultset;
end;

You can parse the cursor and figure out the column names and datatypes with DBMS_SQL. Example here: www.williamrobertson.net/documents/refcursor-to-csv.shtml

edited Aug 7, 2017 at 15:08

answered Aug 7, 2017 at 13:32

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PL/SQL Procedure Use String Select Statement in for loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related