--===========================================================================
-- Copywright 2002, Stefan Mueller, R&D Mannager (http://www.orcl-toolbox.com)
--=============================================================================
-- DESCRIPTION: Writes error stack out to aa text file.  
--
--=============================================================================
-- MODIFICATION HISTORY
-- Person      Date       Comments
-- ---------   ---------- --------------------------------------------
-- Stefan      07/17/2001 Initial Creation;; 
--=============================================================================

set serveroutput on size 1000000 verify off linesize 132
ACCEPT TheOwner CHAR PROMPT "Name of scheme with invalid objects [%logonuser%]:" 
spool inv.log
DECLARE
  eStillInvalid EXCEPTION;
  PRAGMA EXCEPTION_INIT(eStillInvalid,-24344);
  vCursor        INTEGER := DBMS_SQL.Open_Cursor;
  vDummy         INTEGER;
  n              INTEGER;
  maxlevel       INTEGER := 1;
  maxNestedLevel INTEGER := 10;
  errCount       INTEGER := 0;
  ObjectOwner    VARCHAR2(50);

  TYPE          obj IS RECORD (  obj_id    NUMBER
                               , obj_owner VARCHAR2(30)
                               , obj_name  VARCHAR2(30)
                               , obj_type  VARCHAR2(30)
                               , obj_level NUMBER
                               );

  TYPE          objs IS TABLE OF obj
                        INDEX BY BINARY_INTEGER;

  vobj          obj;
  vobjs         objs;



  PROCEDURE compile_obj(pObj IN obj) IS
      vStatement    VARCHAR2(4000);
  
  BEGIN

     dbms_output.put(RPAD(substr(pobj.obj_owner,1,30)||'.'||substr(pobj.obj_name,1,30)||' ('||substr(pobj.obj_type,1,30)||')',61,'.'));

     IF (pobj.obj_type = 'FUNCTION') THEN
       vStatement := 'ALTER FUNCTION "'   ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'PACKAGE') THEN
       vStatement := 'ALTER PACKAGE "'    ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'PACKAGE BODY') THEN
       vStatement := 'ALTER PACKAGE "'    ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE BODY';
     ELSIF (pobj.obj_type = 'PROCEDURE') THEN
       vStatement := 'ALTER PROCEDURE "'  ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'TRIGGER') THEN
       vStatement := 'ALTER TRIGGER "'    ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'TYPE') THEN
       vStatement := 'ALTER TYPE "'       ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'TYPE BODY') THEN
       vStatement := 'ALTER TYPE "'       ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE BODY';
     ELSIF (pobj.obj_type = 'VIEW') THEN
       vStatement := 'ALTER VIEW "'       ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'OPERATOR') THEN
       vStatement := 'ALTER OPERATOR "'   ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'INDEXTYPE') THEN
       vStatement := 'ALTER INDEXTYPE "'  ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'DIMENSION') THEN
       vStatement := 'ALTER DIMENSION "'  ||pobj.obj_owner||'"."'||pobj.obj_name||'" COMPILE';
     ELSIF (pobj.obj_type = 'JAVA CLASS') THEN
       vStatement := 'ALTER JAVA CLASS "' ||pobj.obj_owner||'"."'||pobj.obj_name||'" RESOLVE';
     ELSIF (pobj.obj_type = 'JAVA SOURCE') THEN
        dbms_output.put('...Not compiling Java Source itself');
     ELSE
        dbms_output.put('...unkown type ' ||pobj.obj_type||'!');
     END IF;

     IF (vStatement IS NOT NULL) THEN
       BEGIN
         DBMS_SQL.Parse(vCursor,vStatement,DBMS_SQL.Native);
         vDummy := DBMS_SQL.Execute(vCursor);
         dbms_output.put('...OK');
       EXCEPTION
         WHEN eStillInvalid THEN
            dbms_output.put('...remains invalid');
           errCount := errCount + 1;
         WHEN OTHERS THEN
            dbms_output.put('...ERROR!');
            dbms_output.new_line;
            dbms_output.put(substr('...Problem '||SQLErrm(SQLCode)||' (Error in '||pobj.obj_type||')',1,250));
           errCount := errCount + 1;
       END;
     END IF;
     dbms_output.new_line;
  END;


  PROCEDURE get_obj (pObj IN obj, pLevel IN NUMBER Default 1) IS

  BEGIN
     vobjs(pobj.obj_id).obj_id    := pobj.obj_id;
     vobjs(pobj.obj_id).obj_owner := pobj.obj_owner;
     vobjs(pobj.obj_id).obj_name  := pobj.obj_name;
     vobjs(pobj.obj_id).obj_type  := pobj.obj_type;
     vobjs(pobj.obj_id).obj_level := pLevel;
     maxlevel := GREATEST(maxlevel,pLevel);

    FOR hieobj IN (SELECT /*+ ordered */ object_id, owner, object_name, object_type
                     FROM sys.dependency$,
                          dba_objects
                    WHERE object_id = d_obj#
                      AND p_obj# = pObj.obj_id
                      AND owner != 'SYS'  /* don't catch standard or java.* */
                      AND status != 'VALID'
                      AND object_type != 'UNDEFINED'
                      AND plevel < maxNestedLevel
                  ) LOOP
       
       vobj.obj_id    := hieobj.object_id;
       vobj.obj_owner := hieobj.owner;
       vobj.obj_name  := hieobj.object_name;
       vobj.obj_type  := hieobj.object_type;
       get_obj(vobj, pLevel+1 );  -- get the rest
 
    END LOOP;
  END get_obj;

BEGIN

  ObjectOwner := nvl('&TheOwner',user);

  dbms_output.put_line('Compile objects for '||ObjectOwner); 
  
  -- This is the root or parent of the hierarchie
  FOR invobj IN (SELECT obj.object_id,obj.owner,obj.object_type, obj.object_name
                   FROM dba_objects obj
                  WHERE owner != 'SYS'
                    AND obj.status != 'VALID'
                    AND obj.object_type != 'UNDEFINED'
                    AND owner LIKE UPPER('&TheOwner') 
                  ORDER BY owner, last_ddl_time 
                 ) LOOP
     
     vobj.obj_id    := invobj.object_id;
     vobj.obj_owner := invobj.owner;
     vobj.obj_name  := invobj.object_name;
     vobj.obj_type  := invobj.object_type;

     get_obj(vobj);  -- now get the hierarchie

  END LOOP;

  -- compile the objects. loop over the objects with brut force
  FOR i IN 1 .. maxLevel LOOP
    n := vobjs.first();
      FOR j IN 1 .. vobjs.count() LOOP
        IF (vobjs(n).obj_level = i) THEN
	   compile_obj(vobjs(n));
        END IF;
        n := vobjs.next(n);
      END LOOP;
  END LOOP;

  dbms_sql.close_cursor(vCursor);
  dbms_output.put_line('Worked on '||to_char(vobjs.Count())||' objects, nested at '||to_char(maxLevel)||' levels'); 
  dbms_output.put_line(ErrCount||' errors'); 
END;
/


SELECT owner, status, COUNT(*)
  FROM dba_objects
 WHERE status = 'INVALID'
 GROUP BY owner,status;

spool off

set echo off serveroutput off

prompt inv.log has been spooled