Rem
Rem $Header: sdoimup.sql 19-may-2004.15:27:49 sravada Exp $
Rem
Rem sdoimup.sql
Rem
Rem Copyright (c) 2001, 2004, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdoimup.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    sravada     05/19/04 - 
Rem    sravada     08/12/03 - add more ignores 
Rem    sravada     02/05/03 - remove SETs
Rem    arithikr    02/17/03 - 2802419 - Ignore ORA-29833 during upgrade
Rem    sravada     01/04/02 - add more ignorable errors
Rem    sravada     10/31/01 - obsolete old interfaces
Rem    sravada     10/08/01 - update for 9iR2
Rem    sravada     05/17/01 - remove DynSQL
Rem    sravada     05/04/01 - use Dynamic SQL for select from sdo_version
Rem    sravada     05/02/01 - add version checking
Rem    sravada     04/22/01 - add mdprivs.sql for INTERNAL
Rem    sravada     03/22/01 - change the path to relative
Rem    rkothuri    03/05/01 - ODMA migrate script for Spatial
Rem    rkothuri    03/05/01 - Created
Rem

-- This upgrade is OK even if the current version is 9i.
-- That is, this upgrade will reinstall Spatial if it is already
-- 9i, and upgrades to 9i, if it is not.
-- The c81Xu90X.sql script will do all the required steps.

set serveroutput on;


declare
sdo_version varchar2(32);
obj_count number;
stmt varchar2(400);
cursor_name             INTEGER;
ignore                  INTEGER;
BEGIN 
-- Run as mdsys 

  select count(*) into obj_count
  from dba_objects where owner='MDSYS';

 -- if object count is 0, then spatial is not installed
  if obj_count = 0 then
    DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:EXIT:NOT_INSTALLED:');
    RETURN;
  end if;
  
  begin 

    stmt := ' select mdsys.sdo_admin.sdo_version from dual';
    cursor_name := dbms_sql.open_cursor;
    dbms_sql.parse(cursor_name, stmt, dbms_sql.native);
    dbms_sql.define_column(cursor_name, 1, sdo_version, 30);
    ignore := dbms_sql.execute(cursor_name);
 
    IF (dbms_sql.fetch_rows(cursor_name) > 0) THEN
    dbms_sql.column_value(cursor_name, 1, sdo_version);
    ELSE
      DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:EXIT:NOT_INSTALLED:');
      RETURN;
    END IF;
    EXCEPTION WHEN OTHERS THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:EXIT:NOT_INSTALLED:');
        RETURN;
     END;
    dbms_sql.close_cursor(cursor_name);
  end;
 -- if version is 10.1.0.X then spatial is already upgraded
  if sdo_version like  '10.2.0.%' then
    DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:EXIT:INSTALLED:');
    RETURN;
  end if;
   

     DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:CONNECT_AS_SYSDBA_USER:');
     DBMS_OUTPUT.PUT_LINE
       ('ODMA_DIRECTIVE:SCRIPT:UPGRADE:md/admin/mdprivs.sql:');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:00904');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:00955');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:00942');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:01031');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:01403');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:01418');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:01432');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:01430');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:01433');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:01636');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:04043');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:02260');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:02303');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:06512');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:29807');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:29809');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:29832');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:29833');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:29844');
       DBMS_OUTPUT.PUT_LINE('ODMA_DIRECTIVE:ORA:IGNORE:29830');
     DBMS_OUTPUT.PUT_LINE
       ('ODMA_DIRECTIVE:SCRIPT:UPGRADE:md/admin/sdodbmig.sql:');


END; 
/