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.
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.
Indexed arrays are easier to work with, check it out:
;)Code: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;
/
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.