AW: Object Types in PL/SQL

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,

Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Tel.: +49 201/45 13-289
mailto:stefan.jahnke_at_nospam.bov.de
http://www.bov.de
Abonnieren Sie unseren Newsletter: http://www.bov.de/enews

Kosten senken - strategische IT-Ziele erreichen! BOV Microsoft Day am 24.07.03 in Essen. Anmeldung unter http://www.bov.de/microsoft-day oder mailto:andrea.palluck_at_bov.de.

Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen.

As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above.

-----Ursprüngliche Nachricht-----
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).
>
> Any input ? I couldn't find ANY descenct hints in the fine manual or the
> Feuerstein book :(.
>
> TIA,
> Stefan
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Stefan Jahnke
> INET: Stefan.Jahnke_at_bov.de
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L