does oracle recognize null as a value ???
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: does oracle recognize null as a value ???

  1. #1
    Join Date
    Sep 2000
    Posts
    7

    Exclamation

    i have tried looking thru documentation but have not found anything...i know that null is seen as an unknown value and you can specify null in the where clause when creating a select in sql...but does oracle recognize null as a value ? i believe it does because you can use it in the where clause but i am not sure...if anyone has any thoughts i would appreciate it...thanx


  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587

    Post

    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Here some stuff I've learned. Null "values" are treated differently in SQL and PL/SQL.

    "where COLUMN is null" works in SQL and SQL within a PL/SQL block.

    "if :value is null" in a PL/SQL block is not a predictable evaluation. (at least I haven't been able to produce wide scale predictable results - and I've been bitten a time or two)

    Here's something that ALWAYS works in SQL and PL/SQL:

    FIELD||'$$$' = '$$$' if checking for a null
    FIELD||'$$$' != '$$$' if checking for -not- null

    Sidelines .. "is null" and "is not null" will not pick up an index, "FIELD||'$$$' = '$$$' won't either. On the other hand, it's easier to type "is null" .. just don't use it in PL/SQL.

    -Ken

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by KenEwald
    "if :value is null" in a PL/SQL block is not a predictable evaluation. (at least I haven't been able to produce wide scale predictable results - and I've been bitten a time or two)
    Odd. I've never seen or heard of any issues with this and I use it all the time.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    It's the use of "="... sorry, not "is null"

    Try this:
    -----------
    declare
    myval_1 varchar2(10) := null;
    myval_2 varchar2(10) := null;
    begin
    if :myval_1 = :myval_2 then
    dbms_output.put_line('null equals null');
    else
    dbms_output.put_line('null does not equal null?');
    end if;
    end;
    -----------

    Returns "null does not equal null?"

    You cannot compare values that may contain a NULL value using the equal sign ( "=" ) equality operator or not equal ("<>", "!=") inequality operator.

    -Ken

    [Edited by KenEwald on 03-20-2002 at 11:48 AM]
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yes. Absolutely true.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    However this behavior is not unique to PL/SQL, it is exactly the same in SQL. It's an universal rule (well, with some exceptions as allways ): NULL is never equal (=) to NULL, nor it is ever not equal (!=) to NULL.

    So I still can't see where your statement "Null values are treated differently in SQL and PL/SQL" could prove to be true.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    And to take it the final step, any logical operator with NULL will always return false. And this behaviour is true, as Jurij points out, in all languages that handle 3-valued logic.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Thanks for corrections.

    Bottom line... (from my original post on this thread)

    FIELD||'$$$' = '$$$' if checking for a null
    FIELD||'$$$' != '$$$' if checking for -not- null
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

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