如果要使用对象，请使用对象派生表：

CREATE TYPE artist_t AS OBJECT
(
    ARTISTID      INTEGER,
    NAME          varchar2(100),
    SURNAME       varchar2(100),
    DATE_OF_BIRTH date
);

CREATE TYPE song_t AS OBJECT
(
    SONGID           INTEGER,
    TITLE            varchar2(100),
    GENRE            varchar2(100),
    DATE_OF_CREATION date,
    BPM              NUMBER,
    artist           REF artist_t
);

然后，您可以创建表：

CREATE SEQUENCE artist_seq;

CREATE SEQUENCE song_seq;

CREATE TABLE artist OF artist_t(
  artistid      DEFAULT artist_seq.NEXTVAL
                PRIMARY KEY,
  name          NOT NULL,
  surname       NOT NULL,
  date_of_birth NOT NULL
);

CREATE TABLE song OF song_t(
    SONGID           DEFAULT song_seq.NEXTVAL
                     PRIMARY KEY
                     NOT NULL,
    DATE_OF_CREATION DEFAULT sysdate
);

ALTER TABLE song ADD SCOPE FOR (artist) IS artist;

但是，不能为对象派生表指定IDENTITY列（但可以将DEFAULT与序列一起使用）。

更新

如果您需要artist_song_connection的详细信息，则可以使用song表中的VIEW：

CREATE VIEW artist_song_connection (song, artist) AS
SELECT REF(s),
       s.artist
FROM   song s;

fiddle

对我来说，这看起来像是一个 * 普通的 * 引用完整性问题--只需要创建一个从song表到artist的外键，并在song表中包含一个新列（artistid）。
我认为您不需要任何其他类型（虽然您创建了两个类型）。

SQL> create table artist(
  2      artistid integer generated always as identity primary key not null,
  3      name varchar2(100) not null,
  4      surname varchar2(100) not null,
  5      date_of_birth date
  6  );

Table created.

SQL> create table song(
  2      songid integer generated always as identity primary key not null,
  3      title varchar2(100),
  4      genre varchar2(100),
  5      date_of_creation date default sysdate,
  6      bpm number,
  7      --
  8      artistid integer constraint fk_song_art references artist (artistid)
  9  );

Table created.

程序：

SQL> create or replace procedure p_insert_song
  2    (par_title    in song.title%type,
  3     par_genre    in song.genre%type,
  4     par_date     in song.date_of_creation%type,
  5     par_bpm      in song.bpm%type,
  6     par_artistid in song.artistid%type
  7    )
  8  is
  9  begin
 10    insert into song (title, genre, date_of_creation, bpm, artistid)
 11      values (par_title, par_genre, par_date, par_bpm, par_artistid);
 12  end;
 13  /

Procedure created.

SQL>