ORA-01410 when I use OracleBulkCopy into a BLOB field (SECUREFILE)

User_J1H4Y

LongJsonTest.zip

Hello,

I am getting an ORA-01410 error when inserting a relatively long json string into a blob field via OracleBulkCopy.

I have attached an XUnit test that mimics the behavior and produces this error on my system.

My environment is:

•  .Net Core 5 
•  Oracle Server 19c.
•  Oracle Data Provider for .NET (ODP.NET) Core 2.19.120

I do not get the error when I do any of the following:

• Use BASICFILE instead of SECUREFILE
• Specify "DISABLE STORAGE IN ROW"
• Remove JSON check constraint
• Use INSERT INTO instead of OracleBulkCopy

Am I doing something wrong or is this a Bug?

Alex Keh-Oracle

I've been able to reproduce this bug with DB 19c. However, when I upgrade to DB 21c, I can insert without error. This suggests a bug in the DB layer has been fixed recently, possibly in a newer 19c patch as well. What version minor version of Oracle DB 19c are you using?

User_J1H4Y

I have applied the latest patch in Oracle 19c (19.12 => p32895426_190000_Linux-x86-64.zip). But the error still occurs. 

In Oracle 21c the error actually does not occur.

In the meantime I have also created a service request to Oracle and I am waiting for an answer.

Alex Keh-Oracle

As best as I can tell, this looks like bug 29901025. The described symptoms are similar to yours. That bug fix was merged into the main code line before branching off to 21c. However, it doesn't appear to be backported to 19c.

In the SR, you can ask them to verify my evaluation. I'm not an expert in the DB direct path implementation. If so, you can request the fix to be included in the next 19c patch if you don't plan to upgrade to 21c.

User_J1H4Y

Ok you were right. Patch 29901025 is now also available on Linux for 19.12.

But I have a minimal other test case where I thought it is the same error.

But this test still gives an ORA-01410.

The only difference is that instead of a check json constraint I created a virtual column which uses a value of the json lob.

Then I created an index on the virtual column.

If I now insert the same record as before I still get an ORA-01410 error.

I will also have this investigated.

I attached the customized test case.

LongJsonTestVirtualColumn.zip

Alex Keh-Oracle

It could be a corner case missed by the new bug fix. When you file the SR, be sure to mention the possible relation so that it gets to right DB team to review the new test case.