PL/SQL Block Fails to Update all Records when Subpartitions are being Truncated

(Doc ID 548943.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.1.0.5 to 11.1.0.6 - Release: 10.1 to 11.1
Information in this document applies to any platform.

Symptoms

A PL/SQL block that is bulk fetching then bulk updating or deleting records in a partitioned table, fails to update all records when another session is truncating subpartitions on the same table. The result is less records being updated than expected. 

This problem is triggered when the session performing the bulk DML gets an internal ORA-14403 error during processing of the array batch.  This internal ORA-14403 is normal but in the problem case it is not handled correctly.

Setting an ERRORSTACK on 14403 will help show if that error is occurring and dump errorstack information for every 14403 error.
For example:

SQL> ALTER SYSTEM SET EVENTS '14403 trace name errorstack level 3';

To switch off the event run:

SQL> ALTER SYSTEM SET EVENTS '14403 trace name context off';


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms