How to compare the two VARCHAR2 type parameters?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to compare the two VARCHAR2 type parameters?

  1. #1
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925

    Question

    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

  2. #2
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    Please include the declarative part of your procedure. I suspect the problem is actually with v_button or v_ErrorMsg.

  3. #3
    Join Date
    Oct 2000
    Posts
    13
    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.

  4. #4
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width