ExecuteNonQueryAsync not working properly

[chsakell]

I need to update a single record but it seems that no rows affected.

var query = @"UPDATE TEST_TABLE SET MY_NAME= :param1 WHERE MY_ID = :param2";

using (OracleConnection con = new OracleConnection(_connectionString))
{
    con.TAFMode = new OracleTAFMode(OracleFailoverType.Session, OracleFailoverRestore.NONE);
    await con.OpenAsync();

    using (OracleCommand cmd = new OracleCommand(query, con))
    {
        cmd.BindByName = true;
        OracleParameter param1 = new OracleParameter("param1", model.Name);
        OracleParameter param2 = new OracleParameter("param2", model.Id);
        cmd.Parameters.Add(param1);
        cmd.Parameters.Add(param2);
        cmd.CommandType = CommandType.Text;
        var result = await cmd.ExecuteNonQueryAsync();
    }
}

The new value is different than the new one and the Id param is valid. Even if I remove the WHERE clause, the update never changes the record.

Oracle.ManagedDataAccess.Core: Version 23.4.0

SELECT statements work fine by the way. What's wrong with the above code?

[alexkeh]

I am able to execute your above code successfully and see the update in my table. If you use the literal values instead of parameters, is the table updated?

[chsakell]

No it's not. The interesting thing is that I can see that the SQL statement reaches the database using the following command:

select *  from v$sql v
where to_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss')>ADD_MONTHS(trunc(sysdate,'MM'),-1)

If I copy the SQL statement and run it directly in Oracle, e.g. VS Code SQL developer, the update runs successfully. The above query differs for the updates running in code and directly in a few columns, like _CHILDNUMBER, (0 vs 1) , _LOCKEDTOTAL & _PINNEDTOTAL but I don't know if they mean anything.

[alexkeh]

Can you try running the same ODP.NET app against another DB instance? If it runs, there's something specific to how your DB or DB user is configured. For example, it's possible that specific DB user doesn't have update privileges.

An additional route of diagnostics is turning on ODP.NET tracing. You can see if the DB is reporting any issues/errors back to ODP.NET after it executes the statement. Here's how to enable tracing in your code.

OracleConfiguration.TraceFileLocation = @"C:\traces"; OracleConfiguration.TraceLevel = 7;

[chsakell]

It drove me crazy for a day but it was actually simple. I entered the data using VS Code SQL Developer extension without commiting them (by default it doesn't commit the data) so I was able to see everything working on VS Code but not from a different session such as my code session. I commited the data and everything worked fine. Thanks for the support @alexkeh , I am closing it.