Rem
Rem $Header: sdo/admin/sdocsvws.sql /main/3 2009/10/21 07:52:27 mhorhamm Exp $
Rem
Rem sdocsvws.sql
Rem
Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdocsvws.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    mhorhamm    10/19/09 - Fix views on SDO_DATUMS
Rem    mhorhamm    02/14/05 - Create synonyms for views 
Rem    sravada     08/31/04 - sravada_lrg_1733556
Rem    sravada     08/31/04 - Created
Rem


declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_DATUM_ENGINEERING';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_DATUM_ENGINEERING
      AS
        SELECT
          DATUM_ID,
          DATUM_NAME,
          ELLIPSOID_ID,
          PRIME_MERIDIAN_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE,
          SHIFT_X,
          SHIFT_Y,
          SHIFT_Z,
          ROTATE_X,
          ROTATE_Y,
          ROTATE_Z,
          SCALE_ADJUST
        FROM
          MDSYS.SDO_DATUMS
        WHERE
          DATUM_TYPE = ''ENGINEERING''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_DATUM_GEODETIC';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_DATUM_GEODETIC
      AS
        SELECT
          DATUM_ID,
          DATUM_NAME,
          ELLIPSOID_ID,
          PRIME_MERIDIAN_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE,
          SHIFT_X,
          SHIFT_Y,
          SHIFT_Z,
          ROTATE_X,
          ROTATE_Y,
          ROTATE_Z,
          SCALE_ADJUST
        FROM
          MDSYS.SDO_DATUMS
        WHERE
          DATUM_TYPE = ''GEODETIC''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_DATUM_VERTICAL';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_DATUM_VERTICAL
      AS
        SELECT
          DATUM_ID,
          DATUM_NAME,
          ELLIPSOID_ID,
          PRIME_MERIDIAN_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE,
          SHIFT_X,
          SHIFT_Y,
          SHIFT_Z,
          ROTATE_X,
          ROTATE_Y,
          ROTATE_Z,
          SCALE_ADJUST
        FROM
          MDSYS.SDO_DATUMS
        WHERE
          DATUM_TYPE = ''VERTICAL''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_CRS_COMPOUND';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_CRS_COMPOUND
      AS
        SELECT
          SRID,
          COORD_REF_SYS_NAME,
          CMPD_HORIZ_SRID,
          CMPD_VERT_SRID,
          INFORMATION_SOURCE,
          DATA_SOURCE
        FROM
          MDSYS.SDO_COORD_REF_SYS
        WHERE
          COORD_REF_SYS_KIND = ''COMPOUND''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_CRS_ENGINEERING';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_CRS_ENGINEERING
      AS
        SELECT
          SRID,
          COORD_REF_SYS_NAME,
          COORD_SYS_ID,
          DATUM_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE
        FROM
          MDSYS.SDO_COORD_REF_SYS
        WHERE
          COORD_REF_SYS_KIND = ''ENGINEERING''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_CRS_GEOCENTRIC';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_CRS_GEOCENTRIC
      AS
        SELECT
          SRID,
          COORD_REF_SYS_NAME,
          COORD_SYS_ID,
          DATUM_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE
        FROM
          MDSYS.SDO_COORD_REF_SYS
        WHERE
          COORD_REF_SYS_KIND = ''GEOCENTRIC''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_CRS_GEOGRAPHIC2D';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_CRS_GEOGRAPHIC2D
      AS
        SELECT
          SRID,
          COORD_REF_SYS_NAME,
          COORD_SYS_ID,
          DATUM_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE
        FROM
          MDSYS.SDO_COORD_REF_SYS
        WHERE
          COORD_REF_SYS_KIND = ''GEOGRAPHIC2D''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_CRS_GEOGRAPHIC3D';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_CRS_GEOGRAPHIC3D
      AS
        SELECT
          SRID,
          COORD_REF_SYS_NAME,
          COORD_SYS_ID,
          DATUM_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE
        FROM
          MDSYS.SDO_COORD_REF_SYS
        WHERE
          COORD_REF_SYS_KIND = ''GEOGRAPHIC3D''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_CRS_PROJECTED';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_CRS_PROJECTED
      AS
        SELECT
          SRID,
          COORD_REF_SYS_NAME,
          COORD_SYS_ID,
          SOURCE_GEOG_SRID,
          PROJECTION_CONV_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE
        FROM
          MDSYS.SDO_COORD_REF_SYS
        WHERE
          COORD_REF_SYS_KIND = ''PROJECTED''';
    execute immediate stm;
  end if;
end;
/
 
declare
  stm VARCHAR2(10000);
  rec_count NUMBER;
begin
  select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_CRS_VERTICAL';
  if(rec_count = 0) then
    stm :=
      'CREATE VIEW MDSYS.SDO_CRS_VERTICAL
      AS
        SELECT
          SRID,
          COORD_REF_SYS_NAME,
          COORD_SYS_ID,
          DATUM_ID,
          INFORMATION_SOURCE,
          DATA_SOURCE
        FROM
          MDSYS.SDO_COORD_REF_SYS
        WHERE
          COORD_REF_SYS_KIND = ''VERTICAL''';
    execute immediate stm;
  end if;
end;
/

grant select on MDSYS.SDO_DATUM_ENGINEERING to public;
create or replace public synonym SDO_DATUM_ENGINEERING for MDSYS.SDO_DATUM_ENGINEERING;

grant select on MDSYS.SDO_DATUM_GEODETIC to public;
create or replace public synonym SDO_DATUM_GEODETIC for MDSYS.SDO_DATUM_GEODETIC;

grant select on MDSYS.SDO_DATUM_VERTICAL to public;
create or replace public synonym SDO_DATUM_VERTICAL for MDSYS.SDO_DATUM_VERTICAL;

grant select on MDSYS.SDO_CRS_COMPOUND to public;
create or replace public synonym SDO_CRS_COMPOUND for MDSYS.SDO_CRS_COMPOUND;

grant select on MDSYS.SDO_CRS_ENGINEERING to public;
create or replace public synonym SDO_CRS_ENGINEERING for MDSYS.SDO_CRS_ENGINEERING;

grant select on MDSYS.SDO_CRS_GEOCENTRIC to public;
create or replace public synonym SDO_CRS_GEOCENTRIC for MDSYS.SDO_CRS_GEOCENTRIC;

grant select on MDSYS.SDO_CRS_GEOGRAPHIC2D to public;
create or replace public synonym SDO_CRS_GEOGRAPHIC2D for MDSYS.SDO_CRS_GEOGRAPHIC2D;

grant select on MDSYS.SDO_CRS_GEOGRAPHIC3D to public;
create or replace public synonym SDO_CRS_GEOGRAPHIC3D for MDSYS.SDO_CRS_GEOGRAPHIC3D;

grant select on MDSYS.SDO_CRS_PROJECTED to public;
create or replace public synonym SDO_CRS_PROJECTED for MDSYS.SDO_CRS_PROJECTED;

grant select on MDSYS.SDO_CRS_VERTICAL to public;
create or replace public synonym SDO_CRS_VERTICAL for MDSYS.SDO_CRS_VERTICAL;

commit;
 
set define on;