-
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
-
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
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
-
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
-
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]
-
Yes. Absolutely true.
- Chris
-
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?
-
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
-
Thanks for corrections.
Bottom line... (from my original post on this thread)
FIELD||'$$$' = '$$$' if checking for a null
FIELD||'$$$' != '$$$' if checking for -not- 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|