Calling ref_cursor from another PLSQL function

Question

I have three PLSQL functions: A, B and C.

The idea is this: C is calling B, B is calling A.

• Function A, when it's called by B, returns a numeric value as a status indicator AND a ref cursor with tabular results. E.g. function_A (A1 in varchar2, A2 out sys_refcursor) return number;

• Function B, when it receives the results from A, is expected to reformat the results before passes them on to C, also in a form of a ref cursor.

A is an existing function and it cannot be amended, while B and C will be completely new functions.

The question is, how do I fetch the ref cursor from A? I was able to get the numeric value returned by the function (i.e the status indicator), but I have problem fetching the results of the ref cursor from A.

1. If I'm calling A from B, can I assume that the ref cursor of A is automatically opened?

2. What are the logical steps to get the results from A's ref cursor? E.g. can I fetch the results into an object type?

P/S. I have very limited programming experience and am only few months new in PLSQL.

Any hints will be much appreciated.

Answer 1

Since you have not given us the code functions, we will be based on the description of your functions.

According to the description, you have 3 functions:

Function A.

create or replace function A(A1 in varchar2, A2 out sys_refcursor) return number is
begin
  open A2 for select 1 from dual;
  return 2;
 end;

Function B.

create or replace function B(B1 out sys_refcursor) return number is
 cur sys_refcursor;
 res_A number;
 row_ your_table_a%rowtype;
 begin
  res_A := A('',cur); 
  loop
   fetch cur into row_;
   exit when cur%notfound;
   --proccess with row A
  end loop; 
  open B1 for select 2 from dual;
  return 1;
 end;

Function C

create or replace function C() return   number is
 res_B number; 
 cur sys_refcursor;
 row_ your_table_b%rowtype;
begin
  res_B:= B(cur);
  loop
   fetch cur into row_;
   exit when cur%notfound;
   --proccess with row B
  end loop; 
  return 2;
 end;

Answer 2

Maybe you can try the below snippet. Tried to replicate the scenario you mentioned in the question. Hope this helps.

CREATE OR REPLACE FUNCTION A_TEST(
    A1 IN VARCHAR2,
    A2 OUT sys_refcursor )
  RETURN NUMBER
AS
  lv_num PLS_INTEGER;
BEGIN
  NULL;
  OPEN a2 FOR SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 19;
  RETURN 1;
END;

CREATE OR REPLACE FUNCTION B_TEST
  RETURN sys_refcursor
AS
  lv_cur sys_refcursor;
  lv_num PLS_INTEGER;
BEGIN
  lv_num:=A_TEST('AV',lv_cur);
  RETURN lv_cur;
END;



CREATE OR REPLACE FUNCTION C_TEST
  RETURN sys_refcursor
AS
  tab PLS_INTEGER;
  lv_cur sys_refcursor;
BEGIN
  lv_cur:=B_TEST;
  LOOP
    FETCH lv_cur INTO tab;
    EXIT
  WHEN lv_cur%NOTFOUND;
    dbms_output.put_line(tab);
  END LOOP;
END;