CODEWITHSUNDEEP
c#oracle-database
user7157732
user7157732

Reputation: 347

Oracle command gives error while inserting 0 - C#

I am able to load data into table columns in Oracle by using below code:

 using (OracleConnection thisConnection = new OracleConnection(connectionname))
 {
    string query = "INSERT INTO VRRPlanningResults([WellBore],[PDate],[Reservoir],[ProducerWithdrawal],[ProducerInjection])VALUES(@WellBore,@PDate,@Reservoir,@ProducerWithdrawal,@ProducerInjection)";

    OracleCommand myAccessCommand = new OracleCommand(query, thisConnection);

    var sdate = DateTime.Now.ToString();
    myAccessCommand.Parameters.Add("@WellBore","DK-001");
    myAccessCommand.Parameters.Add("@PDate", DateTime.Parse(sdate));
    myAccessCommand.Parameters.Add("@Reservoir","RESERVOIR");
    myAccessCommand.Parameters.Add("@ProducerWithdrawal",500);
    myAccessCommand.Parameters.Add("@ProducerInjection",0);
  }

All the statements work fine except the last one in which I have mentioned "0". 

An exception of type 'System.ArgumentOutOfRangeException' occurred in Oracle.DataAccess.dll but was not handled in user code

Additional information: Specified argument was out of the range of valid values.

Both ProducerWithdrawal and ProducerInjection are defined as Number type in Oracle. How come 500 is accepted but 0 is not?

System.ArgumentOutOfRangeException was unhandled by user code
  HResult=-2146233086
  Message=Specified argument was out of the range of valid values.
  Source=Oracle.DataAccess
  StackTrace:
       at Oracle.DataAccess.Client.OracleParameter..ctor(String parameterName, OracleDbType oraType)
       at Oracle.DataAccess.Client.OracleParameterCollection.Add(String name, OracleDbType dbType)
       at OracleConnTest.OracleConnTestPluginModel.Initialize() in D:\Office Work\Plugins by Me\111_TestforOracleConnection\OracleConnTest\OracleConnTest\OracleConnTestPluginModel.cs:line 185
       at OracleConnTest.OracleConnTestPluginModel..ctor() in D:\Office Work\Plugins by Me\111_TestforOracleConnection\OracleConnTest\OracleConnTest\OracleConnTestPluginModel.cs:line 56
  InnerException:

EDIT: This is strange. If I mention 0.0000001 it works but if I mention 0, it does not!

Upvotes: 1

Views: 1820

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

The call stack shows what happened. The code calls OracleParameterCollection.Add(String name, OracleDbType dbType but passes an integer value as the OracleDbType enumeration parameter. There is no corresponding enum value for 500, perhaps not even for 0, so you get an ArgumentOutOfRange exception.

While ther is an OracleParameterCollection.Add Method (String, Object) method, it shouldn't be used, precisely because it's impossible to guess the correct overload for integer values. That's why this method ia marked as Obsolete in ADO.NET's Oracle provider. With Microsoft's provider you'd get a compiler warning :

Add(String parameterName, Object value) has been deprecated. Use AddWithValue(String parameterName, Object value). http://go.microsoft.com/fwlink/?linkid=14202"

In the ADO.NET providers, if you want to add a parameter by specifying only a name and value, you can use the AddWithValue method. Even that should be avoided though, as it uses the value's type and size to guess the parameter's attributes. The same holds for Oracle's Add(string,object) method.

For example, double or decimal have no scale, so AddWithValue may create a parameter with the wrong precision for a NUMBER(7,2) field, resulting in unexpected truncations or errors. For fixed width characters, the size will be set at the size of the input value, which may be the wrong size for the underlying field.

Finally, Oracle uses the : prefix for parameters, not @.

A correct query would be:

string query = "INSERT INTO VRRPlanningResults([WellBore],[PDate],[Reservoir],[ProducerWithdrawal],[ProducerInjection]) " + 
               "VALUES(:WellBore,:PDate,:Reservoir,:ProducerWithdrawal,:ProducerInjection)";

...

myAccessCommand.Parameters.Add("WellBore",OracleDbType.NVarchar2,20).Value = "DK-001";
myAccessCommand.Parameters.Add("PDate", OracleDbType).Value = DateTime.Now;
myAccessCommand.Parameters.Add("@ProducerWithdrawal",OracleDbType.Int32) = 500;

...

An even better idea though would be to create the command object in an initialization method and reuse it by passing a new connection and parameter values each time, eg :

public void InitializeCommands()
{
    ...
    _myAccessCommand=new  new OracleCommand(query);
    _myAccessCommand.Parameters.Add("WellBore",OracleDbType.NVarchar2,20);
    ...
}

public void InsertData(string wellBefore,...)
{
    using (var conn = new OracleConnection(connectionname))
    {
        conn.Open();
        _myAccessCommand.Connection=conn;
        _myAccessCommand.Parameters["WellBefore"].Value = wellBefore;
        ...            
        _myAccessCommand.ExecuteNonQuery();
    }
}

Upvotes: 2

Related Questions