SQL & PL/SQL

Hi,

I have a procedure that has a SYS_REFCURSOR as an output parameter. This procedure has a LOOP which does some processing that creates several values that I put into a TABLE collection of a custom RECORD type. Each run through the loop, the current row is added to the table collection.

At the end of my procedure I have my collection all populated with the values already, I just need to shove this collection into a SYS_REFCURSOR.

Omitted some code for brevity:

CREATE OR REPLACE PROCEDURE LOOKUPUSER_BULK(
    p_Input     IN  SYS_REFCURSOR,
    p_Output    OUT SYS_REFCURSOR)
IS
...
    TYPE t_LicenseObject IS RECORD
    (
        Exportable          VARCHAR2(10),
        Status              VARCHAR2(30),
        License_Associated  VARCHAR2(255),
        Status_Description  VARCHAR2(255),
        License_Ind         VARCHAR2(10),
        Desc_String         VARCHAR2(255)        
    );
    TYPE t_LicenseList IS TABLE OF t_LicenseObject;
    v_OutputList    t_LicenseList := t_LicenseList();
...
AS
    BEGIN
    
        LOOP

        ....do work...

           -- Current row gets added to the existing collection here.
           v_OutputList.EXTEND(1);
           SELECT Exportable, Status, License_Associated, Status_Description, License_Ind, Desc_String
           INTO v_OutputList(v_OutputList.LAST)
           FROM DUAL;

      END LOOP;

      -- Copy into SYS_REFCURSOR here.
      OPEN p_Output FOR
      SELECT * FROM v_OutputList;
     
      -- Also tried this
      OPEN p_Output FOR    
      SELECT *
      FROM TABLE(CAST(v_OutputList AS t_LicenseList);

END LOOKUPUSER_BULK;

What is the best way to do this? Thanks.

Edited by: donovan7800 on Dec 20, 2011 9:19 AM