Your application is not written for relational databases, there NULL is never equal NULL, nor is it NOT equal NULL....


but, read
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:7555175291763 <code>
and use the CBO

ops$tkyte@ORA920> set null *null*
ops$tkyte@ORA920> select x, y, nullif( x, y )
  2    from t;

         X          Y NULLIF(X,Y)
---------- ---------- -----------
         1          1 *null*             <<<=== the same
         1          2           1        
         1 *null*               1
*null*              2 *null*             <<<=== different
*null*     *null*     *null*             <<<=== the same

I don't think it works well when expr1 is NULL
 

nullif returns null if the first parameter is null.

*null* 2 *null* <<<=== different
*null* *null* *null* <<<=== the same

you want to full scan all of the time and never use an index?

maybe if you used a function based index on nvl(c1...)

ascii 0 is just that -- ascii 0

it is not a blank (that would be ascii 32).
it is not null (that would be NULL)


ops$tkyte@ORA920> create table t ( msg varchar2(10), x varchar2(1) );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 'chr(0)', chr(0) );

1 row created.

ops$tkyte@ORA920> insert into t values ( 'null', NULL );

1 row created.

ops$tkyte@ORA920> insert into t values ( 'chr(32)', chr(32) );

1 row created.

ops$tkyte@ORA920> insert into t values ( ''' ''', ' ' );

1 row created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> column dumpx format a15
ops$tkyte@ORA920> select msg, dump(x) dumpx, x from t;

MSG        DUMPX           X
---------- --------------- -
chr(0)     Typ=1 Len=1: 0
null       NULL
chr(32)    Typ=1 Len=1: 32
' '        Typ=1 Len=1: 32




someone/program inserted ascii 0 into your table, that is all.  it is not blank, it is not null, it is chr(0) 

chr(0) is the "c" null terminator.

it can appear in the terminal name.

if it does, it could tell a c print program to "stop printing" anymore

he is just removing it.

- it's undocumented, and therefore poses a risk of going away or changing
functionality


that is enough said that should make people just "stop reading"

go there and you might be really mad in the next release.


the only CORRECT way is:

where (a = b or (a is null and b is null))


period. that is my thoughts on it. I would not consider using sys_op_map_nonnull, ignore that man behind the curtain.

the day it appears in the sql reference guide -- go for it (in all releases).

For exmaple, i never recommended alter session set current_schema UNTIL it was documented in 8i. then I said "use it all over the place"

that aside, you don't need dump to do that compare:

ops$tkyte@ORA9IR2> select a, b, decode( a, b, 'Equal', 'Not Equal' )
  2    from t;
 
         A          B DECODE(A,
---------- ---------- ---------
         1          1 Equal
         1          2 Not Equal
         1            Not Equal
                    2 Not Equal
                      Equal


decode itself will do that.  not in PLSQL tho...

In plsql, just write an "is_equal" function that does the right thing 

providing you are 10000% sure that '!' will *never* appear (which almost always turns out to be a false assumption at some point in time)


it is OK. I have no problems with

where (a = b or (a is null and b is null))

though (it really isn't that much more typing)


one advantage that nvl() may have is you can index it and search using the index.

I think its worth the tiny bit of typing it takes.

declare
nullstr1 varchar2(20);
nullstr2 varchar2(20);
notnullstr1 varchar2(20) := 'xxxxxxxxxxx';
notnullstr2 varchar2(20) := 'xxxxxxxxxxx';
notnullstr3 varchar2(20) := 'xxxxxxxxxxxy';
notnullnum1 number := 1;
notnullnum2 number := 1;
notnullnum3 number := 2;
begin
for i in 1 .. 500000
loop
if ( nullstr1 = nullstr2 or (nullstr1 is null and nullstr2 is null) ) then null; end if;
if ( notnullstr1 = notnullstr2 or (notnullstr1 is null and notnullstr2 is null) ) then null; end if;
if ( notnullstr1 = notnullstr3 or (notnullstr1 is null and notnullstr3 is null) ) then null; end if;
if ( notnullnum1 = notnullnum2 or (notnullnum1 is null and notnullnum2 is null) ) then null; end if;
if ( notnullnum1 = notnullnum3 or (notnullnum1 is null and notnullnum3 is null) ) then null; end if;
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.66 0.66 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.67 0.67 0 0 0 1
********************************************************************************
declare
nullstr1 varchar2(20);
nullstr2 varchar2(20);
notnullstr1 varchar2(20) := 'xxxxxxxxxxx';
notnullstr2 varchar2(20) := 'xxxxxxxxxxx';
notnullstr3 varchar2(20) := 'xxxxxxxxxxxy';
notnullnum1 number := 1;
notnullnum2 number := 1;
notnullnum3 number := 2;
begin
for i in 1 .. 500000
loop
if ( nullstr1||'!' = nullstr2||'!' ) then null; end if;
if ( notnullstr1||'!' = notnullstr2||'!' ) then null; end if;
if ( notnullstr1||'!' = notnullstr3||'!' ) then null; end if;
if ( notnullnum1||'!' = notnullnum2||'!' ) then null; end if;
if ( notnullnum1||'!' = notnullnum3||'!' ) then null; end if;
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 2.81 2.75 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.82 2.77 0 0 0 1
********************************************************************************
declare
nullstr1 varchar2(20);
nullstr2 varchar2(20);
notnullstr1 varchar2(20) := 'xxxxxxxxxxx';
notnullstr2 varchar2(20) := 'xxxxxxxxxxx';
notnullstr3 varchar2(20) := 'xxxxxxxxxxxy';
notnullnum1 number := 1;
notnullnum2 number := 1;
notnullnum3 number := 2;
begin
for i in 1 .. 500000
loop
if nvl(nullstr1,'!') = nvl( nullstr2,'!' ) then null; end if;
if nvl(notnullstr1,'!') = nvl( notnullstr2,'!' ) then null; end if;
if nvl(notnullstr1,'!') = nvl( notnullstr3,'!' ) then null; end if;
if nvl(notnullnum1,0) = nvl( notnullnum2,0 ) then null; end if;
if nvl(notnullnum1,0) = nvl( notnullnum3,0 ) then null; end if;
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.09 0 0 0 0
Execute 1 7.06 6.93 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.07 7.03 0 0 0 1

i know

i tested

a) my way
b) your way
c) their way (see above, the NVL() ideas)

