Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.8K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
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 sub-query returns more than one row?
thank you very much.
Best Answer
-
movies_type()
is the CONSTRUCTOR FUNCTION formovies_type
It requires 1 or more instances of
movie_type
. Each one separated by a comma.You're only making a single instance in your SQL.
The magic incantation you seek is:
Cast(multiset( select... ) as movies_type)
How It Works
movies_type
is a Nested Table type.You can Construct an NT of cardinality 1 by specifying a single Scalar SQL statement. (this is what you have in your 3rd attempt)
insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA' ,movies_type( (select ref(m) from movie m where movie_id = 7) -- single element ) ));
Additionally, you can construct an NT of cardinality n by specifying n elements; each one separated by a comma. In this example, I supply 3x Scalar SQL statements.
insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA' ,movies_type( (select ref(m) from movie m where movie_id = 7), -- element 1 (select ref(m) from movie m where movie_id = 8), -- element 2 (select ref(m) from movie m where movie_id = 9) -- element 3 ) ));
But, that's not what you want.
In order to turn a
SELECT
statement into an Array of elements, you need to usecast(multiset() as data_type)
like so:insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA' ,cast(multiset( select ref(m) from movie m where movie_id in (7,8,9) ) as movies_type) ));
Answers
-
I'm going to continue answering your question on DBA StackExchange first.
additionally, this should be posted in SQL & PL/SQL.
I'll copy&paste the answer here afterwards.
-
Code Review Notes:
- Always use 4-digit years in string-date conversions (
yyyy
notyy
) - SQL Object (
Type
) evolution is a PITA. This is why they aren't readily used as columns/tables- Try to add
rating
andruntime
tomovie_type
without dropping/deleting anything.
- Try to add
setup SQL:
create type movie_type as Object ( MOVIE_ID NUMBER(15), TITLE VARCHAR(50) , GENDER VARCHAR(30), -- typo RELEASE_DATE DATE, RUNNING_TIME NUMBER, BUDGET NUMBER ) Final; / create table MOVIE of movie_type; ALTER TABLE MOVIE ADD CONSTRAINT PK_MOVIE_ID PRIMARY KEY (MOVIE_ID); ALTER TABLE MOVIE modify TITLE not null; INSERT INTO MOVIE (MOVIE_ID, TITLE, GENDER, RELEASE_DATE, RUNNING_TIME, BUDGET) VALUES (7,'Star Wars','epic space opera',TO_DATE('25/05/1977', 'DD/MM/YY'),121,11000000); INSERT INTO MOVIE (MOVIE_ID, TITLE, GENDER, RELEASE_DATE, RUNNING_TIME, BUDGET) VALUES (8,'The Empire Strikes Back','epic space opera',TO_DATE('17/05/1980', 'DD/MM/YY'),124,18000000); INSERT INTO MOVIE (MOVIE_ID, TITLE, GENDER, RELEASE_DATE, RUNNING_TIME, BUDGET) VALUES (9,'Return of the Jedi','epic space opera',TO_DATE('25/05/1983', 'DD/MM/YY'),132,32500000); commit; create type movies_type as Table of ref movie_type; / create type person_type as object ( person_id int, first_name varchar2(50 char), last_name varchar2(50 char), dob date, dod date, country varchar2(50 char) ) not final; / create type actor_type under person_type ( starring movies_type ) Final; / create table actor of actor_type NESTED TABLE starring STORE AS starring_nt;
Tear down SQL:
drop table movie; drop table actor; drop type actor_type;drop type movies_type; drop type movie_type; drop type person_type;
- Always use 4-digit years in string-date conversions (
-
movies_type()
is the CONSTRUCTOR FUNCTION formovies_type
It requires 1 or more instances of
movie_type
. Each one separated by a comma.You're only making a single instance in your SQL.
The magic incantation you seek is:
Cast(multiset( select... ) as movies_type)
How It Works
movies_type
is a Nested Table type.You can Construct an NT of cardinality 1 by specifying a single Scalar SQL statement. (this is what you have in your 3rd attempt)
insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA' ,movies_type( (select ref(m) from movie m where movie_id = 7) -- single element ) ));
Additionally, you can construct an NT of cardinality n by specifying n elements; each one separated by a comma. In this example, I supply 3x Scalar SQL statements.
insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA' ,movies_type( (select ref(m) from movie m where movie_id = 7), -- element 1 (select ref(m) from movie m where movie_id = 8), -- element 2 (select ref(m) from movie m where movie_id = 9) -- element 3 ) ));
But, that's not what you want.
In order to turn a
SELECT
statement into an Array of elements, you need to usecast(multiset() as data_type)
like so:insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA' ,cast(multiset( select ref(m) from movie m where movie_id in (7,8,9) ) as movies_type) ));
-
As an alternative to
cast(multiset() as movies_type)
:insert into actor values( actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA', ( select cast(collect(ref(m)) as movies_type) from movie m where movie_id in (7,8,9) ) ));
Or, using MOVIE primary key as OID and scoped REFs :
create table MOVIE of movie_type (MOVIE_ID primary key) object identifier is primary key; alter table starring_nt add (scope for (column_value) is movie);
we may construct REFs via MAKE_REF :
insert into actor values (actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA' , movies_type( make_ref(movie,7) , make_ref(movie,8) , make_ref(movie,9) ) ));