-
Trina --
Read my post again... if you use 'password' your are not applying regexp_instr() against the variable password but against the string 'password'.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi Hrishy,
I changed it, reran the function, tested it. It is still would not work.
SQL> CREATE OR REPLACE FUNCTION trina_verify_function
2 (username varchar2,
3 password varchar2,
4 old_password varchar2)
5 RETURN boolean IS
6 n boolean;
7 m integer;
8 differ integer;
9 isdigit boolean;
10 ischar boolean;
11 ispunct boolean;
12 isupper boolean;
13 digitarray varchar2(20);
14 punctarray varchar2(25);
15 chararray varchar2(52);
16 regular_expr varchar2(50);
17
18 BEGIN
19 digitarray:= '0123456789';
20 chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
21 punctarray:='!"#$%&()``*+,-/:;<=>?_';
22 regular_expr:= '[A-Z]';
23 if regexp_instr(password,regular_expr) > 0 THEN
24 dbms_output.put_line('it has one upper case letter');
25 end if;
26
27 -- if everything is fine retrun true.
28 RETURN(true);
29 END;
30 /
Function created.
SQL> alter user trina identified by trina_7777777777;
User altered.
SQL> alter user trina identified by trina_B77777777;
User altered.
-
regexp-instr() function works fine...
Code:
SQL>
SQL> select regexp_instr('trina_7777777777','[A-Z]') from dual;
REGEXP_INSTR('TRINA_7777777777','[A-Z]')
---------------------------------------------------------------
0
SQL>
SQL> select regexp_instr('trina_B777777777','[A-Z]') from dual;
REGEXP_INSTR('TRINA_B777777777','[A-Z]')
---------------------------------------------------------------
7
SQL>
Why are you not raising application error anymore?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi
Show us the output of
select trina_verify_function('TRINA','trina_55555555555','trina')
from dual
and
select trina_verify_function('TRINA','trinA_55555555555','trina')
from dual
-
Hi Hrishy,
Below are the output on my side.
SQL> select trina_verify_function('TRINA','trina_5555555555', 'trina')
2 from dual;
select trina_verify_function('TRINA','trina_5555555555', 'trina')
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
SQL> select trina_verify_function('TRINA','trinA_5555555555', 'trina')
2 from dual;
select trina_verify_function('TRINA','trinA_5555555555', 'trina')
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
-
Hi
Your function will always return true as you have the return in the wrong place.
Try this
Code:
CREATE OR REPLACE FUNCTION trina_verify_function
(username varchar2,
password varchar2,
old_password varchar2)
RETURN boolean IS
n boolean;
m integer;
differ integer;
isdigit boolean;
ischar boolean;
ispunct boolean;
isupper boolean;
digitarray varchar2(20);
punctarray varchar2(25);
chararray varchar2(52);
regular_expr varchar2(50);
BEGIN
digitarray:= '0123456789';
chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
punctarray:='!"#$%&()``*+,-/:;<=>?_';
regular_expr:= '[A-Z]';
if regexp_instr(password,regular_expr) > 0 THEN
--dbms_output.put_line('it has one upper case letter');
RETURN TRUE ;
end if;
-- if everything is not fine retrun False
RETURN FALSE;
END;
/
Now for the unit tests
Code:
begin
if trina_verify_function('TRINA','trina_55555555555','trina') then
dbms_output.put_line('True');
else
dbms_output.put_line('False');
end if ;
end ;
/
begin
if trina_verify_function('TRINA','trinA_5555555555', 'trina')
then
dbms_output.put_line('True');
else
dbms_output.put_line('False');
end if ;
end ;
/
-
Hi Hrishy,
I applied your code and tried to retest. I did the select and it gave me error. Then, I tried to test out changing the password with and without upper case, it will give me the error.
SQL> CREATE OR REPLACE FUNCTION trina_verify_function
2 (username varchar2,
3 password varchar2,
4 old_password varchar2)
5 RETURN boolean IS
6 n boolean;
7 m integer;
8 differ integer;
9 isdigit boolean;
10 ischar boolean;
11 ispunct boolean;
12 isupper boolean;
13 digitarray varchar2(20);
14 punctarray varchar2(25);
15 chararray varchar2(52);
16 regular_expr varchar2(50);
17
18 BEGIN
19 digitarray:= '0123456789';
20 chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
21 punctarray:='!"#$%&()``*+,-/:;<=>?_';
22 regular_expr:= '[A-Z]';
23 if regexp_instr('password','regular_expr') > 0 THEN
24 -- raise_application_error(-20006, 'Password must contain at least one upper case letter.');
25 return true;
26 end if;
27 -- if everything is fine return false.
28 RETURN (false);
29 END;
30 /
Function created.
SQL> show errors
No errors.
SQL> begin
2 if trina_verify_function('TRINA','trina_5555555555','trina') then
3 dbms_output.put_line('True');
4 else
5 dbms_output.put_line('False');
6 end if;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> begin
2 if trina_verify_function('TRINA','trinA_5555555555','trina') then
3 dbms_output.put_line('True');
4 else
5 dbms_output.put_line('False');
6 end if;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select trina_verify_function('TRINA','trinA_5555555555','trina') from dual;
select trina_verify_function('TRINA','trinA_5555555555','trina') from dual
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
SQL> alter user trina identified by trina_33333333333333;
alter user trina identified by trina_33333333333333
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-28003: password verification for the specified password failed
SQL> alter user trina identified by trina_333B333333;
alter user trina identified by trina_333B333333
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-28003: password verification for the specified password failed
-
Hi
The way to test the function is
Code:
set serveroutput on
begin
if trina_verify_function('TRINA','trina_55555555555','trina') then
dbms_output.put_line('True');
else
dbms_output.put_line('False');
end if ;
end ;
/
begin
if trina_verify_function('TRINA','trinA_5555555555', 'trina')
then
dbms_output.put_line('True');
else
dbms_output.put_line('False');
end if ;
end ;
/
and not by running
select trina_verify_function('TRINA','trinA_5555555555', 'trina')
from dual
23 if regexp_instr('password','regular_expr') > 0 THEN
is wrong it shoud read instead
if regexp_instr(password,regular_expr) > 0 THEN
Last edited by hrishy; 10-20-2009 at 10:39 AM.
-
Hi Hrishy,
Either way, it is still shwoing false.
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL> begin
2 if trina_verify_function('TRINA','trina_5555555555','trina') then
3 dbms_output.put_line('True');
4 else
5 dbms_output.put_line('False');
6 end if;
7 end;
8 /
False
PL/SQL procedure successfully completed.
SQL> begin
2 if trina_verify_function('TRINA','trinA_5555555555','trina') then
3 dbms_output.put_line('True');
4 else
5 dbms_output.put_line('False');
6 end if;
7 end;
8 /
False
PL/SQL procedure successfully completed.
-
Hi Hrishy,
I modified the code to revmove the quote out and tested it again. It worked. However, my question is how do you test when I change the password. It does not error out the second one.
SQL> alter user trina identified by trinA_5555555555;
User altered.
SQL> alter user trina identified by trina_7777788888;
User altered.
====
23 if regexp_instr(password,regular_expr) > 0 THEN
======
SQL> Rem utlpwdmg.sql
SQL>
SQL> CREATE OR REPLACE FUNCTION trina_verify_function
2 (username varchar2,
3 password varchar2,
4 old_password varchar2)
5 RETURN boolean IS
6 n boolean;
7 m integer;
8 differ integer;
9 isdigit boolean;
10 ischar boolean;
11 ispunct boolean;
12 isupper boolean;
13 digitarray varchar2(20);
14 punctarray varchar2(25);
15 chararray varchar2(52);
16 regular_expr varchar2(50);
17
18 BEGIN
19 digitarray:= '0123456789';
20 chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
21 punctarray:='!"#$%&()``*+,-/:;<=>?_';
22 regular_expr:= '[A-Z]';
23 if regexp_instr(password,regular_expr) > 0 THEN
24 -- raise_application_error(-20006, 'Password must contain at least one upper case letter.');
25 return true;
26 end if;
27 -- if everything is fine return false.
28 RETURN (false);
29 END;
30 /
Function created.
SQL> l
1 begin
2 if trina_verify_function('TRINA','trina_5555555555','trina') then
3 dbms_output.put_line('True');
4 else
5 dbms_output.put_line('False');
6 end if;
7* end;
SQL> /
False
PL/SQL procedure successfully completed.
SQL> begin
2 if trina_verify_function('TRINA','trinA_5555555555','trina') then
3 dbms_output.put_line('True');
4 else
5 dbms_output.put_line('False');
6 end if;
7 end;
8 /
True
PL/SQL procedure successfully completed.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|