Rem
Rem $Header: sdo/admin/sdoepsgv.sql /st_sdo_11.2.0/1 2011/03/29 08:44:07 yhu Exp $
Rem
Rem sdoepsgv.sql
Rem
Rem Copyright (c) 2003, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdoepsgv.sql - SDO EPSG legacy Views
Rem
Rem    DESCRIPTION
Rem      This file provides mainly the legacy views substituting for a number
Rem      of "old" MDSYS tables. This makes several other files superfluous,
Rem      which provided for the original tables.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    yhu         03/27/11 - Backport yhu_blr_backport_11828358_11.2.0.2.0
Rem                           from st_sdo_11.2.0
Rem    sravada     04/17/08 - delete txn handle context for session
Rem    rkothuri    04/02/08 - add sdo_crs_geographic_plus_height
Rem    mhorhamm    06/15/07 - Insert WKT3d values
Rem    mhorhamm    02/19/07 - Add triggers
Rem    mhorhamm    08/25/06 - Add grant for SDO_SRID_LIST
Rem    mhorhamm    03/09/06 - Allow uppercase types 
Rem    mhorhamm    01/05/05 - Alter WKT for SRIDs 999998 and 999999 
Rem    sravada     08/31/04 - move sdoepsgl.plb out 
Rem    mhorhamm    08/23/04 - Add synonyms for various types 
Rem    mhorhamm    08/11/04 - Do not grant update/insert/delete on 
Rem                           SDO_PREFERRED_OPS_SYSTEM to public 
Rem    mhorhamm    08/10/04 - Grant less to public 
Rem    mhorhamm    08/09/04 - Add trigger on SDO_COORD_REF_SYSTEM
Rem    sravada     07/29/04 - remove USER_CS_SRS 
Rem    mhorhamm    06/07/04 - Move creation of empty table CS_SRS from 
Rem                           sdoepsgv.sql to sdoepsgd.sql 
Rem    mhorhamm    06/04/04 - Eliminate SDO_ALIASES 
Rem    mhorhamm    06/04/04 - Two triggers had same name 
Rem    mhorhamm    06/04/04 - Move some inserts out of trigger to become 
Rem                           permanent records 
Rem    mhorhamm    06/03/04 - Change some params in insertions in trigger 
Rem    mhorhamm    06/03/04 - Add trigger 
Rem    mhorhamm    04/13/04 - Change table/view name 
Rem    mhorhamm    04/09/04 - Grant SELECT for table SDO_AVAILABLE_OPS, 
Rem                           SDO_AVAILABLE_ELEM_OPS, 
Rem    mhorhamm    03/26/04 - Grant more rights regarding cs_srs 
Rem    mhorhamm    03/15/04 - Make use of MDSYS table names unambiguous 
Rem    mhorhamm    02/02/04 - Make sdoepsgl a plb file 
Rem    mhorhamm    01/29/04 - Make mdsys.cs_srs a table, for now
Rem                           (view could become a problem,
Rem                           when it calls a function)
Rem    mhorhamm    01/26/04 - Refer to sdoepsgl.sql, instead of 
Rem    mhorhamm    01/23/04 - Add "internal" prefix to some functions 
Rem    mhorhamm    01/22/04 - Delete reference to sdoepsgb.plb
Rem    mhorhamm    01/22/04 - Move content of package ecs into sdo_cs 
Rem    mhorhamm    01/21/04 - Change parameter names to using infix "SRID 
Rem    mhorhamm    01/20/04 - Fix names 
Rem    mhorhamm    01/19/04 - Add more grants to public 
Rem    mhorhamm    01/19/04 - Load data directly, rather than by importing dmp 
Rem    mhorhamm    01/13/04 - Table names have changed 
Rem    mhorhamm    01/09/04 - Grant access to mdsys.sdo_coord_ref_sys
Rem    mhorhamm    12/17/03 - Change package name to ecs 
Rem    mhorhamm    12/10/03 - Grant more to public 
Rem    mhorhamm    12/10/03 - Grant more rights to public 
Rem    mhorhamm    12/09/03 - Make MDSYS.SDO_PREFERRED_OPS tables public 
Rem    mhorhamm    11/19/03 - Add public grants for dpt 
Rem    mhorhamm    11/18/03 - Little change to MDSYS.SDO_ELLIPSOIDS view 
Rem    mhorhamm    11/10/03 - Add grant & synonym for tables 
Rem    mhorhamm    11/10/03 - Rename mdsys.legacy_sdo_ellipsoids to mdsys.sdo_ellipsoids 
Rem                           and mdsys.legacy_sdo_datums to mdsys.sdo_datums 
Rem    mhorhamm    11/07/03 - Minor changes 
Rem    mhorhamm    11/07/03 - Do not need INSERTs in MDSYS.SDO_UNITS_OF_MEASURE, any 
Rem                           more - they are in dmp file, now 
Rem    mhorhamm    11/05/03 - Created
Rem

