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();
}