Thanks for posting this, I have a few questions. Why you need to look at the SQL
statement at all. You already know that the statement is ALTER TABLE, so why not
just check for dropped columns.
if ora_dict_obj_type = 'TABLE' THEN
if ora_dict_obj_type = 'ALTER' THEN
for x in get_column_name loop
if ora_is_drop_column (x.column_name) then
object_security.record_destructive_ddl (x.column_name);
end if;
end loop;
else
...
Is it a performance thing? Is it faster to retrieve the SQL all the time and
only check for dropped columns when you have to?
And Is that also the reason for excluding the SYS schema?
chaim
"MacGregor, Ian A." <[EMAIL PROTECTED]> on 06/11/2001 07:18:21 PM
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: Chaim Katz/Completions/Bombardier)
CREATE or replace TRIGGER record_destructive_ddl_trigger before drop or alter
or truncate ON DATABASE
Declare
pcolumn_name dba_tab_columns.column_name%type;
cursor get_column_name is
select column_name from dba_tab_columns where
owner = ora_dict_obj_owner and table_name = ora_dict_obj_name;
sql_dummy varchar2(1);
cursor is_drop_column is
select
'x'
from
sys.x$_kglpn p,
sys.x$_kglcursor c,
v$session s
where
p.kglpnhdl = c.kglhdadr and
p.kglpnses = s.saddr and
instr(lower(replace(c.kglnaobj,' ', null)),'select') = 0 and
instr(lower(replace(c.kglnaobj,' ', null)),'dropcolumn') > 0 and
s.audsid = sys_context('USERENV', 'SESSIONID');
Begin
if (((ora_sysevent = 'DROP') and (ora_dict_obj_type = 'TABLE'))
or ora_sysevent = 'TRUNCATE') and ora_dict_obj_owner != 'SYS' THEN
object_security.record_destructive_ddl(null);
elsif ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE'
and ora_dict_obj_owner != 'SYS' THEN
open is_drop_column;
fetch is_drop_column into sql_dummy;
close is_drop_column;
if sql_dummy = 'x' then
open get_column_name;
loop
fetch get_column_name into pcolumn_name;
exit when get_column_name%notfound;
if ora_is_drop_column(pcolumn_name) then
object_security.record_destructive_ddl(pcolumn_name);
end if;
end loop;
end if;
close get_column_name;
end if;
end;
/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
