How to loop through columns in an oracle pl/sql cursor

Question

I am creating a dynamic cursor and I would like to loop over the columns that exist in the cursor. How would I do that?

For example:

create or replace procedure dynamic_cursor(empid in varchar2, RC IN OUT sys_refcursor) as
     stmt varchar2(100);
   begin
     stmt := 'select * from employees where id = ' || empid;
     open RC for stmt using val;

     for each {{COLUMN OR SOMETHING}}
       --TODO: Get this to work
     loop;
end;

DCookie · Accepted Answer · 2010-04-09 15:36:01Z

3

You will need to use Oracle Dynamic SQL, most likely method 4.

EDIT: Sorry, the above is for Pro*C. You will need to use the DBMS_SQL package. It's fairly complex, but will allow you to parse, execute, and fetch any arbitrary SQL statement you need, all at run time. In particular, have a look at examples 3 and 8.

edited Apr 9, 2010 at 15:36

answered Apr 9, 2010 at 15:01

DCookie

43.7k11 gold badges88 silver badges93 bronze badges

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

1 Comment

Lloyd Over a year ago

I have the dynamic sql working. My example code is oversimplified. What I would like to do is have the stmt variable be much more dynamic, to the point where I would not be commited to a fieldset. That is why I want to loop of the columns in the cursor.

Collectives™ on Stack Overflow

How to loop through columns in an oracle pl/sql cursor

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related