ORA-1003 Executing a PLSQL Stored Procedure That Updates A table With an AFTER UPDATE TRIGGER That Has Been Revalidated (Doc ID 1418795.1)

Last updated on JUNE 21, 2022

Applies to:

Symptoms

Executing a stored procedure call that updates a table with an AFTER UPDATE trigger in a session where the trigger was invalidated and then recompiled and valid fails with:

ERROR at line 1:
ORA-01003: no statement parsed

The following testcase scenario demonstrates the issue:

Steps to Reproduce:


1. Logon as the owner of the database objects created from the code above.


SQL> select status from user_objects where object_name='T1_TRIG';

STATUS
-------
VALID

SQL> exec SP_T1(1,1);

PL/SQL procedure successfully completed.


2. Now invalidate the trigger by recompiling with bad code

SQL> CREATE OR REPLACE TRIGGER T1_TRIG AFTER UPDATE OF C1 ON T1
  2  FOR EACH ROW
  3  DECLARE l_linked_user NUMBER;
  4  BEGIN
  5  IF (:new.c1 = :old.c1) THEN
  6    return;
  7  END IF;
  8  -- use this line to make the trigger invalid !!!!
  9  UNKNOWN_PROCEDURE (:new.c1, :new.c1);
 10  END;
 11  /

Warning: Trigger created with compilation errors.

SQL> select status from user_objects where object_name='T1_TRIG';

STATUS
-------
INVALID

SQL> exec SP_T1(1,1);
BEGIN SP_T1(1,1); END;

*
ERROR at line 1:
ORA-04098: trigger 'USER.T1_TRIG' is invalid and failed re-validation
ORA-06512: at "USER.SP_T1", line 4
ORA-06512: at line 1

This error is expected. However the next call to the same plsql procedure returns a different error:


SQL> exec SP_T1(1,1);
BEGIN SP_T1(1,1); END;

*
ERROR at line 1:
ORA-01003: no statement parsed
ORA-06512: at "USER.SP_T1", line 4
ORA-06512: at line 1


3. Now validate the trigger again and execute the stored procedure.

SQL> CREATE OR REPLACE TRIGGER T1_TRIG AFTER UPDATE OF C1 ON T1
  2  FOR EACH ROW
  3  DECLARE l_linked_user NUMBER;
  4  BEGIN
  5  IF (:new.c1 = :old.c1) THEN
  6    return;
  7  END IF;
  8  -- use this line to make the trigger invalid !!!!
  9  --SP_MO_PAUSEATTENDANTxxx (:new.c1, :new.c1);
 10  END;
 11  /

Trigger created.

SQL> select status from user_objects where object_name='T1_TRIG';

STATUS
-------
VALID

SQL> exec SP_T1(1,1);
BEGIN SP_T1(1,1); END;

*
ERROR at line 1:
ORA-01003: no statement parsed
ORA-06512: at "USER.SP_T1", line 4
ORA-06512: at line 1

Once in this state, the session never recovers unless you disconnect the session and reconnect.

SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options


SQL> connect user/pwd@mydb
Connected.
SQL> exec SP_T1(1,1);

PL/SQL procedure successfully completed.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.