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 184.108.40.206 REM 220.127.116.11 REM 18.104.22.168 REM 22.214.171.124 REM 126.96.36.199 REM 188.8.131.52 -- 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;
Finally I replied to Steven Feuerstein to let me know the bug number that when MoS has been going to finish reporting.
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).