insertion in a table of objects with nested table in oracle

I have a problem inserting in a nested table in oracle

These are the relevant types and tables;

create type movies_type as Table of ref movie_type;

create type actor_type under person_type

(

starring movies_type

) Final;

create table actor of actor_type

NESTED TABLE starring STORE AS starring_nt;

this is how i tried to insert

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YY'),TO_DATE('27/12/2016', 'DD/MM/YY'),'USA', movies_type(select ref(m) from movie m where movie_id in (7, 8, 9))));

this doesn't work, it gives

SQL Error: ORA-00936: missing expression

which isn't very helpful.

i also tried nesting the select statement in parenthesis because i thought it might have been a syntax error

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YY'),TO_DATE('27/12/2016', 'DD/MM/YY'),'USA', movies_type((select ref(m) from movie m where movie_id in (7, 8, 9)))));

but it said

SQL ERROR ORA-01427: single-row subquery returns more than one row

so i changed it to this

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YY'),TO_DATE('27/12/2016', 'DD/MM/YY'),'USA', movies_type((select ref(m) from movie m where movie_id=7))));

which worked but it isn't what i want since it doesn't allow me to have multiple values in

movies_type

i don't understand what the problem is exactly and the errors messages aren't helpful

why does it say missing expression?

and why in the second case it gives single-row subquery returns more than one row?

thank you very much.

Solution

You want to use CAST and COLLECT to aggregate the references into a collection:

insert into actor values(
  actor_type(
    29,
    'Carrie',
    'Fisher',
    DATE '1956-10-21',
    DATE '2016-12-27',
    'USA',
    (
      SELECT CAST( COLLECT(REF(m)) AS movies_type )
      FROM   movie m
      WHERE  movie_id IN (7, 8, 9)
    )
  )
);

db<>fiddle here

insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YY'),TO_DATE('27/12/2016', 'DD/MM/YY'),'USA', movies_type(select ref(m) from movie m where movie_id in (7, 8, 9))));

why does it say missing expression?

Because you have movie_type() where the () brackets are for the object's constructor and then inside you have a SELECT statement that is nested inside another statement that is not surrounded in () brackets (as the surrounding brackets are for the constructor).

so i changed it to this
insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YY'),TO_DATE('27/12/2016', 'DD/MM/YY'),'USA', movies_type((select ref(m) from movie m where movie_id=7))));
and why in the second case it gives single-row subquery returns more than one row?

Because, when you use WHERE movie_id IN (7,8,9) the query is returning 3 rows and it needs to return only a single row; for that you need to aggregate the multiple rows into a single row containing a collection.

what's wrong with multiple rows as parameters to the constructor?

The constructor expects a single item in each argument so you would need to use:

insert into actor values (
  actor_type(
    29,
    'Carrie',
    'Fisher',
    DATE '1956-10-21',
    DATE '2016-12-27',
    'USA',
    movies_type(
      (select ref(m) from movie m where movie_id=7),
      (select ref(m) from movie m where movie_id=8),
      (select ref(m) from movie m where movie_id=9)
    )
  )
);

This would work (assuming movie_id is unique; if it isn't and things like the theatrical version and the director's cut of the same movie have the same movie_id then it still would raise exceptions) but it may use a table/index scans for each SELECT and would be less efficient than collating all the rows in a single SELECT using CAST/COLLECT.