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!
New contributor
SDJ is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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 (
robeing 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.
1