CODEWITHSUNDEEP
plsqloracle11g
Gaurav Soni
Gaurav Soni

Reputation: 6338

Fetching rows from a function returning ref cursor dynamically

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?

Upvotes: 0

Views: 1964

Answers (1)

vijayaragavan
vijayaragavan

Reputation: 176

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.

Upvotes: 1

Related Questions