Bulk update does not work as expected. Update sets column to null, when a date value was expected. — oracle-tech

    Forum Stats

  • 3,715,507 Users
  • 2,242,778 Discussions
  • 7,845,371 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Bulk update does not work as expected. Update sets column to null, when a date value was expected.

Frank Speck
Frank Speck Member Posts: 3 Red Ribbon
edited September 2020 in ODP.NET

I am experimenting with the managed Odp.Net Driver and ran into a problem that seems to be a bug in ODP.Net.

I have a program below that will demonstrate the problem. The Run()-method executes two updates.

Each update-command executed individually works as expected.

These two update commands executed consecutively do not throw an exception but do not work as expected.

The second update command fails resulting in null-values in the column mydate of the table.

It is expected that the update will set mydate to 2000-09-29 (first row) and 2000-09-30 (second row).

I am using the Oracle.ManagedDataAccess NuGet Package Version 19.8.0 and target .NET Framework 4.7.2

Steps to setup the test database for reproduction

  1. change the connection string in the program to match your database
  2. create the table and insert the two records in the table
  3. run the program

After the excecution of the program, the MYDATE-Column of the table is null although the last update statement sets values for this column in both rows.

CREATE TABLE T41333 (  ID NUMBER NOT NULL , MYNUMBER NUMBER , MYTEXT VARCHAR2(255) , MYDATE DATE , CONSTRAINT T41333_PK PRIMARY KEY   (    ID   )  ENABLE );INSERT INTO T41333 (ID, MYNUMBER, MYTEXT, MYDATE) VALUES ('13', '1000', 'Initial', TO_DATE('2006-01-01 07:12:29', 'YYYY-MM-DD HH24:MI:SS'));INSERT INTO T41333 (ID, MYNUMBER, MYTEXT, MYDATE) VALUES ('42', '1000', 'Initial', TO_DATE('2006-01-01 07:12:29', 'YYYY-MM-DD HH24:MI:SS'));commit;

Program to demonstrate the behavior

using Oracle.ManagedDataAccess.Client;using System;using System.Collections;using System.Data;namespace OdpProblem{    class Program    {        private const string CONNECTION_STRING =            "User Id=myUser;Password=myPasswork;Data Source=\"//myserver:1521/myservice\";Pooling=false;";        static void Main(string[] args)        {            new OdpProblem(CONNECTION_STRING).Run();            Console.WriteLine("Press 'Enter' to continue");            Console.ReadLine();        }    }    public class OdpProblem    {        private readonly OracleConnection connection;        private readonly OracleCommand singleInstanceCmd;        private readonly ArrayList singleInstanceParameters;        public OdpProblem(string connectionString)        {            this.connection = new OracleConnection(connectionString);            this.singleInstanceCmd = connection.CreateCommand();            this.singleInstanceParameters = this.CreateParameter();        }        public void Run()        {            this.connection.Open();            this.Execute(1, new object[]            {                new object[] {10},                new object[] {"First update setting mydate to NULL"},                null,                new object[] {13}            });            this.Execute(2, new object[]            {                new object[] {20, 30},                new object[] {"Second update setting mydate to 2000-09-29", "Second update setting mydate to 2000-09-30"},                new object[] {new DateTime(2000, 09, 29), new DateTime(2000, 09, 30)},                new object[] {13, 42}            });        }        private void Execute(int count, object[] parameterValues)        {            // Clear            this.singleInstanceCmd.CommandText = string.Empty;            this.singleInstanceCmd.ArrayBindCount = count;            this.singleInstanceCmd.Parameters.Clear();            // Init            this.singleInstanceCmd.CommandText =                "UPDATE T41333 SET MYNUMBER = :myNumber, MYTEXT = :myText, MYDATE = :myDate where ID = :id";            for (int i = 0; i < this.singleInstanceParameters.Count; i++)            {                var parameter = (IDbDataParameter) this.singleInstanceParameters[i];                parameter.Value = parameterValues[i];                this.singleInstanceCmd.Parameters.Add(parameter);            }            // Execute            var affectedRows = this.singleInstanceCmd.ExecuteNonQuery();            Console.WriteLine($"AffectedRows: {affectedRows}");        }        private ArrayList CreateParameter()        {            OracleParameter Create(string paramName, OracleDbType oracleDbType) =>                new OracleParameter(paramName, oracleDbType)                {                    IsNullable = false,                    Direction = ParameterDirection.Input                };            return new ArrayList            {                Create("myNumber", OracleDbType.Int64),                Create("myText", OracleDbType.Char),                Create("myDate", OracleDbType.Date),                Create("id", OracleDbType.Int64)            };        }    }}

What am I doing wrong?

Frank Speck

Answers

  • Frank Speck
    Frank Speck Member Posts: 3 Red Ribbon
    edited August 2020

    I think I found the problem.

    If I change the above code from

                this.Execute(1, new object[] 
                { 
                    new object[] {10}, 
                    new object[] {"First update setting mydate to NULL"}, 
                    null, 
                    new object[] {13} 
                }); 

    to

                this.Execute(1, new object[] 

                { 

                    new object[] {10}, 

                    new object[] {"First update setting mydate to NULL"}, 

                    new object[] { DBNull.Value }, 

                    new object[] {13} 

                }); 

    the update-statements work as expected.

    Could anyone clarify, why the code posted in my original question leads to an error after the following update? The second update sets the column mydate to null when it should set mydate to a date.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,751 Employee
    edited August 2020

    Hi Frank,

    The correct parameter array binding method to submit NULL values is to use an array with NULL value(s), which is why your updated code works.

    The first case is not the correct usage, which leads to some unintended side effects.

    I'm not sure this is a clear cut bug. A case could be made that incorrect API usage should generate an error message. I'll follow up with the dev team.

    Frank SpeckFrank Speck
  • Frank Speck
    Frank Speck Member Posts: 3 Red Ribbon
    edited August 2020

    Hi Alex,

    thank you for your answer and clarification.

    An error message could be a good idea as it might have helped us to spot a nasty update bug more easily.

Sign In or Register to comment.