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 sub-query returns more than one row?
thank you very much.