- 3,715,917 Users
- 2,242,907 Discussions
- 7,845,683 Comments
Forum Stats
Discussions
Categories
- 17 Data
- 362.2K Big Data Appliance
- 7 Data Science
- 1.6K Databases
- 474 General Database Discussions
- 3.7K Java and JavaScript in the Database
- 22 Multilingual Engine
- 487 MySQL Community Space
- 5 NoSQL Database
- 7.6K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 417 SQLcl
- 42 SQL Developer Data Modeler
- 184.9K SQL & PL/SQL
- 21K SQL Developer
- 1.9K Development
- 3 Developer Projects
- 32 Programming Languages
- 135.1K Development Tools
- 9 DevOps
- 3K QA/Testing
- 256 Java
- 6 Java Learning Subscription
- 10 Database Connectivity
- 67 Java Community Process
- 1 Java 25
- 9 Java APIs
- 141.1K Java Development Tools
- 6 Java EE (Java Enterprise Edition)
- 153K Java Essentials
- 135 Java 8 Questions
- 86.2K Java Programming
- 270 Java Lambda MOOC
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 10 Java SE
- 13.8K Java Security
- 3 Java User Groups
- 22 JavaScript - Nashorn
- 18 Programs
- 125 LiveLabs
- 30 Workshops
- 9 Software
- 3 Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 3 Deutsche Oracle Community
- 11 Español
- 1.9K Japanese
- 2 Portuguese
Bulk update does not work as expected. Update sets column to null, when a date value was expected.

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
- change the connection string in the program to match your database
- create the table and insert the two records in the table
- 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?
Answers
-
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.
-
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.
-
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.