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
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):Then you can create the data:
The
MEDICO_TY
value is stored in theDIPENDENTE_TAB
without the need for a nested table for the collection defined in the sub-type.You can get the data out using:
db<>fiddle here