oracle DDL trigger

Question

below is my query. I want to get the column that are newly created in table for audit purpose.So, I have a created a DDL trigger to capture the event. But the trigger is not working for added column.Drop column event is captured properly but not add column. Can anyone pls help me on this? I tried with 'AFTER alter ON SCHEMA' tats also not working.

CREATE OR REPLACE TRIGGER trg_test
   BEFORE CREATE OR ALTER OR DROP ON SCHEMA
DECLARE
   CURSOR get_column_name(i_owner IN VARCHAR2,i_obj_name IN VARCHAR2)
   IS
      SELECT column_name
        FROM all_tab_columns
       WHERE owner = i_owner AND table_name = i_obj_name;

--   pcolumn_name   dba_tab_columns.column_name%TYPE;
BEGIN
   IF (    ora_dict_obj_type = 'TABLE'
       AND ora_sysevent IN ('ALTER')
       AND ora_dict_obj_owner = 'SYS'
      )
   THEN
      FOR x IN get_column_name(ora_dict_obj_owner ,ora_dict_obj_name)
      LOOP
         IF ora_is_drop_column (x.column_name)
         THEN
            dbms_output.put_line('Deleted column :' || x.column_name);
         END IF;
         IF ora_is_alter_column (x.column_name)
         THEN
            dbms_output.put_line('Newly added column :' || x.column_name);
         END IF;
      END LOOP;

   END IF;
END;
/

Answer 1

Actually, the trigger gets fired, the only problem is that Oracle does not have an ora_is_new_column function or something like that. At least I could not find anything...

For my test case, the ora_is_alter_column worked propery when I executed an 'alter table aaa modify (colbbb...)'.

The best substitution I could come up with was to log the 'alter table statements':

CREATE OR REPLACE TRIGGER ddl_trigger
BEFORE ALTER ON SCHEMA
DECLARE
 v_sql_text_list  dbms_standard.ora_name_list_t;
 v_sql_text_count binary_integer;
BEGIN
  v_sql_text_count := ora_sql_txt(v_sql_text_list);
  for v_i in 1..v_sql_text_count 
  loop
    dbms_output.put_line('sqltext(' || v_i || ')=' || v_sql_text_list(v_i));
  end loop;
END ddl_trigger;
/