SCOPE for a table of REFs

Question

I am designing an object-relational model with Oracle (18.4.0) and I would like to add a SCOPE constraint to a table type column of an object table. Is it possible? Here a simplified model:

CREATE OR REPLACE TYPE t_cycler AS OBJECT (
    name VARCHAR2(50)
);

CREATE TABLE cycler OF t_cycler (
    name PRIMARY KEY
);


CREATE OR REPLACE TYPE t_cycler_list IS TABLE OF REF t_cycler;

CREATE OR REPLACE TYPE t_team AS OBJECT (
    name VARCHAR2(50),
    cyclers t_cycler_list
);

CREATE TABLE team OF t_team (
    name PRIMARY KEY
)
NESTED TABLE cyclers STORE AS cyclers_tab;

I need that team.cyclers only contains REFs to objects in cycler. I look into the documentation but unfortunately it does not say a lot about SCOPE constraint, like here:

You can constrain a column type, collection element, or object type attribute to reference a specified object table. Use the SQL constraint subclause SCOPE IS when you declare the REF.

But the only example it provides is about a simple column type. I tried specifying SCOPE IS cycler in several ways inside the creation of the team table but with no results.

Answer 1

You want to add the scope to the COLUMN_VALUE pseudo-column of the nested table:

ALTER TABLE cyclers_tab ADD SCOPE FOR ( COLUMN_VALUE ) IS cycler;

If you then do:

INSERT INTO cycler ( name ) VALUES ( 'c1.1' );
INSERT INTO cycler ( name ) VALUES ( 'c1.2' );

INSERT INTO team (
  name,
  cyclers
) VALUES (
  'team1',
  t_cycler_list(
    ( SELECT REF(c) FROM cycler c WHERE name = 'c1.1' ),
    ( SELECT REF(c) FROM cycler c WHERE name = 'c1.2' )
  )
);

Then you can insert the row. But, if you have another table of the same object type:

CREATE TABLE cycler2 OF t_cycler (
    name PRIMARY KEY
);

INSERT INTO cycler2 ( name ) VALUES ( 'c2.1' );

And try to do:

INSERT INTO team (
  name,
  cyclers
) VALUES (
  'team2',
  t_cycler_list(
    ( SELECT REF(c) FROM cycler2 c WHERE name = 'c2.1' )
  )
);

Then you get the error:

ORA-22889: REF value does not point to scoped table

db<>fiddle here