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.

    Update: here is the type movie_type and table movie:

    create type movie_type as Object
    (
      MOVIE_ID NUMBER(15),
      TITLE VARCHAR(50) ,
      GENRE  VARCHAR(30),
      RELEASE_DATE DATE,
      RUNNING_TIME NUMBER,
      BUDGET NUMBER
    ) Final;
    

    table creation:

    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;

    relevant insertions in movie:

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



  • 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. 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)
    ));
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2