Rem
Rem $Header: sdo/admin/locpatchi.sql /st_sdo_11.2.0/14 2011/04/20 11:24:28 yhu Exp $
Rem
Rem locpatchi.sql
Rem
Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      locpatchi.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    yhu         04/15/11 - add relate.sql for bug 11939823
Rem    yhu         04/05/11 - add sdopidx.sql and prvtpidx.plb
Rem    yhu         03/27/11 - add patch script for bug 11828358
Rem    zzhang      12/08/10 - Fix bug 10382105
Rem    yhu         11/22/10 - add patch script for bug 9740355
Rem    yhu         01/29/10 - set 3D SDO_ROOT_MBR : gtype=3008, etype=1007
Rem    yhu         11/23/09 - add patch script for bug 9128263
Rem    yhu         10/08/09 - add patch script for bugs 8940352 and 8940643
Rem    sravada     09/09/09 - Created
Rem

Alter user MDSYS default tablespace SYSAUX;

alter session set current_schema=SYS;
COLUMN java_fname NEW_VALUE java_file NOPRINT;
SELECT dbms_registry.script('JAVAVM', '@sdoloadj.sql') AS java_fname FROM DUAL;
@&java_file
alter session set current_schema=MDSYS;

--revoke public execute privilege with grant option
-- this is necessary for upgrade cases
BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON MDSYS.SDO_GEOR_INT FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON SDO_GEOR_INT FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
END;
/

BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON MDSYS.SDO_GEOR_AUX FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'GRANT  EXECUTE ON MDSYS.SDO_GEOR_AUX TO PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON SDO_GEOR_AUX FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON SDO_GEOR_AUX TO PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
END;
/

BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON MDSYS.SDO_GEOR FROM PUBLIC';
     EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
     EXECUTE IMMEDIATE 'GRANT EXECUTE ON MDSYS.SDO_GEOR TO PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON SDO_GEOR FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON SDO_GEOR TO PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
END;
/

BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON MDSYS.SDO_GEOR_ADMIN FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON MDSYS.SDO_GEOR_ADMIN TO PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON SDO_GEOR_ADMIN FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON SDO_GEOR_ADMIN TO PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
END;
/

BEGIN
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON MDSYS.SDO_GEOR_UTL FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON MDSYS.SDO_GEOR_UTL TO PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'REVOKE EXECUTE ON SDO_GEOR_UTL FROM PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
  BEGIN
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON SDO_GEOR_UTL TO PUBLIC';
    EXCEPTION WHEN OTHERS THEN NULL;
  END;
END;
/



ALTER TABLE MDSYS.SDO_COORD_OP_PARAMS ADD (
  UNIT_OF_MEAS_TYPE VARCHAR2(50 byte));

@@prvtgmd.plb
@@sdopidx.sql
@@prvtsidx.plb
@@prvtpidx.plb
@@relate.sql
@@sdogeom.sql
@@sdoutlh.sql
@@prvtgeom.plb
@@prvt3gl.plb
@@sdoutlb.plb
@@prvtprdx.plb
@@prvttmd.plb
@@sdogmmb.plb
@@sdocs.sql
@@prvtcs.plb
@@sdoepsgd.sql
@@sdoepsgl.plb
@@sdoepsgv.sql

CREATE OR REPLACE VIEW ALL_ANNOTATION_TEXT_METADATA AS
SELECT F_TABLE_SCHEMA OWNER, F_TABLE_NAME TABLE_NAME,
               F_TEXT_COLUMN COLUMN_NAME,
               MAP_BASE_SCALE,
               TEXT_DEFAULT_EXPRESSION TEXT_EXPRESSION,
               TEXT_DEFAULT_ATTRIBUTES TEXT_ATTRIBUTES
FROM SDO_ANNOTATION_TEXT_METADATA, ALL_OBJECTS a
       where a.object_name = F_TABLE_NAME and a.owner = F_TABLE_SCHEMA
             and a.object_type in ('TABLE', 'SYNONYM', 'VIEW')
UNION ALL
SELECT F_TABLE_SCHEMA OWNER, F_TABLE_NAME TABLE_NAME,
               F_TEXT_COLUMN COLUMN_NAME,
               MAP_BASE_SCALE,
               TEXT_DEFAULT_EXPRESSION ,
               TEXT_DEFAULT_ATTRIBUTES
FROM SDO_ANNOTATION_TEXT_METADATA, ALL_OBJECT_TABLES a
       where a.table_name = F_TABLE_NAME and a.owner = F_TABLE_SCHEMA ;

-- set 3D SDO_ROOT_MBR: gtype=3008 etype=1007  
declare
begin
 update sdo_index_metadata_table a 
   set a.sdo_root_mbr = 
         sdo_geometry(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1007, 3), 
                      a.sdo_root_mbr.SDO_ORDINATES)
   where a.sdo_root_mbr.sdo_gtype = 3003;
end;
/

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

set define on