Monday, July 6, 2009

Audit DDL with a BEFORE DDL Trigger.

create table DDL_AUDIT
(audit_date date,
username varchar2(30),
instance_number integer,
database_name varchar2(9),
object_type varchar2(19),
object_owner varchar2(30),
object_name varchar2(128),
sql_text varchar2(2000));



CREATE OR REPLACE TRIGGER BEFORE_DDL_TRG BEFORE DDL ON DATABASE
DECLARE
l_sql_text ora_name_list_t;
l_count NUMBER;
l_puser VARCHAR2(30) := NULL;
l_sql varchar2(2000);
BEGIN
l_count := ora_sql_txt(l_sql_text);
l_puser := SYS_CONTEXT('USERENV', 'KZVDVCU');

l_count := ora_sql_txt(l_sql_text);
for i in 1..l_count
loop
l_sql := l_sql||l_sql_text(i);
end loop;

insert into ddl_audit (audit_date, username, instance_number, database_name, object_type, object_owner, object_name, sql_text)
values (sysdate, l_puser,ora_instance_num,ora_database_name,ora_dict_obj_type,ora_dict_obj_owner,ora_dict_obj_name,l_sql);

exception
when others then
null;
END;
/
show errors;

#### or if you want screen output.

CREATE OR REPLACE TRIGGER BEFORE_DDL_TRG BEFORE DDL ON DATABASE
DECLARE
l_sql_text ora_name_list_t;
l_count NUMBER;
l_puser VARCHAR2(30) := NULL;
BEGIN
l_count := ora_sql_txt(l_sql_text);
l_puser := SYS_CONTEXT('USERENV', 'KZVDVCU');

dbms_output.put_line(l_puser);
dbms_output.put_line(ora_instance_num);
dbms_output.put_line(ora_database_name);
dbms_output.put_line(ora_dict_obj_type);
dbms_output.put_line(ora_dict_obj_owner);
dbms_output.put_line(ora_dict_obj_name);

l_count := ora_sql_txt(l_sql_text);
for i in 1..l_count
loop
dbms_output.put_line(l_sql_text(i));
end loop;

exception
when others then
null;
END;
/
show errors;