RE: invalid procedure/trigger

[Charu Joshi]

Depending on the interdependencies among the modules, you MAY have to run the creator script and the created script several times before all the objects become 'VALID'.   I think it would be better to write a PL/SQL script which repeats the two steps (1. Find Invalid  2. Compile Invalid) until all the packages are invalid. You can use NDS to ALTER..COMPILE.   Good luck, Charu. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bernardus Deddy Hoeydiono Sent: Thursday, January 09, 2003 4:37 AM To: Multiple recipients of list ORACLE-L Subject: RE: invalid procedure/trigger Hi Shuan,   Have known the Third party of oracle from Quest Software , The product name is TOAD. Using this application you can check the valid or invlaid the procedure and also you can recompile it. Or, you can create the script to check function, procedure,view,and trigger from table dba_objects.           set termout off         set feed off         set heading off         set echo off         set verify off         set linesize 180         set pagesize  0           spool alter_all_invalid_list.sql           select 'promp '|| object_name||chr(10)||'ALTER '|| decode( OBJECT_TYPE,'PACKAGE BODY','PACKAGE',OBJECT_TYPE) ||' ' ||object_name||' COMPILE;'           from user_objects         where status ='INVALID'         and upper(object_name) != 'DEBUG'         order by object_type, object_name;           prom exit           spool off           exit     And create the batch file :     sqlplus username/password @alter_a;;_invalid_list.sql   The script above you can run anytime or you can put into the scheduller which running every night.     Thank's   Bernardus Deddy Hoeydiono.   -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of shuan.tay(PCI嚙瘦嚙磋嚙踝蕭) Sent: Thursday, January 09, 2003 9:59 AM To: Multiple recipients of list ORACLE-L 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. ********************************************************* Disclaimer This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ********************************************************* Visit us at http://www.mahindrabt.com