Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: User Defined Data Types

Re: User Defined Data Types

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sun, 05 Oct 2003 15:45:42 -0700
Message-ID: <1065393947.695894@yasure>


Daniel Morgan wrote:

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

Brilliant.

Nothing more need to said.

Thanks again.

-- 
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)
Received on Sun Oct 05 2003 - 17:45:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US