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).
