Re: capturing DDL change with a trigger

From: ddf <oratune_at_msn.com>
Date: Thu, 3 Apr 2014 07:48:12 -0700 (PDT)
Message-ID: <4b4f0068-3118-4833-b38a-8d3978a1fd31_at_googlegroups.com>

On Wednesday, April 2, 2014 1:01:02 PM UTC-6, geos wrote:
> Hi!
>
>
>
> For a few tables I wanted to store a DDL statement which led to a table
>
> change. I created after DDL trigger which runs after ALTER statement. It
>
> works and captures DDL code, but it is previous version of the code, not
>
> "new" version.
>
>
>
> I thought DDL statement worked like this:
>
>
>
> -- implicit commit
>
> -- DDL statement
>
> -- implicit commit
>
> (here I thought DDL "is updated" in dictionary)
>
> -- after this DDL trigger starts and is able to see the changed DDL
>
>
>
> But it looks like not working that way (?). What am I missing? Could you
>
> give me some hints or explanation of this behaviour?
>
>
>
> thank you,
>
> geos
>
>
>
> --
>
>
>
> create or replace trigger trg
>
> after alter on scott.schema
>
> declare
>
> begin
>
> if dictionary_obj_type in ('TABLE') then
>
>
>
> insert into scott.repo
>
> (owner, object_name, object_type, crt_dt, last_dt, def_code)
>
> select owner, object_name, object_type, created
>
> , last_ddl_time, dbms_metadata.get_ddl(dictionary_obj_type,
>
> dictionary_obj_name, dictionary_obj_owner)
>
> from all_objects
>
> where object_type = dictionary_obj_type
>
> and object_name = dictionary_obj_name
>
> and owner = dictionary_obj_owner;
>
> end if;
>
>
>
> exception
>
> when others then raise_application_error(-20000, sqlerrm);
>
> end;

The DDL extracted by dbms_metadata.get_ddl captures the create table statement BEFORE that text has been changed. If you run a similar statement after the table is altered and the implicit commit is executed you will get the modified CREATE statement:

SQL> alter table emp add mazupo varchar2(12);

Table altered.

SQL>
SQL> desc emp

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 EMPNO                                                                               NOT NULL NUMBER(4)
 ENAME                                                                                        VARCHAR2(10)
 JOB                                                                                          VARCHAR2(9)
 MGR                                                                                          NUMBER(4)
 HIREDATE                                                                                     DATE
 SAL                                                                                          NUMBER(7,2)
 COMM                                                                                         NUMBER(7,2)
 DEPTNO                                                                                       NUMBER(2)
 MAZUPO                                                                                       VARCHAR2(12)

SQL>

SQL> set long 100000
SQL> select owner, object_name, object_type, created

  2       , last_ddl_time, dbms_metadata.get_ddl('TABLE', 'EMP', 'GRIBNAUT') last_ddl
  3     from all_objects
  4     where object_type = 'TABLE'
  5     and object_name = 'EMP'
  6     and owner = 'GRIBNAUT';

OWNER                          OBJECT_NAME                    OBJECT_TYPE         CREATED   LAST_DDL_
------------------------------ ------------------------------ ------------------- --------- ---------

LAST_DDL

---

GRIBNAUT                       EMP                            TABLE               03-APR-14 03-APR-14

  CREATE TABLE "GRIBNAUT"."EMP"

   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),

OWNER                          OBJECT_NAME                    OBJECT_TYPE         CREATED   LAST_DDL_
------------------------------ ------------------------------ ------------------- --------- ---------

LAST_DDL

---

        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
        "MAZUPO" VARCHAR2(12)

   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

OWNER                          OBJECT_NAME                    OBJECT_TYPE         CREATED   LAST_DDL_
------------------------------ ------------------------------ ------------------- --------- ---------

LAST_DDL

---

  TABLESPACE "USERS" SQL>
SQL> select * from repo;

OWNER                          OBJECT_NAME                         OBJECT_TYPE                         CRT_DT    LAST_DT
------------------------------ ----------------------------------- ----------------------------------- --------- ---------

DEF_CODE

---

GRIBNAUT                       EMP                                 TABLE                               03-APR-14 03-APR-14

  CREATE TABLE "GRIBNAUT"."EMP"

   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),

OWNER                          OBJECT_NAME                         OBJECT_TYPE                         CRT_DT    LAST_DT
------------------------------ ----------------------------------- ----------------------------------- --------- ---------

DEF_CODE

---

        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)

   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   TABLESPACE "USERS"

OWNER                          OBJECT_NAME                         OBJECT_TYPE                         CRT_DT    LAST_DT
------------------------------ ----------------------------------- ----------------------------------- --------- ---------

DEF_CODE

---

SQL> The trigger won't capture that change because it's not posted until the alter table statement has completed successfully.

David Fitzjarrell Received on Thu Apr 03 2014 - 16:48:12 CEST