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

SELECT 'ALTER VIEW ' || object_name || ' COMPILE' s
              FROM dba_objects WHERE owner = UPPER('&MGUSER') AND
            status = 'INVALID';

PROMPT *** compile_schema &MGUSER   *** 

BEGIN
  DBMS_UTILITY.compile_schema(UPPER('&MGUSER'), FALSE);
  FOR z IN (SELECT 'ALTER VIEW ' || 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 'ALTER VIEW ' || object_name || ' COMPILE' s
              FROM dba_objects WHERE owner = UPPER('&MGUSER') AND
            status = 'INVALID';


SPOOL OFF
SET TERMOUT ON
SET ECHO ON
EXIT