ArrayBindCount > 1_000_000 raises a ORA-03146: invalid buffer length for TTC field

[sierrodc]

Hi,

we are migrating Oracle from 11g to 19c. We have the following code:

var size = 1_048_600;
var array = new decimal[size];
using (var target = new OracleConnection("..."))
{
    await target.OpenAsync();
    await target.ExecuteAsync("TRUNCATE TABLE TEST");

    var cmd = target.CreateCommand();
    cmd.CommandText = @"
                    INSERT INTO TEST_BULK(ONE)
                    VALUES(:0)";
    cmd.ArrayBindCount = array.Length;

    cmd.Parameters.Add(new OracleParameter() { OracleDbType = OracleDbType.Decimal, Value = array });

    await cmd.ExecuteNonQueryAsync();
}

This is working fine with oracle 11g. With oracle 19c we are getting this exception: ORA-03146: invalid buffer length for TTC field at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution.

No issue if size < 1.000.000 No issue using different data format (we tried decimal, int, strings..) No issue inserting multiple columns No issue with oracle 11g No issue using OracleBulkCopy

If size > 1.000.000 (actually something > 1.048.575) we get the error in oracle 19c. Here is also the trace trace_level7.log (just the last Kbs of about 4-5Gb of traces)

I don't know if this error is related to the library or to the database itself.

[sierrodc]

We've also some code in python and the following code works fine:

    size = 10_000_000    

    with pcm.get_oracle_connection(db_connection_info) as conn:
        with conn.connection.cursor() as cursor:
            cursor.prepare("INSERT INTO TEST_BULK(ONE) VALUES(:0)")
            cursor.bindarraysize = size
            cursor.executemany(None, [[i] for i in range(1,size)])

        conn.commit()

but yep, I don't know if cx_oracle (8.3.0) does somethig different. I forgot to mention that previous code doesn't work neither with .net7 nor .net framework 4.8

[alexkeh]

From the trace, I see you are using ODP.NET Core 21c. Can I presume that you didn't see this problem when using ODP.NET 21c with an Oracle DB 11g? If that's the case, it points to a regression in the Oracle DB. If not, what ODP.NET version and type (unmanaged, managed, core) were you using earlier without the bug?

I do see an existing known ODP.NET bug (34641097) that occurs with parameter array binding. This seems to affect ODP.NET 19c and 21c, but does not affect ODP.NET 23c.

There is a known Oracle DB 19c bug (30955880/31625618) with the same ORA-03146 error. This bug was fixed in 19.10. What 19c version patch do you have installed?

[sierrodc]

Hi, yes exactly, the program is the same using .net7.0.3 and Oracle.ManagedDataAccess.Core 3.21.100. Pointing to oracle11.2.0.4.0 everything works fine. It is not working using oracle19.14.0.0.220118 (according to our dba, the bugfix you've mentioned is included in our version). I tried also with .net4.8 and Oracle.ManagedDataAccess, same issue.

So, do you suggest to contact OracleDB support?

[alexkeh]

It's not bug 30955880/31625618, but it's a different DB regression. The standard procedure is for the RDBMS team to fix the regression. I would contact Oracle DB Support, create an SR, and have them fix the DB bug. I recommend trying to run against the latest 19c version, 19.19, to verify whether this bug hasn't already been fixed, which will save you the trouble of filing the SR. In all likelihood, Oracle Support will ask to upgrade to the latest DB release update to eliminate known issues that have already been fixed.

You can also try ODP.NET 23c dev release when it comes out on NuGet Gallery in a week or two on NuGet Gallery. If that works, you can use 23c for development, but it's not a production release.

[sierrodc]

Hi, first of all thanks for the support.

Update: I also tried with OracleFree v23.2.0.0.0 (last image available container-registry.oracle.com/database/free:latest) and the above code fails when size > 3_000_000 (with the same ORA-03146 error code).

Now:

1. I'll talk with DBAs and probably we'll open a SR.
2. The fastest workaround (well, we don't have many programs thet are inserting a lot of records all at once with arraybinding) is to chunk inserts. It seems feasible for now.

If I have extra updates I'll let you know.

[alexkeh]

Sorry, when I said to try 23c when it comes out, I meant ODP.NET 23c, not DB 23c. The underlying bug is very likely in the DB. ODP.NET manifests the bug because that is the Oracle interface you are directly working with.

ODP.NET 23c was working with large ArrayBindCounts and 19c DBs.

[sierrodc]

Hi,

yes, I was assuming that ora 23c db > 19.19 => the patch is included. Just a try.

BTW, our DBA found this article in Oracle KB: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=101097918720095&parent=EXTERNAL_SEARCH&sourceId=PROBLEM&id=2586858.1&_afrWindowMode=0&_adf.ctrl-state=osgbq0g2t_4

So, it's not a regression, it is something wanted for security reasons.