Forum Stats

  • 3,826,098 Users
  • 2,260,595 Discussions
  • 7,896,786 Comments

Discussions

insertion in a table of objects with nested table in oracle

User_5EVCZ
User_5EVCZ Member Posts: 1 Green Ribbon
edited May 23, 2022 6:47AM in SQL & PL/SQL

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

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    Answer ✓

    movies_type() is the CONSTRUCTOR FUNCTION for movies_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 use cast(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)
    ));
    


    User_5EVCZ

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown

    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.

    User_5EVCZ
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    edited May 22, 2022 3:25PM

    Code Review Notes:

    • Always use 4-digit years in string-date conversions (yyyy not yy )
    • SQL Object (Type ) evolution is a PITA. This is why they aren't readily used as columns/tables
      • Try to add rating and runtime to movie_type without dropping/deleting anything.

    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;
    
    User_5EVCZ
  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
    Answer ✓

    movies_type() is the CONSTRUCTOR FUNCTION for movies_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 use cast(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)
    ));
    


    User_5EVCZ
  • odie_63
    odie_63 Member Posts: 8,492 Silver Trophy

    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)
       ) 
    ));
    
    User_5EVCZ