Rem
Rem $Header: sdo/admin/sdoepsgx.sql /main/8 2009/12/02 05:47:49 mhorhamm Exp $
Rem
Rem sdoepsgx.sql
Rem
Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdoepsgx.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    09/08/06 - Do not load .asc file
Rem    mhorhamm    09/01/06 - Load sample GEOID03 grid file for Hawaii
Rem    mhorhamm    06/24/05 - Drop two directories 
Rem    mhorhamm    06/24/05 - work directory does not match all platforms, 
Rem                           correctly 
Rem    mhorhamm    06/07/05 - Initially deregister XML schema, if necessary 
Rem    mhorhamm    04/05/05 - Close file after closing LOB 
Rem    mhorhamm    12/09/04 - Do not load NTv2 file 
Rem    sravada     08/31/04 - sravada_lrg_1733556
Rem    sravada     08/31/04 - Created
Rem


ALTER SESSION SET current_schema=SYS;

DECLARE
  ORCL_HOME_DIR VARCHAR2(128);
  ORCL_WORK_DIR VARCHAR2(128);
  Src_loc       BFILE;
  Dest_loc      CLOB;
  CURSOR PARAM_FILES IS
    SELECT
      COORD_OP_ID,
      PARAMETER_ID,
      PARAM_VALUE_FILE_REF
    FROM
      MDSYS.SDO_COORD_OP_PARAM_VALS
    WHERE
      PARAMETER_ID IN (8656, 8657, 8658, 8666);
  PARAM_FILE PARAM_FILES%ROWTYPE;
  ACTUAL_FILE_NAME VARCHAR2(128);
  platform NUMBER;
