How can I insert subtype values in sql oracle?

Question

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.

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

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 
/ 

I have tried the following:

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

and with removing the columns:

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

Also, adding the nested table values albumReviews did not work either:

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

Again, I'm not fully across ORDs and probably missing something, but any help is appreciated!

Answer 1

Your second INSERT is missing the albumReviews values. Simply adding a NULL before the last number will make this statement work:

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')), null, 165));

A few lessons here:

1. Keep your examples as simple as possible. More people will help with a simpler example. And in the process of simplifying the code you probably would have found the problem yourself.
2. Nobody uses object-relational technology anymore. It's ridiculous that it's this difficult to insert simple data. Sometimes it feels like hardly anybody even uses Oracle anymore. Tell your professor that they're at least two generations behind. Unless this assignment is a cautionary tale.