/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON;
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  l_custom_obj_prefix varchar2(30) := upper(trim('&1'));
  --
  -- Get all invalid objects in the order they should be compiled:
  --   1. Package Specifications
  --   2. Views
  --   3. Package Bodies
  --   4. Type Specification
  --   5. Type Bodies
  --
  CURSOR csr_get_invalid IS
  SELECT *
  FROM  (SELECT uo.object_name
               ,uo.object_type
               ,decode(uo.object_type, 'PACKAGE', 1
                                     , 'VIEW', 2
                                     , 'PACKAGE BODY', 3) recomp_seq
         FROM   user_objects uo
         WHERE (uo.object_name LIKE 'XXAS%' OR
                uo.object_name LIKE l_custom_obj_prefix)
         AND    uo.status = 'INVALID'
         AND    uo.object_type IN ('PACKAGE', 'VIEW', 'PACKAGE BODY'))
  ORDER BY recomp_seq;

  l_sql varchar2(400);

BEGIN

  --
  -- Also recompile customer objects if they exist in the patch
  --
  l_custom_obj_prefix := l_custom_obj_prefix || '%';
  --
  -- Loop through all invalid objects and compile
  --
  FOR csr_rec in csr_get_invalid LOOP

    IF csr_rec.object_type = 'PACKAGE' THEN
      --
      -- Package specs
      --
      l_sql := ' ALTER PACKAGE "' || csr_rec.object_name || '"'
            || ' COMPILE SPECIFICATION';

    ELSIF csr_rec.object_type = 'VIEW' THEN
      --
      -- Views
      --
      l_sql := ' ALTER VIEW "' || csr_rec.object_name || '" COMPILE';

    ELSIF csr_rec.object_type = 'PACKAGE BODY' THEN
      --
      -- Package bodies
      --
      l_sql := ' ALTER PACKAGE "' || csr_rec.object_name || '"'
            || ' COMPILE BODY';

    END IF;

    --
    -- Recompile the invalid object
    --
    BEGIN
		EXECUTE IMMEDIATE l_sql;
    EXCEPTION
        WHEN others THEN NULL;
    END;
  END LOOP;

END;
/
COMMIT;
EXIT;