How to return a collection of records from a stored procedure?

[Nilesh Raje]

How to return a collection of records from a stored procedure.Can anyone give me some example code?

[Gene Hilpert]

Here is one way.

   CREATE OR REPLACE PACKAGE myschema.my_pk AS
   TYPE out_cursor IS REF CURSOR;  -- depending on you application calling  the procedure you may not need the RECORD and TYPE statements

   TYPE my_typerec IS RECORD (
                        field1   VARCHAR2 (10)
                       ,field2   VARCHAR2 (10)
                       ,field3   VARCHAR2 (10)
                       ,field4   VARCHAR2 (10)
                       ,field5   VARCHAR2 (10)

                     );

   TYPE my_typecur IS REF CURSOR
      RETURN myschema.my_pk.my_typerec;

   PROCEDURE my_procedure (parm1 IN VARCHAR2
                          ,parm2 IN VARCHAR2
                          ,outcursor   OUT myschema.my_pk.my_typecur -- if you dont need the RECORD or TYPE then this is just out_cursor
                          );
END my_pk;

CREATE OR REPLACE PACKAGE BODY myschema.my_pk AS
   PROCEDURE my_procedure (parm1 IN VARCHAR2
                          ,parm2 IN VARCHAR2
                          ,outcursor   OUT myschema.my_pk.my_typecur -- if you dont need the RECORD or TYPE then this is just out_cursor
                          ) AS
   BEGIN
      OPEN outcursor FOR
         SELECT   field1
                 ,field2
                 ,field3
                 ,field4
                 ,field5
           FROM   my_table;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         outcursor := NULL;
   END;
END my_pk;