-- Import the data, as compiled based on EPSG data, and updated with legacy data from previous Spatial versions
--host imp system/manager file=/private/adestore/mhorhamm/mhorhamm_sdo_main_view/sdo/admin/epsg.dmp TABLES=SDO_COORD_AXES,SDO_COORD_AXIS_NAMES,SDO_COORD_OPS,SDO_COORD_OP_METHODS,SDO_COORD_OP_PARAMS,SDO_COORD_OP_PARAM_USE,SDO_COORD_OP_PARAM_VALS,SDO_COORD_OP_PATHS,SDO_COORD_REF_SYS,SDO_COORD_SYS,SDO_DATUMS2,SDO_ELLIPSOIDS2,SDO_PREFERRED_OPS_SYSTEM,SDO_PREFERRED_OPS_USER,SDO_PRIME_MERIDIANS,SDO_UNITS_OF_MEASURE,SDO_ALIASES touser=MDSYS
-- @@sdoepsgl.plb

begin
  begin
    execute immediate 'drop trigger mdsys.cs_srs_trigger';
    exception when others then NULL;
  end;

  begin
    execute immediate 'drop trigger mdsys.sdo_crs_insert_trigger';
    exception when others then NULL;
  end;

  begin
    execute immediate 'drop trigger mdsys.sdo_crs_delete_trigger';
    exception when others then NULL;
  end;

  begin
    execute immediate 'truncate table mdsys.sdo_cs_srs';
    exception when others then NULL;
  end;
end;
/

INSERT INTO MDSYS.SDO_CS_SRS (
  CS_NAME,
  SRID,
  AUTH_SRID,
  AUTH_NAME,
  WKTEXT,
  WKTEXT3D,
  CS_BOUNDS
  ) (
  SELECT
    COORD_REF_SYS_NAME "CS_NAME",
    SRID "SRID",
    SRID "AUTH_SRID",
    INFORMATION_SOURCE "AUTH_NAME",
    NVL(
      LEGACY_WKTEXT,
      MDSYS.sdo_cs.internal_det_srid_wkt(srid)) "WKTEXT",
    sdo_cs.get_3d_wkt(srid) "WKTEXT3D",
    LEGACY_CS_BOUNDS "CS_BOUNDS"
  FROM
    MDSYS.SDO_COORD_REF_SYS);

UPDATE MDSYS.SDO_CS_SRS SET WKTEXT = '', wktext3d = '' WHERE SRID IN (999998, 999999);

-- create the sdo_crs_geographic_plus_height table
declare
begin
  begin
  execute immediate
  'create table sdo_crs_geographic_plus_height as ( ' ||
      ' select srid from sdo_crs_geographic3d ' ||
      ' union '||
      ' select cmpd.srid from sdo_crs_compound cmpd, ' ||
      ' sdo_crs_geographic2d g2d where cmpd.cmpd_horiz_srid = g2d.srid)';
  exception when others then null;
  end;
