CODEWITHSUNDEEP
oracle-databasestored-proceduresoracle-cursor
user241841
user241841

Reputation: 23

ORACLE: Select from REF CURSOR returned from stored procedure

I have some procedure which returns REF CURSOR(I can't change it or avoid using it), but contents may be different (number of columns) depending on an IN variable. Passing one value we'll get two columns, and passing another value we'll get three columns. I need to get results from that cursor using a plain SELECT statement so I ended up with PIPLINE FUNCTION. But I can't find a solution for dynamically changing set of columns got from cursor. Is there any other way to "select" from cursor or correctly handle dynamically changing set of columns? Thanks a lot!

CREATE OR REPLACE package MYPKG as 
    TYPE REC IS RECORD (
      A NUMBER(18),
      B DATE,
      C NUMBER(18) DEFAULT 1
    );
    TYPE TCURSOR IS REF CURSOR;
    type CUR_TAB is table of REC;
    procedure CUR_PROC(CUR OUT TCURSOR, SEL_TYPE IN NUMBER); --some procedure with undefined columns
    FUNCTION CUR_TAB_PIPLINED(P_SEL_TYPE IN NUMBER) RETURN MYPKG.CUR_TAB PIPELINED;

END MYPKG;
/


CREATE OR REPLACE package body MYPKG 
as 
   function CUR_TAB_PIPLINED(P_SEL_TYPE IN NUMBER) return MYPKG.CUR_TAB  PIPELINED
   as 
           TAB_DATA CUR_TAB; 
           TEMP_CUR TCURSOR;
   begin 
           MYPKG.CUR_PROC(TEMP_CUR, P_SEL_TYPE);
           fetch TEMP_CUR bulk collect into TAB_DATA; 
           close TEMP_CUR; 
           pipe row(TAB_DATA(1)); --getting 1 row is enough for example
   end;

   PROCEDURE CUR_PROC (CUR OUT TCURSOR, SEL_TYPE IN NUMBER) as 
   BEGIN
        IF SEL_TYPE = 1 THEN 
           OPEN CUR FOR SELECT 1 AS A, SYSDATE AS B FROM dual;
        ELSE
           OPEN CUR FOR SELECT 1 AS A, SYSDATE AS B, 5 AS C FROM dual;
        END IF;
   end; 
END MYPKG;
/


SELECT * FROM TABLE(MYPKG.CUR_TAB_PIPLINED(1));  --ORA-00942

SELECT * FROM TABLE(MYPKG.CUR_TAB_PIPLINED(2));  --works good

Upvotes: 1

Views: 617

Answers (1)

MT0
MT0

Reputation: 168578

You need to fetch the correct number of columns for the cursor:

CREATE OR REPLACE package body MYPKG 
as 
   function CUR_TAB_PIPLINED(P_SEL_TYPE IN NUMBER) return MYPKG.CUR_TAB  PIPELINED
   as 
     v_a NUMBER(18);
     v_b DATE;
     v_c NUMBER(18);
     TEMP_CUR TCURSOR;
   begin
     MYPKG.CUR_PROC(TEMP_CUR, P_SEL_TYPE);
     IF p_sel_type = 2 THEN
       LOOP
         FETCH temp_cur INTO v_a, v_b, v_c;
         EXIT WHEN temp_cur%NOTFOUND;
         PIPE ROW (REC(v_a, v_b, v_c));
       END LOOP;
     ELSE
       LOOP
         FETCH temp_cur INTO v_a, v_b;
         EXIT WHEN temp_cur%NOTFOUND;
         PIPE ROW (REC(v_a, v_b, 1));
       END LOOP;
     END IF;
     CLOSE TEMP_CUR; 
   EXCEPTION
     WHEN NO_DATA_NEEDED THEN
       CLOSE TEMP_CUR;
   end;

   PROCEDURE CUR_PROC (CUR OUT TCURSOR, SEL_TYPE IN NUMBER) as 
   BEGIN
        IF SEL_TYPE = 1 THEN 
           OPEN CUR FOR SELECT 1 AS A, SYSDATE AS B FROM dual;
        ELSE
           OPEN CUR FOR SELECT 1 AS A, SYSDATE AS B, 5 AS C FROM dual;
        END IF;
   end; 
END MYPKG;
/

Note: you also need to ensure the cursor is closed if you stop calling the pipelined function before the cursor is exhausted (i.e. catch the NO_DATA_NEEDED exception).

db<>fiddle here

Upvotes: 1

Related Questions