Ocasion receive "ORA-01403 no data found" in multi thread

edmi · Post by **edmi** » Thu 23 Jul 2009 11:36

I am encountering a very strange problem using ODAC and have been looking for a solution for month and now became very frustrated, hope experts here can help.

I have a test multi thread program that will insert large amount of data into oracle 11g database, and occasionally I will receive "ORA-01403 no data found" error from the database. Below is the coding environments:

- Using Odac 650d11 pro
- Program compiled using RAD 2007
- A table with primary key with no trigger
- 50 threads, each thread to insert 1000 record, insert one record follow by another record
- insert statement is like 'insert into table1 (...) values (...)'
- most of the record is duplicated, so primary key violation will be occurred, but exception is catch and continue next record
- each insert statement will be follow by a select statement from another table
- The error occurred at insert statement, not select statement

Each time the program is finished, I will find a few "ORA-01403 no data found" errors out of many primary key violation error. I don't have any idea how on earth this no data found error comes from, I tried to use both TOraQuery & TOraSQL, result is the same. Can anyone help?

edmi · Post by **edmi** » Thu 23 Jul 2009 11:39

If I use Odac direct, I get OCI_NO_DATA error instead.
I need to get this strange issue solve for a client as soon as possible, hope someone can help up, thanks.

edmi · Post by **edmi** » Thu 23 Jul 2009 11:40

Just to provide more information, when using ADO driver, this problem doesn't existed, so I am suspecting this is a internal problem in odac's driver...

edmi · Post by **edmi** » Thu 23 Jul 2009 11:47

another strange thing is, if taken out the select statement, the problem will not occur. And weird is, even with select statement, the error is occurred at the insert statement, not the select statement...
the select statement is something like 'select count(*) from table', so I don't think it will cause no data found error in any case.

Challenger · Post by **Challenger** » Thu 23 Jul 2009 13:01

Do you use separate instances of TOraSession for eaech thread?

edmi · Post by **edmi** » Thu 23 Jul 2009 15:19

Yes, each thread is having their own TOraSession, so in my case, 50 connection is made to Oracle database. The problem wasn't occurred when using only few threads to perform data insert & select. So if using ODAC for enterprise project which required more threads, it doesn't meet our requirement anymore...

edmi · Post by **edmi** » Fri 24 Jul 2009 04:23

I am going to post my test program so devart's development team can help testing on it. where should I send my program?

Challenger · Post by **Challenger** » Tue 28 Jul 2009 10:59

Please send it to support*devart*com.

edmi · Post by **edmi** » Tue 11 Aug 2009 09:44

Test program & source code sent to support team. waiting for reply.