How to compare the two VARCHAR2 type parameters?

[sambavan]

Hi Folks

On the following code


PROCEDURE CongratPg(
p_pid IN NUMBER DEFAULT 0,
p_userName IN VARCHAR2 DEFAULT NULL,
p_passwd1 IN VARCHAR2 DEFAULT NULL,
p_passwd2 IN VARCHAR2 DEFAULT NULL,
p_submit IN VARCHAR2 DEFAULT '1')
IS
--
BEGIN

IF (p_userName = p_passwd1) THEN
v_errorMsg := 'Your user name and password cannot be the same.';
v_button := 'Submit';
ELSIF ( NVL(p_passwd1,NULL) <> NVL(p_passwd2,NULL)) THEN
v_errorMsg := 'Please re-enter your password';
v_button := 'Submit';
END IF;

END CongratPg;



The problem I'm having here is that I'm not able to generate the error messages. Instead i'm getting

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Could some one tell me how to do the comparison.

Thanx,
Sam

[carp]

Please include the declarative part of your procedure. I suspect the problem is actually with v_button or v_ErrorMsg.

[jolavarria]

I think you got a mistake in this part of the code:

( NVL(p_passwd1,NULL) <> NVL(p_passwd2,NULL)

1) if p_passwd1 and p_passwd2 are both NULL you do not change their value in order to compare then so you compare two NULL´s.

That´s a common mistake.
Always remember NULL is different from any other NULL.

2) If only one p_passwd1 or p_passwd2 are not null you can not compare a character with null, you should use <<character>> is not null or <<character>> is null.

So try changing that NULL values and see what happens.

[carp]

While I don't believe the NVL functions are causing the error that you are receiving, the code (as written) doesn't make much sense.
'NVL(p_passwd1,NULL)' translates to "If the value of p_passwd1 is NULL, use the value NULL".
Since this code does not provide any real function, I suspect what you really meant was:

( NVL(p_passwd1,'NULL') <> NVL(p_passwd2,'NULL')).

However, since you have not followed up on this issue, I also assume you've worked it out.