Oracle Reference Cursor with Custom Type

Question

I am trying to write a stored procedure which has a reference cursor and custom type. I wanted to fetch the result into the custom type and then print the result in a loop. So far I tried this below by doing some research on how to do it. But I got struck with some errors. Could anybody please direct me the correct way to do this.

CREATE OR REPLACE type BUNDLE_OBJECT as object
(
  customer_ref varchar2(20),
  product_id   number(9),
  bundle_name  varchar2(100)
);
/

create or replace type t_bundle IS TABLE OF BUNDLE_OBJECT;
/


create or replace procedure bundledata(plist VARCHAR2, bundle_f varchar2 /*either not or empty string*/
) is

 C1 sys_refcursor;
 lt_bundle_recd t_bundle := t_bundle();
 querystring varchar2(1000);

begin 

querystring := 'select BUNDLE_OBJECT(customer_ref,product_id,bundle_name)
  from (select cpad.customer_ref,
               cpad.product_seq,
               cpad.product_id,
               pa.attribute_bill_name,
               cpad.attribute_value
          from PVCustProductAttrDetails2 cpad,
               productattribute          pa
         where cpad.product_id = pa.product_id
           and cpad.product_attribute_subid = pa.product_attribute_subid
           and pa.attribute_bill_name in
               (''BUNDLE_NAME'', ''ENTITY_ID'', ''SERVICE_ACTIVATION_DATE''))
pivot(max(attribute_value)
   for attribute_bill_name in(''BUNDLE_NAME'' as BUNDLE_NAME,
                              ''ENTITY_ID'' as SALES_ID,
                              ''SERVICE_ACTIVATION_DATE'' as
                              SERVICE_ACTIVATION_DATE))
where product_id in (' || plist || ') or BUNDLE_NAME is ' || bundle_f || ' NULL';

open C1 for querystring; 
FETCH C1 bulk collect into lt_bundle_recd;
close c1;

    FOR IDX IN 1 .. lt_bundle_recd.COUNT 
      LOOP 
        DBMS_OUTPUT.PUT_LINE(lt_bundle_recd(IDX).CUSTOMER_REF || ' ' || lt_bundle_recd(IDX).PRODUCT_ID || ' ' || lt_bundle_recd(IDX).BUNDLE_NAME); 
      END LOOP; 
END;
/

Error I am facing is below:

SQL> show errors
Errors for PROCEDURE M_ADMIN.BUNDLEDATA:
LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------
4/33     PLS-00310: with %ROWTYPE attribute, 'T_BUNDLE' must name a table, cursor or cursor-variable
4/1      PL/SQL: Item ignored
4/64     PLS-00320: the declaration of the type of this expression is incomplete or malformed
4/64     PL/SQL: Item ignored
29/6     PLS-00455: cursor 'C1' cannot be used in dynamic SQL OPEN statement
29/1     PL/SQL: Statement ignored
30/20    PLS-00320: the declaration of the type of this expression is incomplete or malformed
30/6     PL/SQL: SQL Statement ignored
31/58    PLS-00320: the declaration of the type of this expression is incomplete or malformed
31/58    PL/SQL: ORA-00904: "C2"."BUNDLE_NAME": invalid identifier
31/5     PL/SQL: SQL Statement ignored
36/45    PLS-00201: identifier 'INDX' must be declared
36/9     PL/SQL: Statement ignored

New error is :

SQL> exec bundledata('2205','');
begin bundledata('2205',''); end;
ORA-00942: table or view does not exist
ORA-06512: at "M_ADMIN.BUNDLEDATA", line 29
ORA-06512: at line 1

Answer 1

Try something like this - ie, construct the object as you query and then just a simple bulk collect to populate the nested table

CREATE OR REPLACE type BUNDLE_OBJECT as object
(
  customer_ref varchar2(20),
  product_id   number(9),
  bundle_name  varchar2(100)
);
/

create or replace type t_bundle IS TABLE OF BUNDLE_OBJECT;
/

create or replace procedure bundledata(plist VARCHAR2, bundle_f varchar2 /*either not or empty string*/
) is

 C1 sys_refcursor;
 lt_bundle_recd t_bundle := t_bundle();

  querystring varchar2(1000);

begin 

querystring := 'select BUNDLE_OBJECT(customer_ref,product_id,bundle_name)
  from (select cpad.customer_ref,
               cpad.product_seq,
               cpad.product_id,
               pa.attribute_bill_name,
               cpad.attribute_value
          from CPADATTRS cpad,
               PRATTRS          pa
         where cpad.product_id = pa.product_id
           and cpad.product_attribute_subid = pa.product_attribute_subid
           and pa.attribute_bill_name in
               (''BUNDLE_NAME'', ''ENTITY_ID'', ''SERVICE_ACTIVATION_DATE''))
pivot(max(attribute_value)
   for attribute_bill_name in(''BUNDLE_NAME'' as BUNDLE_NAME,
                              ''ENTITY_ID'' as SALES_ID,
                              ''SERVICE_ACTIVATION_DATE'' as
                              SERVICE_ACTIVATION_DATE))
where product_id in (' || plist || ') or BUNDLE_NAME is ' || bundle_f || ' NULL';

open C1 for querystring; 
FETCH C1 bulk collect into lt_bundle_recd;
close c1;

    FOR IDX IN 1 .. lt_bundle_recd.COUNT 
      LOOP 
        DBMS_OUTPUT.PUT_LINE(lt_bundle_recd(INDX).CUSTOMER_REF || ' ' || lt_bundle_recd(INDX).PRODUCT_ID || ' ' || lt_bundle_recd(INDX).BUNDLE_NAME); 
      END LOOP; 
END;
/