Oracle Object-Relational - Is there a way to declare a nested table of a subtype?

Question

Type declaration:

CREATE TYPE DIPENDENTE_TY AS OBJECT(
    NOME VARCHAR2(20),
    CF CHAR(16),
    DATAN DATE
) NOT FINAL;
/
CREATE TYPE AMMINISTRATORE_TY UNDER DIPENDENTE_TY(
    
);
/
CREATE TYPE MEDICO_TY UNDER DIPENDENTE_TY(
    SPECIALITA VARCHAR2(20),
    REPARTO VARCHAR2(20),
    MEMBER PROCEDURE INSERISCI_VISITA(PAZIENTE VARCHAR,
                                                                        DATAV DATE,
                                                                        TIPOV VARCHAR2,
                                                                        TICKET INTEGER)
);
/
CREATE TYPE PAZIENTE_TY AS OBJECT(
    CF CHAR(16),
    NOME VARCHAR2(20),
    COGNOME VARCHAR2(20)
);
/

CREATE TYPE VISITA_TY AS OBJECT(
    DATA DATE,
    TIPO VARCHAR2(20),
    TICKET INTEGER
);
/
CREATE TYPE COLL_REF_VISITA_TY AS TABLE OF REF VISITA_TY;
/
ALTER TYPE MEDICO_TY ADD ATTRIBUTE VISITATO COLL_REF_VISITA_TY;
/
ALTER TYPE PAZIENTE_TY ADD ATTRIBUTE FA_VISITA COLL_REF_VISITA_TY;
/
ALTER TYPE VISITA_TY ADD ATTRIBUTE DIP REF PAZIENTE_TY CASCADE;
/
ALTER TYPE VISITA_TY ADD ATTRIBUTE DIM REF MEDICO_TY CASCADE;
/
CREATE TABLE PAZIENTE_TAB OF PAZIENTE_TY
NESTED TABLE FA_VISITA STORE AS VISITE_PAZIENTE_TAB;
/
CREATE TABLE DIPENDENTE_TAB OF DIPENDENTE_TY;
/
CREATE TABLE VISITA_TAB OF VISITA_TY;
--(
--DIP SCOPE IS PAZIENTE_TAB,        
--DIM SCOPE IS DIPENDENTE_TAB              
--);
/

I need to declare the VISITATO nested table of Medico_ty, but it is a subtype of Dipendente_ty and so I only have the table for instances of type Dipendente_ty. How can I declare the nested table only for the Medico_ty instances?

EDIT I get the following error when I try to define the table for Dipendente_ty:

ORA-02320: failure in creating storage table for nested table column TREAT(SYS_NC_ROWINFO$ AS "SQL_PFOHOKUIIIAMJALHSUZHUBDGJ"."MEDICO_TY")."VISITATO" ORA-06512: at "SYS.DBCLOUD_SYS_SEC", line 1404
ORA-06512: at "SYS.DBCLOUD_SYS_SEC", line 2224
ORA-06512: at line 2

Answer 1

You do not need to declare a nested table for the sub-type (and can use forward declaration of the types to get rid of all of your ALTER statements):

CREATE TYPE DIPENDENTE_TY AS OBJECT(
    NOME VARCHAR2(20),
    CF CHAR(16),
    DATAN DATE
) NOT FINAL;

CREATE TYPE AMMINISTRATORE_TY UNDER DIPENDENTE_TY();

CREATE TYPE MEDICO_TY;

CREATE TYPE PAZIENTE_TY;

CREATE TYPE VISITA_TY AS OBJECT(
    DATA DATE,
    TIPO VARCHAR2(20),
    TICKET INTEGER,
    DIP REF PAZIENTE_TY,
    DIM REF MEDICO_TY
);

CREATE TYPE COLL_REF_VISITA_TY AS TABLE OF REF VISITA_TY;

CREATE TYPE MEDICO_TY UNDER DIPENDENTE_TY(
    SPECIALITA VARCHAR2(20),
    REPARTO VARCHAR2(20),
    VISITATO COLL_REF_VISITA_TY,
    MEMBER PROCEDURE INSERISCI_VISITA(
      PAZIENTE VARCHAR,
      DATAV DATE,
      TIPOV VARCHAR2,
      TICKET INTEGER
    )
);

CREATE TYPE PAZIENTE_TY AS OBJECT(
    CF CHAR(16),
    NOME VARCHAR2(20),
    COGNOME VARCHAR2(20),
    FA_VISITA COLL_REF_VISITA_TY
);

CREATE TABLE DIPENDENTE_TAB OF DIPENDENTE_TY;

CREATE TABLE PAZIENTE_TAB OF PAZIENTE_TY
NESTED TABLE FA_VISITA STORE AS VISITE_PAZIENTE_TAB;

CREATE TABLE VISITA_TAB OF VISITA_TY(
  DIP SCOPE IS PAZIENTE_TAB,        
  DIM SCOPE IS DIPENDENTE_TAB
);

ALTER TABLE VISITE_PAZIENTE_TAB
  ADD SCOPE FOR ( COLUMN_VALUE ) IS VISITA_TAB;

Then you can create the data:

INSERT INTO visita_tab VALUES ( VISITA_TY( SYSDATE, 'tipo1', 1, NULL, NULL ) );
INSERT INTO visita_tab VALUES ( VISITA_TY( SYSDATE, 'tipo2', 2, NULL, NULL ) );
INSERT INTO visita_tab VALUES ( VISITA_TY( SYSDATE, 'tipo3', 3, NULL, NULL ) );

INSERT INTO DIPENDENTE_TAB VALUES (
  MEDICO_TY(
    'nome',
    'cf______________',
    SYSDATE,
    'specialita',
    'reparto',
    COLL_REF_VISITA_TY(
      ( SELECT REF(v) FROM  visita_tab v WHERE ticket = 1 ),
      ( SELECT REF(v) FROM  visita_tab v WHERE ticket = 2 )
    )
  )
);

INSERT INTO visita_tab VALUES (
  VISITA_TY(
    SYSDATE,
    'tipo4',
    4,
    NULL,
    ( SELECT TREAT(REF(m) AS REF MEDICO_TY)
      FROM dipendente_tab m
      WHERE nome = 'nome'
      AND   VALUE(m) IS OF ( MEDICO_TY )
    )
  )
);

The MEDICO_TY value is stored in the DIPENDENTE_TAB without the need for a nested table for the collection defined in the sub-type.

You can get the data out using:

SELECT data,
       tipo,
       ticket,
       v.dim.nome,
       v.dim.cf,
       v.dim.datan,
       v.dim.specialita,
       v.dim.reparto,
       dv.column_value.tipo
FROM   visita_tab v
       OUTER APPLY v.dim.VISITATO dv;

db<>fiddle here