just to give an overview of "the ways" and their respective performance.

decode( l_old, l_new, 1, 0 )

too... in SQL...

all enhancement requests -- go through metalink.

Not that I'm aware of.

that is OK - that they use it internally.

for they are free to let "bugs" live if they are not affected by them...

for they are free to change drastically the behaviour of the function, because they own it, it is theirs.


It is not RISKY to employ an internal, undocumented function. They "own" it and can it do whatever they need it to do. A patch set could radically change the way it functions - in a way that doesn't affect them, but does affect you.

Caveat Emptor.

It is not "known" if emplid <> NULL

tri valued logic


ops$tkyte%ORA10GR2> select * from dual where null = null;

no rows selected

ops$tkyte%ORA10GR2> select * from dual where null <> null;

no rows selected

ops$tkyte%ORA10GR2> select * from dual where null is null;

D
-
X



"where emplid <> 123 OR emplid is null" 

ops$tkyte%ORA10GR2> select * from dual where '' = '';

no rows selected

ops$tkyte%ORA10GR2> select * from dual where '' is null;

D
-
X

zero length strings are null

did you actually type that in and test it before you posted?

or are you guessing it would probably work?

I know what the answer to my question is.... Now... check your work :)

In short, give a full example - demonstrating that what you say is probably true - sort of like I do all of the time....

no, I mean, show us in plsql please.

show us this actually working:

If (updating and decode(:old.field,:new.field,1,0)=0) Then
 (  Insert into auditing_tables )
End If;

you cannot

ops$tkyte%ORA11GR2> exec dbms_output.put_line( decode (NULL, NULL,'NULL equiv2 NULL in Decode', 'NULL not equiv2 NULL in Decode') ); 
BEGIN dbms_output.put_line( decode (NULL, NULL,'NULL equiv2 NULL in Decode', 'NULL not equiv2 NULL in Decode') ); END;

                            *
ERROR at line 1:
ORA-06550: line 1, column 29:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

that was my point directly above. I'm well aware that decode treats null=null - but it doesn't help us here.

sysdate/systimestamp are statement level deterministic.

they are deterministic within that single sql statement - they'll always return the same value in an execution of that single sql statement.

Well, if you are (planning on) using SYS_OP_MAP_NONNULL, then we are talking about using in SQL (not PLSQL).

So perhaps maybe be safe than sorry, use decode - its even more succinct.

SQL> with t as (
  2    select 1 x,    1    y from dual union all
  3    select 1 x,    2    y from dual union all
  4    select 1 x,    null y from dual union all
  5    select null x, null y from dual )
  6  select x,y,decode(x,y,'Y') pseudo_equal from t;

         X          Y P
---------- ---------- -
         1          1 Y
         1          2
         1
                      Y

But yes, I would like to see SYS_OP_MAP_NONNULL supported and in the documentation as well.

Nice to know - thanks for posting this.