Nested tables: Insert values into specific columns of nested table

Question

I have a question regarding insert query for a nested table. I have an object like this:

 CREATE OR REPLACE TYPE obj_animals as OBJECT (
   row_id INTEGER,
   animal_id INTEGER,
   attr1 VARCHAR2(30),
   attr2 VARCHAR2(30),
   attr3 VARCHAR2(30)
 );

I have a table type like this:

CREATE OR REPLACE TYPE obj_animals_tab IS TABLE OF obj_animals;

Nested table is contained in this:

CREATE TABLE animals 
(
     animal_id INTEGER,
     animal_ptr obj_animals_tab
)
 NESTED TABLE animals_ptr STORE AS obj_animals_tab;

How do I insert into (row_id,animal_id,attr1) columns or (row_id,animal_id & attr3) columns in the nested table using dynamic SQL?

I tried something like this:

INSERT INTO animals(animal_id,animal_ptr(row_id,animal_id,attr3))
VALUES(1,obj_animals_tab(obj_animals(1,1,'4')));

This threw me an error: Missing comma. Can you tell me a way to implement this? Thanks!

Answer 1

Insert statement must be like this:

INSERT INTO animals (animal_id, animal_ptr)
     VALUES (1, obj_animals_tab(obj_animals(1,1,'4')));

In order to update existing values you can use this syntax:

UPDATE 
   (SELECT t.animal_id as animal_id_T, obj.animal_id as animal_id_OBJ, 
      attr1, attr2, attr3 
   FROM animals t
      NATURAL JOIN TABLE(obj_animals_tab) obj)
SET attr3 = 'xyz'
WHERE animal_id_T = 1
   AND animal_id_OBJ = 1;

Maybe it is not very smart to use animal_id in both table and object type.

Answer 2

INSERT INTO animals(animal_id,animal_ptr(row_id,animal_id,attr3))
VALUES(1,obj_animals_tab(obj_animals(
row_id => 1,
animal_id => 1,
attr3 => '4'
)));

Try this out. working for me.