sql oracle-database object-relational-model

Oracle OR - how to guarantee that a nested table has references to all the subtypes of a supertype

I'm designing an object-relational model database. I have a supertype named "topico_t" and 4 subtypes of this supertype named "anotacao_t", "tarefa_t", "memo_t" and "contacto_t". See here the DDL snippet:

CREATE OR REPLACE TYPE categoria_t AS OBJECT(
 nome    VARCHAR2(25),
 pai     REF categoria_t
);

CREATE OR REPLACE TYPE categoria_tab_t AS TABLE OF REF categoria_t;

CREATE OR REPLACE TYPE topico_t AS OBJECT(
 titulo VARCHAR2(100),
 ultimaAlteracao DATE,
 categorias categoria_tab_t
 --referencias topico_tab_t
) NOT FINAL;

CREATE OR REPLACE TYPE topico_tab_t AS TABLE OF REF topico_t;

ALTER TYPE topico_t ADD ATTRIBUTE referencias topico_tab_t CASCADE;

CREATE OR REPLACE TYPE periodo_t AS OBJECT(
 inicio  DATE,
 fim     DATE
);

CREATE OR REPLACE TYPE repeticao_t AS OBJECT(
 frequencia  VARCHAR2(10), 
 duracao     periodo_t
);

CREATE OR REPLACE TYPE anotacao_t UNDER topico_t(
 periodo periodo_t,
 repeticao repeticao_t
);


CREATE OR REPLACE TYPE telefone_t AS OBJECT(
 numero  VARCHAR2(25)
);

CREATE OR REPLACE TYPE telefone_tab_t AS TABLE OF telefone_t;

CREATE OR REPLACE TYPE morada_t AS OBJECT(
 rua     VARCHAR2(100),
 localidade  VARCHAR2(50),
 codigoPostal VARCHAR2(10)
);

CREATE OR REPLACE TYPE morada_tab_t AS TABLE OF morada_t;

CREATE OR REPLACE TYPE contacto_t UNDER topico_t(
 telefones telefone_tab_t,
 moradas   morada_tab_t,
 email     VARCHAR2(100),
 url       VARCHAR2(150)
);

CREATE OR REPLACE TYPE tarefa_t UNDER topico_t(
 dataFim       DATE,
 completo  NUMBER(1,0),
 conteudo  VARCHAR2(255)
);

CREATE OR REPLACE TYPE memo_t UNDER topico_t(
 conteudo VARCHAR2(255)
);


CREATE TABLE categorias OF categoria_t;


CREATE TABLE topicos OF topico_t
 NESTED TABLE categorias STORE AS categorias_nested
 NESTED TABLE referencias STORE AS referencias_nested;

So, then I populate this table "topicos" with this:

  INSERT INTO topicos VALUES (tarefa_t(
  'Dissertacao',
  TO_DATE('2018/02/13', 'YYYY/MM/DD'),
  categoria_tab_t((select ref(c) from categorias c where nome='FEUP')),
  topico_tab_t((select ref(t) from topicos t where titulo='Diogo Pereira'), -- which is an object of the subtype "contacto_t"
  (select ref(t) from topicos t where titulo='Comecar a dissertacao'), -- which is an object of the subtype "memo_t"
  (select ref(t) from topicos t where titulo='Apresentar Dissertacao'), -- which is an object of the subtype "tarefa_t"
  (select ref(t) from topicos t where titulo='Reuniao com Orientador da Dissertacao')), -- which is an object of the subtype "anotacao_t"
  TO_DATE('2018/08/13', 'YYYY/MM/DD'),
  0,
  'Dissertacao all over again'));

So, I have to build a query or even a PL/SQL block that returns me all the rows of the table "topicos" that contains at least, in the nested table "referencias", one instance object for each of this 4 subtypes mentioned above. Ideally this query would return me that row (mentioned in the above INSERT).

Best regards and hope you're having a good Worker's Day! ;)

Solution

You have a mistake in your DDL code. You are creating a base type topico_t and then some subtypes under that type, but you're creating a single table of base type objects only. The problem with this is that you're expecting that table to hold objects of any subtype, which will not be the case due to what is known as object slicing. The table you've created has only the columns which correspond to the fields you've defined for the base type.

Therefore, you need to create tables for all of the subtypes which you will eventually instantiate. You should only create a table for the base type if it makes sense for that type to be instantiated, that is, if an object of the base type which doesn't belong to any of the subtypes could exist.

After doing this, the query you're trying to write should use the function TREAT. Note that, according to the documentation, this function returns NULL when the expression is not of the type you're trying to cast to, so the query you need to write should be as simple as testing if there is at least one row with a non-null result field when selecting from the nested table and attempting to cast to each of the subtypes you've created. Check the example on the documentation to see how to use this function.