13 Replies Latest reply: Oct 14, 2010 1:28 AM by 63723 RSS

    Editing Nested table collection

    805032
      Hi,
      We have a Nested table colletion which will contain around 500 rows of object type.
      Now i need to update this table collection basing on the value of a column in object type.
      Do i need to iterate through the collection using for loop and update the nested table or is there any other way using single Update command

      "Update Table(<<nested_table>>) set <<something>> = value " doesn't work.

      I am new to pl\sql and to collections, please help me with this.

      Thanks in advance
      Harsha
        • 1. Re: Editing Nested table collection
          FlorianW.
          Dear Harsha!

          Please have a look at the following link:

          http://www.developer.com/db/article.php/10920_3379271_3/Oracle-Programming-with-PLSQL-Collections.htm

          The second example "2.)Update entry 'MULTIMEDIA' to a new value. Only possible with a nested table!!" shows exactly what you need.

          Yours sincerely

          Florian W.
          • 2. Re: Editing Nested table collection
            802709
            could you please provide your full scripts. also you should update your original table

            update table set NestedField = NestedType(1,2,3,4) where ... like that.

            by the way nested table is not a suggested way.
            • 3. Re: Editing Nested table collection
              805032
              Hi Mustafa,
              Thanks for reply.

              PFB an example of whati am talking.

              Create Type test_obj is object of ( userid number, name varchar2(10) );
              Create Type test_table is table of test_obj;

              test_table is having the following data
              ------------------------------------------------------------------------
              test_obj(1, 'ABC')
              test_obj(2, 'XYZ')
              test_obj(3, 'PQR')
              test_obj(4, 'qwe')
              test_obj(5, 'rty')

              Now i want to update the test_table and set names of all the entries with userid >3 to null.
              Pls let me know how to achieve it with out looping.

              TIA
              Harsha
              • 4. Re: Editing Nested table collection
                FlorianW.
                Dear Harsha!

                What about this?
                CREATE TYPE test_obj IS OBJECT (userid NUMBER, name VARCHAR2(10));
                CREATE TYPE test_table IS TABLE OF test_obj;
                
                DECLARE
                -- Inititalize ntab of type test_table with 4 rows
                  ntab test_table := test_table(test_obj(1, 'Scott'), test_obj(2, 'Tiger'),
                                                test_obj(4, 'hr'),    test_obj(5, 'sh'));
                BEGIN
                -- Loop through all the rows
                  FOR i IN 1..ntab.COUNT
                  LOOP
                    DBMS_OUTPUT.PUT_LINE('Before Update');
                    DBMS_OUTPUT.PUT_LINE(ntab(i).userid || ' ' || ntab(i).name);
                    DBMS_OUTPUT.PUT_LINE('After Update');
                -- Check if userid > 3
                    IF (ntab(i).userid > 3) THEN
                -- Set name to NULL
                      ntab(i).name := NULL;
                    END IF;
                    DBMS_OUTPUT.PUT_LINE(ntab(i).userid || ' ' || ntab(i).name);
                  END LOOP;
                END;
                /
                Yours sincerely

                Florian W.
                • 5. Re: Editing Nested table collection
                  805032
                  Hi Florain,
                  Thanks for the reply.

                  I was doing the samething, but i was thinking about the performance implications, i will be having anywhere between 500 -750 rows in test_table type so is it advisable to loop through everything? I was wondering if there is anyother way to achieve this with out looping through whole table type.

                  TIA
                  Harsha
                  • 6. Re: Editing Nested table collection
                    63723
                    If your nested table is a column of a db table, you can do it in a single sql statement:
                    ZEP@DEV>
                    ZEP@DEV>create  Type test_obj is object  ( userid number, name varchar2(10) )
                      2  /
                    
                    Type created.
                    
                    ZEP@DEV>Create Type test_table_ntt is table of test_obj
                      2  /
                    
                    Type created.
                    
                    
                    ZEP@DEV>create  table test_tab (
                      2               id number,
                      3               test_table test_table_ntt)
                      4  nested table test_table store as nt_st
                      5  /
                    
                    Table created.
                    
                    ZEP@DEV>insert into test_tab values
                      2  (1,
                      3  test_Table_ntt(test_obj(1, 'ABC'),
                      4             test_obj(2, 'XYZ'),
                      5             test_obj(3, 'PQR'),
                      6             test_obj(4, 'qwe'),
                      7             test_obj(5, 'rty')));
                    
                    1 row created.
                    
                    
                    ZEP@DEV>Select  * from table(select test_table from test_Tab where id = 1);
                    
                        USERID NAME
                    ---------- ----------
                             1 ABC
                             2 XYZ
                             3 PQR
                             4 qwe
                             5 rty
                    one  update:
                    ZEP@DEV>update table(select test_table
                      2                 from test_Tab
                      3                where id =1)
                      4     set name = null
                      5   where userid >3;
                    
                    2 rows updated.
                    
                    ZEP@DEV>commit;
                    
                    Commit complete.
                    
                    
                    ZEP@DEV>Select  * from table(select test_table from test_Tab where id = 1);
                    
                        USERID NAME
                    ---------- ----------
                             1 ABC
                             2 XYZ
                             3 PQR
                             4
                             5
                    
                    ZEP@DEV>drop table test_tab;
                    
                    Table dropped.
                    
                    ZEP@DEV>drop type test_Table_ntt;
                    
                    Type dropped.
                    
                    ZEP@DEV>drop type test_obj;
                    
                    Type dropped.
                    
                    ZEP@DEV>
                    • 7. Re: Editing Nested table collection
                      805032
                      But my problem is that the nested table type is not stored in any table, it is the ouput of a procedure.

                      TIA
                      Harsha
                      • 8. Re: Editing Nested table collection
                        805112
                        Hello,

                        You can obtain the result in a new variable ntab_aux of ttype test table, using the TABLE command and BULK COLLECT. Then you can assign this new collection to your ntab old variable.

                        CREATE OR REPLACE TYPE test_obj IS OBJECT (userid NUMBER, name VARCHAR2(10));
                        /

                        CREATE TYPE test_table IS TABLE OF test_obj;
                        /

                        DECLARE
                        -- Inititalize ntab of type test_table with 4 rows
                        ntab test_table := test_table(test_obj(1, 'Scott'), test_obj(2, 'Tiger'),
                        test_obj(4, 'hr'), test_obj(5, 'sh'));
                        ntab_aux test_table;
                        BEGIN
                        -- Loop through all the rows and print values
                        DBMS_OUTPUT.PUT_LINE('Before Update');
                        FOR i IN 1..ntab.COUNT
                        LOOP
                        DBMS_OUTPUT.PUT_LINE(ntab(i).userid || ' ' || ntab(i).name);
                        END LOOP;

                        -- populate a second collection
                        select * bulk collect into ntab_aux
                        from (select test_obj(userid,
                        case when userid<=3 then name
                        else NULL
                        end)
                        from TABLE(CAST (ntab as test_table)));
                        -- assign the result to original collection and delete auxiliary
                        ntab := ntab_aux;
                        ntab_aux.delete;

                        -- Loop through all the rows and print values
                        DBMS_OUTPUT.PUT_LINE('After Update');
                        FOR i IN 1..ntab.COUNT
                        LOOP
                        DBMS_OUTPUT.PUT_LINE(ntab(i).userid || ' ' || ntab(i).name);
                        END LOOP;

                        END;
                        /

                        Hope this helps.
                        • 9. Re: Editing Nested table collection
                          63723
                          In this case you don't need:

                          *1)* use the pseudo-function *'CAST'*
                          Starting with Oracle Database 10g, the database automatically determines the correct type.

                          *2)* the outermost select
                          ZEP@DEV>create  Type test_obj is object  ( userid number, name varchar2(10) )
                            2  /
                          
                          Type created.
                          
                          ZEP@DEV>Create  Type test_table_ntt is table of test_obj
                            2  /
                          
                          Type created.
                          
                          ZEP@DEV>declare
                            2      test_table  test_table_ntt := test_Table_ntt(test_obj(1, 'ABC'), test_obj(2, 'XYZ'),
                            3                                        test_obj(3, 'PQR'), test_obj(4, 'qwe'),
                            4                                        test_obj(5, 'rty'));
                            5      test_table_2 test_table_ntt;
                            6
                            7      procedure display is
                            8      begin
                            9          for ele in (select userid,
                           10                             name
                           11                        from table(test_table))
                           12          loop
                           13              dbms_output.put_line('userid: ' || ele.userid || ' - ' || 'name: ' || ele.name);
                           14          end loop;
                           15         dbms_output.new_line;
                           16         dbms_output.new_line;
                           17      end;
                           18  begin
                           19      display;
                           20
                           21      select test_obj(userid, case when userid <=3 then name else null end)
                           22      bulk collect into test_Table_2
                           23        from table(test_Table) ;
                           24
                           25      test_Table :=test_Table_2;
                           26      test_table_2.delete;
                           27      display;
                           28  end;
                           29  /
                          userid: 1 - name: ABC
                          userid: 2 - name: XYZ
                          userid: 3 - name: PQR
                          userid: 4 - name: qwe
                          userid: 5 - name: rty
                          userid: 1 - name: ABC
                          userid: 2 - name: XYZ
                          userid: 3 - name: PQR
                          userid: 4 - name:
                          userid: 5 - name:
                          
                          PL/SQL procedure successfully completed.
                          
                          ZEP@DEV>drop type test_Table_ntt;
                          
                          Type dropped.
                          
                          ZEP@DEV>drop type test_obj;
                          
                          Type dropped.
                          
                          ZEP@DEV>
                          • 10. Re: Editing Nested table collection
                            805032
                            Hi,
                            Thanks to all for the replies.
                            I was hoping there would be some way to directly update the nested table type as in the case where nested table type is a part of table directly, now i think i can safely assume that there is no direct way. I need to check out the temp table solution to see the time it is taking for large table types.

                            Thanks again for replies.
                            Harsha
                            • 11. Re: Editing Nested table collection
                              Billy~Verreynne
                              The correct solution is most likely not using a nested table structure at all. It has a number of limitations. It impacts performance.

                              You need to justify why a nested table is needed and why relational 3NF does not suffice. Nested table structures are very much a very unique exception to the rule.
                              • 12. Re: Editing Nested table collection
                                63723
                                And you'll get the error :ORA-14457: disallowed Nested Table column in a Temporary table

                                To submit a request I suggest you to:
                                1) Read this http://wikis.sun.com/display/Forums/Forums+FAQ
                                - Format your code, etc..
                                2) Read a little bit of docs: http://tahiti.oracle.com/
                                2.1) Show otn forum you have worked to get around the problem
                                3) Write :
                                - VERSION db
                                - WHAT you are trying to achieve in terms of design code, db, etc.
                                - WHY : maybe you are just looking in the wrong direction and you need others solutions.
                                - DDL to replicate your test.
                                - DML to populate your data structures
                                - OUTPUT ERROR


                                You gave us a simple definition of a simple object type and a nt of about 500 rows. This isn't a "large table types".
                                • 13. Re: Editing Nested table collection
                                  63723
                                  It was a duplication of my previous message!