set define on

PROMPT *
PROMPT * Compile Schema

exec dbms_utility.compile_schema(USER)

exec dbms_utility.compile_schema(USER)

SET SCAN ON
SET DEFINE ON
SET ARRAYSIZE    1
SET FEEDBACK     OFF
SET LINESIZE     116
SET PAGESIZE     50

SET SERVEROUTPUT ON

COLUMN LABEL NEW_VALUE LABEL_VALUE NOPRINT
COLUMN RTYPE NOPRINT
COLUMN SEQ   NOPRINT
COLUMN TEXT  FORMAT A70 TRUNCATE
COLUMN TYPE  FORMAT A2
COLUMN COL   FORMAT A3
COLUMN DLINE FORMAT 99999 HEADING LINE
COLUMN ELINE NOPRINT

DEFINE LABEL_VALUE = "There were no errors"
REM Create the compilation report
TTITLE SKIP 2 CENTER 'COMPILATION REPORT' SKIP 1 
SET HEADING OFF
SELECT NULL FROM DUAL
/
/ 
TTITLE OFF
SET HEADING ON
BREAK ON NAME SKIP 1 ON TYPE SKIP 1

SELECT 
  '' LABEL,
  'E' RTYPE,
  E.NAME,
  DECODE (E.TYPE,'PACKAGE BODY','PB','PACKAGE','PS',TYPE) TYPE,
  E.LINE        ELINE,
  E.SEQUENCE    SEQ,
  E.LINE        DLINE, 
  TO_CHAR(E.POSITION) COL, 
  E.TEXT
FROM  USER_ERRORS E
/
PROMPT &&LABEL_VALUE