oracle how to pass null from .net

Question

I have a parameter in my oracle function that i want to pass null to it,

what i did is:

cmd.Parameters.AddWithValue("al_acc_br", DBNull.Value);

however, i am getting error that the number of parameters or the type is not correct and as you know we can't see the actual query from .net library

the number is absolutely correct, but in my way to debug the issue i would like to ask you if i am passing the null correctly

thanks

Update

some code

using (OracleConnection cn = new OracleConnection(OracleConnString(oracleHost, oraclePort, oracleServiceName, oracleUsername, oraclePassword)))
            {
                OracleDataAdapter oda = new OracleDataAdapter();
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = cn;
                cmd.CommandText = oracleSchema + "." + oracleFunction;
                cmd.CommandType = CommandType.StoredProcedure;
                fillParameters(cmd);
                oda.SelectCommand = cmd;

i know that i am using a deprecated library but it is fine for us

Update 2

the parameter in my oracle function has these types:

Varchar(8), Varchar(40), Numeric(4), Numeric(6), Varchar(35), Numeric(3), Date, Numeric(20,6), Datetime

and what i do is:

for all varchar(x) i insert them from c# as string for all numeric(x) i insert them from c# as integer for all numeric(x,y) i insert them from c# as double for all Date and Datetime i insert them like this:

cmd.Parameters.AddWithValue("as_date_time", DateTime.Now.ToString("dd/MMM/yyyy"));//'18/MAY/2015'

am i wrong please?

What is the complete command ? and how have you defined the parameters in the actual command text — Habib, Aug 31 '15 at 19:39
And please provide the full stack trace of the error as well. — sstan, Aug 31 '15 at 19:41
According to [this answer](http://stackoverflow.com/a/2359370/1189566) DBNull.Value should be perfectly fine. Leads me to believe @Habib might be on to something with your command object or the procedure itself. — sab669, Aug 31 '15 at 19:43
@sstan you don't need that, because i just need to know if null is correct passed or not — Marco Dinatsoli, Aug 31 '15 at 19:44
@MarcoDinatsoli, and how are the parameters defined in the proc, can you just paste that particular part of the code ? looks like a minor typo..., as `DBNull.Value` should work for null values — Habib, Aug 31 '15 at 19:45
`Google works wonders if used properly` try this link http://stackoverflow.com/questions/12444115/passing-dbnull-value-and-empty-textbox-value-to-database — MethodMan, Aug 31 '15 at 19:47
@bvj that is what i am planing to do if i couldn't solve the problem now — Marco Dinatsoli, Aug 31 '15 at 19:50
@MarcoDinatsoli I'm guessing the parameter type cannot be determined by specifying Null, so consequently the `AddWithValue()` call fails. — bvj, Aug 31 '15 at 19:52
@bvj i had the same feeling like you, but then i copied the number of parameter (during the run time) and it was exactly the same even though i have more than 20 null parameters. However, do you suggest adding the null values using `Add(string, DBNull.value)` ? — Marco Dinatsoli, Aug 31 '15 at 19:55
AddWithValue in theory should not fail since it's up to the database at that point to determine the datatype of the parameter value being passed in.. if the OP would just paste the full function signature so that we can see the params and the associated datatype then it would help a great deal.. also is there a parameter really named `as_date_time` if so then you need to pass that parameter as datetime by casting it as well — MethodMan, Aug 31 '15 at 19:55
@MarcoDinatsoli My last comment to avoid the "discussion" warning. Just adding the parameter without specifying a value likely defaults the parameter to Null. You will need to specify the type however. BTW, DBNull.value is not a type. Instead, try specifying something like `Add("al_acc_br", OracleType.DateTime)`. — bvj, Aug 31 '15 at 19:56
@MethodMan as i show you in the question, i am adding the datetime as a string (but good formatted) do you think that is wrong and i show add it as an object? — Marco Dinatsoli, Aug 31 '15 at 19:58
hey guys, if you all want the all definition of my oracle function tell me please. i can add it but i have to change the actual name because it is for a client — Marco Dinatsoli, Aug 31 '15 at 19:59

Nicolas C · Answer 1 · 2015-08-31T20:17:59.497

1

If a database column is a date, you cannot store a string and don't need to take care about any string.format.

Replace cmd.Parameters.AddWithValue("as_date_time", DateTime.Now.ToString("dd/MMM/yyyy"));

By

cmd.Parameters.AddWithValue("as_date_time", DateTime.Now));

And for null value: cmd.Parameters.AddWithValue("as_date_time", System.DBNull));

edited Aug 31 '15 at 20:17

answered Aug 31 '15 at 20:11

Nicolas C

225
2
8

bvj · Answer 2 · 2015-09-01T03:31:24.673

0

using System;
using System.Collections.Generic;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      var cmd = new OracleCommand();
      cmd.Parameters.Add("param1", OracleType.DateTime);
      var value = cmd.Parameters["param1"];
      Console.WriteLine(value.Value == null ? "null" : value.Value.ToString());
    }
  }
}

EDIT

For clarity and as a follow up to my initial comments, the above is meant to show a parameter can be added without a specific value providing the type is indicated. The resulting parameter value in the example is indeed null, not DBNull, but null.

edited Sep 01 '15 at 03:31

answered Aug 31 '15 at 20:22

bvj

3,294
31
30

Wrong answer - `"null"` is not an option – T.S. Sep 01 '15 at 02:12
@T.S. I suppose you meant `null` is not an option. The OP stated: _i want to pass null to it,_ – bvj Sep 01 '15 at 03:11
Well. Ok. `OracleParameter` is not working same way as `SqlParameter`. `var value = cmd.Parameters["param1"];` will return `OracleDate` – T.S. Sep 01 '15 at 03:32
@T.S. Understood, but I followed the OP's example where the `cmd.Parameters` are constructed by `new OracleCommand()`. That aside, I'm curious if the underlying driver will accept and convert `null` to SQL `NULL`. – bvj Sep 01 '15 at 03:43
ODP.NET, unlike SqlClient has no problems with `null` when its assigned as value. But (at least on out parameters) when value is read, it will have oracle data type with [oracle] null value. When you do `ToString` on it, it will print `'null'`. What you need to do is `((OracleDate)p.value).IsNull` – T.S. Sep 01 '15 at 10:37

score 0 · Answer 3 · answered Sep 01 '15 at 02:37

You need to be careful with Oracle dates. This error

the number of parameters or the type is not correct

is pretty clear to me. If you can count parameters and make sure they are listed in order (unless you specify "bind by name" on oracle command) - this is step one. Second step is to understand oracle date. If your procedure has any date or timestamp parameter, this will not work

cmd.Parameters.AddWithValue("as_date_time", DateTime.Now.ToString("dd/MMM/yyyy"));

Simply because oracle data types and .net data types do not match. General recommendation is to never pass dates as strings. You really shooting yourself into foot by doing so.

This is your generic syntax to add parameters

OracleParameter date = new OracleParameter();
date.OracleDbType = OracleDbType.Date; // or Timestamp
date.Value = myDateValue; 
orclCommand.Parameters.Add(date);

Now, remeber, if your myDateValue is nullable - this is OK. ODP.net (unlike sql client) takes DBNull or null with no issues.

oracle how to pass null from .net

Update

Update 2

3 Answers3