Skip to Main Content
  • Questions

  • How to assign record type data to ref cursor

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Anuradha.

Asked: June 11, 2019 - 7:50 am UTC

Last updated: June 13, 2019 - 10:16 am UTC

Version: 11.1.1.9

Viewed 10K+ times! This question is

You Asked

Hi Tom,

This is a requirement in OBIEE cloud, where we use a anonymous block to get the expected result.

We have an Anonymous block to capture selective results(selective rows and columns) of a query into record type.
Now to pass this record type outside of the block, the only option we have is to use ref cursors.


Could you please help me on, how to assign data from record type to ref cursor.


Below is the code used:

DECLARE
TYPE ParticipantRatePayout is record 
(
 p_name  CN_SRP_PARTICIPANT_HDR_RO_V.PARTICIPANT_NAME%TYPE,
 cp_name  CN_COMP_PLANS_ALL_VL.DISPLAY_NAME%TYPE,
 pc_name  CN_PLAN_COMPONENTS_ALL_VL.DISPLAY_NAME%TYPE,
 fo_name  CN_FORMULAS_ALL_VL.FORMULA_NAME%TYPE,
 min_amt  CN_RATE_DIM_TIERS_ALL.MINIMUM_AMOUNT%TYPE,
 iter  CN_RATE_DIMENSIONS_ALL_VL.NUMBER_TIER%TYPE,
 commission  CN_RATE_TABLE_VALUES_ALL.COMMISSION_VALUE%TYPE
);
TYPE par_rate_pay IS TABLE OF ParticipantRatePayout;
rate_pay par_rate_pay;
prp ParticipantRatePayout;
 TYPE irefcursor IS REF CURSOR;
 ixdo_cursor irefcursor;
 TYPE orefcursor IS REF CURSOR return ParticipantRatePayout;
 oxdo_cursor orefcursor; 
  p_name  CN_SRP_PARTICIPANT_HDR_RO_V.PARTICIPANT_NAME%TYPE := NULL;
  cp_name  CN_COMP_PLANS_ALL_VL.DISPLAY_NAME%TYPE   := NULL;
  pc_name  CN_PLAN_COMPONENTS_ALL_VL.DISPLAY_NAME%TYPE  := NULL;
  fo_name  CN_FORMULAS_ALL_VL.FORMULA_NAME%TYPE   := NULL;
  iter  CN_RATE_DIMENSIONS_ALL_VL.NUMBER_TIER%TYPE  := 0;
  commission CN_RATE_TABLE_VALUES_ALL.COMMISSION_VALUE%TYPE  :=  0;
  min_amt  CN_RATE_DIM_TIERS_ALL.MINIMUM_AMOUNT%TYPE  :=  0;
  cnt  NUMBER       :=  0;
 CURSOR RATEPAY IS
SELECT /*+ PARALLEL(5) */
 P.PARTICIPANT_NAME,PR.PERIOD_NAME,CP.DISPLAY_NAME AS COMP_PLAN,PC.DISPLAY_NAME AS PLAN_COMP,
 FV.FORMULA_NAME,RTV.COMMISSION_VALUE,RDT.MINIMUM_AMOUNT,RDT.MAXIMUM_AMOUNT,RDT.TIER_SEQUENCE,RD.NUMBER_TIER
FROM
 CN_SRP_FORM_RATE_TABLES_ALL FR,
 CN_SRP_COMP_PLANS_ALL SCP,
 CN_COMP_PLANS_ALL_VL CP,
 CN_SRP_PLAN_COMPONENTS_ALL SPC,
 CN_PLAN_COMPONENTS_ALL_B PCB,
 CN_PLAN_COMPONENTS_ALL_VL PC,
 CN_COMP_PLAN_COMPONENTS_ALL CPC,
 CN_SRP_FORM_METRICS_ALL SFM,
 CN_FORMULA_RATE_TABLES_ALL FRT,
 CN_RATE_TABLES_ALL_VL RT,
 CN_RATE_TABLE_DIMS_ALL RTD,
 CN_RATE_DIMENSIONS_ALL_VL RD,
 CN_RATE_DIM_TIERS_ALL RDT,
 CN_RATE_TABLE_VALUES_ALL RTV,
 CN_FORMULAS_ALL_VL FV,
 CN_PERIODS_VL PR,
 CN_SRP_PARTICIPANT_DETAILS_ALL PD,
 CN_SRP_PARTICIPANT_HDR_RO_V P,
 CN_REPOSITORIES_ALL_VL R
