insertion in a table of objects with nested table in oracle

Question

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.

MT0 · Accepted Answer · 2022-05-22 09:46:17Z

2

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.

edited May 22, 2022 at 9:46

answered May 21, 2022 at 23:25

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user062 Over a year ago

Thank you very much for the detailed and informative answer, i don't get your explanation of why it said missing statement as i understand, if i were to replace the select with actual refs it would work fine, so why doesn't it work when trying to initialize it with a select statement.

user062 Over a year ago

you said that "SELECT statement that is nested inside another statement that is not surrounded in () brackets", why does it have to be surrounded by brackets?

user062 Over a year ago

also, i get that the multi row error was because of multiple rows returned from the select statement, but why is that a problem? what's wrong with multiple rows as parameters to the constructor?

MT0 Over a year ago

@user062 Regarding your comment "why does it have to be surrounded by brackets?" the answer is that is how the SQL syntax is defined and not doing it is a syntax error and Oracle communicates that by saying missing expression (i.e. you wrote something but the SQL engine cannot parse it as any of the options by its allowed SQL syntax so a valid expression is missing).

MT0 Over a year ago

As for your other comments, I've updated the answer.

|

Collectives™ on Stack Overflow

insertion in a table of objects with nested table in oracle

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related