end;
/
grant select on mdsys.sdo_crs_geographic_plus_height to public;
commit;


CREATE OR REPLACE TRIGGER
  MDSYS.CS_SRS_TRIGGER
INSTEAD OF
  UPDATE OR
  INSERT OR
  DELETE
ON
  MDSYS.CS_SRS
FOR EACH ROW
BEGIN
  MDSYS.sdo_cs.sdo_cs_context_invalidate;

  if(not(:old.srid is null)) then
    delete from sdo_coord_ref_system crs where crs.srid = :old.srid;
  end if;
  if(not(:new.srid is null)) then
    if(upper(trim(:new.wktext)) like 'GEOGCS%') then
      insert into sdo_coord_ref_system (
        SRID,
        COORD_REF_SYS_NAME,
        COORD_REF_SYS_KIND,
        COORD_SYS_ID,
        DATUM_ID,
        GEOG_CRS_DATUM_ID,
        SOURCE_GEOG_SRID,
        PROJECTION_CONV_ID,
        CMPD_HORIZ_SRID,
        CMPD_VERT_SRID,
        INFORMATION_SOURCE,
        DATA_SOURCE,
        IS_LEGACY,
        LEGACY_CODE,
        LEGACY_WKTEXT,
        LEGACY_CS_BOUNDS,
        IS_VALID,
        SUPPORTS_SDO_GEOMETRY)
      values(
        :new.SRID,
        :new.CS_NAME,
        'GEOGRAPHIC2D',
        null,
        1000000123,
        1000000123,
        null,
        null,
        null,
        null,
        null,
        null,
        'TRUE',
        null,
        :new.WKTEXT,
        :new.CS_BOUNDS,
        'TRUE',
        'TRUE');
    else
      insert into sdo_coord_ref_system (
        SRID,
        COORD_REF_SYS_NAME,
        COORD_REF_SYS_KIND,
        COORD_SYS_ID,
        DATUM_ID,
        GEOG_CRS_DATUM_ID,
        SOURCE_GEOG_SRID,
        PROJECTION_CONV_ID,
        CMPD_HORIZ_SRID,
        CMPD_VERT_SRID,
        INFORMATION_SOURCE,
        DATA_SOURCE,
        IS_LEGACY,
        LEGACY_CODE,
        LEGACY_WKTEXT,
        LEGACY_CS_BOUNDS,
        IS_VALID,
        SUPPORTS_SDO_GEOMETRY)
      values(
        :new.SRID,
        :new.CS_NAME,
        'PROJECTED',
        null,
        null,
        1000000123,
        1000000123,
        null,
        null,
        null,
        null,
        null,
        'TRUE',
        null,
        :new.WKTEXT,
        :new.CS_BOUNDS,
        'TRUE',
        'TRUE');
    end if;
  end if;
END;
/

CREATE OR REPLACE TRIGGER
  MDSYS.SDO_CRS_INSERT_TRIGGER
INSTEAD OF
  INSERT
ON
  MDSYS.SDO_COORD_REF_SYSTEM
FOR EACH ROW
DECLARE
  already_exists  NUMBER;
