passing sys_refcursor from function to sys_refcursor out parameter in procedure

I have problem with outputting results from the sys_refcursor returned from the function stored in the variable crs_scenarios. I then want to pass this collection of data to the output parameter pout_result. I get the error PLS-00487: Invalid reference to variable ‘POUT_RESULT’. Can you advise me please, how to solve this issue?

Thanks a lot!

declare
                           pin_scenarioName          scenarios.scen_name%TYPE := 'zz_berlin_testen';
                           pin_scenarioRegion        inf_ausbaugebiete.ausg_name%TYPE DEFAULT NULL;
                           pin_scenarioCutOffDate    scenarios.scen_cut_off_date%TYPE DEFAULT NULL;
                           pin_scenarioStatus        scenario_status.scst_name%TYPE DEFAULT NULL;
                           pout_result               SYS_REFCURSOR;
                           pout_strerrorcode         VARCHAR2(1000);
                           pout_strerrormessage      VARCHAR2(1000);

BEGIN
    pout_result := funk30.pbi$capi_export_pck.cfn_getscenarios(pin_scenarioName       =>   pin_scenarioName,
                                                                 pin_scenarioRegion     =>   pin_scenarioRegion,
                                                                 pin_scenarioCutOffDate =>   pin_scenarioCutOffDate,
                                                                 pin_scenarioStatus     =>   pin_scenarioStatus,
                                                                 pout_strerrorcode      =>   pout_strerrorcode,
                                                                 pout_strerrormessage    =>  pout_strerrormessage);

    dbms_output.put_line(pout_result.ID || ' ' ||pout_result.NAME || ' ' ||pout_result.CUT_OFF || ' ' ||pout_result.STATUS ||
                         ' ' || pout_result.PRIORITY || ' ' ||pout_result.PARENT_SCENARIO|| ' ' ||pout_result.REGION|| ' ' || pout_result.REMARK);
    

END cpr_getscenarios;


Function:

FUNCTION mfn_getscenarios(pin_scenarioName          IN scenarios.scen_name%TYPE DEFAULT NULL,
                            pin_scenarioRegion        IN inf_ausbaugebiete.ausg_name%TYPE DEFAULT NULL,
                            pin_scenarioCutOffDate    IN scenarios.scen_cut_off_date%TYPE DEFAULT NULL,
                            pin_scenarioStatus        IN scenario_status.scst_name%TYPE DEFAULT NULL,
                            pout_strerrorcode         OUT VARCHAR2,
                            pout_strerrormessage      OUT VARCHAR2)
    RETURN SYS_REFCURSOR IS

    crs_scenarios SYS_REFCURSOR;
    n_cnt_exists  NUMBER;
    ex_scennotexists EXCEPTION;
    strprocedurename VARCHAR2(128) := package_name || '.mfn_getScenarios';
  BEGIN
    BEGIN

      SELECT 1 INTO n_cnt_exists FROM scenarios WHERE rownum = 1;

    EXCEPTION
      WHEN no_data_found THEN
        RAISE ex_scennotexists;
    END;

    OPEN crs_scenarios FOR
      SELECT scen.scen_id         id,
           scen.scen_name         NAME,
           scen.scen_cut_off_date cut_off,
           scst.scst_name         STATUS,
           scen.scen_priority     PRIORITY,
           parent.scen_name       PARENT_SCENARIO,
           ausg.ausg_name         REGION,
           scen.scen_comment      REMARK
      FROM scenarios scen,
           scenarios parent,
           scenario_status scst,
           inf_ausbaugebiete ausg
      WHERE scen.scen_scst_id = scst.scst_id
      AND scen.scen_parent_scen_id = parent.scen_id(+)
      AND scen.scen_ausg_id= ausg.ausg_id(+)
      AND lower(scen.scen_name) like nvl(lower('%'||pin_scenarioName||'%'), lower(scen.scen_name))
      AND NVL(ausg.ausg_name, 'xxxxx') = nvl(pin_scenarioRegion, NVL(ausg.ausg_name, 'xxxxx'))
      AND scen.scen_cut_off_date = nvl(pin_scenarioCutOffDate, scen.scen_cut_off_date)
      AND scst.scst_name = nvl(pin_scenarioStatus, scst.scst_name);

    pout_strerrorcode    := '0';
    pout_strerrormessage := '';

    RETURN crs_scenarios;

  EXCEPTION
    WHEN ex_scennotexists THEN
      pout_strerrorcode    := 'API-00239';
      pout_strerrormessage := rtrim(api_err_pck.apierrormsg('API-00239', strprocedurename), ' @');
      RETURN NULL;
    WHEN OTHERS THEN
      pout_strerrorcode    := '-1';
      pout_strerrormessage := substr(SQLERRM, instr(SQLERRM, 'ORA') + 11, length(SQLERRM));
      RETURN NULL;
  END mfn_getscenarios;

Answer

Sys_refcursor is only a SQL definition. If you want to run it, you have to FETCH data. So pout_result varible has no data itself.

——– function —–

create or replace FUNCTION mfn 
    RETURN SYS_REFCURSOR IS

    crs_scenarios SYS_REFCURSOR;
      BEGIN
   
    OPEN crs_scenarios FOR
      SELECT dummy from dual;

    RETURN crs_scenarios;

  END ;

— execution

set serveroutput on
declare
                    
pout_result   SYS_REFCURSOR;
                        
type pout_result_tab is table of dual%rowtype; -- cursor datatype
pout_result_t pout_result_tab;
BEGIN
    pout_result := mfn;
    fetch pout_result bulk collect  into  pout_result_t;  -- bulk collect because I assume you have recordset, not one record
    dbms_output.put_line(pout_result_t(1).dummy);
    

END ;
/

— Result

X


PL/SQL procedure successfully completed.

Advertisement

Answer