Function retuning Sys Refcursor

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;

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