BEGIN
  MDSYS.sdo_cs.sdo_cs_context_invalidate;

  INSERT INTO MDSYS.SDO_COORD_REF_SYS (
    SRID,
    COORD_REF_SYS_NAME,
    COORD_REF_SYS_KIND,
    COORD_SYS_ID,
    DATUM_ID,
    GEOG_CRS_DATUM_ID,
    SOURCE_GEOG_SRID,
    PROJECTION_CONV_ID,
    CMPD_HORIZ_SRID,
    CMPD_VERT_SRID,
    INFORMATION_SOURCE,
    DATA_SOURCE,
    IS_LEGACY,
    LEGACY_CODE,
    LEGACY_WKTEXT,
    LEGACY_CS_BOUNDS,
    IS_VALID,
    SUPPORTS_SDO_GEOMETRY)
  VALUES (
    :new.SRID,
    :new.COORD_REF_SYS_NAME,
    :new.COORD_REF_SYS_KIND,
    :new.COORD_SYS_ID,
    :new.DATUM_ID,
    :new.GEOG_CRS_DATUM_ID,
    :new.SOURCE_GEOG_SRID,
    :new.PROJECTION_CONV_ID,
    :new.CMPD_HORIZ_SRID,
    :new.CMPD_VERT_SRID,
    :new.INFORMATION_SOURCE,
    :new.DATA_SOURCE,
    :new.IS_LEGACY,
    :new.LEGACY_CODE,
    :new.LEGACY_WKTEXT,
    :new.LEGACY_CS_BOUNDS,
    :new.IS_VALID,
    :new.SUPPORTS_SDO_GEOMETRY);

  INSERT INTO MDSYS.SDO_CS_SRS (
    CS_NAME,
    SRID,
    AUTH_SRID,
    AUTH_NAME,
    WKTEXT,
    wktext3d,
    CS_BOUNDS
    )
  VALUES (
    :new.COORD_REF_SYS_NAME,
    :new.SRID,
    :new.SRID,
    :new.INFORMATION_SOURCE,
    NVL(
      :new.LEGACY_WKTEXT,
      MDSYS.sdo_cs.internal_det_srid_wkt(:new.srid)),
    sdo_cs.get_3d_wkt(:new.srid),
    :new.LEGACY_CS_BOUNDS);

    if(:new.COORD_REF_SYS_KIND = 'GEOGRAPHIC3D') then
      insert into sdo_crs_geographic_plus_height values (:new.srid);
    elsif(:new.COORD_REF_SYS_KIND = 'COMPOUND') then
      insert into sdo_crs_geographic_plus_height (
        select :new.srid from sdo_crs_geographic2d g2d 
	where g2d.srid = :new.CMPD_HORIZ_SRID);
    end if;

END;
/

CREATE OR REPLACE TRIGGER
  MDSYS.SDO_CRS_DELETE_TRIGGER
INSTEAD OF
  DELETE
ON
  MDSYS.SDO_COORD_REF_SYSTEM
FOR EACH ROW
DECLARE
  already_exists  NUMBER;
BEGIN
  DELETE FROM MDSYS.SDO_CS_SRS WHERE SRID = :old.SRID;
  DELETE FROM MDSYS.SDO_COORD_REF_SYS WHERE SRID = :old.SRID;
  DELETE FROM mdsys.sdo_crs_geographic_plus_height WHERE srid = :old.srid;
END;
/

CREATE OR REPLACE TRIGGER
  MDSYS.SDO_coord_op_param_val_TRIGGER
AFTER
  INSERT OR
  UPDATE or
  delete
ON
  MDSYS.SDO_coord_op_param_vals
for each row
BEGIN
  MDSYS.sdo_cs.sdo_cs_context_invalidate;

  update
    sdo_cs_srs
  set
    wktext = 'Getting updated'
  where
    srid in (
      select
        crs.srid
      from
        sdo_coord_ref_sys crs
      where
        crs.projection_conv_id = :new.coord_op_id);
end;
/

CREATE OR REPLACE TRIGGER
  MDSYS.SDO_coord_op_param_val_TRIGG2
AFTER
  INSERT OR
  UPDATE or
  delete
ON
  MDSYS.SDO_coord_op_param_vals
BEGIN
  update
    sdo_cs_srs
  set
    wktext = MDSYS.sdo_cs.internal_det_srid_wkt(srid),
    wktext3d = mdsys.sdo_cs.get_3d_wkt(srid)
  where
    wktext = 'Getting updated';
end;
/

-- The following lines originate from sdowkt.sql:

