RE: invalid procedure/trigger

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Thu, 09 Jan 2003 06:08:47 -0800
Message-ID: <F001.0052B1D4.20030109060847@fatcity.com>

Errata..

>>steps (1. Find Invalid 2. Compile Invalid)
>>until all the packages are invalid.

It should have been...
until all the objects become valid.

Regards,
Charu
  -----Original Message-----
  From: Charu Joshi [mailto:joshic_at_mahindrabt.com]   Sent: Thursday, January 09, 2003 1:55 PM   To: 'ORACLE-L_at_fatcity.com'
  Subject: RE: invalid procedure/trigger

  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: root_at_fatcity.com [mailto:root_at_fatcity.com]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: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of
shuan.tay(PCI¾G¸R³Ô)
      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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Charu Joshi
  INET: joshic_at_mahindrabt.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Thu Jan 09 2003 - 08:08:47 CST