I'm observing the following with regards to PLSQL_CCFLAGS and "AFTER LOGON ON DATABASE" triggers:
* Compiled PLSQL_CCFLAGS setting is not honored
* Code that runs is not necessarily what is shown in dba_plsql_object_settings
* Doesn't matter if the trigger is owned by SYS or another schema in 11g - does in 19c
* It IS however affected by the system level (i.e. initialization parameter) value of PLSQL_CCFLAGS - but only this

I can repeat this test case in 11.2.0.4.0 through 19.6.0.0.0.

If I use SYS as the owner of the LOGON trigger, things are a little different in 19c - but no different in 11g. For the demo/test-cases I'll use SYS but if you change to SYSTEM in 19c you can see things change a litte.

---

TEST 1: Create a simple table based ON INSERT trigger to prove that PLSQL_CCFLAGS are indeed honored and works as expected for an ON INSERT trigger:

create table system.my_results (dt timestamp, msg varchar2(20));

alter session set plsql_ccflags = 'foobar:true';

create or replace trigger system.my_tab_trigger
before insert on system.my_results
referencing new as new for each row
begin
   $if $$foobar $then
      :new.msg := 'foobar is true';
   $else
      :new.msg := 'foobar is false';
   $end
end;
/

We can double check the compiled settings with:

select plsql_ccflags from dba_plsql_object_settings where name = 'MY_TAB_TRIGGER';
set serveroutput on size unlimited
begin
  dbms_preprocessor.print_post_processed_source (
    object_type => 'TRIGGER',
    schema_name => 'SYSTEM',
    object_name => 'MY_TAB_TRIGGER');
end;
/

And we can do a test and prove from the data that it's working as expected:

delete from system.my_results;
insert into system.my_results values (systimestamp, null);
select msg from system.my_results;

Actual output:

SQL> select msg from system.my_results;

MSG
--------------------
foobar is true

SQL>

Result includes: "foobar is true" <---- expected result, things working as expected so far

Clean-up:

drop table system.my_results;

---

TEST 2: Similar test but using a LOGON ON DATABASE trigger instead:

create table system.my_results (dt timestamp, msg varchar2(20));

alter session set plsql_ccflags = 'foobar:true';

create or replace trigger sys.my_logon_trig after logon on database enable
begin
   $if $$foobar $then
      insert into system.my_results values (systimestamp, 'foobar is true');
   $else
      insert into system.my_results values (systimestamp, 'foobar is false');
   $end
end;
/

Again, we can double check the compiled settings with:

select plsql_ccflags from dba_plsql_object_settings where name = 'MY_LOGON_TRIG';
set serveroutput on size unlimited
begin
  dbms_preprocessor.print_post_processed_source (
    object_type => 'TRIGGER',
    schema_name => 'SYS',
    object_name => 'MY_LOGON_TRIG');
end;
/

-- Confirm that the plsql_ccflags system parameter isn't set
select value from v$system_parameter2 where name = 'plsql_ccflags';

Sample output from the above queries:

SQL> select plsql_ccflags from dba_plsql_object_settings where name = 'MY_LOGON_TRIG';

PLSQL_CCFLAGS
--------------------------------------------------------------------------------
foobar:true

SQL> set serveroutput on size unlimited
SQL> begin
  2    dbms_preprocessor.print_post_processed_source (
  3      object_type => 'TRIGGER',
  4      schema_name => 'SYS',
  5      object_name => 'MY_LOGON_TRIG');
  6  end;
  7  /
begin
insert into system.my_results values (systimestamp, 'foobar is true');
end;

PL/SQL procedure successfully completed.

SQL> select value from v$system_parameter2 where name = 'plsql_ccflags';

VALUE
--------------------------------------------------------------------------------


SQL>

So everything looks good, and it looks like from the above validations that the trigger is compiled with "foobar is true" and everything is good.

But when we test it, we get unexpected results:

delete from system.my_results;
connect / as sysdba
select msg from system.my_results;

Actual output:

SQL> select msg from system.my_results;

MSG
--------------------
foobar is false

SQL>

Result includes: "foobar is false" <---- NOT THE EXPECTED RESULT

---

Recompiling while specifying plsql_ccflags makes no difference:

-- Doing this makes no difference to the LOGON ON DATABASE test case above:
alter session set plsql_ccflags = 'foobar:true';
alter trigger sys.my_logon_trig compile;

-- Also doing this makes no difference:
alter trigger sys.my_logon_trig compile plsql_ccflags = 'foobar:true' reuse settings;

In all cases, regardless of whether the LOGON trigger is owned by SYS or SYSTEM and regardless of what it says in dba_plsql_object_settings or from dbms_preprocessor.print_post_processed_source, the results is that the trigger runs with plsql_ccflags = 'foobar:false' (or to be more accurate, foobar != TRUE)

The only way to get it to work is by adjusting plsql_ccflags at the system level. Example:

alter system set plsql_ccflags = 'foobar:true';

delete from system.my_results;
connect / as sysdba
select msg from system.my_results;

Changing at the system level does work and does produce the desired/expected output:

SQL> select msg from system.my_results;

MSG
--------------------
foobar is true

SQL>

That is the expected output. But that was only achieved by setting plsql_ccflags='foobar:true' at the SYSTEM level.

Cleanup:

drop trigger sys.my_logon_trig;
drop table system.my_results;

Hence the question is: why do we get this unexpected behaviour from the LOGON trigger only? The table scoped trigger in the first example worked as expected but the logon on database trigger did not. The latter seemed to ignore the settings as shown in dba_plsql_object_settings and dbms_preprocessor.print_post_processed_source ?

Thanks

SQL> alter session set plsql_ccflags = 'foobar:true';

Session altered.

SQL> create or replace procedure system.my_logon_trig_proc is
  2  begin
  3     $if $$foobar $then
  4        insert into system.my_results values (systimestamp, 'foobar is true');
  5     $else
  6        insert into system.my_results values (systimestamp, 'foobar is false');
  7     $end
  8  end;
  9  /

Procedure created.

SQL>
SQL> create or replace trigger system.my_logon_trig after logon on database enable
  2  begin
  3     system.my_logon_trig_proc;
  4  end;
  5  /

Trigger created.

SQL>
SQL> delete from system.my_results;

1 row deleted.

SQL> connect system/oracle@db19_pdb1
Connected.
SQL> select msg from system.my_results;

MSG
--------------------
foobar is true

1 row selected.