create or replace view mdsys.geodetic_srids as
  select srid from MDSYS.CS_SRS where WKTEXT like 'GEOGCS%'
  minus
  select srid from MDSYS.SDO_COORD_REF_SYS where coord_ref_sys_kind = 'GEOCENTRIC';


-- The previous lines originate from sdowkt.sql

declare 
begin
  begin
   execute immediate 
   ' DROP TABLE MDSYS.SDO_ANGLE_UNITS ';
  exception when others then NULL;
  end;
end;
/

CREATE OR REPLACE VIEW MDSYS.SDO_ANGLE_UNITS AS
  SELECT
    SHORT_NAME "SDO_UNIT",
    UNIT_OF_MEAS_NAME "UNIT_NAME",
    (FACTOR_B/FACTOR_C) "CONVERSION_FACTOR"
  FROM
    MDSYS.SDO_UNITS_OF_MEASURE
  WHERE
    lower(UNIT_OF_MEAS_TYPE)='angle';

declare 
begin
  begin
   execute immediate 
    ' DROP TABLE MDSYS.SDO_AREA_UNITS ';
  exception when others then NULL;
  end;
end;
/

CREATE OR REPLACE VIEW MDSYS.SDO_AREA_UNITS AS
  SELECT
    SHORT_NAME "SDO_UNIT",
    UNIT_OF_MEAS_NAME "UNIT_NAME",
    (FACTOR_B/FACTOR_C) "CONVERSION_FACTOR"
  FROM
    MDSYS.SDO_UNITS_OF_MEASURE
  WHERE
    lower(UNIT_OF_MEAS_TYPE)='area';
    -- made up value 'area' (does not exist, yet, in MDSYS.SDO_UNITS_OF_MEASURE)

declare 
begin
  begin
   execute immediate 
   ' DROP TABLE MDSYS.SDO_DIST_UNITS ';
  exception when others then NULL;
  end;
end;
/

CREATE OR REPLACE VIEW MDSYS.SDO_DIST_UNITS AS
  SELECT
    SHORT_NAME "SDO_UNIT",
    UNIT_OF_MEAS_NAME "UNIT_NAME",
    (FACTOR_B/FACTOR_C) "CONVERSION_FACTOR"
  FROM
    MDSYS.SDO_UNITS_OF_MEASURE
  WHERE
    lower(UNIT_OF_MEAS_TYPE)='length';

CREATE OR REPLACE VIEW MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT AS
  SELECT
    ELLIPSOID_NAME "NAME",
    SEMI_MAJOR_AXIS "SEMI_MAJOR_AXIS",
    INV_FLATTENING "INVERSE_FLATTENING"
  FROM
    MDSYS.SDO_ELLIPSOIDS
  WHERE
    NOT (INV_FLATTENING IS NULL)
UNION
  SELECT
    ELLIPSOID_NAME "NAME",
    SEMI_MAJOR_AXIS "SEMI_MAJOR_AXIS",
    (SEMI_MAJOR_AXIS/(SEMI_MAJOR_AXIS - SEMI_MINOR_AXIS)) "INVERSE_FLATTENING"
  FROM
    MDSYS.SDO_ELLIPSOIDS
  WHERE
    (INV_FLATTENING IS NULL)
    AND
    (SEMI_MINOR_AXIS < SEMI_MAJOR_AXIS)
UNION
  SELECT
    ELLIPSOID_NAME "NAME",
    SEMI_MAJOR_AXIS "SEMI_MAJOR_AXIS",
    1000000000000 "INVERSE_FLATTENING"
  FROM
    MDSYS.SDO_ELLIPSOIDS
  WHERE
    (INV_FLATTENING IS NULL)
    AND
    NOT (SEMI_MINOR_AXIS < SEMI_MAJOR_AXIS);

-- This table creation has been moved here from sdocsdef.sql:
declare
begin
  begin
   execute immediate
     ' DROP TABLE MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT ';
  exception when others then NULL;
  end;
