Oracle-L: RE: invalid procedure/trigger

From: Bernardus Deddy Hoeydiono <deddy_at_sdi.co.id>
Date: Wed, 08 Jan 2003 20:18:43 -0800
Message-ID: <F001.0052AF3B.20030108201843@fatcity.com>

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 '||

ecode( 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.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Bernardus Deddy Hoeydiono
INET: deddy_at_sdi.co.id

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 Wed Jan 08 2003 - 22:18:43 CST