René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Validating social security numbers with PL/SQL

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;