Rem
Rem $Header: sdo/admin/locdbmig.sql /st_sdo_11.2.0/3 2010/10/19 10:02:49 sravada Exp $
Rem
Rem locdbmig.sql
Rem
Rem Copyright (c) 2005, 2010, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      locdbmig.sql - LOCatorDBMIGration
Rem
Rem    DESCRIPTION
Rem      Used for upgradig the Locator as part of DB upgrade if SDO is not
Rem      present
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sravada     06/21/10 - lrg 4680044
Rem    sravada     08/20/09 - bug 8740141
Rem    sravada     08/27/06 - bug 5453881
Rem    sravada     05/25/05 - sravada_cleanup_label_difs_2
Rem    sravada     05/25/05 - Created
Rem


Alter session set current_schema=MDSYS;

/* Performa any upgrade actions if SDO is not installed.
    If SDO is installed, all of these actions are done as part of
       sdodbmig.sql  script. */

COLUMN :script_name NEW_VALUE comp_file NOPRINT
Variable script_name varchar2(50)
Variable sdo_cnt  number;

declare
  sdo_status VARCHAR2(20) := NULL;
begin
  -- Check whether SDO is installed.
  -- If not, install Locator
  select count(*) into :sdo_cnt from all_objects where owner='MDSYS' and
  object_name = 'SDO_GEOMETRY';
  sdo_status := dbms_registry.status('SDO');
  if (:sdo_cnt = 0) then
    :script_name := '?/rdbms/admin/nothing.sql';
  elsif (sdo_status is NULL or sdo_status = 'OPTION OFF') then
    :script_name := '@sdomigrt.sql';
  else
    :script_name := '?/rdbms/admin/nothing.sql';
  end if;
end;
/

select :script_name from dual;
@&comp_file

/* unload all the java classes so that they can be recreated with catmloc.sql
   or catmd.sql. This will prevent java classes from being invalid
    if the seed is used with SE install */

COLUMN loc_fname NEW_VALUE loc_file NOPRINT;
SELECT dbms_registry.script('JAVAVM','@sdounloadj.sql') AS loc_fname FROM DUAL;

@&loc_file

alter session set current_schema=MDSYS;

declare
begin
 begin
    execute immediate ' drop package body mdsys.sdo_topo ';
    exception when others then NULL;
 end;
 begin
    execute immediate ' drop package mdsys.sdo_net_mem ';
    exception when others then NULL;
 end;
 begin
    execute immediate ' drop public synonym sdo_net_mem ';
    exception when others then NULL;
 end;
end;
/


/* Now load catmdloc.sql only of SDO is not installed. If SDO is installed,
  the top level script will load catmd.sql */

COLUMN :script_name NEW_VALUE comp_file NOPRINT
Variable script_name varchar2(50)
 
declare
  sdo_status VARCHAR2(20) := NULL;
begin
  -- Check whether SDO is installed.
  -- If not, install Locator
  sdo_status := dbms_registry.status('SDO');
  if (sdo_status is NULL or sdo_status = 'OPTION OFF') then
    :script_name := '@catmdloc.sql';
  else
    :script_name := '?/rdbms/admin/nothing.sql';
  end if;
end;
/
 
select :script_name from dual;
@&comp_file
 

-- reset the session id back to SYS
Alter session set current_schema=SYS;