whatinvalid

What EBusiness Suite Database Objects are INVALID --  Summary

Example: 

#---------------------------------------------------------------#

#- What (whatinvalid)                                          -#

#---------------------------------------------------------------#

                 OATC (oratest) Apps Invalid Objects           Page:   1

                  whatinvalid -- Sorted by Schema Name

Owner

Schema     Object Type               Count

---------- -------------------- ----------

APPS       FUNCTION                      2

           PACKAGE                       6

           PACKAGE BODY                  6

           PROCEDURE                     5

           SYNONYM                       1

           TRIGGER                       3

           VIEW                          2

**********                      ----------

Total                                   25

#!/usr/bin/ksh

#whatinvalid ()

#-----------------------------------------------------------------------#

#    Module Name:  whatinvalid   (c) May 2012                           #

#                                                                       #

#    Purpose:      Report on the Invalid Objects by Schama.             #

#                                                                       #

#    Maintenance                                                        #

#    Date          Author        Description                            #

#    -----------   ------------  -------------------------------------  #

#    10-Oct-2009   M.Barone      Module design/creation                 #

#-----------------------------------------------------------------------#

{

        DIRNAM=$(dirname $0)

        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')

        HNAME=$(uname -n)

        #---------------------------------------------------------------#

        #  TEST:  Number ($#) of arguments passed to this function?     #

        #---------------------------------------------------------------#

        if      [ $# = 0 ]

        then

            clear

            echo

            echo  " #--------------------------------------------------#"

            echo  " # whatinvalid:             Oracle Apps Forms Users #"

            echo  " #--------------------------------------------------#"

            echo                                                              

            #-----------------------------------------------------------#

            #  Trap: Set CNTL-C CNTL-D CNTL-\Before Password Protection #

            #-----------------------------------------------------------#

            trap        "stty echo; return"     2                              

            trap        "stty echo; return"     3

            stty -echo

            read PASSWD?" Please Enter the Oracle APPS Passwd:  "

            stty echo

            echo

            #------------------------------------------------------------#

            #  Trap: Reset CNTL-C CNTL-D CNTL-\After Password Protection #

            #------------------------------------------------------------#

            trap        2

            trap        3

            clear

        else

            PASSWD=$1

            clear

        fi

#-----------------------------------------------------------------------#

#  Initialize                                                           #

#-----------------------------------------------------------------------#

        SPOOLNM=/tmp/whatinvalid_$$.txt

#-----------------------------------------------------------------------#

#  SQL*Plus ("Here-Document")                                           #

#-----------------------------------------------------------------------#

sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  APPS/$PASSWD

        SPOOL ${SPOOLNM}

        clear   BREAKS

        clear   BUFFER

        clear   COLUMNS

        clear   COMPUTES

        clear   SCREEN

        clear   SQL

        clear   TIMING

        set     feedback        off

        set     flush           on

        set     heading         on

        set     pause           off

        set     space           1

        set     termout         on

        set     verify          on

        set     linesize        80

        set     pagesize        999

        prompt #--------------------------------------------------------#

        prompt #- What (whatinvalid)                                   -#

        prompt #--------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR

        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        BREAK   ON  REPORT

        BREAK   ON  owner          skip 2       ON REPORT

        COMPUTE SUM LABEL 'Total'  OF  objcnt   ON OWNER

        COMPUTE SUM LABEL 'Total'  OF  objcnt   ON REPORT

        COLUMN  OWNER           format  A10     Heading "Owner|Schema"

        COLUMN  OBJECT_NAME     format  A40     Heading "Object Name"

        COLUMN  OBJECT_TYPE     format  A20     Heading "Object Type"

        COLUMN  OBJCNT          format  999999999       Heading "Count"  

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps Invalid Objects'      -

                RIGHT   'Page:'         FORMAT 999 SQL.pno       skip 1  -

                CENTER  'whatinvalid -- Sorted by Schema Name'   skip 2

        alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

        SELECT  DISTINCT owner, object_type, count(1) objcnt

          FROM  dba_objects

         WHERE  STATUS <>  'VALID'

        GROUP   BY owner, object_type

        ORDER   BY 1,2;

END_FILE

print

print  "Please see  /tmp/whatinvalid_$$.txt"

print

}