Outer function returning multi-line values with the help of refcursors

I'm learning ref cursors, record type concepts. Below is the logic I'm trying [I kn which is wrong at few lines], On high level, Outer function 'get_modified_ssd_rsd2' has order number as one of parameter [with multiple lines under it], where one by one line_ids are passed to inner function. 'get_modified_ssd_rsd1' which is inner function returning 2dates in refcursors. It is tested successfully. I'm trying/ask is, how can I return orderno, lineid, date1,date2 - four parameters via outer function [i.e. get_modified_ssd_rsd2] in refcursor?

CREATE OR REPLACE FUNCTION get_modified_ssd_rsd2 (
    p_depot_i                 IN VARCHAR2,
    p_planned_delivery_dttm   IN DATE,
    p_service_level_id        IN NUMBER,
    p_rmaord_no               IN NUMBER)

    RETURN SYS_REFCURSOR
IS
    CURSOR c1 (p_rmaord_no NUMBER)
    IS SELECT line_id
          FROM SalesOrerLinesTable
         WHERE 1 = 1 AND order_number = 801259549;

    l_ssd              tablename.column%TYPE;
    l_rsd              tablename.column%TYPE;
    l_rmaord_no        tablename.column%TYPE;
    j                  NUMBER := 0;
    l_dates            SYS_REFCURSOR;
    
    TYPE line_ssdrsd_rectype IS REF CURSOR;
    line_ssdrsd_type   line_ssdrsd_rectype;

    TYPE rec_orderline
        IS RECORD
    (
        rmaord_no             tablename.column%TYPE,
        line_id               tablename.column%TYPE,
        schedule_ship_date    tablename.column%TYPE,
        request_ship_date     tablename.column%TYPE
    );
    ro                 rec_orderline;

BEGIN
    l_rmaord_no := p_rmaord_no;

    FOR i IN c1 (l_rmaord_no)LOOP
        j := j + 1;

        l_dates :=
            get_modified_ssd_rsd1 (p_depot_i,
                                   p_planned_delivery_dttm,
                                   p_service_level_id,
                                   i.line_id);

        lst (j).rmaord_no := l_rmaord_no;
        lst (j).line_id := i.line_id;
        lst (j).schedule_ship_date := l_dates.l_ssd;
        lst (j).request_ship_date := l_dates.l_rsd;
    END LOOP;

EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (' Error - ' || SQLERRM);

END get_modified_ssd_rsd2;

Thank you!

• Tried pipelined functions but couldn't capture results from outer functions in refcursors
• Tried record type, record type of refcursor in outer function and then capture multiple lines' returned by inner function in refcursors etc .. etc, but no luck.

Returning results in refcursor via outer function is mandatory ask. Thank you!

This is a somewhat simplified example of what you have, but should illustrate the problem (actually, a solution).

I don't have your tables or other objects so I'm using Scott's sample schema, its EMP (employees) table, returning employee name, job and salary info.

Create types at SQL level, not within the function:

SQL> CREATE OR REPLACE TYPE rec_orderline IS OBJECT
  2  (
  3     ename VARCHAR2 (20),
  4     job VARCHAR2 (20),
  5     sal NUMBER
  6  );
  7  /

Type created.

SQL> CREATE OR REPLACE TYPE rot IS TABLE OF rec_orderline;
  2  /

Type created.

Function:

• declare appropriate variables (ro being the most important)
• I'm populating it with values fetched by the cursor (within the cursor FOR loop)
• pay attention to the way it is done (lines #13 and 14)
• finally, open ref cursor (for values contained in ro) and return it

SQL> CREATE OR REPLACE FUNCTION f_get (i_deptno IN NUMBER)
  2     RETURN SYS_REFCURSOR
  3  IS
  4     ro      rot := rot ();
  5     j       NUMBER := 0;
  6     retval  SYS_REFCURSOR;
  7  BEGIN
  8     FOR i IN (SELECT ename, job, sal
  9                 FROM emp
 10                WHERE deptno = i_deptno)
 11     LOOP
 12        j := j + 1;
 13        ro.EXTEND;
 14        ro (j) := rec_orderline (i.ename, i.job, i.sal);
 15     END LOOP;
 16
 17     OPEN retval FOR SELECT * FROM TABLE (ro);
 18
 19     RETURN retval;
 20  END;
 21  /

Function created.

Testing:

SQL> SELECT f_get (10) FROM DUAL;

F_GET(10)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ENAME                JOB                         SAL
-------------------- -------------------- ----------
CLARK                MANAGER                    2450
KING                 PRESIDENT                  5000
MILLER               CLERK                      1300


SQL>

Now, apply that to your function.