Rem
Rem $Header: sdocmprg.sql 28-jun-2007.11:14:59 alwu Exp $
Rem
Rem sdocmprg.sql
Rem
Rem Copyright (c) 2001, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdocmprg.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    alwu        06/28/07 - fix invalid value$cache package
Rem    alwu        06/25/07 - fix invalid object SDO_DROP_USER_BEFORE trigger.
Rem                           trigger moved out of prvtgmd.sql
Rem    alwu        06/14/07 - fix sdo_rdf_triple_s compile
Rem    alwu        06/14/07 - fix lrg 3014999: new rdf upgrade logic no longer
Rem                           loads type and packages
Rem    alwu        09/08/06 - fix lrg 2540627: a downgrade scenario that could
Rem                           not find rdf_match_impl_t
Rem    alwu        08/28/06 - add compile body for RDF_MATCH_IMPL_T
Rem    mhorhamm    02/23/06 - Make compilation of packages conditional 
Rem    mhorhamm    01/27/06 - Recompile OpenLS-related packages 
Rem    sravada     10/18/04 - move validate to SYS 
Rem    mhorhamm    09/23/04 - Recompile OGC functions 
Rem    sravada     07/30/04 - 
Rem    sravada     07/08/03 - add exception handler
Rem    sravada     07/03/03 - validate all public synonyms created by MDSYS
Rem    sravada     02/05/03 - move validation to a procedure
Rem    sravada     11/26/02 - version: use RDBMS version
Rem    sravada     08/12/02 - verion to 10.0.0.0
Rem    sravada     02/05/02 - update version to 9.2.0.1
Rem    sravada     10/23/01 - owner to schema
Rem    sravada     10/08/01 - Merged sravada_ship_it_ninertwo
Rem    sravada     10/08/01 - Created
Rem

EXECUTE dbms_registry.loaded('SDO');

declare
 stmt varchar2(200);
 syn varchar2(32);
  type                    cursor_type is REF CURSOR;
 query_crs               cursor_type ;
begin

  stmt := ' select SNAME from sys.synonyms ' ||
          ' where creator=''MDSYS'' and SYNTYPE=''PUBLIC'' '||
          ' and TABTYPE<> ''JAVA CLASS'' ';

   OPEN query_crs FOR stmt;
  LOOP 
  BEGIN
    fetch query_crs into  syn;
    EXIT when query_crs%NOTFOUND ;

    begin
      EXECUTE immediate ' alter public synonym '||syn||' compile';
     EXCEPTION 
     WHEN OTHERS THEN 
      syn := ' ';
    end;
    commit;
  end;
  END LOOP;
  close query_crs;
end;
/

declare
 stmt varchar2(200);
 syn varchar2(32);
  type                    cursor_type is REF CURSOR;
 query_crs               cursor_type ;
begin
  -- Recompile OGC functions, for they got invalidated during recompilation of other synonyms
  stmt := ' select SNAME from sys.synonyms ' ||
          ' where tname like ''OGC_%'' and creator=''MDSYS'' and SYNTYPE=''PUBLIC'' '||
          ' and TABTYPE<> ''JAVA CLASS'' ';

   OPEN query_crs FOR stmt;
  LOOP 
  BEGIN
    fetch query_crs into  syn;
    EXIT when query_crs%NOTFOUND ;

    begin
      EXECUTE immediate ' alter public synonym '||syn||' compile';
     EXCEPTION 
     WHEN OTHERS THEN 
      syn := ' ';
    end;
    commit;
  end;
  END LOOP;
  close query_crs;
end;
/

declare
 stmt varchar2(400);
 obj varchar2(32);
  type                    cursor_type is REF CURSOR;
 query_crs               cursor_type ;
begin
  -- Recompile OGC functions, for they got invalidated during recompilation of other synonyms
  stmt :=
    'select
       OBJECT_NAME
     from
       all_objects
     where
       OWNER = ''MDSYS'' and
       (object_name like ''SDO_OLS%''  or 
        object_name like ''RDF_%''     or
        object_name like ''SDO_RDF%'') and
       object_type = ''PACKAGE BODY''';

   OPEN query_crs FOR stmt;
  LOOP 
  BEGIN
    fetch query_crs into  obj;
    EXIT when query_crs%NOTFOUND ;

    begin
      EXECUTE immediate ' alter package '||obj||' compile body';
     EXCEPTION 
     WHEN OTHERS THEN 
      obj := ' ';
    end;
    commit;
  end;
  END LOOP;
  close query_crs;
end;
/

BEGIN
  BEGIN
    EXECUTE immediate 'alter type mdsys.rdf_match_impl_t compile body';
  EXCEPTION WHEN OTHERS THEN NULL; END;

  BEGIN
    EXECUTE immediate 'alter procedure mdsys.LOAD_PREDEFINED_RULEBASES compile';
  EXCEPTION WHEN OTHERS THEN NULL; END;

  BEGIN
    EXECUTE immediate 'alter type mdsys.sdo_rdf_triple_s compile body ';
  EXCEPTION WHEN OTHERS THEN NULL; END;

  BEGIN
    EXECUTE immediate 'alter type sdo_rdf_triple_s compile body ';
  EXCEPTION WHEN OTHERS THEN NULL; END;

  BEGIN
    EXECUTE immediate 'alter trigger mdsys.sdo_drop_user_before compile ';
  EXCEPTION WHEN OTHERS THEN NULL; END;

  BEGIN
    EXECUTE immediate 'alter package mdsys.VALUE$CACHE compile ';
  EXCEPTION WHEN OTHERS THEN NULL; END;

  BEGIN
    EXECUTE immediate 'alter package mdsys.VALUE$CACHE compile body ';
  EXCEPTION WHEN OTHERS THEN NULL; END;
END;
/


EXECUTE SYS.validate_sdo;

select COMP_ID, SCHEMA, VERSION, STATUS, COMP_NAME
 from dba_registry where comp_id='SDO';