11 Replies Latest reply: Dec 28, 2011 1:40 PM by Barbara Boehmer RSS

    bulk collect into collection of objects

    907278
      create or replace type typ_obj as object
      ( l_x number(10),
      l_y varchar2(10),
      l_z varchar2(10)
      );

      create type typ_obj_tt is table of typ_obj;

      desc temp_table;
      c_x number(10)
      c_p number(10)

      desc temp2_table
      c_q number(10)
      c_y varchar2(10)
      c_z varchar2(10)

      procedure prc_x(p_obj_out OUT typ_obj_tt)
      is
      cursor c1
      is
      select t1.c_x,
      t2.c_y,
      t2.c_z
      from
      temp_table t1,
      temp2_table t2
      where
      ....
      ;
      begin
      open c1;
      fetch c1 bulk collect into p_obj_out;
      close c1;

      end;

      throws error


      can you tell
      how to get the data into this output table type object

      thanks in advance..any help will be highly appreciated..

      Edited by: 904275 on Dec 23, 2011 10:10 AM
        • 1. Re: bulk collect into collection of objects
          Barbara Boehmer
          Please see the modification to line 6 of the procedure in the demonstration below.
          SCOTT@orcl_11gR2> create or replace type typ_obj as object
            2    (l_x  number   (10),
            3       l_y  varchar2 (10),
            4       l_z  varchar2 (10));
            5  /
          
          Type created.
          
          SCOTT@orcl_11gR2> create type typ_obj_tt is table of typ_obj;
            2  /
          
          Type created.
          
          SCOTT@orcl_11gR2> create table temp_table
            2    (c_x  number   (10),
            3       c_p  number   (10))
            4  /
          
          Table created.
          
          SCOTT@orcl_11gR2> insert all
            2  into temp_table values (1, 2)
            3  into temp_table values (3, 4)
            4  select * from dual
            5  /
          
          2 rows created.
          
          SCOTT@orcl_11gR2> create table temp2_table
            2    (c_q  number   (10),
            3       c_y  varchar2 (10),
            4       c_z  varchar2 (10))
            5  /
          
          Table created.
          
          SCOTT@orcl_11gR2> insert all
            2  into temp2_table values (5, 6, 7)
            3  into temp2_table values (8, 9, 10)
            4  select * from dual
            5  /
          
          2 rows created.
          
          SCOTT@orcl_11gR2> create or replace procedure prc_x
            2    (p_obj_out OUT typ_obj_tt)
            3  is
            4    cursor c1 is
            5    -- modified line below:
            6    select TYP_OBJ (t1.c_x, t2.c_y, t2.c_z)
            7    from   temp_table t1, temp2_table t2;
            8  begin
            9    open c1;
           10    fetch c1 bulk collect into p_obj_out;
           11    close c1;
           12  end prc_x;
           13  /
          
          Procedure created.
          
          SCOTT@orcl_11gR2> show errors
          No errors.
          SCOTT@orcl_11gR2> variable g_ref refcursor
          SCOTT@orcl_11gR2> declare
            2    v_obj_out  typ_obj_tt;
            3  begin
            4    prc_x (v_obj_out);
            5    open :g_ref for select t.* from table (v_obj_out) t;
            6  end;
            7  /
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> print g_ref
          
                 L_X L_Y        L_Z
          ---------- ---------- ----------
                   1 6          7
                   3 6          7
                   1 9          10
                   3 9          10
          
          4 rows selected.
          • 2. Re: bulk collect into collection of objects
            907336
            or u can try with functions also in oracle 10g

            CREATE OR REPLACE TYPE TAB_ROW AS OBJECT (TNAME VARCHAR2(30), TABTYPE VARCHAR2(7), CLUSTERID NUMBER);

            CREATE OR REPLACE TYPE TAB_TYPE AS TABLE OF TAB_ROW;

            CREATE OR REPLACE FUNCTION fn_tabfun_test
            RETURN tab_type
            AS
            c_ref_cur tab_type := tab_type ();
            BEGIN
            SELECT tab_row (tname, tabtype, clusterid)
            BULK COLLECT INTO c_ref_cur
            FROM tab;

            RETURN c_ref_cur;
            END;

            select * from table(fn_tabfun_test());
            • 3. Re: bulk collect into collection of objects
              907278
              ton of thanks guys..really helpful..
              i have a problem with using limit with bulk collect.if i limit it to 20 then out of 62 recds it displays only 2 recds.. why so..and can it be resolved

              ALSO I have one more doubt is this method of selection of columns compatible with union operator.I have a query with union in the cursor select statement..and with bulk collectr into collection of objects as discussed above it throws error ORA-22950: cannot order objects with without map or order method
              can you please clarify it.
              • 4. Re: bulk collect into collection of objects
                Barbara Boehmer
                i have a problem with using limit with bulk collect.if i limit it to 20 then out of 62 recds it displays only 2 recds.. why so..and can it be resolved
                When you bulk collect, it overwrites whatever was there. So, when you use a loop with bulk collect into ... limit 20 on a cursor of 62 rows, the first loop writes the first 20 rows, then the second loop overwrites the first 20 rows with the rows 21-40, then the third loop overwrites rows 21-40 with rows 41-60, then the last loop overwrites rows 41-60 with rows 61-62. So, rows 61-62 are the only rows in your output parameter.

                You should either not limit the rows or do your processing on each set within the loop, instead of using the results in an output parameter.
                • 5. Re: bulk collect into collection of objects
                  Barbara Boehmer
                  ALSO I have one more doubt is this method of selection of columns compatible with union operator.I have a query with union in the cursor select statement..and with bulk collectr into collection of objects as discussed above it throws error ORA-22950: cannot order objects with without map or order method
                  can you please clarify it.
                  When you use UNION, it has to sort/order to eliminate duplicates. If you don't care about duplicates then you can use UNION ALL without any changes. However, if you need to use UNION to eliminate duplicates, then, as the error message states, you need to use a MAP or ORDER method. The following demonstration resolves the problem by using a MAP method. The modifications to the type_obj are in upper case. I just did a UNION of the same select within the cursor.
                  SCOTT@orcl_11gR2> create or replace type typ_obj as object
                    2    (l_x  number   (10),
                    3       l_y  varchar2 (10),
                    4       l_z  varchar2 (10),
                    5       MAP MEMBER FUNCTION TYPE_OBJ_SORT RETURN VARCHAR2);
                    6  /
                  
                  Type created.
                  
                  SCOTT@orcl_11gR2> SHOW ERRORS
                  No errors.
                  SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE BODY TYP_OBJ
                    2  AS
                    3    MAP MEMBER FUNCTION TYPE_OBJ_SORT RETURN VARCHAR2
                    4    IS
                    5    BEGIN
                    6        RETURN l_x || ',' || l_y || ',' || l_z;
                    7    END TYPE_OBJ_SORT;
                    8  END;
                    9  /
                  
                  Type body created.
                  
                  SCOTT@orcl_11gR2> SHOW ERRORS
                  No errors.
                  SCOTT@orcl_11gR2> create type typ_obj_tt is table of typ_obj;
                    2  /
                  
                  Type created.
                  
                  SCOTT@orcl_11gR2> create table temp_table
                    2    (c_x  number   (10),
                    3       c_p  number   (10))
                    4  /
                  
                  Table created.
                  
                  SCOTT@orcl_11gR2> insert all
                    2  into temp_table values (1, 2)
                    3  into temp_table values (3, 4)
                    4  select * from dual
                    5  /
                  
                  2 rows created.
                  
                  SCOTT@orcl_11gR2> create table temp2_table
                    2    (c_q  number   (10),
                    3       c_y  varchar2 (10),
                    4       c_z  varchar2 (10))
                    5  /
                  
                  Table created.
                  
                  SCOTT@orcl_11gR2> insert all
                    2  into temp2_table values (5, 6, 7)
                    3  into temp2_table values (8, 9, 10)
                    4  select * from dual
                    5  /
                  
                  2 rows created.
                  
                  SCOTT@orcl_11gR2> create or replace procedure prc_x
                    2    (p_obj_out OUT typ_obj_tt)
                    3  is
                    4    cursor c1 is
                    5    select typ_obj (t1.c_x, t2.c_y, t2.c_z)
                    6    from   temp_table t1, temp2_table t2
                    7    UNION
                    8    select typ_obj (t1.c_x, t2.c_y, t2.c_z)
                    9    from   temp_table t1, temp2_table t2;
                   10  begin
                   11    open c1;
                   12    fetch c1 bulk collect into p_obj_out;
                   13    close c1;
                   14  end prc_x;
                   15  /
                  
                  Procedure created.
                  
                  SCOTT@orcl_11gR2> show errors
                  No errors.
                  SCOTT@orcl_11gR2> variable g_ref refcursor
                  SCOTT@orcl_11gR2> declare
                    2    v_obj_out  typ_obj_tt;
                    3  begin
                    4    prc_x (v_obj_out);
                    5    open :g_ref for select t.* from table (v_obj_out) t;
                    6  end;
                    7  /
                  
                  PL/SQL procedure successfully completed.
                  
                  SCOTT@orcl_11gR2> print g_ref
                  
                         L_X L_Y        L_Z
                  ---------- ---------- ----------
                           1 6          7
                           1 9          10
                           3 6          7
                           3 9          10
                  
                  4 rows selected.
                  • 6. Re: bulk collect into collection of objects
                    907278
                    thank you barbara. It was really helpfull..lot of thanks....can you enlighten me on what does the member function do and how..

                    thanks and regards
                    libeesh
                    • 7. Re: bulk collect into collection of objects
                      Barbara Boehmer
                      Please see the following section of the online documentation for member methods and scroll down for map methods and order methods:

                      http://docs.oracle.com/cd/E11882_01/appdev.112/e11822/adobjbas.htm#i477669
                      • 8. Re: bulk collect into collection of objects
                        907278
                        can u tell how to block the count of recors fetched into the cursor(same)..
                        as the cursor is big containing unions of many select staement the query can return thousands of records .
                        so is there any way to limit the count of rows else bulk collect wiill take more time only after that only we can count the records
                        • 9. Re: bulk collect into collection of objects
                          Barbara Boehmer
                          You can use FETCH and BULK COLLECT and LIMIT together, but you need to do your processing within the loop, as in the example in the link below:

                          http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#LNPLS897

                          Every time that you fetch, it overwrites the previous collection, so you cannot fetch repeatedly and have all rows in an out parameter, just the last set.
                          • 10. Re: bulk collect into collection of objects
                            907278
                            fetch xos_cur bulk collect into l_sv_out limit 100 ; --table type object

                            x:=l_sv_out.count;
                            intermediate_sv.extend(x);--intermediate_sv is a table type obj of the type same as l_sv_out
                            for i in 1..100 loop
                            intermediate_sv(y):=l_sv_out(i); ----throws error while calling the procedure from an anonymous block
                            y:=y+1;
                            end loop;

                            exit when xoserve_cur%notfound;
                            end loop;
                            p_sv_out:=intermediate_sv;


                            can u give a solutn for this..
                            I tried this to improve the bulk colecting speed and limit the no of rows (code for that not added) to ex:500
                            • 11. Re: bulk collect into collection of objects
                              Barbara Boehmer
                              The following corrects the syntax of your code enough to make it run without error and produce the correct results, but I doubt that you will see any performance improvement.
                              SCOTT@orcl_11gR2> create or replace type typ_obj as object
                                2    (l_x  number   (10),
                                3       l_y  varchar2 (10),
                                4       l_z  varchar2 (10),
                                5       MAP MEMBER FUNCTION TYPE_OBJ_SORT RETURN VARCHAR2);
                                6  /
                              
                              Type created.
                              
                              SCOTT@orcl_11gR2> SHOW ERRORS
                              No errors.
                              SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE BODY TYP_OBJ
                                2  AS
                                3    MAP MEMBER FUNCTION TYPE_OBJ_SORT RETURN VARCHAR2
                                4    IS
                                5    BEGIN
                                6        RETURN l_x || ',' || l_y || ',' || l_z;
                                7    END TYPE_OBJ_SORT;
                                8  END;
                                9  /
                              
                              Type body created.
                              
                              SCOTT@orcl_11gR2> SHOW ERRORS
                              No errors.
                              SCOTT@orcl_11gR2> create type typ_obj_tt is table of typ_obj;
                                2  /
                              
                              Type created.
                              
                              SCOTT@orcl_11gR2> create table temp_table
                                2    (c_x  number   (10),
                                3       c_p  number   (10))
                                4  /
                              
                              Table created.
                              
                              SCOTT@orcl_11gR2> insert all
                                2  into temp_table values (1, 2)
                                3  into temp_table values (3, 4)
                                4  select * from dual
                                5  /
                              
                              2 rows created.
                              
                              SCOTT@orcl_11gR2> create table temp2_table
                                2    (c_q  number   (10),
                                3       c_y  varchar2 (10),
                                4       c_z  varchar2 (10))
                                5  /
                              
                              Table created.
                              
                              SCOTT@orcl_11gR2> insert all
                                2  into temp2_table values (5, 6, 7)
                                3  into temp2_table values (8, 9, 10)
                                4  into temp2_table values (11, 12, 13)
                                5  into temp2_table values (14, 15, 16)
                                6  select * from dual
                                7  /
                              
                              4 rows created.
                              
                              SCOTT@orcl_11gR2> create or replace procedure prc_x
                                2    (p_sv_out OUT typ_obj_tt)
                                3  is
                                4    cursor xos_cur is
                                5    select typ_obj (t1.c_x, t2.c_y, t2.c_z)
                                6    from   temp_table t1, temp2_table t2
                                7    UNION
                                8    select typ_obj (t1.c_x, t2.c_y, t2.c_z)
                                9    from   temp_table t1, temp2_table t2;
                               10    l_sv_out      typ_obj_tt;
                               11    x          number;
                               12  begin
                               13    p_sv_out := typ_obj_tt ();
                               14    open xos_cur;
                               15    loop
                               16        fetch xos_cur bulk collect into l_sv_out limit 5;
                               17        exit when l_sv_out.count = 0;
                               18        x := p_sv_out.count;
                               19        p_sv_out.extend (l_sv_out.count);
                               20        for i in 1 .. l_sv_out.count loop
                               21          p_sv_out (x + i) := l_sv_out (i);
                               22        end loop;
                               23    end loop;
                               24    close xos_cur;
                               25  end prc_x;
                               26  /
                              
                              Procedure created.
                              
                              SCOTT@orcl_11gR2> show errors
                              No errors.
                              SCOTT@orcl_11gR2> variable g_ref refcursor
                              SCOTT@orcl_11gR2> declare
                                2    v_obj_out  typ_obj_tt;
                                3  begin
                                4    prc_x (v_obj_out);
                                5    open :g_ref for select t.* from table (v_obj_out) t;
                                6  end;
                                7  /
                              
                              PL/SQL procedure successfully completed.
                              
                              SCOTT@orcl_11gR2> print g_ref
                              
                                     L_X L_Y        L_Z
                              ---------- ---------- ----------
                                       1 12         13
                                       1 15         16
                                       1 6          7
                                       1 9          10
                                       3 12         13
                                       3 15         16
                                       3 6          7
                                       3 9          10
                              
                              8 rows selected.