This discussion is archived
10 Replies Latest reply: Sep 2, 2011 6:27 AM by Peter RSS

elements order in Nested Tables

Fa Newbie
Currently Being Moderated
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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points