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 JULY 05, 2017

Applies to:

PL/SQL - Version 10.2.0.5 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.

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:

CREATE TABLE T1(C1 VARCHAR(50));
/

INSERT INTO T1 VALUES ('Hello');

COMMIT;

CREATE OR REPLACE TRIGGER T1_TRIG AFTER UPDATE OF C1 ON T1
FOR EACH ROW
DECLARE l_linked_user NUMBER;
BEGIN
IF (:new.c1 = :old.c1) THEN
  return;
END IF;
-- use this line to make the trigger invalid !!!!
--SP_MO_PAUSEATTENDANTxxx (:new.c1, :new.c1);
END;
/

CREATE OR REPLACE PROCEDURE "SP_T1" (v_module_id number, v_session_id number)
AS
BEGIN
 UPDATE T1 SET C1 = 'xxx';
COMMIT;
END;
/


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  SP_MO_PAUSEATTENDANTxxx (: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 'SCOTT.T1_TRIG' is invalid and failed re-validation
ORA-06512: at "SCOTT.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 "SCOTT.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 "SCOTT.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 scott/tiger@MYDB
Connected.
SQL> exec SP_T1(1,1);

PL/SQL procedure successfully completed.

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