BEGIN
  SELECT platform_id INTO platform FROM v$database;
  IF(platform = 15) THEN /* On the VMS platform, GET_ENV cannot be used */
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY admin_dir AS ''ORA_ROOT:[MD.ADMIN]''';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY work_dir AS ''ORA_ROOT:[WORK]''';
  ELSE
    DBMS_SYSTEM.GET_ENV('T_WORK', ORCL_WORK_DIR);
    DBMS_SYSTEM.GET_ENV('ORACLE_HOME', ORCL_HOME_DIR);
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY admin_dir AS ''' || ORCL_HOME_DIR || '/md/admin''';
    EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY work_dir AS ''' || ORCL_WORK_DIR || '''';
  END IF;

  FOR PARAM_FILE IN PARAM_FILES LOOP
    CASE UPPER(PARAM_FILE.PARAM_VALUE_FILE_REF)
      /* NADCON */
      WHEN 'ALASKA.LAS'   THEN ACTUAL_FILE_NAME := 'alaska.laa';
      WHEN 'ALASKA.LOS'   THEN ACTUAL_FILE_NAME := 'alaska.loa';
      WHEN 'ALHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'alhpgn.laa';
      WHEN 'ALHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'alhpgn.loa';
      WHEN 'ARHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'arhpgn.laa';
      WHEN 'ARHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'arhpgn.loa';
      WHEN 'AZHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'azhpgn.laa';
      WHEN 'AZHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'azhpgn.loa';
      WHEN 'CNHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'cnhpgn.laa';
      WHEN 'CNHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'cnhpgn.loa';
      WHEN 'COHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'cohpgn.laa';
      WHEN 'COHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'cohpgn.loa';
      WHEN 'CONUS.LAS'    THEN ACTUAL_FILE_NAME := 'conus.laa';
      WHEN 'CONUS.LOS'    THEN ACTUAL_FILE_NAME := 'conus.loa';
      WHEN 'CSHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'cshpgn.laa';
      WHEN 'CSHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'cshpgn.loa';
      WHEN 'EMHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'emhpgn.laa';
      WHEN 'EMHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'emhpgn.loa';
      WHEN 'ESHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'eshpgn.laa';
      WHEN 'ESHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'eshpgn.loa';
      WHEN 'ETHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'ethpgn.laa';
      WHEN 'ETHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'ethpgn.loa';
      WHEN 'FLHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'flhpgn.laa';
      WHEN 'FLHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'flhpgn.loa';
      WHEN 'GAHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'gahpgn.laa';
      WHEN 'GAHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'gahpgn.loa';
      WHEN 'HAWAII.LAS'   THEN ACTUAL_FILE_NAME := 'hawaii.laa';
      WHEN 'HAWAII.LOS'   THEN ACTUAL_FILE_NAME := 'hawaii.loa';
      WHEN 'HIHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'hihpgn.laa';
      WHEN 'HIHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'hihpgn.loa';
      WHEN 'IAHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'iahpgn.laa';
      WHEN 'IAHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'iahpgn.loa';
      WHEN 'ILHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'ilhpgn.laa';
      WHEN 'ILHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'ilhpgn.loa';
      WHEN 'INHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'inhpgn.laa';
      WHEN 'INHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'inhpgn.loa';
      WHEN 'KSHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'kshpgn.laa';
      WHEN 'KSHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'kshpgn.loa';
      WHEN 'KYHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'kyhpgn.laa';
      WHEN 'KYHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'kyhpgn.loa';
      WHEN 'LAHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'lahpgn.laa';
      WHEN 'LAHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'lahpgn.loa';
      WHEN 'MDHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'mdhpgn.laa';
      WHEN 'MDHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'mdhpgn.loa';
      WHEN 'MEHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'mehpgn.laa';
      WHEN 'MEHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'mehpgn.loa';
      WHEN 'MIHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'mihpgn.laa';
      WHEN 'MIHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'mihpgn.loa';
      WHEN 'MNHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'mnhpgn.laa';
      WHEN 'MNHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'mnhpgn.loa';
      WHEN 'MOHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'mohpgn.laa';
      WHEN 'MOHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'mohpgn.loa';
      WHEN 'MSHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'mshpgn.laa';
      WHEN 'MSHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'mshpgn.loa';
      WHEN 'NBHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'nbhpgn.laa';
      WHEN 'NBHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'nbhpgn.loa';
      WHEN 'NDHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'ndhpgn.laa';
      WHEN 'NDHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'ndhpgn.loa';
      WHEN 'NEHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'nehpgn.laa';
      WHEN 'NEHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'nehpgn.loa';
      WHEN 'NJHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'njhpgn.laa';
      WHEN 'NJHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'njhpgn.loa';
      WHEN 'NMHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'nmhpgn.laa';
      WHEN 'NMHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'nmhpgn.loa';
      WHEN 'NVHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'nvhpgn.laa';
      WHEN 'NVHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'nvhpgn.loa';
      WHEN 'NYHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'nyhpgn.laa';
      WHEN 'NYHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'nyhpgn.loa';
      WHEN 'OHHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'ohhpgn.laa';
      WHEN 'OHHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'ohhpgn.loa';
      WHEN 'OKHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'okhpgn.laa';
      WHEN 'OKHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'okhpgn.loa';
      WHEN 'PRVI.LAS'     THEN ACTUAL_FILE_NAME := 'prvi.laa';
      WHEN 'PRVI.LOS'     THEN ACTUAL_FILE_NAME := 'prvi.loa';
      WHEN 'PVHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'pvhpgn.laa';
      WHEN 'PVHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'pvhpgn.loa';
      WHEN 'SDHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'sdhpgn.laa';
      WHEN 'SDHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'sdhpgn.loa';
      WHEN 'STGEORGE.LAS' THEN ACTUAL_FILE_NAME := 'stgeorge.laa';
      WHEN 'STGEORGE.LOS' THEN ACTUAL_FILE_NAME := 'stgeorge.loa';
      WHEN 'STLRNC.LAS'   THEN ACTUAL_FILE_NAME := 'stlrnc.laa';
      WHEN 'STLRNC.LOS'   THEN ACTUAL_FILE_NAME := 'stlrnc.loa';
      WHEN 'STPAUL.LAS'   THEN ACTUAL_FILE_NAME := 'stpaul.laa';
      WHEN 'STPAUL.LOS'   THEN ACTUAL_FILE_NAME := 'stpaul.loa';
      WHEN 'TNHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'tnhpgn.laa';
      WHEN 'TNHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'tnhpgn.loa';
      WHEN 'UTHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'uthpgn.laa';
      WHEN 'UTHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'uthpgn.loa';
      WHEN 'VAHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'vahpgn.laa';
      WHEN 'VAHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'vahpgn.loa';
      WHEN 'WIHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'wihpgn.laa';
      WHEN 'WIHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'wihpgn.loa';
      WHEN 'WMHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'wmhpgn.laa';
      WHEN 'WMHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'wmhpgn.loa';
      WHEN 'WOHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'wohpgn.laa';
      WHEN 'WOHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'wohpgn.loa';
      WHEN 'WSHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'wshpgn.laa';
      WHEN 'WSHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'wshpgn.loa';
      WHEN 'WTHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'wthpgn.laa';
      WHEN 'WTHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'wthpgn.loa';
      WHEN 'WVHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'wvhpgn.laa';
      WHEN 'WVHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'wvhpgn.loa';
      WHEN 'WYHPGN.LAS'   THEN ACTUAL_FILE_NAME := 'wyhpgn.laa';
      WHEN 'WYHPGN.LOS'   THEN ACTUAL_FILE_NAME := 'wyhpgn.loa';
      /* NTv2 */
