Function retuning Sys Refcursor-CodePudding

How to call a function returning sys refcursor in select statement. I have created a function like this and I want to call in the select statement returning both values coming from function. So I used in the query like this, but it is returning cursor in place of column values.

Function HCLT_GET_TASK_DATES(i_ownerid IN NUMBER, i_itemid IN NUMBER)
  RETURN SYS_REFCURSOR IS
  o_DATACUR SYS_REFCURSOR;
begin
  open o_DATACUR for
    select nvl(TO_CHAR(min(pref_start), 'DD-MON-YYYY'), '') AS MIN_DATE,
           nvl(TO_CHAR(max(pref_finish), 'DD-MON-YYYY'), '') AS MAX_DATE
      from autoplanallocation
     WHERE project_id = i_ownerid
       AND task_id = i_itemid;
  RETURN o_DATACUR;
END;
/

SELECT HCLT_GET_TASK_DATES(267157, 15334208),
       tv.taskid,
       tv.wbs_code AS wbscode,
       tv.taskcode,
       tv.act_name,
       ltrim(regexp_replace(tv.stageactorlovs, '[^#]*#(\d ?),', ',\1'), ',') as stageactorlovs,
       tv.createdat,
       tv.pushedtoTaskModule,
       tv.OVERALLSTATUS AS overallstatus1,
       tv.ACTIVITY_CODE_ID,
       tv.wbs_code,
       TO_CHAR(tv.pref_st, 'DD-MON-YYYY') AS pref_st,
       TO_CHAR(tv.pref_fn, 'DD-MON-YYYY') AS pref_fn,
       tv.ACTL_EFFORT,
       tv.rollup_effort,
       tv.overAllStatus,
       tv.FIELD5,
       tv.FIELD4,
       tv.activity_code_id
  FROM task_view tv, autoplanallocation al
 WHERE al.project_id = tv.ownerid( )
   and al.task_id = tv.taskid( )
   and tv.ownertype = 'Prj'
   AND tv.ownerid = 267157
   AND (tv.overAllStatus = 'All' OR 'All' = 'All')
   AND (TaskId IN
       ((SELECT xyz
            FROM (SELECT ToItemID xyz
                    FROM ItemTraceability it
                   WHERE it.FromOwnerType = 'Prj'
                     AND it.FromOwnerID = 267157
                     AND it.FromItemType = it.FromItemType
                     AND it.FromChildItemType = 'USTRY'
                     AND it.FromItemID = 15334208
                     AND it.ToOwnerType = 'Prj'
                     AND it.ToOwnerID = 267157
                     AND it.ToItemType = it.ToItemType
                     AND it.ToChildItemType = 'Tsk'
                  UNION ALL
                  SELECT FromItemID
                    FROM ItemTraceability it
                   WHERE it.ToOwnerType = 'Prj'
                     AND it.ToOwnerID = 267157
                     AND it.ToItemType = it.ToItemType
                     AND it.ToChildItemType = 'USTRY'
                     AND it.ToItemID = 15334208
                     AND it.FromOwnerType = 'Prj'
                     AND it.FromOwnerID = 267157
                     AND it.FromItemType = it.FromItemType
                     AND it.FromChildItemType = 'Tsk'))))
 ORDER BY UPPER(wbs_code) ASC;

CodePudding user response：

I do not think there is a native way of parsing nested cursors using SQL or PL/SQL code.

In Java with an Oracle JDBC database driver, you can:

Use oracle.jdbc.driver.OraclePreparedStatement.executeQuery to get a java.sql.ResultSet
Which can be cast to an oracle.jdbc.driver.OracleResultSet
Then you can iterate through the rows of the result set and for each row you can use oracle.jdbc.driver.OracleResultSet.getCursor() to get the nested cursor.
- You can then iterate through that nested cursor in exactly the same way you iterated through the outer cursor to extract rows from the nested cursor.
- You should then close the nested cursor (although it will be automatically closed when the containing parent cursor is closed).
Finally, close the parent cursor.

If you want an SQL solution then do not return a cursor and return a nested table collection data type instead.

Or, for a single row with multiple columns, return an object type:

CREATE TYPE date_range_obj AS OBJECT(
  start_date DATE,
  end_date   DATE
)
/

CREATE FUNCTION HCLT_GET_TASK_DATES(
  i_ownerid IN autoplanallocation.project_id%TYPE,
  i_itemid  IN autoplanallocation.task_id%TYPE
)
RETURN date_range_obj
IS
  v_range date_range_obj;
begin
  SELECT date_range_obj(MIN(pref_start), MAX(pref_finish))
  INTO   v_range
  FROM   autoplanallocation
  WHERE  project_id = i_ownerid
  AND    task_id = i_itemid;

  RETURN v_range;
END;
/

Then, for example:

SELECT HCLT_GET_TASK_DATES(1,2).start_date,
       HCLT_GET_TASK_DATES(1,2).end_date
FROM   DUAL;

db<>fiddle here

CodePudding user response：

If you are able to change this design, then it would be better to do in plain join and aggregation (or possibly with left join lateral in case of low cardinality input).

But there's a way to achieve the desired result with plain SQL in 11g and above using the ability of dbms_xmlgen package to process arbitrary cursor. Below is the code:

create table t_lkp (id,dt)
as
select
  trunc(level/4   1)
  , date '2022-01-01'   level
from dual
connect by level < 11

create or replace function f_lkp (
  p_id in int
)
return sys_refcursor
as
  o_res sys_refcursor;
begin
  open o_res for
    select
      min(dt) as dtfrom
      , max(dt) as dtto
    from t_lkp
    where id = p_id;
  
  return o_res;
end;
/

with a as (
  select
    level as i,
    dbms_xmlgen.getxmltype(
      /*ctx doesn't accept sys_refcursor, so we had to create a context*/
      ctx => DBMS_XMLGEN.NEWCONTEXT(f_lkp(level))
    ) as val
  from dual
  connect by level < 6
)
select
  i
  , xmlquery(
    '/ROWSET/ROW/DTFROM/text()'
    passing a.val returning content null on empty
  ) as dtfrom
  , xmlquery(
    '/ROWSET/ROW/DTTO/text()'
    passing a.val returning content null on empty
  ) as dtto
from a

 I | DTFROM              | DTTO               
-: | :------------------ | :------------------
 1 | 2022-01-02 00:00:00 | 2022-01-04 00:00:00
 2 | 2022-01-05 00:00:00 | 2022-01-08 00:00:00
 3 | 2022-01-09 00:00:00 | 2022-01-11 00:00:00
 4 | null                | null               
 5 | null                | null

db<>fiddle here

Please note, that it will open too many cursors in case of large input dataset and parallel processing, which will dramatically consume resourses. So it would be much better to use plain join.