How to fetch data from Cursor Oracle/Toad

Question

I have function that returns cursor with multiple date values and i need to fetch those data one by one into this:

CALLMISUSER01.INSERT_DEP004(First data of the cursor, Second data of the cursor, Third data of the cursor. . . etc);

my main function is:

Create or replace FUNCTION Rep_dates(pdt_ref_date IN DATE)
RETURN SYS_REFCURSOR IS
    pcr_result SYS_REFCURSOR;
BEGIN
    OPEN pcr_result FOR
    SELECT  
        trunc(pdt_ref_date),
        trunc(pdt_ref_date) - 1,
        trunc(pdt_ref_date) - 2,
        trunc(pdt_ref_date) - 3,
        trunc(pdt_ref_date, 'mm'),       
        add_months(trunc(pdt_ref_date, 'MM'), -1) first_day_of_last_month,
        add_months(trunc(pdt_ref_date, 'MM'), -2) first_day_of_last_month,
        add_months(trunc(pdt_ref_date, 'MM'), -3) first_day_of_last_month,
        add_months(trunc(pdt_ref_date, 'Q'), -6) first_day_of_last_quarter,
        add_months(trunc(pdt_ref_date, 'Q'), -9) first_day_of_last_quarter,
        add_months(trunc(pdt_ref_date, 'Q'), -12) first_day_of_last_quarter,
        add_months(trunc(pdt_ref_date, 'Q'), -15) first_day_of_last_quarter,
        add_months(trunc(pdt_ref_date, 'Q'), -18) first_day_of_last_quarter
    FROM dual;

    RETURN pcr_result;
END;

Answer 1

Firstly, give proper aliases for the columns returned as REF CURSOR from your function. Then you may define a matching RECORD type and FETCH the cursor into it. This is one method you may refer to individual columns. Other option is to define separate variables for each column.

Function

CREATE OR REPLACE FUNCTION rep_dates(pdt_ref_date IN DATE)
RETURN SYS_REFCURSOR IS
    pcr_result SYS_REFCURSOR;
BEGIN
    OPEN pcr_result FOR
    SELECT  
        trunc(pdt_ref_date) as  pdt_ref_date,
        trunc(pdt_ref_date) - 1 pdt_ref_date_1,
        trunc(pdt_ref_date) - 2 pdt_ref_date_2,
        trunc(pdt_ref_date) - 3 pdt_ref_date_3,
        trunc(pdt_ref_date, 'mm') as pdt_ref_date_mon ,       
        add_months(trunc(pdt_ref_date, 'MM'), -1) first_day_of_last_month,
        add_months(trunc(pdt_ref_date, 'MM'), -2) first_day_of_2nd_last_month,
        add_months(trunc(pdt_ref_date, 'MM'), -3) first_day_of_3rd_last_month,
        add_months(trunc(pdt_ref_date, 'Q'), -6) first_day_of_last_quarter_1,
        add_months(trunc(pdt_ref_date, 'Q'), -9) first_day_of_last_quarter_2,
        add_months(trunc(pdt_ref_date, 'Q'), -12) first_day_of_last_quarter_3,
        add_months(trunc(pdt_ref_date, 'Q'), -15) first_day_of_last_quarter_4,
        add_months(trunc(pdt_ref_date, 'Q'), -18) first_day_of_last_quarter_5
    FROM dual;

    RETURN pcr_result;
END;
/

Calling block

DECLARE
     m_curs                        SYS_REFCURSOR;
     TYPE pcr_rec IS RECORD ( 
     pdt_ref_date                  DATE,
     pdt_ref_date_1                DATE,
     pdt_ref_date_2                DATE,
     pdt_ref_date_3                DATE,
     pdt_ref_date_mon              DATE,
     first_day_of_last_month       DATE,
     first_day_of_2nd_last_month   DATE,
     first_day_of_3rd_last_month   DATE,
     first_day_of_last_quarter_1   DATE,
     first_day_of_last_quarter_2   DATE,
     first_day_of_last_quarter_3   DATE,
     first_day_of_last_quarter_4   DATE,
     first_day_of_last_quarter_5   DATE
 );
 pcr_res pcr_rec;
BEGIN
     m_curs := rep_dates(SYSDATE);--pass your pdt_ref_date
     FETCH m_curs INTO pcr_res;

     CALLMISUSER01.INSERT_DEP004(pcr_res.pdt_ref_date,pcr_res.pdt_ref_date_1,
      --..... --all other column aliases.
      pcr_res.first_day_of_last_quarter_5);

END;
/