end;
/

CREATE TABLE MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT (name VARCHAR2(64));

CREATE OR REPLACE VIEW MDSYS.SDO_PROJECTIONS_OLD_FORMAT AS (
  SELECT DISTINCT
    OP.COORD_OP_NAME "NAME"
  FROM
    MDSYS.SDO_COORD_REF_SYS CRS,
    MDSYS.SDO_COORD_OPS OP
  WHERE
    CRS.PROJECTION_CONV_ID = OP.COORD_OP_ID
UNION
  SELECT
    NAME
  FROM
    MDSYS.SDO_PROJECTIONS_OLD_SNAPSHOT);

CREATE OR REPLACE VIEW MDSYS.SDO_DATUMS_OLD_FORMAT AS
  SELECT
    DATUM_NAME "NAME",
    SHIFT_X,
    SHIFT_Y,
    SHIFT_Z,
    ROTATE_X,
    ROTATE_Y,
    ROTATE_Z,
    SCALE_ADJUST
  FROM
    MDSYS.SDO_DATUMS;

grant select on mdsys.sdo_cs_srs to public;
--grant insert on mdsys.sdo_cs_srs to public;
--grant update on mdsys.sdo_cs_srs to public;
--grant delete on mdsys.sdo_cs_srs to public;

grant select on mdsys.geodetic_srids to public;

grant select on MDSYS.SDO_AREA_UNITS to public;
grant select on MDSYS.SDO_DIST_UNITS to public;
grant select on MDSYS.SDO_ANGLE_UNITS to public;
create or replace  public synonym SDO_AREA_UNITS for MDSYS.SDO_AREA_UNITS;
create or replace  public synonym SDO_DIST_UNITS for MDSYS.SDO_DIST_UNITS;
create or replace  public synonym SDO_ANGLE_UNITS for MDSYS.SDO_ANGLE_UNITS;

grant select on MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT to public;
grant select on MDSYS.SDO_PROJECTIONS_OLD_FORMAT to public;
grant select on MDSYS.SDO_DATUMS_OLD_FORMAT to public;
create or replace  public synonym SDO_ELLIPSOIDS_OLD_FORMAT for 
 MDSYS.SDO_ELLIPSOIDS_OLD_FORMAT;
create or replace  public synonym SDO_PROJECTIONS_OLD_FORMAT for 
 MDSYS.SDO_PROJECTIONS_OLD_FORMAT;
create or replace  public synonym SDO_DATUMS_OLD_FORMAT for 
 MDSYS.SDO_DATUMS_OLD_FORMAT;

grant select on MDSYS.SDO_COORD_OPS to public;
--grant insert on MDSYS.SDO_COORD_OPS to public;
--grant update on MDSYS.SDO_COORD_OPS to public;
--grant delete on MDSYS.SDO_COORD_OPS to public;
create or replace  public synonym SDO_COORD_OPS for 
 MDSYS.SDO_COORD_OPS;

grant select on MDSYS.SDO_AVAILABLE_OPS to public;
create or replace  public synonym SDO_AVAILABLE_OPS for 
 MDSYS.SDO_AVAILABLE_OPS;
grant select on MDSYS.SDO_AVAILABLE_ELEM_OPS to public;
create or replace  public synonym SDO_AVAILABLE_ELEM_OPS for 
 MDSYS.SDO_AVAILABLE_ELEM_OPS;
grant select on MDSYS.SDO_AVAILABLE_NON_ELEM_OPS to public;
create or replace  public synonym SDO_AVAILABLE_NON_ELEM_OPS for 
 MDSYS.SDO_AVAILABLE_NON_ELEM_OPS;

grant select on MDSYS.SDO_COORD_OP_PATHS to public;
--grant insert on MDSYS.SDO_COORD_OP_PATHS to public;
--grant update on MDSYS.SDO_COORD_OP_PATHS to public;
--grant delete on MDSYS.SDO_COORD_OP_PATHS to public;
create or replace  public synonym SDO_COORD_OP_PATHS for 
 MDSYS.SDO_COORD_OP_PATHS;

