Oracle Pro*C for insert with a sub select query causing ORA-01403: no data found

Question

I am using C++ code with embedded Pro*C (Version: 11.2.0.3.0) for Oracle DB. I am running a bulk insert clause as below:

insert int TBL1 (col1, col2) 
select a.col1, b.col2 from TBL2 a, TBL3 b
where a.col1 = :v and a.col2 = b.col2

I run this query for a set of records to be inserted, and binding values for :v in place.

However, while some records could be inserted, some failed with

ORA-01403: no data found

I see from sqlca.sqlerrd[2], the number of rows that could be inserted. So, I know M out N records could be inserted. Now, I would like to know which records did fail, so I need a clue of list of all a.col1 values that could cause this failure. Is there any way out? Any clue or direction would be very helpful.

Answer 1

This is a bit long for a comment.

The error you are referencing is a PL/SQL error, documented here. This is not an error that an insert would normally produce.

My one guess is that the table has an insert trigger and this trigger is causing the problem.

It is also possible that your code is in a larger block, and something else in the block is causing the error.