i've created the table "employe" and "medecin" with all types needed, like this:

"medecin" table inherits from "employe" table

create type temploye as object( NUM_EMP Number(7),
                                NOM_EMP varchar2(30),
                                PRENOM_EMP varchar2(30),
                                ADRESSE_EMP varchar2(100),
                                TEL_EMP varchar2(10)
                                ) not final;
/

create type tmedecin under temploye( NUM_MED number(7),
                                     SPECIALITE varchar2(40),
                                     medecin_soigne t_set_ref_soigne,
                                     directeur_service t_set_ref_service
                                )not final;
/

CREATE TABLE EMPLOYE of temploye (NUM_EMP primary key);
/

-- Table MEDECIN 
create table MEDECIN of tmedecin
(
    constraint pk_NUM_MED primary key(NUM_MED),
    SPECIALITE check(SPECIALITE IN ('Anesthésiste','Cardiologue','Généraliste','Orthopédiste'))
)
nested table medecin_soigne store as table_medecin_soigne,
nested table directeur_service store as table_directeur_service;
/

is there a way to insert elements in the "medecin" table without adding the attributes of "employe", I had an error when i did it, it says i need to specify all arguments. i did something like this:

INSERT INTO EMPLOYE VALUES (temploye(4,'BOUROUBI','Taous','Lotissement Dauphin n°30 DRARIA/ALGER','021356085'));

INSERT INTO MEDECIN VALUES (tmedecin(4, 
'Orthopédiste', T_SET_REF_SOIGNE(), T_SET_REF_SERVICE ()));

CodePudding user response：

You used create type tmedecin under temploye so a tmedecin has all the attributes of the parent type temploye as well as the extra attributes defined in the sub-type.

If you want to pass in a tmedecin type then you need to declare all the attributes:

INSERT INTO MEDECIN VALUES (
  tmedecin(
    4,
    'NOM',
    'PRENOM',
    'ADRESSE',
    'TEL',
    4, 
    'Orthopédiste',
    T_SET_REF_SOIGNE(),
    T_SET_REF_SERVICE ()
  )
);

But you could declare them as NULL:

INSERT INTO MEDECIN VALUES (
  tmedecin(
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    5, 
    'Orthopédiste',
    T_SET_REF_SOIGNE(),
    T_SET_REF_SERVICE ()
  )
);

Or, you could use INSERT and name the columns rather than passing in a TYPE:

INSERT INTO MEDECIN (
  NUM_MED,
  SPECIALITE,
  medecin_soigne,
  directeur_service
) VALUES (
  6, 
  'Orthopédiste',
  T_SET_REF_SOIGNE(),
  T_SET_REF_SERVICE ()
);

(Which will implicitly create the other columns with the default value, which unless otherwise specified would be NULL.)

However

You do not need the MEDECIN table as you could insert tmedecin types into the EMPLOYEE table:

INSERT INTO EMPLOYE VALUES (
  tmedecin(
    5,
    'NOM',
    'PRENOM',
    'ADRESSE',
    'TEL',
    5, 
    'Orthopédiste',
    T_SET_REF_SOIGNE(),
    T_SET_REF_SERVICE ()
  )
);

And can even create similar constraints using:

ALTER TABLE employe ADD CONSTRAINT employe__spceialite__chk CHECK (
  OBJECT_VALUE IS NOT OF (tmedecin)
  OR TREAT(OBJECT_VALUE AS tmedecin).SPECIALITE = 'Anesthésiste'
  OR TREAT(OBJECT_VALUE AS tmedecin).SPECIALITE = 'Cardiologue'
  OR TREAT(OBJECT_VALUE AS tmedecin).SPECIALITE = 'Généraliste'
  OR TREAT(OBJECT_VALUE AS tmedecin).SPECIALITE = 'Orthopédiste'
);

CREATE UNIQUE INDEX test_uk1 ON employe(
  TREAT(OBJECT_VALUE AS tmedecin).NUM_MED
);

db<>fiddle here