Helping me find the quilty SQL, which wreck my package all the time

From: Joe Bayer <joebayerii(no-spam)_at_hotmail.com>
Date: Sat, 14 Feb 2004 12:40:56 GMT
Message-ID: <sHoXb.10992$5W3.5167@nwrddc02.gnilink.net>

We are using 9i on Linux.
One of our package is always become invalid, and we have to have another crontab job running every minute to re-compile it. The funny thing is, the application will fail when calling it if the package is invalid, (Is it default that Oracle will try to re-compile it if the package is invalid, but in our case, oracle just returns an error and application will fail). Any suggestions?

I need to find the quilty sql, so I can tell developer to put one more line like 'Alter package ... compile ' in their code.

Based on Tom Kyte's suggestion, I created a trigger trying to catch the quilty sql.

create or replace trigger ddl_monitor_trigger

    before create or alter or drop on schema     declare
 l_sysevent varchar2(25);
 l_status varchar2(25);

    begin

        select ora_sysevent into l_sysevent from dual;
        select status into l_status from user_objects where object_name =
'MY_PACKAGE';
        if (l_status = 'INVALID' )
        then
               if ( l_sysevent in ('DROP','CREATE') )
               then
                    insert into system.quilty_sql (insert_time,command_type)
                    select  sysdate,ora_sysevent  from dual;
               elsif ( l_sysevent = 'ALTER' )
               then
             insert into system.quilty_sqltext
             select  sysdate, *
              from  v$open_cursor
             where upper(sql_text) like 'ALTER%' ;
  end if;
       end if;

   end;

The problem is, sql like 'ALTER TABLE bla truncate partition', 'ALTER table split partition ', etc ,etc
will not be catched into v$open_cursor, if we take a snapshot of v$sql, there will be too many potential quilty ones, and it will be difficult for me to tell which one is the real ONE. (the package dependency shows 1200 items).

Thanks for your help. Received on Sat Feb 14 2004 - 06:40:56 CST