Re: Recording Destructive SQL Trapping "Alter Table Drop Column"

From: <Chaim.Katz_at_Completions.Bombardier.com>
Date: Tue, 12 Jun 2001 12:31:36 -0700
Message-ID: <F001.00326435.20010612123037@fatcity.com>

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." <ian_at_SLAC.Stanford.EDU> on 06/11/2001 07:18:21 PM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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: Chaim.Katz_at_Completions.Bombardier.com

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: ListGuru_at_fatcity.com (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). Received on Tue Jun 12 2001 - 14:31:36 CDT