Re: capturing DDL change with a trigger

"geos" <geos_at_SPAMPRECZ.autograf.pl> a écrit dans le message de news: lhhmpa$u0i$1_at_news.task.gda.pl...
| 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;

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

Regards
Michel Received on Wed Apr 02 2014 - 22:31:35 CEST