grant select on MDSYS.SDO_PREFERRED_OPS_SYSTEM to public;
--grant insert on MDSYS.SDO_PREFERRED_OPS_SYSTEM to public;
--grant update on MDSYS.SDO_PREFERRED_OPS_SYSTEM to public;
--grant delete on MDSYS.SDO_PREFERRED_OPS_SYSTEM to public;
create or replace  public synonym SDO_PREFERRED_OPS_SYSTEM for 
 MDSYS.SDO_PREFERRED_OPS_SYSTEM;

grant select on MDSYS.SDO_PREFERRED_OPS_USER to public;
grant insert on MDSYS.SDO_PREFERRED_OPS_USER to public;
grant update on MDSYS.SDO_PREFERRED_OPS_USER to public;
grant delete on MDSYS.SDO_PREFERRED_OPS_USER to public;
create or replace  public synonym SDO_PREFERRED_OPS_USER for 
 MDSYS.SDO_PREFERRED_OPS_USER;

grant select on MDSYS.SDO_COORD_REF_SYS to public;
--grant insert on MDSYS.SDO_COORD_REF_SYS to public;
--grant update on MDSYS.SDO_COORD_REF_SYS to public;
--grant delete on MDSYS.SDO_COORD_REF_SYS to public;
create or replace  public synonym SDO_COORD_REF_SYS for 
 MDSYS.SDO_COORD_REF_SYS;

grant select on MDSYS.SDO_COORD_REF_SYSTEM to public;
--grant insert on MDSYS.SDO_COORD_REF_SYSTEM to public;
--grant update on MDSYS.SDO_COORD_REF_SYSTEM to public;
--grant delete on MDSYS.SDO_COORD_REF_SYSTEM to public;
create or replace  public synonym SDO_COORD_REF_SYSTEM for 
 MDSYS.SDO_COORD_REF_SYSTEM;

grant select on MDSYS.SDO_UNITS_OF_MEASURE to public;
--grant insert on MDSYS.SDO_UNITS_OF_MEASURE to public;
--grant update on MDSYS.SDO_UNITS_OF_MEASURE to public;
--grant delete on MDSYS.SDO_UNITS_OF_MEASURE to public;
create or replace  public synonym SDO_UNITS_OF_MEASURE for 
 MDSYS.SDO_UNITS_OF_MEASURE;

grant select on MDSYS.SDO_PRIME_MERIDIANS to public;
--grant insert on MDSYS.SDO_PRIME_MERIDIANS to public;
--grant update on MDSYS.SDO_PRIME_MERIDIANS to public;
--grant delete on MDSYS.SDO_PRIME_MERIDIANS to public;
create or replace  public synonym SDO_PRIME_MERIDIANS for 
 MDSYS.SDO_PRIME_MERIDIANS;

grant select on MDSYS.SDO_ELLIPSOIDS to public;
--grant insert on MDSYS.SDO_ELLIPSOIDS to public;
--grant update on MDSYS.SDO_ELLIPSOIDS to public;
--grant delete on MDSYS.SDO_ELLIPSOIDS to public;
create or replace  public synonym SDO_ELLIPSOIDS for 
 MDSYS.SDO_ELLIPSOIDS;

grant select on MDSYS.SDO_DATUMS to public;
--grant insert on MDSYS.SDO_DATUMS to public;
--grant update on MDSYS.SDO_DATUMS to public;
--grant delete on MDSYS.SDO_DATUMS to public;
create or replace  public synonym SDO_DATUMS for 
 MDSYS.SDO_DATUMS;

