DanBrother的部落格

Oracle Password Policy Checking PL/SQL Function Using REGEXP_COUNT

create or replace function pwd_policy_check_fnc(pwd varchar2)
return pls_integer
is
v_ct smallint default null;
begin
v_ct:=REGEXP_COUNT(pwd, '^[a-zA-Z][a-zA-Z0-9]{7,}$');  -- Password with more than 7 Alphanumeric characters without special ones and start with alphabet
return(nvl(v_ct,0));
end;
/

col "Checking Results:" format a40
select 'Null Value => '||pwd_policy_check_fnc('') as "Checking Results:" from dual
union all
select 'All with numbers => '||pwd_policy_check_fnc('12345678') as result from dual
union all
select 'Starting with number => '||pwd_policy_check_fnc('1234abcd') from dual
union all
select 'Containing special characters => '||pwd_policy_check_fnc('#1234Ab%') from dual
union all
select 'Containing special characters => '||pwd_policy_check_fnc('Apple123!') from dual
union all
select 'Less than 8 characters => '||pwd_policy_check_fnc('apple12') from dual
union all
select 'Less than 8 characters => '||pwd_policy_check_fnc('orange3') from dual
union all
select 'Constaining space => '||pwd_policy_check_fnc('Apple 123') from dual
union all
select 'Constaining space => '||pwd_policy_check_fnc('Apple123 ') from dual
union all
select 'Matching password rule => '||pwd_policy_check_fnc('apple1234') from dual;

>>
  
Checking Results:
----------------------------------------
Null Value => 0
All with numbers => 0
Starting with number => 0
Containing special characters => 0
Containing special characters => 0
Less than 8 characters => 0
Less than 8 characters => 0
Constaining space => 0
Constaining space => 0
Matching password rule => 1