/*    WHEN 'NTV2_0.GSB'   THEN ACTUAL_FILE_NAME := 'ntv20.gsa';*/
      /* GEOID03 */
/*    WHEN 'G2003H01.ASC' THEN ACTUAL_FILE_NAME := 'g2003h01.asc'; */
      ELSE                     ACTUAL_FILE_NAME := NULL;
    END CASE;
    IF(NOT (ACTUAL_FILE_NAME IS NULL)) THEN
      BEGIN
        Src_loc := BFILENAME('ADMIN_DIR', ACTUAL_FILE_NAME);
        DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
        EXCEPTION
          WHEN OTHERS THEN
            Src_loc := BFILENAME('WORK_DIR', ACTUAL_FILE_NAME);
            DBMS_LOB.OPEN(Src_loc, DBMS_LOB.LOB_READONLY);
      END;
      UPDATE MDSYS.SDO_COORD_OP_PARAM_VALS SET PARAM_VALUE_FILE = EMPTY_CLOB() WHERE COORD_OP_ID = PARAM_FILE.COORD_OP_ID AND PARAMETER_ID = PARAM_FILE.PARAMETER_ID RETURNING PARAM_VALUE_FILE INTO Dest_loc;
      DBMS_LOB.OPEN(Dest_loc, DBMS_LOB.LOB_READWRITE);
      DBMS_LOB.LOADFROMFILE(Dest_loc, Src_loc, DBMS_LOB.LOBMAXSIZE);
      DBMS_LOB.CLOSE(Dest_loc);
      DBMS_LOB.CLOSE(Src_loc);
      DBMS_LOB.FILECLOSE(Src_loc);
    END IF;
  END LOOP;

--  DECLARE
--    usr VARCHAR2(30);
--    url VARCHAR2(200);
--    cnt NUMBER;
--  BEGIN
--    usr := 'MDSYS';
--    url := 'http://www.oracle.com/2004/spatial/epsg/gridfile/schema/sdoepsggrid.xsd';
--
--    EXECUTE IMMEDIATE
--      'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2'
--      INTO cnt using usr, url;
--    IF (cnt <> 0) THEN
--      DBMS_XMLSCHEMA.deleteSchema(url, DBMS_XMLSCHEMA.DELETE_CASCADE);
--    end if;
--    EXCEPTION WHEN OTHERS THEN
--      DBMS_OUTPUT.PUT_LINE('Delete Schema ' || url || ' failed.');
--  END;

--  DECLARE
--    usr VARCHAR2(30);
--    url VARCHAR2(200);
--    cnt NUMBER;
--  BEGIN
--    usr := 'MDSYS';
--    url := 'http://www.oracle.com/2009/spatial/vis3d/schema/sdo3d.xsd';
--
--    EXECUTE IMMEDIATE
--      'SELECT count(*) FROM dba_xml_schemas WHERE owner=:1 AND schema_url=:2'
--      INTO cnt using usr, url;
--    IF (cnt <> 0) THEN
--      DBMS_XMLSCHEMA.deleteSchema(url, DBMS_XMLSCHEMA.DELETE_CASCADE);
--    end if;
--    EXCEPTION WHEN OTHERS THEN
--      DBMS_OUTPUT.PUT_LINE('Delete Schema ' || url || ' failed.');
--  END;

  BEGIN 
  BEGIN /* Load .xsd and .xsl files into XML DB: */
