Re: User Defined Data Types

Rene Nyffenegger wrote:

>>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
>
>
>

Very close but a far more difficult a validation than I normally use ... I'll try to duplicate what you did with the following:

IF TRANSLATE(n, 'A0123456789', 'BAAAAAAAAAA') = 'AAA-AA-AAAA' THEN

   RETURN;
ELSE
   RAISE_APPLICATION_ERROR(-20001,'Invalid social security number'); END IF; And if my guess is correct ... you'll never SUBSTRing a social security or telephon number again to verify its format.

Thanks.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)