REM ****************
REM dbms_compile 1.3
REM ****************

SET TERMOUT ON
SET ECHO OFF

SPOOL dbms_compile.log

ACCEPT Server PROMPT 'Enter DATABASE name (default - MAGICASH): ' DEFAULT MAGICASH
ACCEPT SYSpsw PROMPT 'Enter SYS password: ' DEFAULT NCTSYS 
ACCEPT MGUSER PROMPT 'Enter USER name (default - MAGICASH5): ' DEFAULT MAGICASH5  

CONNECT SYS/&SYSpsw@&Server AS SYSDBA


PROMPT *** compile_schema &MGUSER   *** 


SELECT   object_type || '_' || object_name || ':INVALID' s
  FROM dba_objects
 WHERE owner = UPPER('&MGUSER')
   AND status = 'INVALID';


PROMPT *** 

BEGIN
  DBMS_UTILITY.compile_schema(UPPER('&MGUSER'), FALSE);
  FOR z IN (SELECT 'ALTER ' || object_type || '  ' || object_name || ' COMPILE' s
  FROM dba_objects
 WHERE owner = UPPER('&MGUSER')
   AND status = 'INVALID') LOOP
    BEGIN
      EXECUTE IMMEDIATE z.s;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/


PROMPT *** 
SELECT   object_type || '_' || object_name || ':INVALID' s
  FROM dba_objects
 WHERE owner = UPPER('&MGUSER')
   AND status = 'INVALID';



SPOOL OFF
SET TERMOUT ON
SET ECHO ON
EXIT