WHERE 
 P.PARTICIPANT_ID  = FR.PARTICIPANT_ID
 AND (P.PARTICIPANT_NAME IN (:Participant) OR LEAST(:Participant) IS NULL)
 AND P.PARTICIPANT_ID  = PD.PARTICIPANT_ID
 AND CP.DISPLAY_NAME  = 'NA_SPECIALTY_IC11'
 AND PC.DISPLAY_NAME  = 'Order 1'
 AND FV.FORMULA_NAME  = 'ORD 1 Annual PM'
 AND SCP.PARTICIPANT_ID   = FR.PARTICIPANT_ID
 AND SCP.SRP_COMP_PLAN_ID  = FR.SRP_COMP_PLAN_ID 
 AND SPC.SRP_COMP_PLAN_ID = FR.SRP_COMP_PLAN_ID 
 AND CP.COMP_PLAN_ID  = SCP.COMP_PLAN_ID
 AND SPC.COMP_PLAN_ID  = SCP.COMP_PLAN_ID
 AND SPC.COMP_PLAN_COMPONENT_ID = CPC.COMP_PLAN_COMPONENT_ID
 AND SPC.SRP_PLAN_COMPONENT_ID  = FR.SRP_PLAN_COMPONENT_ID 
 AND SPC.PARTICIPANT_ID   = FR.PARTICIPANT_ID
 AND SPC.SRP_COMP_PLAN_ID  = SCP.SRP_COMP_PLAN_ID 
 AND PC.PLAN_COMPONENT_ID  = SPC.PLAN_COMPONENT_ID
 AND PCB.PLAN_COMPONENT_ID  = SPC.PLAN_COMPONENT_ID
 AND PC.PLAN_COMPONENT_ID  = CPC.PLAN_COMPONENT_ID
        AND CP.COMP_PLAN_ID   = CPC.COMP_PLAN_ID
 AND SPC.ORG_ID   = CPC.ORG_ID
 AND PCB.ORG_ID   = CPC.ORG_ID
 AND SFM.PARTICIPANT_ID   = FR.PARTICIPANT_ID
 AND SFM.SRP_COMP_PLAN_ID  = FR.SRP_COMP_PLAN_ID 
 AND SFM.SRP_PLAN_COMPONENT_ID  = FR.SRP_PLAN_COMPONENT_ID
 AND SFM.SRP_FORM_METRIC_ID  = FR.SRP_FORM_METRIC_ID
 AND FR.RATE_TABLE_ID  = FRT.RATE_TABLE_ID
 AND FR.FORMULA_ID  = FRT.FORMULA_ID 
 AND FRT.FORMULA_ID  = SFM.FORMULA_ID 
 AND FRT.RATE_TABLE_ID  = RT.RATE_TABLE_ID
 AND FRT.ORG_ID   = CPC.ORG_ID
 AND RT.ORG_ID    = FRT.ORG_ID
 AND RT.RATE_TABLE_ID  = RTD.RATE_TABLE_ID
 AND RT.ORG_ID    = RTD.ORG_ID
 AND RTD.RATE_DIMENSION_ID  = RD.RATE_DIMENSION_ID
 AND RD.RATE_DIMENSION_ID = RDT.RATE_DIMENSION_ID
 AND RTD.ORG_ID   = RDT.ORG_ID  
 AND RD.DIM_UNIT_CODE  = 'AMOUNT'
 AND RTV.RATE_TABLE_ID  = RTD.RATE_TABLE_ID
 AND RTV.ORG_ID   = RDT.ORG_ID
 AND RTV.SRP_FORM_RATE_TABLE_ID = FR.SRP_FORM_RATE_TABLE_ID
 AND RTV.RATE_SEQUENCE  = RDT.TIER_SEQUENCE
 AND FR.FORMULA_RATE_TABLE_ID    = FRT.FORMULA_RATE_TABLE_ID
 AND FV.FORMULA_ID  = FRT.FORMULA_ID
 AND FV.ORG_ID    = RDT.ORG_ID
 --AND PR.PERIOD_YEAR  = '2019'
 AND (PR.PERIOD_YEAR in (:Year) OR LEAST(:Year) IS NULL)
 --AND PR.PERIOD_ID  = '2019001'
 AND (PR.PERIOD_NAME IN (:PERIOD) OR LEAST(:PERIOD) IS NULL)
 AND P.ORG_ID    = PD.ORG_ID
 AND PD.ORG_ID   = RDT.ORG_ID 
 AND P.ORG_ID = R.ORG_ID
 AND R.ORG_NAME IN (:BU)
 AND (TO_DATE(TO_CHAR (PR.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= TO_DATE(TO_CHAR (PD.START_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY'))
 AND (TO_DATE(TO_CHAR (PR.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') <= TO_DATE(TO_CHAR (PD.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') OR PD.END_DATE IS NULL)
 AND (TO_DATE(TO_CHAR (PR.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') <= TO_DATE(TO_CHAR (SCP.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') OR SCP.END_DATE IS NULL) 
 AND (TO_DATE(TO_CHAR (PR.END_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY') >= TO_DATE(TO_CHAR (SCP.START_DATE, 'MM/DD/YYYY'), 'MM/DD/YYYY')) 
ORDER BY P.PARTICIPANT_NAME,PR.PERIOD_NAME,CP.DISPLAY_NAME,PC.DISPLAY_NAME,FV.FORMULA_NAME,RDT.TIER_SEQUENCE;

BEGIN 

 --OPEN :xdo_cursor FOR
 --SELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') FROM DUAL;
 FOR X IN RATEPAY
 LOOP
  IF(p_name <> X.PARTICIPANT_NAME OR cp_name <> X.COMP_PLAN OR pc_name <> X.PLAN_COMP OR fo_name <> X.FORMULA_NAME) THEN
   rate_pay.extend;
   cnt  := cnt + 1;
   p_name  := X.PARTICIPANT_NAME;
   cp_name  := X.COMP_PLAN;
   pc_name  := X.PLAN_COMP;
   fo_name  := X.FORMULA_NAME;
   iter  := X.NUMBER_TIER;
   commission  := X.COMMISSION_VALUE;
   min_amt  := X.MINIMUM_AMOUNT;
   
   rate_pay(cnt).p_name := X.PARTICIPANT_NAME;
   rate_pay(cnt).cp_name := X.COMP_PLAN;
   rate_pay(cnt).pc_name := X.PLAN_COMP;
   rate_pay(cnt).fo_name := X.FORMULA_NAME;   
   rate_pay(cnt).iter := X.NUMBER_TIER;   
   rate_pay(cnt).commission := X.COMMISSION_VALUE;
   rate_pay(cnt).min_amt := X.MINIMUM_AMOUNT;
   
  END IF;
   
  IF (rate_pay(cnt).commission <> X.COMMISSION_VALUE) THEN
    rate_pay.extend;
    cnt  := cnt + 1;
    rate_pay(cnt).p_name := X.PARTICIPANT_NAME;
    rate_pay(cnt).cp_name := X.COMP_PLAN;
    rate_pay(cnt).pc_name := X.PLAN_COMP;
    rate_pay(cnt).fo_name := X.FORMULA_NAME;   
    rate_pay(cnt).iter := X.NUMBER_TIER;   
    rate_pay(cnt).commission := X.COMMISSION_VALUE;
    rate_pay(cnt).min_amt := X.MINIMUM_AMOUNT;    
  END IF;
  IF (X.NUMBER_TIER = X.TIER_SEQUENCE) THEN
   p_name  := NULL;
   cp_name  := NULL;
   pc_name  := NULL;
   fo_name  := NULL;
   iter  := 0;
   commission  := 0;
   min_amt  := 0;   
  END IF;
 END LOOP;
 --OPEN :xdo_cursor FOR SELECT * FROM TABLE(CAST(rate_pay as ParticipantRatePayout));

END;


Thanks

and Chris said...

Sounds to me like you want a pipelined table function. These return a table of your record type. Which you can query with the table operator:

create or replace package pkg as 

  type rec is record (
    c1 int, c2 int
  );
  
  type rec_tab is table of rec;

  function f ( num_rows int )
    return rec_tab
    pipelined;
end;
/

create or replace package body pkg as 

  function f ( num_rows int )
    return rec_tab
    pipelined as
    rw rec;
  begin
    
    for i in 1 .. num_rows loop
      rw.c1 := i;
      rw.c2 := i * 2 ;
      pipe row ( rw );
    end loop;
    
    return;
  end f;
end;
/

select * from table ( pkg.f ( 5 ) );

C1    C2   
    1     2 
    2     4 
    3     6 
    4     8 
    5    10


So it's just a matter of running the query and processing the result.

If for some reason you require a ref cursor, just open one running the function:

var cur refcursor;

begin
  open :cur for
    select * from table ( pkg.f ( 5 ) );
end;
/

print :cur;

        C1         C2
---------- ----------
         1          2
         2          4
         3          6
         4          8
         5         10


If you want to know more about these, see:
https://blogs.oracle.com/oraclemagazine/pipelined-table-functions

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Limitation to use only anonymous block which can return REF CURSOR only

Anuradha Mupdathi, June 13, 2019 - 7:05 am UTC

Thanks Chris.

In OBIEE SAAS Environment, we don't have access to create any object (function/package). So looking for ways to access it through Anonymous Pl/SQL block. And the restriction is also on that the anonymous block can return only REF CURSOR.

Since, we need to select few rows/columns based on some record comparison, we can't directly open a select query also for REF CURSOR. So we tried to use record type, so that record type can be assigned to REF CURSOR.

In case, if record type cannot be assigned to REF CURSOR, is there anyother approach to use.

Please suggest an approach.

Thanks
Chris Saxon
June 13, 2019 - 10:16 am UTC

You can convert associative arrays to tables from 12c:

create or replace package pkg as 
  type rec is record (
    c1 int, c2 int
  );
  type rec_arry is table of rec 
    index by pls_integer;
end;
/

var cur refcursor;

declare
  a   pkg.rec_arry;
begin
  a(1).c1 := 1;
  a(1).c2 := 2;
  
  open :cur for 
    select * from table ( a );
end;
/

print :cur

        C1         C2
---------- ----------
         1          2


But you need to define the arary type in a package. So if you can't create objects, I believe you're out of luck...

You'll have to do the conversion in your SQL statement. Which should be possible.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library