How to call a member procedure in a subclass/subtype? (object relational)

[masterfrag]

I want at this point call the member procedure AddVisita of one row where the object is a medico_ty in dipendente_tab
(gettin the row like this (think) select treat(value(d) as medico_ty) d
from dipendente_tab d
where d.cf=constant )
for fill a row of visita_tab and the nested table in paziente_tab(Fa Visita) and dipendente_tab where dipendente is of medico_ty (FaVisita).
Like this d.AddVisita(par 1,par2,par3,par 4), is possible? how to make it?

This is the code(no syntax error):

1)CREATE type Visita_ty 

2)CREATE TYPE COLL_REF_VISITA_TY AS TABLE OF REF VISITA_TY


3)CREATE TYPE Dipendente_ty AS OBJECT
 (
    Nome VARCHAR2(20),
    Cognome VARCHAR2(20),
    Cf VARCHAR2(20),
    DataN Date

 ) NOT FINAL



4)CREATE TYPE Amministrativo_ty UNDER Dipendente_ty 
   (
   Livello NUMBER,
   Mansione VARCHAR(20)
   )FINAL



5)CREATE  TYPE Medico_ty UNDER Dipendente_ty
   (
  Specialita VARCHAR(20),
  FaVisita Coll_Ref_Visita_ty,
  Reparto VARCHAR(20),
  MEMBER PROCEDURE AddVisita (CF VARCHAR2,Data DATE,Tipo VARCHAR2,Ticket Number)
  )FINAL

6)CREATE TYPE Paziente_ty AS OBJECT
 (
   CF VARCHAR2(16),
   NOME VARCHAR2(20),
   COGNOME VARCHAR2(20),
   FaVisita Coll_Ref_Visita_ty
 )

7)CREATE type Visita_ty AS OBJECT (
    Tipo VARCHAR(20),
    Data DATE,
    Ticket number,
    Paziente REF Paziente_ty,
    Medico   REF Medico_ty
	)
8)CREATE TYPE BODY Medico_ty is 
      MEMBER PROCEDURE AddVisita(CF VARCHAR,Data DATE, Tipo VARCHAR,Ticket 
                                                                  number) is
      DECLARE
          Visita Visita_ty;
          RefPaziente REF Paziente_ty;
      BEGIN
      	SELECT REF(P) INTO REFPAZIENTE
      	FROM Paziente_TAB P
      	WHERE P.CF=CF
      	VISITA :=Visita_ty(Data,Tipo,Ticket,RefPaziente,REF(SELF))
      	INSERT INTO Visita_TAB values Visita
     	INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita)
      	INSERT INTO TABLE (SELECT P.FaVisita
                         FROM PAZIENTE_TAB P
                         WHERE P.CF=CF ) VALUES REF(VISITA)
      END

9)CREATE TABLE Paziente_TAB OF Paziente_ty
	(CF PRIMARY KEY)
 	NESTED TABLE FaVisita
               STORE AS PazienteFaVisita_TAB

10)CREATE TABLE Dipendente_TAB OF Dipendente_ty
       (CF PRIMARY KEY) 
       NESTED TABLE TREAT(OBJECT_VALUE AS medico_ty).FaVisita STORE AS visite_tab

11)CREATE TABLE Visita_TAB OF Visita_ty
         (Paziente SCOPE IS Paziente_TAB,
          Medico    SCOPE IS Dipendente_TAB)    

// some insert

INSERT INTO PAZIENTE_TAB (nome,cognome,cf,favisita)
VALUES('rosario','brescia','ros80lit04edk19f',Coll_Ref_Visita_ty())

INSERT INTO PAZIENTE_TAB (nome,cognome,cf,favisita)
VALUES('antonio','rosato','ant80lit04edk19f',Coll_Ref_Visita_ty())

INSERT INTO dipendente_tab values(medico_ty( 'rosario','manfredoina','rosak2442jk3','10-feb-1960','psicologia',
coll_ref_visita_ty(),'psi1'))

INSERT INTO dipendente_tab values(medico_ty( 'manuele','doria','fafasd32442jk3','10-gen-1970','chirurgia',coll_ref_visita_ty(),'chi1'))

[amitpatel66]

Are you getting any runtime errors?

[masterfrag]

No runtime error, i run all the code and create normally table and type, and if i tried to insert manually it works ( i tried some query and go well) but insted of us all this (not clear) code below i wanna to use the AddVisita procedure for insert in the various nested table/visita_tab :

INSERT INTO visita_tab values('chirurgia','25-feb-2010',30,
                                             (select ref(p)       
                                              from paziente_tab p
                                              where p.cf='ros80lit04edk19f'),
                                             (select treat(ref(d) as  ref medico_ty)
                                              from dipendente_tab d
                                               where d.cf='fafasd32442jk3'))


INSERT INTO TABLE
(SELECT TREAT(value(D) AS Medico_ty).FaVisita 
FROM Dipendente_tab d 
where d.cf='fafasd32442jk3'
)
SELECT  ref(v)
FROM visita_tab v 
WHERE v.medico=(select ref(m) 
                             from dipendente_tab m
                              where m.cf='fafasd32442jk3')



INSERT INTO TABLE(
SELECT FaVisita 
FROM Paziente_tab P 
WHERE p.cf='ros80lit04edk19f'
)
SELECT  ref(v)
FROM visita_tab v 
WHERE  v.paziente=(SELECT ref(paz) 
                                    FROM paziente_tab paz
                                    WHERE paz.cf='ros80lit04edk19f')

Thx for the fast answer :).