ExecuteNonQuery is hanging on UPDATE queries.

ToolTimeTabor

I am having a problem where an Oracle update command, executed using the ExecuteNonQuery() method is hanging.  INSERT and DELETE work fine.  But, when I try to do a simple UPDATE command, it steps into the method and just sits there.  If I go restart the service and run it again, it works.  So, I know the syntax works for both the INSERT and UPDATE, but sometimes it hangs.  Even when I stop Visual Studio debugger and restart, the INSERT will work but the UPDATE won't until I restart the service. 

Any thoughts?  If the Oracle service was "hung" I would think that INSERT would have the same problem.  Once it hangs, shouldn't it be hung for both operations?  But its not.  Once it hangs, only the UPDATE is affected.  Never seen anything like this.

I have an audit table defined as follows:

CREATE TABLE AUDIT_LOG ( AUDIT_LOG_ID NUMBER NOT NULL ENABLE, AUDIT_LOG_SOURCE VARCHAR2(150 BYTE) NOT NULL ENABLE, AUDIT_LOG_LOCATION VARCHAR2(150 BYTE) NOT NULL ENABLE, AUDIT_LOG_MESSAGE VARCHAR2(4000 BYTE) NOT NULL ENABLE, AUDIT_LOG_CRITICALITY VARCHAR2(50 BYTE) DEFAULT 'Information' NOT NULL ENABLE, AUDIT_LOG_TYPE VARCHAR2(50 BYTE) DEFAULT 'Audit' NOT NULL ENABLE, AUDIT_LOG_CREATED DATE DEFAULT sysdate NOT NULL ENABLE, AUDIT_LOG_CREATOR VARCHAR2(150 BYTE) DEFAULT USER NOT NULL ENABLE)

My test code is in a click event on a Windows Form.  The connection string is valid and the dbConnection opens without errors.  The first command is an INSERT command to the audit table.  It works great (fast).  However, when I get to the UPDATE command, it steps in and hangs.  There is no

        private void menuTest_Click(object sender, EventArgs e)        {            OracleConnection dbConnection = new OracleConnection(this.ConnectionString);            dbConnection.Open();            string insertQuery = "INSERT INTO AUDIT_LOG (AUDIT_LOG_SOURCE, AUDIT_LOG_LOCATION, AUDIT_LOG_MESSAGE, AUDIT_LOG_CRITICALITY, AUDIT_LOG_TYPE) VALUES ('CheckIn', 'SYS', 'Successful', 'Information', 'Audit')";            OracleCommand insertCommand = dbConnection.CreateCommand();                       insertCommand.CommandType = CommandType.Text;            insertCommand.CommandText = insertQuery;                       try { insertCommand.ExecuteNonQuery();  }            catch(Exception ex) { MessageBox.Show(ex.Message);}            string updateQuery = "UPDATE AUDIT_LOG SET AUDIT_LOG_CREATOR = 'TEST'";            OracleCommand updateCommand = dbConnection.CreateCommand();            updateCommand.CommandType = CommandType.Text;            updateCommand.CommandText = updateQuery;            try { updateCommand.ExecuteNonQuery(); }            catch (Exception ex) { MessageBox.Show(ex.Message); }          dbConnection.Close();        }    

Alex Keh-Oracle

Which Oracle provider are you using? Is it ODP.NET Core, managed, or unamanaged? Which version?

How many rows are expected to be updated?

David2005

Hello I have opened the same issue I think (https://community.oracle.com/tech/developers/discussion/4476660/managed-driver-19-9-0-bulk-update-problem#latest).

In my case I am working with Oracle.ManagedDataAccess 19.9.0. With this version bulk insert (using array binding) work perfectly, but Update process never ending, or take a lot of time and abort the process manually (I have create a console test application).

Answer you question Alex, I expected to update from 10K to 300K registers. But, in the current test I am trying to update 65K registers.