SET TERMOUT ON
SET ECHO OFF
SET VERIFY OFF
SET LINESIZE 300
SET PAGESIZE 0

SPOOL 04_RECOMPILE_USERS.log

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

CONNECT SYS/&SYSpsw@&Server AS SYSDBA




PROMPT *************************************************************


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

PROMPT *** SELECT MAGICASH5 ERRORS *** 

SELECT * FROM DBA_ERRORS
WHERE OWNER IN (UPPER('MAGICASH5'));

PROMPT *************************************************************



SPOOL OFF

SET TERMOUT ON
SET ECHO ON
EXIT