[BUG] - Using latest Oracle.ManagedDataAccess.OracleBulkCopy corrupts any data inserted into CLOB columns.

[carmanj]

Hi there,

We recently switched from using the unmanaged Oracle.DataAccess driver to using the Oracle.ManagedDataAccess driver. It appears that data corruption is occurring when using ManagedDataAccess.OracleBulkCopy to load data into tables that have CLOB columns. See below for a code sample to demonstrate the issue.

using Oracle.ManagedDataAccess.Client;
using System.Collections.Generic;
using System.Configuration;
using System.Data;

namespace OracleBulkCopy_Demo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["alphaora15"].ConnectionString))
            {
                connection.Open();
                using (OracleBulkCopy bulkCopy = new OracleBulkCopy(ConfigurationManager.ConnectionStrings["alphaora15"].ConnectionString)
                { DestinationTableName = "EMAILS", BulkCopyTimeout = 600, BatchSize = 200000 })
                {
                    var emails = new List<EMAILS>
                    {
                        new EMAILS { EMAILID = 1, SUBJECT = "ABCD_TEST1", BODY = "First test body." },
                        new EMAILS { EMAILID = 2, SUBJECT = "ABCD_TEST2", BODY = "Second test body." }
                    };

                    // Convert list of emails to DataTable
                    DataTable dataTable = new DataTable();
                    dataTable.Columns.Add("EMAILID", typeof(string));
                    dataTable.Columns.Add("SUBJECT", typeof(string));
                    dataTable.Columns.Add("BODY", typeof(string));

                    foreach (var email in emails)
                    {
                        dataTable.Rows.Add(email.EMAILID, email.SUBJECT, email.BODY);
                    }
                    bulkCopy.WriteToServer(dataTable);
                }
                connection.Close();

                /*
                 * Result:
                 *  EmailID: 1, Subject: 䅂䍄彔䕓吱 (hex: BADCT), Body: 䙩牳琠瑥獴⁢潤礮 (hex: iFsr tett)
                 *  EmailID: 2, Subject: 䅂䍄彔䕓吲 (hex: BADCT), Body: 卥捯湤⁴敳琠扯摹 (hex: eSocdnt s)
                 */
            }
        }

        public struct EMAILS
        {
            public int EMAILID;
            public string SUBJECT;
            public string BODY;
        }
        /*
         * SCHEMA:
            CREATE TABLE Emails (
              EmailID INTEGER,
              Subject CLOB NULL,
              Body CLOB NULL
            );
        */
    }
}

It seems apparent that there is a very particular character encoding problem happening when using OracleBulkCopy on CLOB columns when examining the hex values and comparing them to the expected result.

Driver versions: 21.11.0 -21.13.0, 23.3.2-dev
Oracle Version: 19.3.0.0
.NET Version: 4.8

Can a bug be submitted/tracked to resolve this issue? Let me know if anything else is needed.

Thanks,
Justin Carman

[alexkeh]

I tried your test case, but got the correct results. What is your DB NLS setting when you run:
select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET'

[carmanj]

I tried your test case, but got the correct results. What is your DB NLS setting when you run: select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET'

Hi Alex,

That's very interesting...this is happening on all of our 19C databases (and even for kicks, on some ancient 12C databases we only have around for archival purposes). All of our DB servers are set to use UTF8 for the NLS_NCHAR_CHARACTERSET. I've included the rest of the NLS_DATABASE_PARAMETERS, in the event they're of use to you.

Thanks,
Justin Carman

[carmanj]

I tried your test case, but got the correct results. What is your DB NLS setting when you run: select value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET'

Out of curiosity, what Oracle DB version were you running when you tried this? I'm wondering if perhaps this is a DB bug rather than a driver one. However, it is curious that I have no issue if I return to using the unmanaged driver - I would have a harder time understanding how this is a configuration/DB server issue, unless this was (for some reason) actually a bug in the older unmanaged driver that we were using.

Any thoughts?

Thanks,
Justin Carman

[alexkeh]

I tried the test with both 21c and 19c DB. Both were Autonomous DBs, which means they were using a relatively new release update version. Both did not reproduce the problem.

It's possible the problem could be a DB bug or perhaps some interaction between NLS settings between client and server.

[carmanj]

I tried the test with both 21c and 19c DB. Both were Autonomous DBs, which means they were using a relatively new release update version. Both did not reproduce the problem.

It's possible the problem could be a DB bug or perhaps some interaction between NLS settings between client and server.

