ORA-01403: no data found; on plain INSERT statement

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
Posts: 6
Joined: Wed 22 Aug 2007 05:42

ORA-01403: no data found; on plain INSERT statement

Post by JerryJVL » Wed 10 Dec 2008 02:57

There is a problem (at least in OraDirect 4.50) with mutli-threaded use of transactions in a specific way that causes this error.

Using a multi-threaded app (each thread has its own oracle connection), when performing INSERTs into different tables from each thread, enclosed in a try-catch with a 'Commit' in the try and a 'Rollback' in the catch, every once in a while the mentioned Oracle error will occur, when both threads are performing INSERTs at about the same time (over their separate connections).

After establishing this problem did not occur with ODP.NET, I discovered that this error occurred because there was no explicit transaction 'Begin' in the code; adding the explicit "Begin" made the problem disappear.

As such, this is not a show-stopper bug, since making all transactions completely explicit is a feasable workaround, and the implicit 'Begin' is not actually supported by ODP.NET, so it is not really a 'missing feature' either if we cannot use it.

Still, this may be worth investigating to make sure this problem cannot occur in other scenario's... and I thought this post might be a useful reference for anyone else out there with the same problem, because there was no help to be found on the web when I looked.

So, to summarise; this problem occurs for me using the following:
- OraDirect 4.50
- A multi-threaded application (each thread has its own connection)
- Inserts on each thread occurring at about the same time inside a transaction
- The transaction is not explicitly started with 'BeginTransaction'

Stack trace when the problem occurs:

CoreLab.Oracle.OracleException: ORA-01403: no data found
at CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at CoreLab.Oracle.OracleCommand.ExecuteNonQuery()
at ORA01403.Program.CopyLoop(String prefix) in C:\OnceOff\ORA01403\Program.cs:line 205

Site Admin
Posts: 8248
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 12 Dec 2008 11:19

Thank you for your post. We recommend to refer to our samples using our components. The sample for the OracleTransaction class in our documentation ( http://www.devart.com/dotconnect/oracle/docs/ ) contains the transaction that is explicitly started with the BeginTransaction() method.
Still, this may be worth investigating to make sure this problem cannot occur in other scenario's...
Please describe the possible problems more clearly.

Post Reply