SQL & PL/SQL

RDBMS version: 19c

Due to special requirement, I have to create triggers dynamically from a stored procedure.

So, I am testing the basic code from an anonymous block. But, there is a compilation error while trying to execute the CREATE TRIGGER code. Any idea what the root cause is ?

create user a0user identified by Depsy#467;
grant create session, create trigger to a0user;

create user d0user identified by Stom#936;
grant create session, create table to d0user;

set serveroutput on

DECLARE
  v_str1 VARCHAR2(10000);
  v_str2 VARCHAR2(10000);
  
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
begin
   for usr in ( select username
                   from dba_users
                   where username = 'A0USER'
           )
       loop
           v_str1 := 'CREATE OR replace TRIGGER '||usr.username||'.SET_SCHEMA_TRG
                           after logon on '||usr.username||'.SCHEMA
                           begin
                           execute immediate ''ALTER SESSION SET current_schema = D0USER''
                           end';
           v_str2 := 'ALTER TRIGGER '||usr.username||'.SET_SCHEMA_TRG ENABLE' ;
           
           dbms_output.put_line(v_str1);
           dbms_output.put_line(v_str2);
           
           execute immediate v_str1;
           execute immediate v_str2;
       end loop;
   end;
end;
/

When executing the above anonymous block, I get the following error.

CREATE OR replace TRIGGER A0USER.SET_SCHEMA_TRG
                           after logon on A0USER.SCHEMA
                           begin
                           execute immediate 'ALTER SESSION
SET current_schema = D0USER'
                           end
ALTER TRIGGER A0USER.SET_SCHEMA_TRG ENABLE
ERROR:
ORA-24344: success with compilation error
ORA-06512: at line 22
ORA-06512: at line 22

Warning: PL/SQL compilation errors.

SQL> 22
22*             execute immediate v_str1;
SQL>

SQL> select status from dba_triggers where trigger_name = 'SET_SCHEMA_TRG' and owner = 'A0USER';

STATUS
--------
ENABLED

SQL> select status from dba_objects where object_name = 'SET_SCHEMA_TRG' and owner = 'A0USER';

STATUS
-------
INVALID