Hi Alex,

So, I compared the client and server NLS settings. The client (a Windows machine) was using AMERICAN_AMERICA.WE8MSWIN1252. While different from AMERICAN_AMERICA.AL32UTF8, it appears to be correct per the Oracle documentation, here.

I additionally tried this on another client running a more recent version of 19c (their version is on-prem Extreme Performance 19.21, and ours is on-prem Enterprise 19.3) - they had the same problem we're having. That other client has the character set configuration as us:

Are there any other configurations you can think of that might be at play here?

Thanks,
Justin Carman

[alexkeh]

Hi Justin,
What happens when you use NCLOB data types instead of CLOB? Same bug?

[carmanj]

Hi Justin, What happens when you use NCLOB data types instead of CLOB? Same bug?

Hi Alex,

I tried NCLOB and had the same result (broken, with the bad data/characters) on the 19.21 and 19.3 DBs. I also tried BLOB (even though this wouldn't work for our use case), and it did work as expected. So, it seems to be something strictly with CLOB/NCLOB columns, for whatever reason. We were digging through the Oracle forums regarding some similar bug reports, namely 32702522 and started comparing the two DLLs.

We noticed that in the driver before that bug was fixed, the VARCHAR2 datatype didn't look like it had a OracleDbType getting set:

After the fix was implemented for 32702522, it was getting set to NVARCHAR2:

From what we could tell CLOB/NCLOB didn't change, and is getting set to OracleDbType.NChar:

Thanks,
Justin Carman

[carmanj]

Hi Justin, What happens when you use NCLOB data types instead of CLOB? Same bug?

Hi Alex,

I had a conversation with our IT team today regarding our Oracle DB configurations. We are running on Linux with Oracle Linux, and all our DBs are Pluggable Databases - the configurations are pretty much default/vanilla values. I'm not sure if this information is relevant to our issue. However, I am thinking that possibly the Autonomous version has this working because of a patch that neither us nor our clients who have on-Prem systems seem to have. It's still strange that the unmanaged driver works fine.

I am planning to spin up a free Autonomous DB sometime this week to see if I can reproduce it. I may also spin up a Linux VM and try to install Oracle Linux, though this is definitely out of my development wheelhouse, haha.

Thanks,
Justin Carman

[alexkeh]

Hi Justin,
I filed bug 36442140 to track this issue.

After discussing this issue with the ODP.NET dev team, there may be an issue with your CLOB and NCLOB binding when using UTF8. Can your team open an Oracle Support service request (SR) and reference this bug? My team can then provide you with a diagnostic drop to verify the root cause, or at least narrow it down significantly.

Usually, your Oracle DBA will be able to open up the SR for you.

[alexkeh]

Hi Justin,
We were able to reproduce the bug now. Your team can still file an SR. When we have a verification patch ready, you can try it out via the SR.

[carmanj]

Hi Justin, We were able to reproduce the bug now. Your team can still file an SR. When we have a verification patch ready, you can try it out via the SR.

Hi Alex,

That's awesome news, glad it wasn't just me going crazy! Do you suspect this is an issue with the ManagedDatAccess driver, or does it spill deeper into the actual 19c server end of things? I just ask, because we ship our application with the ManagedDataAccess drivers, and I'm trying to figure out if a driver upgrade from nuget will be all that's required for our clients, or whether or not they're going to need to require them to upgrade to a specific 19c version or have a specific patch.

Sorry for the delay, I'm still working with our IT folks to see if we can get Oracle support again. It looks like we previously had access to SRs, but we no longer do.

Thanks so much for you and your team's time and effort on this!
Justin Carman

[alexkeh]

Hi Justin,
It most likely looks is a conversion issue for ODP.NET bulk copy with UFTF8 CLOB/NCLOB, not a DB issue.

[carmanj]

Your team can still file an SR. When we have a verification patch ready, you can try it out via the SR.

Hi Alex,

That's great, I think that's probably the path of least problems for us too, as opposed to getting a specific DB version/patch on each of our Oracle clients that use this functionality. I won't know until next week if we can get a new support contract. If we cannot, what does this process look like? Bugs just aren't prioritized and are put into backlogs, and at some point in the future, the bug may be fixed in the driver release?

Thanks!
Justin Carman

[alexkeh]

We'll fix the bug as typical. We just won't have a way to validate it in your environment. We'll validate it in our own. Then, we ship the bug fix as part of our next ODP.NET quarterly patch release on NuGet Gallery.