Getting a Ref Cursor from Custom Cursor Type

We have an existing PL SQL procedure called "Get()" that, when provided with an order_no_ and a sequence_no_, returns a named REF CURSOR type called "CURSOR_TYPE". I need to make an "overload" of this method where the programmer just passes in the order_no_ (no sequence_no_), and it calls that method repeatedly and collects all of the records for all sequence_no_ values into a single return "CURSOR_TYPE" REF CURSOR. Here's what I wrote to try to accomplish this, trying to follow the example shown here.

    PROCEDURE Get(order_no_ IN VARCHAR2, results_cursor OUT CURSOR_TYPE)
    AS
    --Declare a "nested table type" table
    TYPE CoC_RowType IS TABLE OF customer_order_charge_cfv%ROWTYPE; 
    CoC_RowTable CoC_RowType := CoC_RowType();
    CURSOR c1 IS
      SELECT coc.SEQUENCE_NO
      FROM customer_order_charge_cfv coc
      WHERE coc.ORDER_NO = order_no_;
    BEGIN
    FOR i in c1 LOOP
        CoC_RowTable.extend();
        Get(order_no_, i.sequence_no, results_cursor);
        FETCH results_cursor INTO CoC_RowTable(CoC_RowTable.count);
    END LOOP;

    OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

    END Get;

When I try to compile this code into my package, however, I get the following error:

    Compilation errors for PACKAGE BODY GLOB1APP.GFS_CUSTOMER_ORDER_CHARGE_CFP

    Error: PLS-00382: expression is of wrong type
    Line: 81
    Text: OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

    Error: PL/SQL: ORA-22905: cannot access rows from a non-nested table item
    Line: 81
    Text: OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

    Error: PL/SQL: SQL Statement ignored
    Line: 81
    Text: OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

And it appears to erroring on this line:

    OPEN results_cursor FOR SELECT * FROM TABLE(CoC_RowTable);

What am I doing wrong? And how can I accomplish my goal? Is this totally the wrong path to accomplishing this?

Why not change the existing procedure and query to do both? Simple example:

SQL> create table customer_order_charge_cfv(
  2    order_no number,
  3    sequence_no number
  4  );

Table CUSTOMER_ORDER_CHARGE_CFV created.

SQL> insert into customer_order_charge_cfv
  2  select 1,1 from dual union all
  3  select 1,2 from dual;

2 rows inserted.

SQL> create or replace PROCEDURE Get(
  2    order_no_ IN VARCHAR2, 
  3    sequence_no_ IN VARCHAR2 default null, 
  4    results_cursor OUT SYS_REFCURSOR
  5  ) AS
  6  BEGIN
  7  OPEN results_cursor FOR 
  8    SELECT *
  9    FROM customer_order_charge_cfv coc
 10    WHERE coc.ORDER_NO = order_no_
 11    and (coc.sequence_no = sequence_no_ or sequence_no_ is null);
 12  END Get;
 13  /

Procedure GET compiled

SQL> var rc refcursor;

SQL> exec get(1,2,:rc);

PL/SQL procedure successfully completed.


SQL> print :rc;

  ORDER_NO SEQUENCE_NO
---------- -----------
         1           2

SQL> exec get(1,results_cursor => :rc);

PL/SQL procedure successfully completed.

SQL> print :rc;

  ORDER_NO SEQUENCE_NO
---------- -----------
         1           1
         1           2

Regards, Stew