oracle - How to make advanced operations on SYS_REFCURSOR?

Question

I have several procedures and functions that should make some computations on the same set of data (such that If I change the query of this set of data, the change will be reflected on all the other functions).

What I'm trying to do is creating a function that generates a SYS_REFCURSOR corresponding to the shared set of data.

For example :

MyTable : table(ID number, Amount number);
Type RefCursor** IS REF CURSOR RETURN MyTable%ROWTYPE;

The function that returns a cursor for the shared set of data :

function GetSharedSetOfData(SomeArgument varchar2) RETURN RefCursor is                
     Cur  RefCursor;
begin
    Open Cur for select * from MyTable where 
                        Condition1 and/or Condition2;
    return Cur;
end;

This function should be used in other functions like :

-- Function A
function Function_A_GetTheTotalOfAllRows return number is 
    Output number;
begin
    select sum(Amount) into Output from table(CAST(GetSharedSetOfData(SomeArgumentValue) as MyTable%rowtype));
    return Output;
end;

-- Function B
function Function_B_GetPartialTotalOfAllRows return number is 
    Output number;
begin
    select sum(Amount) into Output from table(CAST(GetSharedSetOfData(SomeArgumentValue) as MyTable%rowtype)) where ID < 100 ;
    return Output;
end;

-- Function C
function Function_C_GetOtherComputation return number is 
    Output number;
begin
    select sum(Amount) into Output from table(CAST(GetSharedSetOfData(SomeArgumentValue) as MyTable%rowtype)) where condition_Xx... ;
    return Output;
end;

The function GetSharedSetOfData compiles and work properly. However the following queries don't compile.

select sum(Amount) into Output from table(CAST(GetSharedSetOfData(SomeArgumentValue) as MyTable%rowtype)); 

I'm getting the error :

ORA-00911: invalid character it refers to the character '%' on MyTable%rowtype

Does anyone know how to do that please ?

Thanks. Cheers,

question from:https://stackoverflow.com/questions/66052345/how-to-make-advanced-operations-on-sys-refcursor

Answer 1

How about this?

Sample table:

SQL> select * From mytable;

        ID     AMOUNT
---------- ----------
      7369        800
      7566       2975
      7788       3000

Your first function:

SQL> create or replace function getsharedsetofdata(someargument varchar2)
  2    return sys_refcursor is
  3    cur  sys_refcursor;
  4  begin
  5      open cur for select * from mytable where 1 = 1;
  6      return cur;
  7  end;
  8  /

Function created.

Your second function (modified, quite a lot):

SQL> create or replace function Function_A
  2    return number is
  3    rc       sys_refcursor;
  4    l_id     mytable.id%type;
  5    l_amount mytable.amount%type;
  6    Output   number := 0;
  7  begin
  8      rc := getsharedsetofdata('a');
  9      loop
 10        fetch rc into l_id, l_amount;
 11        exit when rc%notfound;
 12        output := output + l_amount;
 13      end loop;
 14      return output;
 15  end;
 16  /

Function created.

Testing:

SQL> SELECT function_a from dual;

FUNCTION_A
----------
      6775

SQL>

---

As you can see, it works; see if you can adjust it to your needs.