Home » SQL & PL/SQL » SQL & PL/SQL » How to call a member procedure in a subtype/subclass (object relational oracle) (oracle 10g expression edition, windows xp service pack3)
How to call a member procedure in a subtype/subclass (object relational oracle) [message #439927] Wed, 20 January 2010 04:23 Go to next message
masterfrag
Messages: 8
Registered: January 2010
Junior Member
Hi all,
this is the code(no sintax 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'))
 


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(FaVisita) and dipendente_tab where dipendente is of medico_ty (FaVisita).
Like this d.AddVisita(par1,par2,par3,par4), is possible? how to make it?

Thx for the help.
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #439934 is a reply to message #439927] Wed, 20 January 2010 04:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
masterfrag wrote on Wed, 20 January 2010 15:53
Hi all,
this is the code(no sintax error):


SQL> INSERT INTO dipendente_tab values
  2  (medico_ty( 'manuele','doria','fafasd32442jk3','10-gen-1970','chirurgia',coll_ref_visita_ty(),'chi1'));
(medico_ty( 'manuele','doria','fafasd32442jk3','10-gen-1970','chirurgia',coll_ref_visita_ty(),'chi1'))
                                               *
ERROR at line 2:
ORA-01843: not a valid month


do you think it`s a valid statement(without any error?)

sriram Smile

[Updated on: Wed, 20 January 2010 04:54]

Report message to a moderator

Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #439936 is a reply to message #439934] Wed, 20 January 2010 04:57 Go to previous messageGo to next message
masterfrag
Messages: 8
Registered: January 2010
Junior Member
INSERT INTO dipendente_tab values (medico_ty( 'manuele','doria','fafasd32442jk3','10-gen-1970','chirurgia',coll_ref_visita_ty(),'chi1'));

-1 row inserted

^^' no error i execute all the code in the sql comand section of oracle 10g, the format of date is correct, where u have tryed to launch the insert and getted the error ?
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #439947 is a reply to message #439936] Wed, 20 January 2010 05:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
the format of date is correct

No, it is not as ramoradba showed you, and I get the same error.
Never rely on implicit conversion and default format, always use TO_DATE with an explicit format.
And I advice you to use month number and not month name as you may have users with other languages than yours.

Regards
Michel
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #439968 is a reply to message #439927] Wed, 20 January 2010 06:20 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
In this case the month number is necessary rather than advisable if we're going to run the test case. You've presumably got your db language set to something other than english. I've got no idea what month gen actually is.
And as Michel says you should include a format mask as well.
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #439971 is a reply to message #439968] Wed, 20 January 2010 06:45 Go to previous messageGo to next message
masterfrag
Messages: 8
Registered: January 2010
Junior Member
Thx for the advice, i will set all date field with
to_date with this mask 'yyyy/mm/dd'.
My problem is how calling the AddVisita(par1,par2,par3,par4) procedure Smile, signature of AddVisita is declared in medico_ty, the body of the procedure is sequently declared. Dipendente_tab is of dipendente_ty <- super of medico_ty(under,extend), than in dipendente_tab i have medico_ty and amministrativo_ty "istances".

[Updated on: Wed, 20 January 2010 06:47]

Report message to a moderator

Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #439995 is a reply to message #439927] Wed, 20 January 2010 08:51 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
masterfrag wrote on Wed, 20 January 2010 11:23
Hi all,
this is the code(no sintax error):



I really don't think so.

SQL> CREATE type Visita_ty
  2  /

Type created.

SQL> CREATE TYPE COLL_REF_VISITA_TY AS TABLE OF REF VISITA_TY
  2  /

Type created.

SQL>
SQL> CREATE TYPE Dipendente_ty AS OBJECT
  2   (
  3      Nome VARCHAR2(20),
  4      Cognome VARCHAR2(20),
  5      Cf VARCHAR2(20),
  6      DataN Date
  7
  8   ) NOT FINAL
  9  /

Type created.

SQL>
SQL>
SQL> CREATE TYPE Amministrativo_ty UNDER Dipendente_ty
  2     (
  3     Livello NUMBER,
  4     Mansione VARCHAR(20)
  5     )FINAL
  6  /

Type created.

SQL>
SQL>
SQL> CREATE  TYPE Medico_ty UNDER Dipendente_ty
  2     (
  3    Specialita VARCHAR(20),
  4    FaVisita Coll_Ref_Visita_ty,
  5    Reparto VARCHAR(20),
  6    MEMBER PROCEDURE AddVisita (CF VARCHAR2,Data DATE,Tipo VARCHAR2,Ticket Number)
  7    )FINAL
  8  /

Type created.

SQL>
SQL> CREATE TYPE Paziente_ty AS OBJECT
  2   (
  3     CF VARCHAR2(16),
  4     NOME VARCHAR2(20),
  5     COGNOME VARCHAR2(20),
  6     FaVisita Coll_Ref_Visita_ty
  7   )
  8  /

Type created.

SQL>
SQL> CREATE type Visita_ty AS OBJECT (
  2      Tipo VARCHAR(20),
  3      Data DATE,
  4      Ticket number,
  5      Paziente REF Paziente_ty,
  6      Medico   REF Medico_ty
  7     )
  8  /

Type created.

SQL>
SQL> CREATE TYPE BODY Medico_ty is
  2        MEMBER PROCEDURE AddVisita(CF VARCHAR,Data DATE, Tipo VARCHAR,Ticket
  3                                                                    number) is
  4        DECLARE
  5            Visita Visita_ty;
  6            RefPaziente REF Paziente_ty;
  7        BEGIN
  8             SELECT REF(P) INTO REFPAZIENTE
  9             FROM Paziente_TAB P
 10             WHERE P.CF=CF
 11             VISITA :=Visita_ty(Data,Tipo,Ticket,RefPaziente,REF(SELF))
 12             INSERT INTO Visita_TAB values Visita
 13             INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita)
 14             INSERT INTO TABLE (SELECT P.FaVisita
 15                           FROM PAZIENTE_TAB P
 16                           WHERE P.CF=CF ) VALUES REF(VISITA)
 17        END
 18
 19  /

Warning: Type Body created with compilation errors.

SQL>
SQL> CREATE TABLE Paziente_TAB OF Paziente_ty
  2     (CF PRIMARY KEY)
  3     NESTED TABLE FaVisita
  4                 STORE AS PazienteFaVisita_TAB
  5  /

Table created.

SQL>
SQL> CREATE TABLE Dipendente_TAB OF Dipendente_ty
  2         (CF PRIMARY KEY)
  3         NESTED TABLE TREAT(OBJECT_VALUE AS medico_ty).FaVisita STORE AS visite_tab
  4  /

Table created.

SQL>
SQL> CREATE TABLE Visita_TAB OF Visita_ty
  2           (Paziente SCOPE IS Paziente_TAB,
  3            Medico    SCOPE IS Dipendente_TAB)
  4  /

Table created.

SQL> show errors
Errors for TYPE BODY MEDICO_TY:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/7      PLS-00103: Encountered the symbol "DECLARE" when expecting one of
         the following:
         begin function package pragma procedure subtype type use
         <an identifier> <a double-quoted delimited-identifier> form
         current cursor external language
         The symbol "begin" was substituted for "DECLARE" to continue.

11/8     PLS-00103: Encountered the symbol "VISITA" when expecting one of
         the following:
         . ( * @ % & - + ; / at for mod remainder rem
         <an exponent (**)> and or group having intersect minus order

LINE/COL ERROR
-------- -----------------------------------------------------------------
         start union where connect || multiset
         The symbol ";" was substituted for "VISITA" to continue.

12/8     PLS-00103: Encountered the symbol "INSERT" when expecting one of
         the following:
         . ( * % & = - + ; < / > at in is mod remainder not rem
         <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
         LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
         The symbol ";" was substituted for "INSERT" to continue.

13/7     PLS-00103: Encountered the symbol "INSERT" when expecting one of

LINE/COL ERROR
-------- -----------------------------------------------------------------
         the following:
         . ( , @ % ; return returning
         The symbol ";" was substituted for "INSERT" to continue.

14/8     PLS-00103: Encountered the symbol "INSERT" when expecting one of
         the following:
         . ( , % ; return returning
         The symbol ";" was substituted for "INSERT" to continue.

17/7     PLS-00103: Encountered the symbol "END" when expecting one of the
         following:

LINE/COL ERROR
-------- -----------------------------------------------------------------
         . ( , % ; return returning

SQL>


There are many statement separators missing and there are a number of different conceptual errors regarding the use of object types, collections and the REF modifier in the procedure.

Try to build a valid member procedure first and the you could execute something like the following to launch the procedure.

declare
	xxx medico_ty;
begin
	select treat(value(d) as medico_ty) d
		into xxx
	from dipendente_tab d
	where d.cf='rosak2442jk3';
	xxx.AddVisita('ros80lit04edk19f',date'2010-01-20','visita',123456);
end;
/




Bye Alessandro

[Updated on: Wed, 20 January 2010 09:01]

Report message to a moderator

Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440005 is a reply to message #439995] Wed, 20 January 2010 09:38 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
And here follows the sample of a possible implementation.

SQL> CREATE type Visita_ty
  2  /

Type created.

SQL> CREATE TYPE COLL_REF_VISITA_TY AS TABLE OF REF VISITA_TY
  2  /

Type created.

SQL>
SQL> CREATE TYPE Dipendente_ty AS OBJECT
  2   (
  3      Nome VARCHAR2(20),
  4      Cognome VARCHAR2(20),
  5      Cf VARCHAR2(20),
  6      DataN Date
  7
  8   ) NOT FINAL
  9  /

Type created.

SQL>
SQL>
SQL> CREATE TYPE Amministrativo_ty UNDER Dipendente_ty
  2     (
  3     Livello NUMBER,
  4     Mansione VARCHAR(20)
  5     )FINAL
  6  /

Type created.

SQL>
SQL>
SQL> CREATE  TYPE Medico_ty UNDER Dipendente_ty
  2     (
  3    Specialita VARCHAR(20),
  4    FaVisita Coll_Ref_Visita_ty,
  5    Reparto VARCHAR(20),
  6    MEMBER PROCEDURE AddVisita(
  7                     CF VARCHAR,
  8                     Data DATE,
  9                     Tipo VARCHAR,
 10                     Ticket number
 11             )
 12    )FINAL
 13  /

Type created.

SQL>
SQL> CREATE TYPE Paziente_ty AS OBJECT
  2   (
  3     CF VARCHAR2(16),
  4     NOME VARCHAR2(20),
  5     COGNOME VARCHAR2(20),
  6     FaVisita Coll_Ref_Visita_ty
  7   )
  8  /

Type created.

SQL>
SQL> CREATE type Visita_ty AS OBJECT (
  2      Tipo VARCHAR(20),
  3      Data DATE,
  4      Ticket number,
  5      Paziente REF Paziente_ty,
  6      Medico   REF Medico_ty
  7     )
  8  /

Type created.

SQL>
SQL>
SQL> CREATE TABLE Paziente_TAB OF Paziente_ty
  2     (CF PRIMARY KEY)
  3     NESTED TABLE FaVisita
  4                 STORE AS PazienteFaVisita_TAB
  5  /

Table created.

SQL>
SQL> CREATE TABLE Dipendente_TAB OF Dipendente_ty
  2         (CF PRIMARY KEY)
  3         NESTED TABLE TREAT(OBJECT_VALUE AS medico_ty).FaVisita STORE AS visite_tab
  4  /

Table created.

SQL>
SQL> CREATE TABLE Visita_TAB OF Visita_ty
  2           (Paziente SCOPE IS Paziente_TAB,
  3            Medico    SCOPE IS Dipendente_TAB)
  4  /

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE TYPE BODY MEDICO_TY is
  2     MEMBER PROCEDURE AddVisita(
  3                     CF VARCHAR,
  4                     Data DATE,
  5                     Tipo VARCHAR,
  6                     Ticket number
  7             )
  8             is
  9                     Visita Visita_ty;
 10                     RefPaziente REF Paziente_ty;
 11                     rvisita ref visita_ty;
 12                     rself ref medico_ty;
 13                     IN_cf VARCHAR2(100) := cf;
 14             BEGIN
 15                     dbms_output.put_line('cf='||SELF.CF);
 16                     select treat(ref(d) as ref medico_ty)
 17                             into rself
 18                     from dipendente_tab d
 19                     where d.cf = self.cf;
 20                     SELECT REF(P)
 21                             INTO REFPAZIENTE
 22                     FROM Paziente_TAB P
 23                     WHERE P.CF=IN_CF;
 24                     VISITA := Visita_ty(Tipo,Data,Ticket,RefPaziente,rself);
 25                     INSERT INTO Visita_TAB v
 26                             values Visita
 27                             returning ref(v) into rvisita;
 28                     --INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita);
 29                     self.FaVisita.extend;
 30                     self.FaVisita(self.FaVisita.last) := rvisita;
 31                     INSERT INTO TABLE (
 32                                     SELECT P.FaVisita
 33                                     FROM PAZIENTE_TAB P
 34                                     WHERE P.CF=IN_CF
 35                             )
 36                             values (rvisita);
 37        END;
 38  end;
 39  /

Type body created.

SQL>
SQL>
SQL> truncate table visita_tab
  2  /

Table truncated.

SQL> truncate table PAZIENTE_TAB
  2  /

Table truncated.

SQL> truncate table Dipendente_TAB
  2  /

Table truncated.

SQL>
SQL> alter session set nls_date_format = 'dd-mon-yyyy'
  2  /

Session altered.

SQL> alter session set nls_date_language = 'ITALIAN'
  2  /

Session altered.

SQL>
SQL> INSERT INTO PAZIENTE_TAB (nome,cognome,cf,favisita)
  2  VALUES('rosario','brescia','ros80lit04edk19f',Coll_Ref_Visita_ty())
  3  /

1 row created.

SQL> INSERT INTO PAZIENTE_TAB (nome,cognome,cf,favisita)
  2  VALUES('antonio','rosato','ant80lit04edk19f',Coll_Ref_Visita_ty())
  3  /

1 row created.

SQL> INSERT INTO dipendente_tab values(medico_ty( 'rosario','manfredoina','rosak2442jk3','10-feb-1960','psicologia',
  2  coll_ref_visita_ty(),'psi1'))
  3  /

1 row created.

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

1 row created.

SQL>
SQL> commit
  2  /

Commit complete.

SQL>
SQL> select *
  2  from dipendente_tab
  3  /

NOME                 COGNOME              CF                   DATAN
-------------------- -------------------- -------------------- -----------
rosario              manfredoina          rosak2442jk3         10-feb-1960
manuele              doria                fafasd32442jk3       10-gen-1970

SQL> select *
  2  from PAZIENTE_TAB
  3  /

CF               NOME                 COGNOME              FAVISITA
---------------- -------------------- -------------------- ------------------------------------------------------------------------------------------
ros80lit04edk19f rosario              brescia              COLL_REF_VISITA_TY()
ant80lit04edk19f antonio              rosato               COLL_REF_VISITA_TY()

SQL> select *
  2  from Visita_TAB
  3  /

no rows selected

SQL>
SQL> declare
  2     xxx medico_ty;
  3  begin
  4     select treat(value(d) as medico_ty) d
  5             into xxx
  6     from dipendente_tab d
  7     where d.cf='rosak2442jk3';
  8     xxx.AddVisita('ros80lit04edk19f',date'2010-01-20','visita',123456);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select *
  2  from dipendente_tab
  3  /

NOME                 COGNOME              CF                   DATAN
-------------------- -------------------- -------------------- -----------
rosario              manfredoina          rosak2442jk3         10-feb-1960
manuele              doria                fafasd32442jk3       10-gen-1970

SQL> select cf,nome,cognome
  2  from PAZIENTE_TAB
  3  /

CF               NOME                 COGNOME
---------------- -------------------- --------------------
ros80lit04edk19f rosario              brescia
ant80lit04edk19f antonio              rosato

SQL>
SQL> select deref(FAVISITA)
  2  from PAZIENTE_TAB
  3  /
select deref(FAVISITA)
       *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected REF got ESTCEDAR.COLL_REF_VISITA_TY


SQL>
SQL> select TIPO,DATA,TICKET
  2  from Visita_TAB
  3  /

TIPO                 DATA            TICKET
-------------------- ----------- ----------
visita               20-gen-2010     123456

SQL>
SQL> select deref(paziente)
  2  from Visita_TAB
  3  /

DEREF(PAZIENTE)(CF, NOME, COGNOME, FAVISITA)
-----------------------------------------------------------------------------------------------------------------------------------------------------
PAZIENTE_TY('ros80lit04edk19f', 'rosario', 'brescia', COLL_REF_VISITA_TY(00002202087D9A4D782EE45AB8E0440017A45181807D9A4D782EDB5AB8E0440017A4518180))

SQL>
SQL> select deref(medico)
  2  from Visita_TAB
  3  /

DEREF(MEDICO)(NOME, COGNOME, CF, DATAN, SPECIALITA, FAVISITA, REPARTO)
-----------------------------------------------------------------------------------------------------------------------------------------------------
MEDICO_TY('rosario', 'manfredoina', 'rosak2442jk3', '10-feb-1960', 'psicologia', COLL_REF_VISITA_TY(), 'psi1')

SQL>


Bye Alessandro
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440109 is a reply to message #439927] Thu, 21 January 2010 02:39 Go to previous messageGo to next message
masterfrag
Messages: 8
Registered: January 2010
Junior Member
Hi alessandro, thx for the help, i have executed your sample(following all the step), but when try this part of code:
declare
    xxx medico_ty;
   begin
       select treat(value(d) as medico_ty) d
              into xxx
       from dipendente_tab d
       where d.cf='rosak2442jk3';  
    xxx.AddVisita('ros80lit04edk19f',date'2010-01-20','visita',123456);
    end;


I get this two error:
ORA-04063: type body "LAST1.MEDICO_TY" has errors
ORA-06508: PL/SQL:could not find program unit being called: "LAST1.MEDICO_TY"

p.s: i run the sql code in sql control panel of oraclq 10g express edition.

Bye.
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440121 is a reply to message #440109] Thu, 21 January 2010 03:14 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Probably there have been some errors during the creation of the object type medico_ty because what it told you is that the type body is invalid.

Try to drop your previously defined invalid objects and then replace them with valid ones.

Bye Alessandro
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440182 is a reply to message #440121] Thu, 21 January 2010 08:32 Go to previous messageGo to next message
masterfrag
Messages: 8
Registered: January 2010
Junior Member
Hi alessandro,
i have created a new oracle user account then work on fresh db, following all the steps in your sample, and i get the same error i posted in the other message, how i can resolve? can be a problem of oracle version?

Thx a lot for the help.

[Updated on: Thu, 21 January 2010 08:33]

Report message to a moderator

Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440184 is a reply to message #440182] Thu, 21 January 2010 08:40 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Built up the very informativ example (thanks !) from alessandro with no problems.
My ORACLE-version is 10.2.0.1 64 bit.
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440187 is a reply to message #440184] Thu, 21 January 2010 09:00 Go to previous messageGo to next message
masterfrag
Messages: 8
Registered: January 2010
Junior Member
_jum wrote on Thu, 21 January 2010 08:40
Built up the very informativ example (thanks !) from alessandro with no problems.
My ORACLE-version is 10.2.0.1 64 bit.


Hum i use oracle 10 express edition(freeEd), u have run the example in sql panel in homepage of database(logged with a user that have all grant ) or run in sql comand line?


p.s: in detail my version is Application Express 2.1.0.00.39,
10.2.0.1 sqlplus 32 bit .
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440193 is a reply to message #440187] Thu, 21 January 2010 09:22 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Do the following query and post the errors regarding this environment.

select *
from user_errors


Bye Alessandro
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440199 is a reply to message #440193] Thu, 21 January 2010 09:36 Go to previous messageGo to next message
masterfrag
Messages: 8
Registered: January 2010
Junior Member
Ok this is the code:
CREATE OR REPLACE TYPE BODY  "MEDICO_TY" is
                MEMBER PROCEDURE AddVisita(
                       CF VARCHAR,
                       Data DATE,
                        Tipo VARCHAR,
                       Ticket number
               )
               is
                       Visita Visita_ty;
                      RefPaziente REF Paziente_ty;
                     rvisita ref visita_ty;
                      rself ref medico_ty;
                     IN_cf VARCHAR2(100) := cf;
             BEGIN
                      dbms_output.put_line('cf='||SELF.CF);
                     select treat(ref(d) as ref medico_ty)
                              into rself
                     from dipendente_tab d
                     where d.cf = self.cf;
                     SELECT REF(P)
                              INTO REFPAZIENTE
                     FROM Paziente_TAB P
                    WHERE P.CF=IN_CF;
                     VISITA := Visita_ty(Tipo,Data,Ticket,RefPaziente,rself);
                     INSERT INTO Visita_TAB v
                            values Visita
                              returning ref(v) into rvisita;
                    INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita);
                    self.FaVisita.extend;
                    self.FaVisita(self.FaVisita.last) := rvisita;
                    INSERT INTO TABLE (
                                     SELECT P.FaVisita
                                      FROM PAZIENTE_TAB P
                                     WHERE P.CF=IN_CF
                           ) values (rvisita);
       END;
   end;


select *
from user_errors

NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
MEDICO_TY TYPE BODY 1 28 33 PL/SQL: ORA-00903: not a valid table name ERROR 0
MEDICO_TY TYPE BODY 2 28 21 PL/SQL: SQL Statement ignored ERROR 0
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440202 is a reply to message #440199] Thu, 21 January 2010 10:06 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
There is a difference in line number 28 of the BODY:
28            INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita);
28          --INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita);

This line should be commented.
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440205 is a reply to message #440202] Thu, 21 January 2010 10:23 Go to previous messageGo to next message
masterfrag
Messages: 8
Registered: January 2010
Junior Member
_jum wrote on Thu, 21 January 2010 10:06
There is a difference in line number 28 of the BODY:
28            INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita);
28          --INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita);

This line should be commented.


Strange i have tryed with -- this morning and not work,now i retry on a new account with the -- and work great,seriously not understand where i do a mistake Very Happy (only think is paste/copy error ).
Anyway can u explain me some line:
- why this line have to be commented?: --INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita);

-self.FaVisita.extend; <- what is line doing?

Thx a lot for the help!.
Re: How to call a member procedure in a subtype/subclass (object relational oracle) [message #440304 is a reply to message #440205] Fri, 22 January 2010 05:11 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
masterfrag wrote on Thu, 21 January 2010 17:23

Strange i have tryed with -- this morning and not work,now i retry on a new account with the -- and work great,seriously not understand where i do a mistake Very Happy (only think is paste/copy error ).
Anyway can u explain me some line:
- why this line have to be commented?: --INSERT INTO TABLE (SELF.FaVisita) VALUES REF(Visita);

-self.FaVisita.extend; <- what is line doing?

Thx a lot for the help!.



Once the object is loaded into a table it can't be changed.

SQL and PL/SQL are two different things: SQL basically works with tables and is the only way you have to access them, PL/SQL is a normal procedural language and it works mostly with variables. The fact that you use SQL inside PL/SQL is just a facility that Oracle offers you to implement implicit cursors. There's no need to instantiate a new statement, to parse it, to bind variables values, to execute and fetch results, but all these things are performed anyway and you shouldn't think that they're the same thing.

In my opinion, but I didn't find explanations about it, you shouldn't be able to execute a SQL INSERT operation (a DML that produces transactional activity: redo logs and rollback segments) on a PL/SQL variable as self, and that's why it tells you "invalid table name".

The way you tried to use it should exclusively be used to add elements to nested tables allocated as columns of oracle tables (so objects stored in datafiles).

The object medico_ty.FaVisita is a PL/SQL collection, not an SQL nested table, and that's why one simple way to add elements to that collection is to extend it and then append the new element with PL/SQL instructions.

Hope it's clear.


Bye Alessandro
Previous Topic: How to laod excel data into Oracle Db
Next Topic: Type defined inside a type ?
Goto Forum:
  


Current Time: Sun Dec 04 08:25:03 CST 2016

Total time taken to generate the page: 0.09201 seconds