Re: capturing DDL change with a trigger

From: ddf <oratune_at_msn.com>
Date: Thu, 10 Apr 2014 07:09:10 -0700 (PDT)
Message-ID: <8b1264e0-a767-43d3-9a45-22cae38765f2_at_googlegroups.com>

On Tuesday, April 8, 2014 3:03:00 PM UTC-6, joel garry wrote:
> On Monday, April 7, 2014 7:51:04 AM UTC-7, ddf wrote:
>
> >
>
> >
>
> >
>
> > It's after the ALTER executes but before the final implicit commit.
>
> >
>
> > David Fitzjarrell
>
>
>
> Could it be autonomous? https://community.oracle.com/thread/3542656
>
>
>
> jg
>
> --
>
> _at_home.com is bogus.
>
> http://www.theregister.co.uk/2014/04/08/mongodb_major_release/

No:

SQL> create table repo(

  2          owner varchar2(30),
  3          object_name varchar2(35),
  4          object_type varchar2(35),
  5          crt_dt  date,
  6          last_dt date,
  7          def_code varchar2(4000));

Table created.

SQL>
SQL> create or replace trigger trg
  2 after alter on gribnaut.schema
  3 declare
  4 pragma autonomous_transaction;   5 begin
  6 if sys.dictionary_obj_type in ('TABLE') then   7

  8     insert into gribnaut.repo
  9       (owner, object_name, object_type, crt_dt, last_dt, def_code)
 10     select owner, object_name, object_type, created
 11       , last_ddl_time, dbms_metadata.get_ddl(sys.dictionary_obj_type, sys.dictionary_obj_name, sys.dictionary_obj_owner)
 12     from all_objects
 13     where object_type = sys.dictionary_obj_type
 14     and object_name = sys.dictionary_obj_name
 15     and owner = sys.dictionary_obj_owner;

 16
 17 end if;
 18
 19 exception
 20 when others then raise_application_error(-20000, sqlerrm);  21 end;
 22 /

Trigger created.

SQL>
SQL> show errors trigger trg
No errors.
SQL>
SQL> alter table emp add mazupo varchar2(12); alter table emp add mazupo varchar2(12)
*
ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1
ORA-20000: ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at line 18

SQL> David Fitzjarrell Received on Thu Apr 10 2014 - 16:09:10 CEST