Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Conditional compilation in database event triggers... happens at run time

user9540031Feb 10 2024

TL/DR: if part of the code in the body of a database event trigger must be selectively enabled/disabled at compile time, by way of a specific value of PLSQL_CCFLAGS, do not use conditional compilation directly in that trigger's body. Instead, always encapsulate that part of the code into a package, or a stored procedure/function.

Reason: the body of a database event trigger (aka a non-DML trigger) is parsed at run time as an anonymous block. Therefore, conditional compilation works, but it takes place at run time, as opposed to the time when the trigger was last compiled, and it uses the current value of the PLSQL_CCFLAGS parameter in the triggering session, instead of the value set when the trigger was last compiled.

Tested on: 11.2, 19c, 23c FREE.

Demonstration: using DBA rights for setting up the trigger, plus an unprivileged account (SCOTT) for the test session.

As DBA:

create user trg$owner 
no authentication;                   -- if DB version >= 18c 
--identified by "xxx" account lock;  -- otherwise

grant administer database trigger to trg$owner;

alter session set plsql_ccflags="is_xxx:true";

create or replace trigger trg$owner.test_servererror_trig
after servererror
on scott.schema
begin
    dbms_output.put(
        'This is "' || coalesce($$PLSQL_UNIT, 'test_servererror_trig') || '"' 
        $if dbms_db_version.version >= 12 $then 
         || ' (unit type: ' || $$PLSQL_UNIT_TYPE || ')' 
        $end 
         || ', '
    );
   $if $$is_xxx $then
    dbms_output.put('with the xxx option.');
   $else
    dbms_output.put('WITHOUT the xxx option.');
   $end
    dbms_output.new_line;
exception
    when others then null;
end;
/

So here we have created a schema-only account, for the sole purpose of owning the trigger, and then we have created an AFTER SERVERERROR trigger, the body of which uses conditional compilation.

Note: the reason for using a trigger ON SCOTT.SCHEMA instead of a trigger ON DATABASE is because we want that test trigger to apply only to sessions of SCOTT, rather than to every session database-wide.

The trigger is enabled and valid; its compilation settings may be retrieved from the DBA_PLSQL_OBJECT_SETTINGS view, and of course we may use the DBMS_PREPROCESSOR package to get its post-processed text, i.e. its source text after pre-compilation has been carried out using its stored compilation settings.

SQL> select object_name, object_type, status, last_ddl_time
       from dba_objects
      where owner = 'TRG$OWNER';

OBJECT_NAME              OBJECT_TYPE    STATUS    LAST_DDL_TIME
________________________ ______________ _________ ______________________
TEST_SERVERERROR_TRIG    TRIGGER        VALID     2024-02-10 16:31:56

1 row selected.

SQL> select owner, trigger_name, status,
            trigger_type, base_object_type
       from dba_triggers
      where owner = 'TRG$OWNER';

OWNER        TRIGGER_NAME             STATUS     TRIGGER_TYPE    BASE_OBJECT_TYPE
____________ ________________________ __________ _______________ ___________________
TRG$OWNER    TEST_SERVERERROR_TRIG    ENABLED    AFTER EVENT     SCHEMA

1 row selected.

SQL> select name, type, plsql_ccflags
       from dba_plsql_object_settings
      where owner = 'TRG$OWNER';

NAME                     TYPE       PLSQL_CCFLAGS
________________________ __________ ________________
TEST_SERVERERROR_TRIG    TRIGGER    is_xxx:true

1 row selected.

SQL> set serveroutput on

SQL> begin
         dbms_preprocessor.print_post_processed_source(
             object_type => 'TRIGGER',
             schema_name => 'TRG$OWNER',
             object_name => 'TEST_SERVERERROR_TRIG'
         );
     end;
     /

begin
    dbms_output.put(
        'This is "' || coalesce( 'TEST_SERVERERROR_TRIG' , 'test_servererror_trig') || '"'

         || ' (unit type: ' ||  'TRIGGER'        || ')'

         || ', '
    );

    dbms_output.put('with the xxx option.');



    dbms_output.new_line;
