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() ;

Andrew Hare · Accepted Answer · 2009-07-22 15:21:44Z

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, 2009 at 15:21

Andrew Hare

353k75 gold badges649 silver badges642 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dr. Cogent Over a year ago

The answer from Andrew Hare is bogus like Dan U. posted. It's System.DBNull.Value

MensSana Over a year ago

Didn't work for me, not at all in fact... here is how I check it : Command.Parameters["x"].Value.Equals(OracleDecimal.Null)

Kamran Qadir · Accepted Answer · 2014-04-01 06:11:29Z

8

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

answered Apr 1, 2014 at 6:11

Kamran Qadir

47611 silver badges20 bronze badges

Comments

Thor · Accepted Answer · 2016-12-02 12:54:11Z

2

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

answered Dec 2, 2016 at 12:54

Thor

5081 gold badge6 silver badges15 bronze badges

Comments

drumsk · Accepted Answer · 2017-06-21 11:48:59Z

2

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 );

answered Jun 21, 2017 at 11:48

drumsk

416 bronze badges

Comments

bonh · Accepted Answer · 2015-03-12 15:18:15Z

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, 2015 at 15:18

bonh

2,9762 gold badges35 silver badges38 bronze badges

Comments

Jeremy Caney · Accepted Answer · 2024-03-15 00:59:04Z

0

if(((Oracle.DataAccess.Types.OracleString)comm.Parameters["P_MSG"].Value).IsNull)
{ // is null }
else { // is not null }

edited Mar 15, 2024 at 0:59

Jeremy Caney

7,808115 gold badges58 silver badges86 bronze badges

answered Mar 14, 2024 at 14:03

Sergio Ricardo Silva

1

1 Comment

Jeremy Caney Over a year ago

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

ParPar · Accepted Answer · 2014-02-12 09:55:45Z

-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, 2014 at 9:55

ParPar

7,5917 gold badges46 silver badges56 bronze badges

Collectives™ on Stack Overflow

Handling null values in OracleCommand parameters

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related