--    BEGIN
--      DBMS_XMLSCHEMA.registerSchema(
--        SCHEMAURL => 'http://www.oracle.com/2004/spatial/epsg/gridfile/schema/sdoepsggrid.xsd',
--        SCHEMADOC => BFILENAME('WORK_DIR', 'sdoepsggrid.xsd'),
--        LOCAL     => FALSE,
--        OWNER     => 'MDSYS');
--    EXCEPTION WHEN OTHERS THEN
--      DBMS_OUTPUT.PUT_LINE('Register Schema sdoepsggrid.xsd failed.');
--    end;

--    begin
--      DBMS_XMLSCHEMA.registerSchema(
--        SCHEMAURL => 'http://www.oracle.com/2009/spatial/vis3d/schema/sdo3d.xsd',
--        SCHEMADOC => BFILENAME('WORK_DIR', 'sdo3d.xsd'),
--        LOCAL     => FALSE,
--        OWNER     => 'MDSYS');
--    EXCEPTION WHEN OTHERS THEN
--      DBMS_OUTPUT.PUT_LINE('Register Schema sdo3d.xsd failed.');
--    end;

    IF(DBMS_XDB.CREATEFOLDER('/home') = FALSE) THEN
      DBMS_OUTPUT.PUT_LINE('Creation of folder /home in XML DB failed.');
    END IF;

    IF(DBMS_XDB.CREATEFOLDER('/home/MDSYS') = FALSE) THEN
      DBMS_OUTPUT.PUT_LINE('Creation of folder /home/MDSYS in XML DB failed.');
    END IF;

    IF(DBMS_XDB.CREATEFOLDER('/home/MDSYS/epsg') = FALSE) THEN
      DBMS_OUTPUT.PUT_LINE('Creation of folder /home/MDSYS/epsg in XML DB failed.');
    END IF;
    EXCEPTION
        WHEN OTHERS THEN NULL;
   END;

   BEGIN
    BEGIN
      IF( DBMS_XDB.createResource(
            '/home/MDSYS/epsg/sdoepsggrid2nadcon.xsl',
            BFILENAME('ADMIN_DIR', 'sdoepsggrid2nadcon.xsl'),
            NLS_CHARSET_ID('AL32UTF8')) = FALSE) THEN
        DBMS_OUTPUT.PUT_LINE('Loading of sdoepsggrid2nadcon.xsl failed.');
      END IF;
      EXCEPTION
        WHEN OTHERS THEN
          IF( DBMS_XDB.createResource(
                '/home/MDSYS/epsg/sdoepsggrid2nadcon.xsl',
                BFILENAME('WORK_DIR', 'sdoepsggrid2nadcon.xsl'),
                NLS_CHARSET_ID('AL32UTF8')) = FALSE) THEN
            DBMS_OUTPUT.PUT_LINE('Loading of sdoepsggrid2nadcon.xsl failed.');
          END IF;
    END;
    EXCEPTION WHEN OTHERS THEN NULL;
    END;


   BEGIN
    BEGIN
      IF( DBMS_XDB.createResource(
            '/home/MDSYS/epsg/sdoepsggrid2ntv2.xsl',
            BFILENAME('ADMIN_DIR', 'sdoepsggrid2ntv2.xsl'),
            NLS_CHARSET_ID('AL32UTF8')) = FALSE) THEN
        DBMS_OUTPUT.PUT_LINE('Loading of sdoepsggrid2ntv2.xsl failed.');
      END IF;
      EXCEPTION
        WHEN OTHERS THEN
          IF( DBMS_XDB.createResource(
                '/home/MDSYS/epsg/sdoepsggrid2ntv2.xsl',
                BFILENAME('WORK_DIR', 'sdoepsggrid2ntv2.xsl'),
                NLS_CHARSET_ID('AL32UTF8')) = FALSE) THEN
            DBMS_OUTPUT.PUT_LINE('Loading of sdoepsggrid2ntv2.xsl failed.');
          END IF;
    END;
    EXCEPTION WHEN OTHERS THEN NULL;
    END;

    EXECUTE IMMEDIATE 'DROP DIRECTORY ADMIN_DIR';
    EXECUTE IMMEDIATE 'DROP DIRECTORY WORK_DIR';

  END;
END;
/

























ALTER SESSION SET current_schema=MDSYS;