grant select on MDSYS.SDO_COORD_SYS to public;
--grant insert on MDSYS.SDO_COORD_SYS to public;
--grant update on MDSYS.SDO_COORD_SYS to public;
--grant delete on MDSYS.SDO_COORD_SYS to public;
create or replace  public synonym SDO_COORD_SYS for 
 MDSYS.SDO_COORD_SYS;

grant select on MDSYS.SDO_COORD_AXES to public;
--grant insert on MDSYS.SDO_COORD_AXES to public;
--grant update on MDSYS.SDO_COORD_AXES to public;
--grant delete on MDSYS.SDO_COORD_AXES to public;
create or replace  public synonym SDO_COORD_AXES for 
 MDSYS.SDO_COORD_AXES;

grant select on MDSYS.SDO_COORD_AXIS_NAMES to public;
--grant insert on MDSYS.SDO_COORD_AXIS_NAMES to public;
--grant update on MDSYS.SDO_COORD_AXIS_NAMES to public;
--grant delete on MDSYS.SDO_COORD_AXIS_NAMES to public;
create or replace  public synonym SDO_COORD_AXIS_NAMES for 
 MDSYS.SDO_COORD_AXIS_NAMES;

grant select on MDSYS.SDO_COORD_OP_METHODS to public;
--grant insert on MDSYS.SDO_COORD_OP_METHODS to public;
--grant update on MDSYS.SDO_COORD_OP_METHODS to public;
--grant delete on MDSYS.SDO_COORD_OP_METHODS to public;
create or replace  public synonym SDO_COORD_OP_METHODS for 
 MDSYS.SDO_COORD_OP_METHODS;

grant select on MDSYS.SDO_COORD_OP_PARAMS to public;
--grant insert on MDSYS.SDO_COORD_OP_PARAMS to public;
--grant update on MDSYS.SDO_COORD_OP_PARAMS to public;
--grant delete on MDSYS.SDO_COORD_OP_PARAMS to public;
create or replace  public synonym SDO_COORD_OP_PARAMS for 
 MDSYS.SDO_COORD_OP_PARAMS;

grant select on MDSYS.SDO_COORD_OP_PARAM_USE to public;
--grant insert on MDSYS.SDO_COORD_OP_PARAM_USE to public;
--grant update on MDSYS.SDO_COORD_OP_PARAM_USE to public;
--grant delete on MDSYS.SDO_COORD_OP_PARAM_USE to public;
create or replace  public synonym SDO_COORD_OP_PARAM_USE for 
 MDSYS.SDO_COORD_OP_PARAM_USE;

grant select on MDSYS.SDO_COORD_OP_PARAM_VALS to public;
--grant insert on MDSYS.SDO_COORD_OP_PARAM_VALS to public;
--grant update on MDSYS.SDO_COORD_OP_PARAM_VALS to public;
--grant delete on MDSYS.SDO_COORD_OP_PARAM_VALS to public;
create or replace  public synonym SDO_COORD_OP_PARAM_VALS for 
 MDSYS.SDO_COORD_OP_PARAM_VALS;

create or replace  public synonym SDO_TRANSIENT_RULE for MDSYS.SDO_TRANSIENT_RULE;
create or replace  public synonym SDO_TRANSIENT_RULE_SET for MDSYS.SDO_TRANSIENT_RULE_SET;
create or replace  public synonym SDO_SRID_CHAIN for MDSYS.SDO_SRID_CHAIN;
create or replace  public synonym SDO_SRID_LIST for MDSYS.SDO_SRID_LIST;
create or replace  public synonym SDO_TFM_CHAIN for MDSYS.SDO_TFM_CHAIN;
create or replace  public synonym TFM_PLAN for MDSYS.TFM_PLAN;

grant execute on mdsys.sdo_transient_rule to public;
grant execute on mdsys.sdo_transient_rule_set to public;
grant execute on mdsys.sdo_srid_chain to public;
grant execute on mdsys.sdo_srid_list to public;
grant execute on mdsys.sdo_tfm_chain to public;
grant execute on mdsys.tfm_plan to public;