how to convert cursor(SYS_REFCURSOR) to an associative array.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

Hybrid View

  1. #1
    Join Date
    Jul 2014
    Posts
    3

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

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool

    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;
    /
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Jul 2014
    Posts
    3
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width