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.

score 0 · Answer 1 · answered Aug 11 '15 at 12:23

0

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.

answered Aug 11 '15 at 12:23

Gordon Linoff

1,242,037
58
646
786

I know, it's not an insert problem, but caused by the select subquery that couldnot get some matching row for some set of data and while trying to insert through select into, caused this – Dr. Debasish Jana Aug 11 '15 at 12:28
`insert . . . select` has no problem with a `select` that returns no rows. – Gordon Linoff Aug 11 '15 at 12:32
My guess is that the subquery that has a select is run as select into ... variable and then used that variable for insert, and select into causing no data found – Dr. Debasish Jana Aug 11 '15 at 12:36
@DebasishJana . . . `select into` is allowed in a subquery? – Gordon Linoff Aug 11 '15 at 12:39
no but getting translated to by Pro*C, I presume, as I am getting this trouble – Dr. Debasish Jana Aug 11 '15 at 12:40

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

1 Answers1