Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    China
    Posts
    38

    Unanswered: Hint:Comparison with NULL in ?

    Hi all

    In a procedure there is a SQL statement like this:

    SELECT ColName INTO VarTest FROM TableName;
    --column "ColName" and variable "VarTest" have same data type:varchar
    IF VarTest IS NULL OR VarTest = '' THEN
    ...;
    END IF;

    Then,the procedure can be compiled, but it(PL/SQL Developer) hints me:"Comparison with NULL in",and after I change it like this:
    IF VarTest IS NULL THEN
    ...;
    END IF;

    it compiled sucessfully with nothing hint or error.
    Why?

    Thanks!
    Last edited by 884813; 06-01-04 at 22:48.
    Fan Yi

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This is because Oracle behaves in a non-standard way with the "empty string" (''). Instead of treating '' as a non-null string of length zero, Oracle treats it as a NULL - i.e. '' and NULL mean the same thing to Oracle.

    Therefore, the condition VarTest = '' is invalid, because it is equivalent to VarTest = NULL - and of course, nothing can be compared to NULL!

Posting Permissions

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