capturing DDL change with a trigger

[geos]

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;

[Michel Cadot]

"geos" <ge...@SPAMPRECZ.autograf.pl> a écrit dans le message de news: lhhmpa$u0i$1...@news.task.gda.pl...

Your "when others clause is just silly.
Remove it.
Read http://www.orafaq.com/wiki/WHEN_OTHERS

Regards
Michel

[geos]

On 02.04.2014 22:31, Michel Cadot wrote:

> Your "when others clause is just silly.
> Remove it.
> Read http://www.orafaq.com/wiki/WHEN_OTHERS

Thanks, this is secondary issue for me now, I'll correct it later on. Do
you know why the trigger doesn't store the DDL code "after alter" statement?

thank you,
geos

[ddf]

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

[geos]

On 03.04.2014 16:48, ddf wrote:
> The trigger won't capture that change because it's not posted until the alter table statement has completed successfully.

Thanks David. So when does alter table is considered completed
sucessfully? I'm kinda puzzled because the trigger is named AFTER ALTER
and I thought it would fire after the ALTER is completed, not soemewhere
in between... If the following is not true:

-- implicit commit
-- DDL statement
-- implicit commit

(here I thought DDL "was updated" in dictionary)

-- after this DDL trigger starts and is able to see the changed DDL

then what is?

thank you,
geos

[ddf]

It's after the ALTER executes but before the final implicit commit.


David Fitzjarrell

[geos]

On 07.04.2014 16:51, ddf wrote:
> It's after the ALTER executes but before the final implicit commit.

thanks David!

[joel garry]

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
--
@home.com is bogus.
http://www.theregister.co.uk/2014/04/08/mongodb_major_release/

[ddf]

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

[fvil...@gmail.com]

Hi. I have in a similar situation now. Did you find a way to do it? I would thank you any help you could provide.

Thanks in advance.

Regards, Francisco.