title: How can I insert subtype values in sql oracle?
body: <p>I am very new to Object-Relational Databases and Oracle and am trying to insert Subtype values into a table. I am unsure whether it is a syntax error when creating the tables or when inserting them so any help would be great.</p>
  
  <p>It's a music database and have been instructed that there must be 2 subtypes from the table; ALBUM with one varray and one nested table (so any alternate design will not help here). </p>
  
  <pre><code>create or replace type artist_type as object 
  (artistName     varchar(50), 
   artistRole     varchar(25))
  / 
  create type artist_array_type  
  as varray(5) of artist_type
  / 
  create or replace type review_type as object 
  (reviewerName   varchar(25), 
   reviewDate     date,
   reviewText     varchar(250), 
   reviewScore    number)
  /
  create or replace type review_table_type as table of review_type
  /
  create or replace type album_type as object 
  (albumTitle         varchar(50),
  albumPlaytime       number(3), -- minutes
  albumReleaseDate    date, 
  albumGenre      varchar(15),
  albumPrice      number(9,2),
  albumTracks     number(2),
  albumArtists        artist_array_type,
  albumReviews        review_table_type,
  member function discountPrice return number,
  member function containsText (pString1 varchar2, pString2 varchar2) return integer)
  not final 
  /
  create or replace type disk_type under album_type 
  ( mediaType         varchar(10),
  diskNum         number(2), -- number of disks
  diskUsedPrice       number(9,2),
  diskDeliveryCost    number(9,2), 
  overriding member function discountPrice return number)
  /
  create or replace type mp3_type under album_type
  (downloadSize   number, -- size in MB
  overriding member function discountPrice return number)
  /
  -- create tables --
  create table albums of album_type 
  object id system generated
  nested table albumReviews store as store_reviews 
  / 
  </code></pre>
  
  <p>I have tried the following:</p>
  
  <pre><code>insert into albums (albumTitle , albumPlaytime, albumReleaseDate, albumGenre, albumPrice, albumTracks, albumArtists) 
  values (mp3_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 37.00, 32, artist_array_type(artist_type('Bob Dylan', 'Composer'), artist_type('Bob Dylan', 'Vocals')), 165));
  </code></pre>
  
  <p>and with removing the columns:</p>
  
  <pre><code>insert into albums  
  values (mp3_type('The Essential Bob Dylan', 99, '8-Jul-2016', 'Pop', 37.00, 32, artist_array_type(artist_type('Bob Dylan', 'Composer'), artist_type('Bob Dylan', 'Vocals')), 165));
  </code></pre>
  
  <p>Also, adding the nested table values albumReviews did not work either:</p>
  
  <pre><code>    insert into albums  
      values (mp3_type('The Essential Bob Dylan', 
                       99, 
                       '8-Jul-2016', 
                       'Pop', 
                       37.00, 
                       32, 
                       artist_array_type(artist_type('Bob Dylan', 'Composer'), artist_type('Bob Dylan', 'Vocals')), 
                       review_table_type('Shawn', '24-Jul-2018', 'Wife loved it!', 5),
                       165));
  </code></pre>
  
  <p>Again, I'm not fully across ORDs and probably missing something, but any help is appreciated!</p>