Re: invalid procedure/trigger

[Djordje Jankovic]

To check for invalid objects: select object_type, owner, object_name   from dba_objects  where object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION')    AND OWNER IN ('MyOwner1','MyOwner2')    and status = 'INVALID'   order by decode(object_type,'PACKAGE',0,                               'PACKAGE BODY',1,                               'FUNCTION', 2,                               'PROCEDURE',3,                               'TRIGGER',4                               5), owner, object_name; To create sql to recompile the invalid ones:   select 'alter '        || decode(object_type,'PACKAGE BODY','package',lower(object_type))        || ' ' || owner||'.'||object_name || ' compile '        || decode(object_type,'PACKAGE BODY','body;',';')   from dba_objects  where object_type in ('PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION')    AND OWNER IN ('MyOwner1','MyOwner2')    and status = 'INVALID'   order by decode(object_type,'PACKAGE',0,                               'PACKAGE BODY',1,                               'FUNCTION', 2,                               'PROCEDURE',3,                               'TRIGGER',4                               5), owner, object_name;   Djordje ----- Original Message ----- From: shuan.tay(PCI嚙瘦嚙磋嚙踝蕭) To: Multiple recipients of list ORACLE-L Sent: Wednesday, January 08, 2003 9:58 PM Subject: invalid procedure/trigger Dear all DBAs,   Is there any way to check whether the procedures or triggers is valid? and if it's not valid, recompile it automatically. Some of the DBAs here always forgot to check procedures/triggers after modify table.   Thanks in advance.