Fetching rows from a function returning ref cursor dynamically

Question

CREATE OR REPLACE PACKAGE abc
IS
 TYPE abc_cur IS REF CURSOR;

 FUNCTION TEST
  RETURN abc_cur;
 END;
/    

CREATE OR REPLACE PACKAGE BODY abc
IS
FUNCTION TEST
RETURN abc_cur
IS
v_select   VARCHAR2(2000);
 BEGIN
 v_select                   := 'Select a1,a2 from pqr';
 OPEN abc_cur FOR v_select;
 RETURN abc_cur;
 END TEST;
END abc;

This is the dummy package that I have created, in function test select is dynamic that mean data column varies. Now my issue is that when I call this function from any other procedure or function, I get the ref cursor, then how can I fetch the data from that cursor if I don't know which column it is returning dynamically?

vijayaragavan · Accepted Answer · 2012-03-05 19:15:00Z

1

If a1 and a2 are dynamic, change during runtime - you could consider using dbms_sql instead of refcursor. Here is an example of how dynamically the rows are fetched where number of columns and their type is not known until runtime. You could change it according to your requirement.

answered Mar 5, 2012 at 19:15

vijayaragavan

1764 bronze badges

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

Collectives™ on Stack Overflow

Fetching rows from a function returning ref cursor dynamically

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