|
SQL>
SQL>
SQL> create or replace function f_validSSN_yn (i_ssn_tx VARCHAR2) return VARCHAR2
2 is
3 v_out_tx VARCHAR2(1);
4 v_temp_string_tx VARCHAR2(256);
5
6 begin
7 if i_ssn_tx is null then
8 v_out_tx:='N';
9 elsif length(i_ssn_tx)!=11 then
10 v_out_tx:='N';
11 else
12 v_temp_string_tx:=
13 translate(i_ssn_tx,'?-0123456789','?');
14 if v_temp_string_tx is not null
15 then
16 v_out_tx:='N';
17 else
18 if length(replace(i_ssn_tx,'-'))=9 then
19 v_out_tx:='Y';
20 else
21 v_out_tx:='N';
22 end if;
23 end if;
24 end if;
25
26 return v_out_tx;
27 end;
28 /
Function created.
SQL>
SQL> select f_validSSN_yn('12345678') from dual;
F_VALIDSSN_YN('12345678')
--------------------------
N
|