How to update a varray type within a table with a simple update statement?

Question

I have a table that has a column defined as varray of a defined type. The production table is way more complicated then the following example.

I am able to select the single columns within the type of the varray. But I would like to update the table with a simple update statement (rather than going through a pl/sql routine).

If this is not possible (and I must go through a pl/sql routine) what is a smart and easy way to code this?

update (select l.id, t.* from my_object_table l, table(l.object_list) t) 
set value2 = 'obj 4 upd' 
where value1 = 10 

ORA-01733: virtual column not allowed here

Here the full example of types etc.

create or replace type my_object 
as object(
      value1                    number,
      value2                    varchar2(10),
      value3                    number);

create or replace type my_object_varray as varray(100000000) of my_object;

create table my_object_table (id number not null, object_list my_object_varray);

insert into my_object_table 
   values (1, my_object_varray (
                    my_object(1,'object 1',10), 
                    my_object(2,'object 2',20),
                    my_object(3,'object 3',30) 
                 )
          );

insert into my_object_table 
   values (2, my_object_varray (
                    my_object(10,'object 4',10), 
                    my_object(20,'object 5',20),
                    my_object(30,'object 6',30) 
                 )
          );

select l.id, t.* from my_object_table l, table(l.object_list) t;

Type created.
Type created.
Table created.
1 row created.
1 row created.

        ID     VALUE1 VALUE2         VALUE3
---------- ---------- ---------- ----------
         1          1 object 1           10
         1          2 object 2           20
         1          3 object 3           30
         2         10 object 4           10
         2         20 object 5           20
         2         30 object 6           30

6 rows selected.

Answer 1

I don't believe you can update a single object's value within a varray from plain SQL, as there is no way to reference the varray index. (The link Alessandro Rossi posted seems to support this, though not necessarily for that reason). I'd be interested to be proven wrong though, of course.

I know you aren't keen on a PL/SQL approach but if you do have to then you could do this to just update that value:

declare
  l_object_list my_object_varray;
  cursor c is
    select l.id, l.object_list, t.*
    from my_object_table l,
    table(l.object_list) t
    where t.value1 = 10
    for update of l.object_list;
begin
  for r in c loop
    l_object_list := r.object_list;
    for i in 1..l_object_list.count loop
      if l_object_list(i).value1 = 10 then
        l_object_list(i).value2 := 'obj 4 upd';
      end if;
    end loop;

    update my_object_table
    set object_list = l_object_list
    where current of c;
  end loop;
end;
/

anonymous block completed

select l.id, t.* from my_object_table l, table(l.object_list) t;

        ID     VALUE1 VALUE2         VALUE3
---------- ---------- ---------- ----------
         1          1 object 1           10 
         1          2 object 2           20 
         1          3 object 3           30 
         2         10 obj 4 upd          10 
         2         20 object 5           20 
         2         30 object 6           30 

SQL Fiddle.

If you're updating other things as well then you might prefer a function that returns the object list with the relevant value updated:

create or replace function get_updated_varray(p_object_list my_object_varray,
  p_value1 number, p_new_value2 varchar2)
return my_object_varray as
  l_object_list my_object_varray;
begin
  l_object_list := p_object_list;
  for i in 1..l_object_list.count loop
    if l_object_list(i).value1 = p_value1 then
      l_object_list(i).value2 := p_new_value2;
    end if;
  end loop;

  return l_object_list;
end;
/

Then call that as part of an update; but you still can't update your in-line view directly:

update (
  select l.id, l.object_list
  from my_object_table l, table(l.object_list) t
  where t.value1 = 10
)
set object_list = get_updated_varray(object_list, 10, 'obj 4 upd');

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table

You need to update based on relevant the ID(s):

update my_object_table
set object_list = get_updated_varray(object_list, 10, 'obj 4 upd')
where id in (
  select l.id
  from my_object_table l, table(l.object_list) t
  where t.value1 = 10
);

1 rows updated.

select l.id, t.* from my_object_table l, table(l.object_list) t;

        ID     VALUE1 VALUE2         VALUE3
---------- ---------- ---------- ----------
         1          1 object 1           10 
         1          2 object 2           20 
         1          3 object 3           30 
         2         10 obj 4 upd          10 
         2         20 object 5           20 
         2         30 object 6           30 

SQL Fiddle.

If you wanted to hide the complexity even further you could create a view with an instead-of trigger that calls the function:

create view my_object_view as
  select l.id, t.* from my_object_table l, table(l.object_list) t
/

create or replace trigger my_object_view_trigger
instead of update on my_object_view
begin
  update my_object_table
  set object_list = get_updated_varray(object_list, :old.value1, :new.value2)
  where id = :old.id;
end;
/

Then the update is pretty much what you wanted, superficially at least:

update my_object_view
set value2 = 'obj 4 upd'
where value1 = 10;

1 rows updated.

select * from my_object_view;

        ID     VALUE1 VALUE2         VALUE3
---------- ---------- ---------- ----------
         1          1 object 1           10 
         1          2 object 2           20 
         1          3 object 3           30 
         2         10 obj 4 upd          10 
         2         20 object 5           20 
         2         30 object 6           30 

SQL Fiddle.

Answer 2

As the Oracle documentation states here

While nested tables can also be changed in a piecewise fashions, varrays cannot.

There is no way to modify VARRAYS in piecewise fashion. The only things you could do are:

• Convert the data type of your fied into a NESTED TABLE (CREATE TYPE xxx AS TABLE OF yyy)
• Fetch the varray of the row you want to change, modify with in your client language, then update the row to set the modified value on it.

Answer 3

Have you tried this?

UPDATE (
    SELECT value2
    FROM
        TABLE(SELECT object_list FROM my_object_table)
    WHERE value1 = 10
) t
SET t.value2 = 'object 4 upd';

I couldn't test this query, use with care. I'm not sure Oracle can actually do that...