sqloracle-databaseobject-typeobject-relational-model

SCOPE for a table of REFs


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.


Solution

  • 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