update multiple records in multiple nested tables in oracle

Question

I have an oracle table with nested tables in some of the columns. Now, I need to be able to update all the records in each nested table, in each of the records of the main table. How is this accomplished? Any of the ways that I've tried, I get errors about either not be able to perform updates on that view, or single row subquery returns more than one row.

here's an example from to illustrate. I can run an update like this:

    UPDATE TABLE(select entity.name
                 from entity
                 where entity.uidn = 2)
    SET last = 'Decepticon',
    change_date = SYSDATE,
    change_user = USER
    WHERE first = 'Galvatron';

but in this case, the table clause is being executed on a single nested table from a single row. How would an update like this be performed if you didn't want just the entity.uidn which equalled 2?

thanks!

Answer 1

Perhaps the best reason for avoiding nested tables in a database is that they are hard to work with, and the syntax is underdocumneted and hard to grok.

Moving on!

Here is a table with a nested table.

SQL> select f.force_name, t.id, t.name
  2  from transformer_forces f, table(f.force_members) t
  3  /

FORCE_NAME         ID NAME
---------- ---------- --------------------
Autobot             0 Metroplex
Autobot             0 Optimus Prime
Autobot             0 Rodimus
Decepticon          0 Galvatron
Decepticon          0 Megatron
Decepticon          0 Starscream
Dinobot             0 Grimlock
Dinobot             0 Swoop
Dinobot             0 Snarl

9 rows selected.

SQL>

As you can see, each element in the nested table the ID attribute is set to zero in all cases. What we would like to do is update all of them. But, alas!

SQL> update table
  2   ( select force_members from transformer_forces ) t
  3  set t.id = rownum
  4  /
 ( select force_members from transformer_forces ) t
   *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row


SQL> 

It is possible to update all the elements on a nested table for a single row in the holding table:

SQL> update table
  2       ( select force_members from transformer_forces
  3         where force_name = 'Autobot') t
  4      set t.id = rownum
  5  /

3 rows updated.

SQL>

But the only way of doing that for the whole table is a PL/SQL loop. Yuck!

There is an alternative: use a Nested Table Locator, via the NESTED_TABLE_GET_REFS hint. This is a particularly obscure thing (it's not in the main list of hints) but it does the trick:

SQL> update /*+ NESTED_TABLE_GET_REFS */ force_members_nt
  2  set id = rownum
  3  /

9 rows updated.

SQL> select f.force_name, t.id, t.name
  2  from transformer_forces f, table(f.force_members) t
  3  /

FORCE_NAME         ID NAME
---------- ---------- --------------------
Autobot             1 Metroplex
Autobot             2 Optimus Prime
Autobot             3 Rodimus
Decepticon          4 Galvatron
Decepticon          5 Megatron
Decepticon          6 Starscream
Dinobot             7 Grimlock
Dinobot             8 Swoop
Dinobot             9 Snarl

9 rows selected.

SQL>

This hint allows us to bypass the holding table altogether and work with the actual nested table. That is, the object specified in the Nested Table storage clause:

create table transformer_forces (
    force_name varchar2(10)
    , force_members transformers_nt)
nested table force_members store as force_members_nt return as value;
                                    ^^^^^^^^^^^^^^^^