Re: invalid procedure/trigger

[shuan.tay\(PCI鄭祺勛\)]

Thanks to all. It works!   Have a nice day. ----- Original Message ----- From: Djordje Jankovic To: Multiple recipients of list ORACLE-L Sent: Thursday, January 09, 2003 11:33 AM Subject: Re: invalid procedure/trigger 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.