tags:

views:

238

answers:

5

Hi,

we have a table in an Oracle Database which contains a column with the type Char(3 Byte).
Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn't work:

OracleCommand command = null;  
OracleDataReader dataReader = null;  

string sql = "select * from TEST_TABLE where COLUMN_1 = :COLUMN_1";  

try  
{  
    OracleConnection connection = (OracleConnection) dbConnection;  
    command = new OracleCommand( sql, connection );  

    OracleParameter param_1 = new OracleParameter( "COLUMN_1", OracleDbType.Char );  
    command.Parameters.Add( param_1 );

    param_1.Value = DbNull.Value;

    dataReader = command.ExecuteReader( );

    int recordCount = 0;
    while( dataReader.Read( ) == true )
    {
        recordCount++;
    }

    Console.WriteLine( "Count = " + recordCount ); // is 0
}
[...]

Did i miss something? We definitly have some rows which contains a DBNull,
but the circumstance that you would write a 'normal' sql with "is null" and not "= null"
is also noticeable.

Can somebody explain this behaviour? How do you write a parameterized sql where you need a condition to check for a DBNull?

Thanks

+3  A: 

Null is the absence of being set to anything so you won't get the correct behavior with '= null'. Because null is the absence of value, it is not meaningful to say "This variable that lacks any value has the same value as this other variable that lacks any value." You can't have the same value of something else if you don't have a value.

One way to get around this is to create two sql statements, one that accepts the parameter and another with 'is null'. Then use an 'if' statement to choose which one to use.

statement 1:

string sql = "select * from TEST_TABLE where COLUMN_1 = :COLUMN_1

statement 2:

string sql = "select * from TEST_TABLE where COLUMN_1 is null

That is unless you're always comparing to null. Then, just use statement 2

David Oneill
When you do this, beware that Oracle does not index null values, potentially leading to inefficient query plans
erikkallen
+2  A: 

In this situation you have to use IS NULL:

string sql = "select * from TEST_TABLE where COLUMN_1 is null";

Performing any comparison with a null value in SQL will always result in an unknown result, which means you won't get any rows returned.

Phil Ross
+1  A: 

Modify your code like this:

OracleCommand command = null;  
OracleDataReader dataReader = null;  

string sql = "select * from TEST_TABLE where COLUMN_1 IS NULL"

try  
{  
    OracleConnection connection = (OracleConnection) dbConnection;  
    command = new OracleCommand( sql, connection );  

    dataReader = command.ExecuteReader( );

    int recordCount = 0;
    while( dataReader.Read( ) == true )
    {
        recordCount++;
    }

    Console.WriteLine( "Count = " + recordCount ); // is 0
}
Boris Modylevsky
+1  A: 

you can do this:

select *
from   TEST_TABLE
where  (COLUMN_1 = :COLUMN_1 and :COLUMN_1 Is Not Null) Or 
       (COLUMN_1 Is Null     and :COLUMN_1 Is Null)
David Aldridge
Use 'command.BindByName = true' to avoid binding 3 parameters with same value. http://stackoverflow.com/questions/1422032/oracle-ora-01008-not-all-variables-bound-error-w-parameters/1425702#1425702
Christian13467
A: 
SELECT t1.*
  FROM t1, (SELECT :s v FROM dual) tmp
 WHERE t1.s = tmp.v OR (t1.s IS NULL AND tmp.v IS NULL)

This would also do it.

Marius Burz