Home » SQL & PL/SQL » SQL & PL/SQL » ORA-1403 when trying to select after Insert (Oracle 10g)
ORA-1403 when trying to select after Insert [message #682537] Tue, 27 October 2020 18:58 Go to next message
pjizquierdo
Messages: 2
Registered: October 2020
Junior Member
Hello,

I have two procedures in the same package, one for insert and another for process the registry first inserted. For avoiding inconsistent data in case of an error in the second procedure, I don't commit after the insert on the insert procedure, but in the process procedure I need to select data from that registry recently inserted. That used to work very well and I have many other procedures that work perfectly doing the same, but recently this one started to fail, giving me an ORA-1403 when I try to select.

The example is the following:

Procedure Insertrows

Insert into table a
(a.field1, a. field2, a.field3, a.field4)
values
(xxx,yyy,www,zzz);

Procedure Processrows

Select field1, field2, field3, field4
into vfield1, vfield2, vfield3, vfield4
from table a
where a.field1 = xxx
and a.field2 = yyy;

I hope someone can guide me why this was working and now is not.

Thank you!


Re: ORA-1403 when trying to select after Insert [message #682538 is a reply to message #682537] Tue, 27 October 2020 19:12 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

I am convinced that the ORA-01403 error is legitimate; that there is no row that meets the WHERE criteria.
Other than your unsubstantiated claim this error is unexpected, you offer no proof that any REAL problem definitively exists.
Re: ORA-1403 when trying to select after Insert [message #682539 is a reply to message #682537] Tue, 27 October 2020 20:56 Go to previous messageGo to next message
flyboy
Messages: 1895
Registered: November 2006
Senior Member
Hello,

so, what changed recently outside Oracle?

Based on your description, the procedure PROCESSROWS is called in different database session than INSERTROWS.

I would start investigating the actual call flow - the tool which calls these procedures.
Re: ORA-1403 when trying to select after Insert [message #682540 is a reply to message #682539] Tue, 27 October 2020 21:10 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
If/when Session A INSERT row(s) & does not issue any COMMIT, then Session B can NEVER read any uncommitted rows that were INSERTED by different session.
Re: ORA-1403 when trying to select after Insert [message #682548 is a reply to message #682538] Wed, 28 October 2020 07:54 Go to previous message
pjizquierdo
Messages: 2
Registered: October 2020
Junior Member
Thank you, I will read it again carefully.

Regards,
Previous Topic: SQL to retrieve lines based on a static value
Next Topic: A query that works in SquirrelSLQL fails in C#
Goto Forum:
  


Current Time: Sun Nov 29 19:40:43 CST 2020