Handling null values in OracleCommand parameters

Question

I'm trying to determine how to specify null as a parameter value in an OracleCommand using the following C# code. I've excerpted the relevant bits, but basically the point is if sal_id comes in with the value 0 it should be stored as null. I've tried Null, "Null", and a couple other things but so far no luck.

cmd.CommandText = "INSERT INTO tcustomer(cust_id, salutation_id) VALUES(ORADBA.SEQCUST.NEXTVAL, :salid) RETURNING cust_id INTO :newcid" ;

if (sal_id==0) {
  cmd.Parameters.Add("salid", Null) ;
} else {
  cmd.Parameters.Add("salid", sal_id) ;
}

cmd.Parameters.Add("newcid", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue ;

cmd.ExecuteNonQuery() ;

String newcidval = cmd.Parameters["newcid"].Value.ToString() ;
cmd.Dispose() ;

score 19 · Accepted Answer · answered Jul 22 '09 at 15:21

19

Try System.DBNull insead of null.

The DBNull class represents a nonexistent value. In a database, for example, a column in a row of a table might not contain any data whatsoever. That is, the column is considered to not exist at all instead of merely not having a value. A DBNull object represents the nonexistent column

answered Jul 22 '09 at 15:21

Andrew Hare

344,730
71
640
635

2

The answer from Andrew Hare is bogus like Dan U. posted. It's System.DBNull.Value – Dr. Cogent Feb 24 '16 at 18:44
Didn't work for me, not at all in fact... here is how I check it : `Command.Parameters["x"].Value.Equals(OracleDecimal.Null)` – MensSana Nov 14 '22 at 20:46

score 8 · Answer 2 · answered Apr 01 '14 at 06:11

DBNull can not be assigned to any other data type so have to implicit convert it to object.

cmd.Parameters.Add(new OracleParameter(":number_column", OracleType.Number)).Value = (s.Class.HasValue) ? s.Class.Value : (object)DBNull.Value;

where s.Class is int? and parameter value is set as (object)DBNull.Value in case it is null

score 2 · Answer 3 · answered Dec 02 '16 at 12:54

DBNull.Value works for some but I needed to cast it to a Oracle Type that implemented INullable. I recommend this approach if you are using a Nullable data type:

    (Oracle.ManagedDataAccess.Types.OracleDecimal)command.Parameters["pKey"].Value)).IsNull

score 2 · Answer 4 · answered Jun 21 '17 at 11:48

you can check Status of your OracleParameter object - if it equals to OracleParameterStatus.NullFetched, nothing was fetched, otherwise use param's Value attribute.

Example:

var _myParam = new OracleParameter( "p_param", OracleDbType.Object, ParameterDirection.Output ) { UdtTypeName = "my_schema.MY_TYPE" };

... (add command parameter)
... (execute command)

var _myResponse = _myParam.Status == OracleParameterStatus.NullFetched ? null : MapMyTypeFromOracleType( (MyType)_myParam.Value );

score 0 · Answer 5 · answered Mar 12 '15 at 15:18

0

You could add an empty parameter (it will be null to start), and only set the value if sal_id != 0:

var p = cmd.Parameters.Add("salid", OracleDbType.Int32);
if (sal_id != 0) {
    p.Value = sal_id;
}

answered Mar 12 '15 at 15:18

bonh

2,863
2
33
37

score -2 · Answer 6 · answered Feb 12 '14 at 09:55

-2

For me DBNull.Value didn't work, so I did it like this:

cmd.Parameters.Add("salid", "Null") ;

and it worked great.

answered Feb 12 '14 at 09:55

ParPar

7,355
7
43
56

Handling null values in OracleCommand parameters

6 Answers6