1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem to return associative arrays as ref cursors

Discussion in 'SQL PL/SQL' started by baluaggala, Feb 13, 2014.

  1. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    i have one table called accounts
    accounts
    ---------
    acct name
    acct-number
    company_id

    cur c1 is select * from accts where company_id is null;

    cur c2 is select * from accts where company_id is not null;

    TYPE col1type is table of c1%rowtype index by pls_integer;

    col1 col1type;

    TYPE col2type is table of c2%rowtype index by plsq_integer;.

    col2 col2type;

    BEGIN

    // need to load cursor data into associative arrays

    like fetch c1 bulkcollect into col1;

    lly c2 into col2 collections..



    i need to traverse through bith 2 collections and based on business logic i need to identify unmatched records from both collections

    and populate into new temp collection

    ------------------------------------------

    my doubt is i have 3 sets of associative arrays ready with output................even i am able to print the data

    with dbms_output but how can i return to front end?????

    to pass these collections to ref cursor.....its getting error as associative array is plsql type

    if there is any solution to convert associative array to nested table and pass to ref_cursor???

    my goal is to return associatice arrays to front end ???? how can i do that
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
  3. baluaggala

    baluaggala Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    i gone through the above link, no where returning collections concept i found.



    my objective is to return the collection to front-end can we do that???


    if so how can we return??
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You want to make an associative array a ref cursor, and you can't do that directly. You can do this:


    Code (SQL):

    SQL> CREATE OR REPLACE TYPE integer_table IS TABLE OF INTEGER;
      2  /


    TYPE created.


    SQL>
    SQL> CREATE OR REPLACE FUNCTION pass_array (dummy IN NUMBER)
      2  RETURN sys_refcursor
      3  AS
      4     vendor_key_table   integer_table;
      5
      6     refcur  sys_refcursor;
      7     CURSOR tst
      8     IS
      9        SELECT   purch_order, SUM (dollars),
     10                 CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
     11            FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
     12                    FROM DUAL
     13                  UNION ALL
     14                  SELECT 1 purch_order, 8 dollars, 123452 vendor_key
     15                    FROM DUAL
     16                  UNION ALL
     17                  SELECT 2 purch_order, 7 dollars, 433738 vendor_key
     18                    FROM DUAL
     19                  UNION ALL
     20                  SELECT 2 purch_order, 4 dollars, 383738 vendor_key
     21                    FROM DUAL
     22                  UNION ALL
     23                  SELECT 2 purch_order, 5 dollars, 387118 vendor_key
     24                    FROM DUAL)
     25        GROUP BY purch_order;
     26
     27     v_purch_order      NUMBER;
     28     v_dollars          NUMBER;
     29
     30     mystr              VARCHAR2 (4000);
     31
     32  BEGIN
     33     OPEN tst;
     34
     35     LOOP
     36        mystr := NULL;
     37
     38        FETCH tst
     39         INTO v_purch_order, v_dollars, vendor_key_table;
     40
     41        IF tst%NOTFOUND
     42        THEN
     43           EXIT;
     44        END IF;
     45
     46     END loop;
     47
     48        --
     49        -- Pass the collection back to the calling program
     50        --
     51        OPEN refcur FOR SELECT COLUMN_VALUE vendor_key FROM TABLE(vendor_key_table);
     52
     53
     54     CLOSE tst;
     55          RETURN refcur;
     56  END;
     57  /


    FUNCTION created.


    SQL>
    SQL> SHOW errors FUNCTION pass_array



    No errors.



    SQL>
    SQL> variable pink refcursor
    SQL>
    SQL> EXEC :pink:=pass_array(1);


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> print :pink


    VENDOR_KEY
    ----------
        433738
        387118
        383738


    SQL>
     

    Notice I opened the ref cursor for a select that returns the array (in this case a table of values).