Regression: watching for database change with OracleDependency stopped working with Oracle.ManagedDataAccess.Core version 23.8.0

[0xced]

I have written a basic sample code, using Testcontainers for .NET, which uses OracleDependency to watch for database changes. Note that the Oracle.ManagedDataAccess.Core version is 23.7.0.

watch.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>true</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Oracle.ManagedDataAccess.Core" Version="23.7.0" />
    <PackageReference Include="Testcontainers.Oracle" Version="4.4.0" />
  </ItemGroup>

</Project>

Program.cs

using Testcontainers.Oracle;

var resetEvent = new ManualResetEventSlim(false);

await using var container = new OracleBuilder().WithImage("gvenzl/oracle-free:23-slim-faststart").Build();
await container.StartAsync();
var connectionString = container.GetConnectionString();

using var executor = new OracleExecutor(connectionString, sysDba: false);
using (var sysDbaExecutor = new OracleExecutor(connectionString, sysDba: true))
{
    sysDbaExecutor.ExecuteNonQuery($"grant change notification to {executor.UserId}");
}

executor.ExecuteNonQuery("create table dept (deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno))");
executor.Watch("select deptno from dept", onChange: (_, eventArgs) =>
{
    Console.WriteLine($"🪄 {eventArgs.Info} detected on {string.Join(',', eventArgs.ResourceNames)}");
    resetEvent.Set();
});
executor.ExecuteNonQuery("insert into dept (deptno, dname, loc) values(10, 'Accounting', 'New York')");

var success = resetEvent.Wait(TimeSpan.FromSeconds(20));
if (!success)
{
    Console.WriteLine("💥 change went undetected");
}

var (stdout, _) = await container.GetLogsAsync();
Console.Write(stdout);

return success ? 0 : 1;

OracleExecutor.cs

using Oracle.ManagedDataAccess.Client;

public sealed class OracleExecutor : IDisposable
{
    private readonly OracleConnection _connection;
    private readonly OracleConnectionStringBuilder _connectionString;

    public OracleExecutor(string connectionString, bool sysDba)
    {
        _connectionString = new OracleConnectionStringBuilder(connectionString);
        if (sysDba)
        {
            _connectionString.UserID = "SYS";
            _connectionString.DBAPrivilege = "SYSDBA";
        }
        _connection = new OracleConnection(_connectionString.ConnectionString);
        _connection.UseClientInitiatedCQN = true;
        _connection.Open();
    }

    public void Dispose()
    {
        _connection.Dispose();
    }

    public string UserId => _connectionString.UserID;

    public void ExecuteNonQuery(string sql)
    {
        using var command = new OracleCommand(sql, _connection);
        Console.Write($"▶️ {sql}");
        command.ExecuteNonQuery();
        Console.WriteLine(" ✅");
    }

    public void Watch(string sql, OnChangeEventHandler onChange)
    {
        var watchCommand = new OracleCommand(sql, _connection);

        var dependency = new OracleDependency(cmd: watchCommand, isNotifiedOnce: true, timeout: 300, isPersistent: false);
        dependency.OnChange += (sender, args) =>
        {
            watchCommand.Dispose();
            onChange(sender, args);
        };

        Console.Write($"👁️ {sql}");
        using var reader = watchCommand.ExecuteReader();
        while (reader.Read())
        {
        }
        Console.WriteLine(" ✅");
    }
}

Running this code with dotnet run works fine. The insert detection is instantaneous and the following logs are produced.

▶️ grant change notification to oracle ✅
▶️ create table dept (deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno)) ✅
👁️ select deptno from dept ✅
▶️ insert into dept (deptno, dname, loc) values(10, 'Accounting', 'New York') ✅
🪄 Insert detected on ORACLE.DEPT

Now, if you update the Oracle.ManagedDataAccess.Core package to version 23.8.0 the insert detection stops working and the following logs are produced.

▶️ grant change notification to oracle ✅
▶️ create table dept (deptno number(2,0), dname varchar2(14), loc varchar2(13), constraint pk_dept primary key (deptno)) ✅
👁️ select deptno from dept ✅
▶️ insert into dept (deptno, dname, loc) values(10, 'Accounting', 'New York') ✅
💥 change went undetected

If we look at the Docker container logs we can see some errors that were not happening when using version 23.7.0 of ODP.NET.

Errors in file /opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_260.trc (incident=729) (PDBNAME=FREEPDB1):
ORA-03137: malformed TTC packet from client rejected: [24377] [187] [] [] [] [] [] []
FREEPDB1(3):Incident details in: /opt/oracle/diag/rdbms/free/FREE/incident/incdir_729/FREE_ora_260_i729.trc
2025-05-21T21:46:42.862206+00:00
FREEPDB1(3):Session (200,32786): Bad TTC Packet Detected: Inbound connection from client
FREEPDB1(3):Session (200,32786): Bad TTC Packet Detected: DB Logon User: ORACLE, Remote Machine: Asterix\Asterix, Program: watch.exe, OS User: cedric
FREEPDB1(3):Session (200,32786): Bad TTC Packet Detected: Client IP Address: 192.168.215.1

Since the only difference is the version of ODP.NET driver, it looks like a regression was introduced in version 23.8.0.

[alexkeh]

@0xced Can you turn on ODP.NET tracing to level 7 and share your trace? You can upload it here or email it to dotnet_us(at)oracle.com.

After you register the change notification with ODP.NET 23.8 and before you execute the SQL with the DB change, run the query:
SELECT REGID, TABLE_NAME FROM USER_CHANGE_NOTIFICATION_REGS;

If the registration is not there, it tells us the problem is with the registration step. Otherwise, the problem is with the ability to receive the DB notification.

[0xced]

I updated the code to print the registrations. I can confirm that a registration exists (with REGID = 302) in the USER_CHANGE_NOTIFICATION_REGS table. The registration exists with both version 23.7.0 and 23.8.0 of the driver.

Note that you should be able to reproduce this issue on any machine with Docker Desktop running (or an equivalent such as Orbstack). Yay Testcontainers!

Anyway, here's the trace at level 7: WATCH.EXE_PID_11730_DATE_2025_05_22_TIME_23_25_37_580446.trc

[alexkeh]

@0xced Thanks for the trace file. It confirms the logs in the Docker container. The DB is killing the connection after getting unexpected data from the client. I opened up a bug (37985867) to have the ODP.NET dev team review the trace and diagnose the root cause.