Re: User Defined Data Types

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 5 Oct 2003 17:19:45 GMT
Message-ID: <blpjrg$enh8f$1@ID-82536.news.uni-berlin.de>

> I have reviewed every book I have, tahiti (I'd rather wish it had been
> the island), asktom, etc. and can find not a single example of something
> so I'm tossing it out to the community at large.
>
> I can create user defined data types and attach methods ... well
> documented and quite simple.
>
> But what I can't seem to do is mimic the ability of Oracle's date data
> type to only accept dates and number data type (for example NUMBER(5,2))
> to constraint what it accepts. What I'd like is some way to create
> something like a social security number data type that would only accept
> entiries in the format 999-99-9999 without writing an independent
> constraint.
>
> Any thoughts? Any examples? Thanks.

I am not sure what you want exactly. But the following might be in the direction you're after:

create or replace type social_security_number as object (

  n_ char(11),

  constructor function social_security_number(n in varchar2)     return self as result,

  member function get_ return char
);
/

create or replace type body social_security_number as   

  constructor function social_security_number(n in varchar2)     return self as result is
  begin
    if length(n) <> 11 then
      raise_application_error(-20001,'Invalid social security number');     end if;

    if substr(n, 1,1) < '0' or substr(n, 1,1) > '9' or
       substr(n, 2,1) < '0' or substr(n, 2,1) > '9' or
       substr(n, 3,1) < '0' or substr(n, 3,1) > '9' or
       substr(n, 5,1) < '0' or substr(n, 5,1) > '9' or
       substr(n, 6,1) < '0' or substr(n, 6,1) > '9' or
       substr(n, 8,1) < '0' or substr(n, 8,1) > '9' or
       substr(n, 9,1) < '0' or substr(n, 9,1) > '9' or
       substr(n,10,1) < '0' or substr(n,10,1) > '9' or
       substr(n,11,1) < '0' or substr(n,11,1) > '9' or
       substr(n, 4,1)            <> '-'             or
       substr(n, 7,1)            <> '-'            
     then
       raise_application_error(-20001,'Invalid social security number');

    end if;

    n_ := n;
    return;
  end;

  member function get_ return char is
  begin
    return n_;
  end;

end;
/  

create table s_ (
  name varchar2(50),
  ssn social_security_number
);   

declare
  ssn social_security_number;
begin   

  ssn := social_security_number(n=>'123-45-6789');   insert into s_ values ('Robinson Crusoe', ssn);

  ssn := social_security_number(n=>'987-65-4321');   insert into s_ values ('Capt''n Cook', ssn);

  ssn := social_security_number(n=>'00-000-0000');   insert into s_ values ('Mr. Maryland', ssn);

end;
/

select s.name, s.ssn.get_() from s_ s;

hth

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Sun Oct 05 2003 - 12:19:45 CDT