10 Replies Latest reply: Sep 2, 2011 8:27 AM by Peter RSS

    elements order in Nested Tables

    Fa
      In PL/SQL when I am adding element to a nested table of objects one by one(in order), and then trying to insert that nested table type to a physical table , the order of element changes. how can I prevent this behavior?
        • 1. Re: elements order in Nested Tables
          JustinCave
          Can you post a small script that demonstrates exactly what you are doing? There are a few different ways that I could interpret your question.

          Justin
          • 2. Re: elements order in Nested Tables
            Fa
            Dear Justin,
            sorry my question is not so declarative, here is a sample script:
            -------------------------------
            create or replace
            TYPE coord AS OBJECT
            ( id number,
            x number,
            y number
            );

            create or replace
            TYPE coord_tab
            AS TABLE OF coord;

            create table demo_table ( id number,coords coord_tab)
            NESTED TABLE coords STORE AS coords_tabb;

            DECLARE
            coord_tab1 coord_tab := coords();
            idx number := 1;

            type cur is ref cursor;
            cur1 cur;


            BEGIN
            Open cur1 for select * from another_table;
            LOOP
                 fetch cur1 into record_type;
                 exit when cur1%notfound;

            coords_tab.extend() ;
            coords_tab(idx) := coord(record_type.id, record_type.x , record_type.y);
            idx := idx + 1;

            End LOOP;

            EXECUTE IMMEDIATE 'insert into demo_table values (1,:1)' USING coords_tab ;

            End;
            ----------------------------
            when coords_tab is inserted into the demo_table it changes the order of elements. I want to prevent this behaviour!
            • 3. Re: elements order in Nested Tables
              Barbara Boehmer
              Your example is incomplete and has errors. Oracle does not guarantee the order of anything unless you use an order by clause. So, if you expect the following to produce rows in some order:

              Open cur1 for select * from another_table;

              then you need to add an order by clause, such as:

              Open cur1 for select * from another_table order by id;

              The following does minimal correction, completion, and provides sample data to demonstrate:
              SCOTT@orcl_11gR2> create or replace TYPE coord AS OBJECT
                2    (id  number,
                3       x   number,
                4       y   number);
                5  /
              
              Type created.
              
              SCOTT@orcl_11gR2> create or replace TYPE coord_tab AS TABLE OF coord;
                2  /
              
              Type created.
              
              SCOTT@orcl_11gR2> create table demo_table
                2    (id     number,
                3       coords     coord_tab)
                4  NESTED TABLE coords STORE AS coords_tabb
                5  /
              
              Table created.
              
              SCOTT@orcl_11gR2> create table another_table
                2    (id  number,
                3       x   number,
                4       y   number)
                5  /
              
              Table created.
              
              SCOTT@orcl_11gR2> insert into another_table values (10, 20, 30)
                2  /
              
              1 row created.
              
              SCOTT@orcl_11gR2> insert into another_table values (60, 50, 40)
                2  /
              
              1 row created.
              
              SCOTT@orcl_11gR2> DECLARE
                2    type cur is ref cursor;
                3    cur1 cur;
                4    type rec_type is record
                5        (id  number,
                6         x   number,
                7         y   number);
                8    record_type rec_type;
                9    coords_tab coord_tab := coord_tab();
               10    idx number := 1;
               11  BEGIN
               12    Open cur1 for select * from another_table order by id desc;
               13    LOOP
               14        fetch cur1 into record_type;
               15        exit when cur1%notfound;
               16        coords_tab.extend() ;
               17        coords_tab(idx) := coord(record_type.id, record_type.x , record_type.y);
               18        idx := idx + 1;
               19    End LOOP;
               20  --  EXECUTE IMMEDIATE 'insert into demo_table values (1,:1)' USING coords_tab ;
               21    insert into demo_table values (1,coords_tab);
               22  End;
               23  /
              
              PL/SQL procedure successfully completed.
              
              SCOTT@orcl_11gR2> select * from demo_table
                2  /
              
                      ID
              ----------
              COORDS(ID, X, Y)
              --------------------------------------------------------------------------------
                       1
              COORD_TAB(COORD(60, 50, 40), COORD(10, 20, 30))
              
              
              1 row selected.
              
              SCOTT@orcl_11gR2>
              • 4. Re: elements order in Nested Tables
                Fa
                dear barbara,
                Actually I have used an order by clause in my original source code!
                when using the piece of code as a stored procedure, I want to insert a lot rows into the nested table. but after insertion ,when I try to query the table( demo_table) to check whether ids (in coord(id,x,y)) are consecutive, then there are cases (one or two) which are not.I have created a stored procedure to check that.

                thanx for your reply
                • 5. Re: elements order in Nested Tables
                  Barbara Boehmer
                  Can you post a complete reproducible test case, similar to what I posted, but showing the unordered results?
                  • 6. Re: elements order in Nested Tables
                    Fa
                    well, that's a little difficult. are you trying to say that it's impossible to happen and the problem is from somewhere else!?
                    • 7. Re: elements order in Nested Tables
                      Barbara Boehmer
                      user13046977 wrote:
                      well, that's a little difficult. are you trying to say that it's impossible to happen and the problem is from somewhere else!?
                      Yes, that's what I suspect. If I could even see a complete test case, I might be able to guess under what circumstances the problem might occur. Without a complete test case, all I can do is say that it works when I test using the code that I have provided.
                      • 8. Re: elements order in Nested Tables
                        Fa
                        Finally i found what was my mistake ,
                        I used nested table instead of varraying arrays...

                        Edited by: user13046977 on Oct 27, 2011 3:19 AM
                        • 9. Re: elements order in Nested Tables
                          gaverill
                          when using object tables or collection columns, nested tables are not guaranteed to retain element ordering. varraying arrays (varrays), however, are.

                          note also that if you use object views (or views with collection columns) your view SQL is materializing the collection and you can return it any way (ordered or not) you want.

                          hope this helps...

                          Gerard
                          • 10. Re: elements order in Nested Tables
                            Peter
                            isn't it easier to do the last piece of pl/sql like this:
                            DECLARE
                              c_refcur sys_refcursor;
                              coords_tab coord_tab;
                            BEGIN
                              Open c_refcur for select coord(id,x,y) from another_table order by id desc;
                              fetch c_refcur bulk collect into coords_tab;
                              close c_refcur;
                              --
                              insert into demo_table values(1,coords_tab); 
                              commit; 
                            End;
                            regards,
                            Peter

                            ps: it is also possible to order all kinds of objects using a MAP function in the object