exception
    when others then null;
end;

PL/SQL procedure successfully completed.

Meanwhile, as SCOTT:
(tested in SQL*Plus 19.17)

SQL> set serveroutput on

SQL> select 1/0 as div_by_zero from dual;
select 1/0 as div_by_zero from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

This is "test_servererror_trig" (unit type: ANONYMOUS BLOCK), WITHOUT the xxx option.

SQL> alter session set plsql_ccflags="is_xxx:true";

Session altered.

SQL> select 1/0 as div_by_zero from dual;
select 1/0 as div_by_zero from dual
        *
ERROR at line 1:
ORA-01476: divisor is equal to zero

This is "test_servererror_trig" (unit type: ANONYMOUS BLOCK), with the xxx option.

Surprise! From the readout it looks as though the body of the trigger was treated as an anonymous block:

  • $$PLSQL_UNIT_TYPE was equal to ANONYMOUS BLOCK, which is the documented value for non-DML triggers (doc: link)
  • $$PLSQL_UNIT was NULL, which is consistent with the body being treated as an anonymous blockā€”but not so clearly documented

And most importantly, the value of PLSQL_CCFLAGS set at compile time was ignored! Instead, the value of the PLSQL_CCFLAGS parameter in the current session was used.

(Hence the text returned by DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE was mostly irrelevant.)

What really happened here becomes a lot clearer if we look at the raw SQL trace of the test session:

*** 2024-02-10T16:24:12.145923+01:00 (PDB_RVA(3))
=====================
PARSING IN CURSOR #140271791199872 len=35 dep=0 uid=80 oct=3 lid=80 tim=986354597985 hv=4010842344 ad='6c68bb58' sqlid='fu59mg3rj1678'
select 1/0 as div_by_zero from dual
END OF STMT
PARSE #140271791199872:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=986354597984
EXEC #140271791199872:c=0,e=37,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=986354598097
FETCH #140271791199872:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=986354598141
STAT #140271791199872 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL  (cr=0 pr=0 pw=0 str=1 time=0 us cost=2 size=0 card=1)'
=====================
PARSING IN CURSOR #140271790069824 len=450 dep=1 uid=132 oct=47 lid=132 tim=986354599876 hv=418862802 ad='61305160' sqlid='7spb898cgfpqk'
begin
    dbms_output.put(
        'This is "' || coalesce($$PLSQL_UNIT, 'test_servererror_trig') || '"' 
        $if dbms_db_version.version >= 12 $then 
         || ' (unit type: ' || $$PLSQL_UNIT_TYPE || ')' 
        $end 
         || ', '
    );
   $if $$is_xxx $then
    dbms_output.put('with the xxx option.');
   $else
    dbms_output.put('WITHOUT the xxx option.');
   $end
    dbms_output.new_line;
exception
    when others then null;
end;

END OF STMT
PARSE #140271790069824:c=0,e=1655,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=986354599875
EXEC #140271790069824:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=1,plh=0,tim=986354599956
CLOSE #140271790069824:c=0,e=5,dep=1,type=0,tim=986354599978

The above excerpt makes it perfectly clear that the body of the trigger was parsed at run time, as an anonymous block.

(dep=1 means that is a recursive call, as opposed to dep=0; mis=1 means that this statement was not found is the library cache, so this is going to be a hard parse.)

Bottom line:

  • Non-DML triggers appear to be always soft-parsed, similarly to anonymous blocks
  • Consequently, conditional compilation in the body of non-DML triggers happens at run time, using the settings of the triggering session
  • And therefore, if you expect sections of code using conditional compilation in non-DML triggers to be determined at compile time, such sections must be put into a package, or into a stored procedure/function.

Further, knowing that the body of non-DML triggers will be soft-parsed, the following mention in the documentation (section 9.10.1 Trigger Size Restriction; link) suddenly makes a lot of sense, even today:

If the logic for your trigger requires much more than 60 lines of PL/SQL source text, then put most of the source text in a stored subprogram and invoke the subprogram from the trigger.

Regards,

Comments
Post Details
Added on Feb 10 2024
0 comments
59 views