5 Replies Latest reply on Jun 30, 2005 2:29 PM by 141119

    oracle nested object table

    141119
      I've posted this question in sql/plsql forum but it may belong here.

      My question is how can i individually initialize/assign record to my nested table type wf_acts_t or wf_trans_t( either one or the other)?

      It maybe simply a case where I dont know how to accomplish this goal but I've got a workaround.


      CREATE TYPE wf_act_t IS OBJECT (
      a_NAME VARCHAR2 (100)
      )
      /
      CREATE TYPE wf_acts_t IS
      TABLE OF wf_act_t
      /
      CREATE TYPE wf_tran_t IS OBJECT (
      wf_name VARCHAR2 (100)
      );
      /
      CREATE TYPE wf_trans_t IS
      TABLE OF wf_tran_t
      /

      CREATE TYPE act_t IS OBJECT (
      act wf_acts_t,
      tran wf_trans_t
      )
      /
      create type bunch_of_act is table of act_t

      declare
      my_acts bunch_of_act := bunch_of_act();
      v_loop_count BINARY_INTEGER := 1;

      begin
      for wf_lkup_rec in(select * from wf_activities_lkup)
      loop
      my_acts.extend;
      /*here you cannot address wf_acts_t or wf_trans_t individually*/
      my_acts(v_loop_COUNT) :=
      act_t(
      wf_acts_t(wf_act_t(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null)
      ),
      wf_trans_t ( wf_tran_t (null))
      );



      v_loop_count :=
      v_loop_count +1;

      end loop;

      v_loop_count := 1;
      dbms_output.put_line (my_acts (1).act(1).a_name);
      end;
        • 1. Re: oracle nested object table
          141119
          Actually I have one other question :)...

          Once collection is intialized how do i reference the individual nested table row in plsql?

          thanks!
          • 2. Re: oracle nested object table
            438877
            Hello Mark,

            I'm not sure I interpret your concern correctly,
            but may be this example shows what you need ?
            SQL> declare
              2 
              3   my_acts bunch_of_act := bunch_of_act();
              4   v_loop_count BINARY_INTEGER := 1;
              5   ptr1 wf_acts_t;
              6   ptr2 wf_trans_t;
              7  begin
              8 
              9   for wf_lkup_rec in(select e.* from emp e where rownum < 3) loop
            10 
            11    /* Declare new table object*/
            12    ptr1 := wf_acts_t();
            13 
            14    /* Here we fill table individually */
            15    ptr1.extend;
            16    ptr1(ptr1.last) := wf_act_t(wf_lkup_rec.ename);   
            17 
            18    /* Declare new table object*/
            19    ptr2 := wf_trans_t();
            20 
            21    /* Here we fill table individually */
            22    ptr2.extend;
            23    ptr2(ptr2.last) := wf_tran_t(wf_lkup_rec.empno);   
            24 
            25    my_acts.extend;
            26 
            27    my_acts(my_acts.last) :=
            28    act_t(ptr1, ptr2);
            29 
            30   end loop;
            31 
            32   /* Individually take elements */
            33 
            34   for j in 1..my_acts.count loop
            35 
            36     dbms_output.put_line('Element ' || j || ' of top colletion contains ');
            37     for k in 1..my_acts(j).act.count loop
            38         dbms_output.put_line(' act with name ' || my_acts(j).act(k).a_name);  
            39     end loop;
            40     dbms_output.put_line('and');
            41     for k in 1..my_acts(j).tran.count loop
            42         dbms_output.put_line(' tran with id ' || my_acts(j).tran(k).wf_name); 
            43     end loop;
            44 
            45   end loop;
            46 
            47  end;
            48  /
            Element 1 of top colletion contains
            act with name SMITH
            and
            tran with id 7369
            Element 2 of top colletion contains
            act with name ALLEN
            and
            tran with id 7499
            &nbsp
            PL/SQL procedure successfully completed.
            Rgds.
            • 3. Re: oracle nested object table
              141119
              This is excellent example of what I need. Thanks!

              I'm almost there...

              What I want to add to this process is i want to have 3 loops where records are loaded into structure...

              for loop 1 will only execute once
              loop

              for loop
              loop 2 will execute 2 times inside of loop 1
              end loop

              for loop
              loop
              loop 3 will execute 2 times inside of loop 1
              end loop

              end loop

              The thing i need to figure out now is what the subscript reference sequence will be.

              It should be:
              my_act(1).act(1).a_name
              my_act(1).act(2).a_name
              my_act(1).trans(1).t_name
              my_act(1).trans(2).t_name

              I will have to figure out if the above in terms of subscripting is possible...





              • 4. Re: oracle nested object table
                438877
                It can be something like that (I'm sorry for
                very silly example):
                SQL> declare  
                  2     my_acts bunch_of_act := bunch_of_act(); 
                  3     v_loop_count BINARY_INTEGER := 1; 
                  4     ptr1 wf_acts_t; 
                  5     ptr2 wf_trans_t; 
                  6  begin     
                  7 
                  8    for wf_lkup_rec in(select e.* from emp e where rownum < 3) loop   
                  9 
                10     /* Declare new table object*/
                11      ptr1 := wf_acts_t();
                12 
                13     /* Here we fill table individually */
                14     -- Add some loop:
                15 
                16      for k in 1..2 loop
                17       ptr1.extend;    
                18       ptr1(ptr1.last) := wf_act_t(wf_lkup_rec.ename);        
                19      end loop;
                20 
                21     /* Declare new table object*/    
                22      ptr2 := wf_trans_t();
                23 
                24     /* Here we fill table individually */    
                25     -- Add some loop
                26     for k in 1..2 loop
                27       ptr2.extend;
                28       ptr2(ptr2.last) := wf_tran_t(wf_lkup_rec.empno);    
                29     end loop;      
                30 
                31     my_acts.extend;
                32     my_acts(my_acts.last) :=  act_t(ptr1, ptr2);
                33 
                34    end loop;
                35 
                36    /* Individually take elements */
                37 
                38    for j in 1..my_acts.count loop
                39    
                40       dbms_output.put_line('Element ' || j || ' of top colletion contains '); 
                41 
                42       for k in 1..my_acts(j).act.count loop
                43           dbms_output.put_line(' act with name ' || my_acts(j).act(k).a_name); 
                44       end loop;
                45 
                46       dbms_output.put_line('and');
                47 
                48       for k in 1..my_acts(j).tran.count loop
                49           dbms_output.put_line(' tran with id ' || my_acts(j).tran(k).wf_name);
                50       end loop;
                51    
                52    end loop;
                53    
                54  end;
                55  /
                Element 1 of top colletion contains
                act with name SMITH
                act with name SMITH
                and
                tran with id 7369
                tran with id 7369
                Element 2 of top colletion contains
                act with name ALLEN
                act with name ALLEN
                and
                tran with id 7499
                tran with id 7499
                &nbsp
                PL/SQL procedure successfully completed.
                Rgds.
                • 5. Re: oracle nested object table
                  141119
                  This is exactly what i need...

                  I had for the most part come up with what I needed but I kept getting "reference to unintialized collection" message...

                  I figured out the problem and everything works like a charm...

                  My work with any object oriented stuff(such as it is) is limited so this has been learning experience...


                  Again thanks!