Stack Exchange
During the trigger execution getting error : ORA-00604: error occurred at recursive SQL level 1 ORA-00900: invalid SQL statement
When I am trying to execute this trigger getting following error:
CREATE OR REPLACE TRIGGER create_table_trigger AFTER CREATE ON SCHEMA DECLARE
    lv_sql     VARCHAR2(5000);
   obj_name   VARCHAR2(500);
BEGIN
    obj_name := sys.dictionary_obj_name;
    lv_sql := 'EXEC SYS.DBMS_FGA.add_policy (object_schema     => ''AIM_DBA'',object_name => '''|| obj_name|| ''',policy_name => ''PROTECT_'|| obj_name || ''',audit_condition  => null,audit_column => NULL,handler_schema => ''AIM_DBA'',handler_module =>''WHERE_CLAUSE_PROTECTOR_PKG.TABLE_PROTECTOR'',enable => TRUE,statement_types => ''UPDATE,DELETE'')';
    IF
        sys.dictionary_obj_type = 'TABLE'
    THEN
        dbms_output.put_line(obj_name);
        dbms_output.put_line(lv_sql);
        EXECUTE IMMEDIATE lv_sql;
        dbms_output.put_line(lv_sql);
    END IF;

END;
/

Execution error Message:
SQL> create table aim_dba.WHERE_TST27
(
    dummy varchar2(20)
);  2    3    4
WHERE_TST27
EXEC SYS.DBMS_FGA.add_policy (object_schema     => 'AIM_DBA',object_name => 'WHERE_TST27',policy_name => 'PROTECT_WHERE_TST27',audit_condition  => null,audit_column => NULL,handler_schema =>
'AIM_DBA',handler_module =>'WHERE_CLAUSE_PROTECTOR_PKG.TABLE_PROTECTOR',enable => TRUE,statement_types => 'UPDATE,DELETE')
create table aim_dba.WHERE_TST27
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'AIM_DBA.CREATE_TABLE_TRIGGER'
ORA-00604: error occurred at recursive SQL level 1
ORA-00900: invalid SQL statement
ORA-06512: at line 17*

Purpose of Trigger: Whenever new object create Audit policy would get enabled for that object with this trigger.

EXEC is a SQL*Plus command, not an SQL statement.

I have used  Begin/End instead exec  now getting different error:

ORA-04022: nowait requested, but had to wait to lock dictionary object

The trigger is firing within the DDL create table transaction. Your policy creation can't run until the DDL is implicitly committed and the dictionary lock is released. I don't think you can do this in a trigger.
