how to convert cursor(SYS_REFCURSOR) to an associative array.

[ujjwalsoni]

Hi, i'm new for oracle collection's ,so please direct me for converting cursor(SYS_REFCURSOR) to an associative array.I'm stuck and struggling by a week for this.Please it's urgent.

[LKBrwn_DBA]

Indexed arrays are easier to work with, check it out:

DECLARE
   Emp_Csr   SYS_REFCURSOR;

   TYPE Emp_Type IS TABLE OF Emp%ROWTYPE
      INDEX BY PLS_INTEGER;

   Emp_Arr   Emp_Type;
BEGIN
   OPEN Emp_Csr FOR 'Select * From Emp';

   FETCH Emp_Csr BULK COLLECT INTO Emp_Arr;

   FOR I IN 1 .. Emp_Arr.COUNT
   LOOP
      DBMS_OUTPUT.Put_Line ('Emp#: '|| Emp_Arr ( I ).Empno
                         || ', Name: '|| Emp_Arr ( I ).Ename );
   END LOOP;
END;
/

[ujjwalsoni]

Hi,
Thank's a lot.
And also tried to convert an associative array to cursor(SYS_REFCURSOR) but not succeeded ,so please direct me for also associative array to cursor(SYS_REFCURSOR).

my snippnet like this---


FUNCTION GETXXX_GETFOLDERS1 (IN_ARRAY GENARRAY) RETURN SYS_REFCURSOR AS
OUTTABLE GENARRAY;
GRPID NUMBER;
VCOUNT NUMBER:=0;
return_cursor SYS_REFCURSOR;
BEGIN
FOR I IN 1..IN_ARRAY.COUNT LOOP

VCOUNT:=VCOUNT+1;
SELECT GROUPID INTO GRPID FROM DMBI_GROUPDETAIL WHERE GROUPNAME IN (IN_ARRAY(I));
OUTTABLE(VCOUNT):=GRPID;
END LOOP;

Open return_cursor FOR
SELECT * FROM TABLE (CAST(OUTTABLE AS GENARRAY));

RETURN return_cursor;
END GETXXX_GETFOLDERS1;

----ERROR on linw which is in bold-----
Error(127,5): PL/SQL: SQL Statement ignored
Error(127,43): PL/SQL: ORA-00902: invalid datatype
------------------------------------------------------------------


THANKS in ADVANCE.