Trigger bug

There has a section “CALL Statements in Triggers” in the “Lesson 10: Creating Compound, DDL, and Event Database Triggers” in the Student Guide “Program with PL/SQL – Student Guide II” in this course “Program with PL/SQL” of ODDLS (Oracle Database Deveoper Learning Subscription) subscribed by me.

As you can see from the first screenshot about procedure and trigger code, actually there exists an invoking procedure “log_execution” clause when creating the trigger “log_employee”. And you should also notice that adding an interesting comment “– no semicolon needed” after that invoking statement in order to remind reader not to add an extra semicolon (there is also a conspicuous Note in the second screenshot).

For practicing that trigger code, I separately tested it on oracle 11.2, 12.1, 12.2, 18.3, 19.3 and LiveSQL (current 19.5). Unfortunately PL/SQL compiler alwayws reports the very weird error of PLS-00103 on all the oracle versions I mentioned previously. So yesterday morning I emailed to Steven Feuerstein (Oracle PL/SQL guru all over the world) to ask for him to help me, as a result, he replied to me it looks like a bug if removing the comment “– no semicolon needed” and then it works. He was going to report the issue. The following are three pictures about our emails back and forth.

After his help, today I wrote the following test SQL code snippet on Github.

REM
REM     Script:        invoke_procedure_in_trigger.sql
REM     Author:        Quanwen Zhao
REM     Dated:         Jul 31, 2020
REM
REM     Last tested:
REM             11.2.0.4
REM             12.1.0.2
REM             12.2.0.1
REM             18.3.0.0
REM             19.3.0.0
REM             19.5.0.0 -- LiveSQL
REM
REM     Purpose:
REM       If adding a comment after the call procedure clause in a trigger, PL/SQL compiler
REM       will report the very weird error of PLS-00103. So I emailed to Steven Feuerstein,
REM       he suggests me remove that comment and then it will normally work, which seems to
REM       be a bug.
REM

CREATE TABLE emp (empno NUMBER, ename VARCHAR2(10));

INSERT INTO emp VALUES(7839, 'King');
INSERT INTO emp VALUES(7698, 'Blake');
INSERT INTO emp VALUES(7782, 'Clark');

COMMIT;

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE log_execution IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('log_execution: Emp table has inserted 1 row.');
END;
/

CREATE OR REPLACE TRIGGER log_emp
BEFORE INSERT ON emp
FOR EACH ROW
CALL log_execution -- no semicolon needed
/

Warning: Trigger created with compilation errors.

show errors
Errors for TRIGGER LOG_EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/37     PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         := . ( @ % ;
         The symbol ";" was substituted for "end-of-file" to continue.

-- if eliminating the comment '-- no semicolon needed' after the 'CALL log_execution' clause

CREATE OR REPLACE TRIGGER log_emp
BEFORE INSERT ON emp
FOR EACH ROW
CALL log_execution
/

Trigger created.

INSERT INTO emp VALUES(7566, 'Jones');
log_execution: Emp table has inserted 1 row.

1 row created.

COMMIT;

And I have to say LiveSQL is a pretty nice tool runing Oracle SQL and PL/SQL code, hence I also wrote a SQL script to test my code (in other words, report a bug). Here‘s my LiveSQL script.

Finally I replied to Steven Feuerstein to let me know the bug number that when MoS has been going to finish reporting.

Addendum

Update Aug 01, 2020: Yesterday evening Steven Feuerstein also emailed to me the bug filed number #31698004 and he reported the issue to oracle and was still able to reproduce it on 20c (outside of LiveSQL).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.