Rem
Rem $Header: ovmrelod.sql 25-jan-2005.15:25:38 bspeckha Exp $
Rem
Rem ovmrelod.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      ovmrelod.sql - Reload script
Rem
Rem    DESCRIPTION
Rem          The "patch" script is used to apply bug fixes to the component. 
Rem          It is run in the context of catpatch.sql, after the RDBMS catalog.sql 
Rem          and catproc.sql scripts are run. It is run with a special EVENT set 
Rem          which causes CREATE OR REPLACE statements to only recompile objects 
Rem          if the new source is different than the source stored in the database. 
Rem          Tables, types, and public interfaces should not be changed by patch scripts. 
Rem          
Rem                 ALTER SESSION SET CURRENT_SCHEMA = MYCSYS;
Rem                 EXECUTE dbms_registry.loading(`MYC','My Component Name');
Rem                 Rem Only reload views, private PL/SQL types and packages, and type/package bodies
Rem                 @@mycpvs.plb
Rem                 @@mycview.sql
Rem                 @@myctyb.plb
Rem                 @@mycplb.plb
Rem                  
Rem                 Rem Reload classes if Java is in the database
Rem                 COLUMN file_name NEW_VALUE comp_file NOPRINT;
Rem                 SELECT dbms_registry.script(`JAVAVM','@initmyc.sql') AS file_name FROM DUAL;
Rem                 @&comp_file
Rem                 EXECUTE dbms_registry.loaded(`MYC'); /* uses RDBMS release version number */
Rem                 EXECUTE myc_validate;
Rem                 ALTER SESSION SET CURRENT_SCHEMA = SYS;
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    bspeckha    01/25/05 - use recompileAllObjects 
Rem    bspeckha    10/11/04 - owm_validate changed to validate_owm 
Rem    bspeckha    08/08/03 - fix registry status after upgrade 
Rem    bspeckha    01/17/03 - call recompileObjects
Rem    saagarwa    12/16/02 - 
Rem    saagarwa    11/19/02 - Add calls in the begining and end
Rem    saagarwa    09/30/02 - use enablesystemtriggers_exp
Rem    saagarwa    09/11/02 - Remove creation of system views
Rem    saagarwa    08/28/02 - Recreate views while downgrading
Rem    saagarwa    07/31/02 - Reload OWM on downgrade
Rem    saagarwa    07/28/02 - saagarwa_conflict_view_perf_fix_and_922_scripts
Rem    saagarwa    07/23/02 - Created
Rem

WHENEVER SQLERROR EXIT;
EXECUTE dbms_registry.check_server_instance;
WHENEVER SQLERROR CONTINUE;

execute dbms_registry.downgrading('OWM') ;

VAR old_version varchar2(30);
begin
  select value into :old_version from wm_installation where name = 'OWM_VERSION' ;
end;
/

/* --------------------------------------------------------------------- */
/* Create package specs                                                  */
/* --------------------------------------------------------------------- */
@@owmcpkgs.plb

/* grant some additional privs to wmsys */
grant execute on sys.ltadm to wmsys with grant option;
grant execute on sys.ltutil to wmsys with grant option;
grant execute on sys.lt_ctx_pkg to wmsys with grant option;
grant select on  sys.dba_views to wmsys with grant option;

grant execute on sys.dbms_lob to wmsys with grant option;


/* Recompile any invalid views */
declare
  owner_var varchar2(30);
  object_name_var varchar2(30);

  cursor c1 is
    select owner,object_name  
    from dba_objects 
    where ((owner = 'WMSYS') or
           (owner = 'SYS' and object_name like '%WORKSPACE%'))
    and status != 'VALID'
    and object_type = 'VIEW' ;  

begin
  open c1;
  loop
    fetch c1 into owner_var,object_name_var;
    exit when c1%NOTFOUND;
    execute immediate 'alter view ' || owner_var || '.' || object_name_var || ' compile' ;
  end loop;
  close c1;
end;
/


/* CREATE THE wm_installation view */
@@owmcmdv.plb


/* --------------------------------------------------------------------- */
/* Create package body                                                   */
/* --------------------------------------------------------------------- */
@@owmcpkgb.plb


/* --------------------------------------------------------------------- */
/* Light-weight EnableVersion the tables.                                */
/* --------------------------------------------------------------------- */
execute sys.owm_mig_pkg.AllLwEnableVersioning ;


execute sys.ltric.recreatePtUpdDelTriggers;

/* Recompile any invalid objects */
execute sys.owm_mig_pkg.recompileAllObjects ;

/* --------------------------------------------------------------------- */
/* If there is atleast one versioned table, enable the system triggers.  */
/* This is because reinstall of WM creates triggers as disabled.         */
/* --------------------------------------------------------------------- */
declare
 found integer;
begin
   begin
     select 1 into found from dual where exists (select 1 from wmsys.wm$versioned_tables);
     sys.ltadm.enableSystemTriggers_exp;
   exception
     when no_data_found then null;
     when others then raise;
   end;
end;
/

execute dbms_registry.downgraded('OWM', :old_version) ;

declare
  version_str             varchar2(100)  := '';
  compatibility_str       varchar2(100)  := '';
begin
   dbms_utility.db_version(version_str,compatibility_str);
   version_str := sys.wm$convertDbVersion(version_str);
   if (version_str >= '9.2.0.0.0') then
     if ((version_str >= '9.2.0.7.0' and version_str < 'A.0.0.0.0') or version_str >= 'A.1.0.4.0') then
       execute immediate 'begin sys.validate_owm; end;' ;
     else
       execute immediate 'begin wmsys.validate_owm; end;' ;
     end if ;
   end if;
end;
/
