Re: AW: Object Types in PL/SQL

• Original Message ----- From: "Stefan Jahnke" <Stefan.Jahnke_at_bov.de>
  > That is what I want to do. The object type compiles, but if I use it,
  > PL/SQL tells me that it finds more than one constructors with that
  > signature, which are the "hidden" default constructor and the one I
  > implemented (I think). Now, that leaves me clueless, since it should be
  > possible (manual says so) to override the default constructor from 9.2 on.
  >
  > Any more ideas ?
  >
  

I think the problem is that both the default constructor and your custom one have the same "signature". Ie, they both have the same parameters, which is the way Oracle PL/SQL distinguishes between "overloaded" function and procedure calls.

What you need is to add a fake variable to your constructor so that it looks like this:

CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2, my_fake NUMBER)

and then ignore the darn thing (do nothing with it). That will make tVNR() constructor differ sufficiently from the default one so Oracle considers it an overload for the default. Don't forget that Oracle's default constructor is STILL available when you define your own. The only way it gets distinguished is by the parameter list (name is the same or else it ain't a constructor for the type!).

BTW this works also in 8i, it's not just 9i.

Cheers
Nuno Souto
nsouto_at_optusnet.com.au

> Von: rgaffuri_at_cox.net [mailto:rgaffuri_at_cox.net]
> Gesendet: Dienstag, 15. Juli 2003 14:44
> An: Multiple recipients of list ORACLE-L
> Betreff: Re: AW: Object Types in PL/SQL
>
>
> ok then i missed it. where is the overriding taking place? I saw a base
> constructor.. where was the 'override'?
> >
> > From: Stefan Jahnke <Stefan.Jahnke_at_bov.de>
> > Hi
> >
> > The anonnymous block at the bottom of my email is just a little test
> driver.
> > Basically, I don't use subtyping here. I just override the default
> > constructor.
> > If I don't implement a constructor at all, I would look like this:
> >
> > CREATE OR REPLACE TYPE tVNR AS OBJECT
> > (
> >
> > vVNR VARCHAR2(14),
> >
> > MEMBER FUNCTION getVNR
> > RETURN VARCHAR2
> >
> > ) INSTANTIABLE FINAL;
> >
> > CREATE OR REPLACE TYPE BODY tVNR AS
> >
> > MEMBER FUNCTION getVNR RETURN VARCHAR2 IS
> > BEGIN
> > RETURN SELF.vVNR;
> > END;
> > END;
> >
> > And I would get a hidden, system provided default constructor.
> > That would allow me to do the following (tested it):
> >
> > declare
> > vVNR tVNR;
> > begin
> > vVNR := new tVNR('12345678901');
> > dbms_output.put_line(vVNR.getVNR());
> > end;
> >
> > And I would get the output:
> >
> > 12345678901
> >
> > Unfortunately, that doesn't give me any control regarding the
> initialization
> > of vVNR.
> > Explanation: VNR = "Versichertennummer", which is kind of the Swiss analog
> > to the
> > US Social Security ID, only less unique ;).
> >
> > We don't use Object Relational features IN the database. Just plain
> > relational tables.
> > What we would like to use are Object Types to encapsulate certain things
> > like the above
> > shown VNR to ensure data integrity during data conversion for PL/SQL
> > programs using
> > these types. It's more of a guideline where the developers are constrained
> > to use the
> > types for certain stuff.
> >
> > Enjoy your day,
> >
> > Von: rgaffuri_at_cox.net [mailto:rgaffuri_at_cox.net]
> > Gesendet: Dienstag, 15. Juli 2003 14:05
> > An: Multiple recipients of list ORACLE-L
> > Betreff: Re: Object Types in PL/SQL
> >
> >
> > if im reading your code right... looks like your overriding in an
> anonymous
> > block. in most OO languages overriding is done by a child class. i dont
> see
> > any subclassing here? didnt they add sub-types and 'extends' to 9.2?
> >
> > btw, are you using object oriented design in your database? How efficient
> do
> > you find that?
> > >
> > > From: Stefan Jahnke <Stefan.Jahnke_at_bov.de>
> > > Date: 2003/07/15 Tue AM 06:49:25 EDT
> > > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > > Subject: Object Types in PL/SQL
> > >
> > > Hi list
> > >
> > > I have a problem regardint PL/SQL Object Types. According to the fine
> > > manual, it should be
> > > possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I
> > did
> > > that, Object Type
> > > compiles without complaints:
> > >
> > > CREATE OR REPLACE TYPE tVNR AS OBJECT
> > > (
> > >
> > > vVNR VARCHAR2(14),
> > >
> > > CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2)
> > > RETURN SELF AS RESULT,
> > >
> > > MEMBER FUNCTION getVNR
> > > RETURN VARCHAR2
> > >
> > > ) INSTANTIABLE FINAL;
> > >
> > > CREATE OR REPLACE TYPE BODY tVNR AS
> > >
> > > CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2)
> > > RETURN SELF AS RESULT IS
> > > BEGIN
> > > IF (LENGTH(piVNR)=11) THEN
> > > SELF.vVNR := SUBSTR(piVNR,1,4) || '.' || SUBSTR(piVNR,5,4) || '.'
> ||
> > > SUBSTR(piVNR,9,3);
> > > ELSE
> > > SELF.vVNR := 'invalid';
> > > END IF;
> > > RETURN;
> > > END;
> > >
> > > MEMBER FUNCTION getVNR RETURN VARCHAR2 IS
> > > BEGIN
> > > RETURN SELF.vVNR;
> > > END;
> > > END;
> > >
> > > Now, everytime I want to create an object like this:
> > >
> > > declare
> > > vVNR tVNR;
> > > begin
> > > vVNR := new tVNR('12345678901');
> > > dbms_output.put_line(vVNR.getVNR());
> > > end;
> > >
> > > I get the following error message:
> > >
> > > ERROR at line 4:
> > > ORA-06550: line 4, column 15:
> > > PLS-00307: too many declarations of 'TVNR' match this call
> > > ORA-06550: line 4, column 3:
> > > PL/SQL: Statement ignored
> > >
> > > Looks to me like the PL/SQL enginge isn't able to distinguish the
> default
> > > constructor from the
> > > overridden (my) version, since they have the same signature (of course).
Received on Tue Jul 15 2003 - 07:47:31 CDT