Rem
Rem $Header: admin_recompile_invalid.sql 17-jul-2007.16:07:55 edemembe Exp $
Rem
Rem admin_recompile_invalid.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_recompile_invalid.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    edemembe    07/11/07 - Bug 6157164
Rem    edemembe    07/17/07 - Backport edemembe_bug-6157164 from main
Rem    jsadras     06/11/07 - Backport jsadras_bug-6120807 from
Rem                           st_emcore_10.2.0.3.1db11
Rem    jsadras     06/08/07 - Bug:6120807, reset package set to avoid running into 4061
Rem    jsadras     09/02/05 - Bug:4588918, recompile invalid objects 
Rem    chyu        04/20/05 - removing the logging 
Rem    chyu        04/05/05 - shortening the db logging to avoid buffer 
Rem                           overflow issues 
Rem    chyu        03/22/05 - commenting out the raise exception 
Rem    rpinnama    02/27/03 - Parameterize the user name
Rem    rpinnama    09/26/02 - Remove exit at the end
Rem    rpinnama    05/16/02 - rpinnama_reorg_rep_scripts_2
Rem    rpinnama    05/16/02 - Created
Rem

Rem 
Rem This SQL script requires the following arguments
Rem  1. Name of the repository user
Rem
DEFINE EM_REPOS_USER ="&1"

DECLARE
BEGIN

    FOR crec IN (SELECT object_name, object_type,
                   DECODE(object_type, 'TYPE',              1,
                                       'FUNCTION',          2,
                                       'PROCEDURE',         3,
                                       'TRIGGER',           4,
                                       'VIEW',              5,
                                       'PACKAGE',           6,
                                       'TYPE BODY',         7,
                                       'PACKAGE BODY',      8,
                                       'MATERIALIZED VIEW', 9,
                                                           10) object_type_seq
                   FROM all_objects
                  WHERE owner ='&EM_REPOS_USER'
                    AND status ='INVALID'
                    AND object_type IN ('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 
                                        'PROCEDURE', 'TYPE', 'TYPE BODY', 
                                        'TRIGGER', 'VIEW', 'MATERIALIZED VIEW')
                  ORDER BY object_type_seq, object_type, created)
    LOOP
       BEGIN

           IF (crec.object_type = 'PACKAGE BODY') THEN

             -- If package body is invalid, just compile the body and not
             -- the specification
             EXECUTE IMMEDIATE 'ALTER PACKAGE ' ||
                             crec.object_name || ' compile body';
           ELSIF (crec.object_type = 'TYPE')
           THEN
             -- If type spec is invalid, just compile the specification and not
             -- the body
             EXECUTE IMMEDIATE 'ALTER TYPE ' ||
                          crec.object_name || ' compile specification';
           ELSIF (crec.object_type = 'TYPE BODY')
           THEN
             -- If type body is invalid, just compile the body and not
             -- the specification
             EXECUTE IMMEDIATE 'ALTER TYPE ' ||
                          crec.object_name || ' compile body';
           ELSE
             EXECUTE IMMEDIATE 'ALTER ' || crec.object_type || ' ' ||
                             crec.object_name || ' compile';
           END IF;
       EXCEPTION
           WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE('Recompile Fail:' ||
                   crec.object_name );

               --RAISE;
       END;
    END LOOP;

    -- drop and recreate invalid public synonyms pointing to repository owner
    -- to make them valid.
    FOR rec IN ( SELECT ds.synonym_name,ds.table_owner,ds.table_name
                   FROM dba_objects do,
                        dba_synonyms ds
                  WHERE do.status       = 'INVALID' 
                    AND do.owner        = 'PUBLIC' 
                    AND do.object_type  = 'SYNONYM'
                    AND ds.owner        = 'PUBLIC' 
                    AND ds.table_owner  = '&EM_REPOS_USER' 
                    AND ds.synonym_name = do.object_name)
    LOOP
      BEGIN
        EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM '||rec.synonym_name 
                           ||' FOR '|| rec.table_owner||'.'||rec.synonym_name ;
      EXCEPTION 
        WHEN OTHERS THEN NULL ;
      END ;
    END LOOP ;

END;
/

BEGIN
  -- Reset package state to avoid running into 4061/4068 errors
  DBMS_SESSION.RESET_PACKAGE;
END ;
/