Rem
Rem $Header: sdogmmh.sql 24-apr-2008.14:03:45 sravada Exp $
Rem
Rem sdogmmh.sql
Rem
Rem Copyright (c) 2003, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdogmmh.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     04/24/08 - add OIDs
Rem    sravada     04/02/07 - add missing methods
Rem    sravada     06/13/05 - remove purge dba_recyclebin 
Rem    sravada     12/06/04  - 
Rem    sravada     10/11/04 -  move new methods as alter type
Rem    mhorhamm    09/27/04 - Move some of the logic from sdoogc to sdogmmh, sdogmmb 
Rem    mhorhamm    09/21/04 - Add more OGC functions 
Rem    mhorhamm    09/13/04 - continue
Rem    mhorhamm    09/07/04 - Add functions 
Rem    sravada     08/18/04 - bug 3834721 
Rem    sravada     08/06/04 - change to dba_recyclebin 
Rem    sravada     07/28/04 - purge recyclebin 
Rem    mhorhamm    05/19/04 - Change parameter name from asrid to srid 
Rem    sravada     04/26/04 - add commit 
Rem    mhorhamm    12/09/03 - Check owner of ST_GEOMETRY 
Rem    mhorhamm    12/01/03 - Define types conditionally - if not already done 
Rem    mhorhamm    08/27/03 - Delete initial SETs 
Rem    mhorhamm    08/27/03 - Make SRID default null 
Rem    mhorhamm    08/26/03 - Add more functions to SDO_GEOMETRY 
Rem    mhorhamm    08/26/03 - alter type SDO_GEOMETRY replace 
Rem    mhorhamm    08/25/03 - Add more user-defined functions 
Rem    mhorhamm    08/25/03 - Add more user-defined functions 
Rem    mhorhamm    08/22/03 - Add functions in the SQL/mm section of 
Rem                           user-defined functions 
Rem    mhorhamm    08/21/03 - Add more functions 
Rem    mhorhamm    08/20/03 - Add functions 
Rem    mhorhamm    08/20/03 - Created
Rem


declare
begin
   begin
     execute immediate ' PURGE TABLESPACE SYSTEM USER MDSYS  ';
     execute immediate ' PURGE TABLESPACE SYSAUX USER MDSYS  ';
           exception when others then NULL;
   end;
end;
/

declare
begin
  begin
   execute immediate ' DROP TYPE BODY ST_GEOMETRY ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_POINT ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_CURVE ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_SURFACE ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_CURVEPOLYGON ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_LINESTRING ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_POLYGON ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_GEOMCOLLECTION ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_MULTIPOINT ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_MULTICURVE ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_MULTISURFACE ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_MULTILINESTRING ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_MULTIPOLYGON ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_CIRCULARSTRING ';
           exception when others then NULL;
  end;
  begin
   execute immediate ' DROP TYPE BODY ST_COMPOUNDCURVE ';
           exception when others then NULL;
  end;

end;
/

DECLARE
BEGIN
  begin
   EXECUTE IMMEDIATE ' DROP table sdo_st_tolerance ';
   exception when others then NULL;
  end;
END;
/

CREATE GLOBAL TEMPORARY TABLE MDSYS.SDO_ST_TOLERANCE ( Tolerance NUMBER );
GRANT select, insert,delete on MDSYS.SDO_ST_TOLERANCE to PUBLIC;
create or replace public synonym SDO_ST_TOLERANCE for MDSYS.SDO_ST_TOLERANCE;


DECLARE
BEGIN
  BEGIN
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_GEOMETRY 
  OID ''4BA3494AB3EEE5B6E040578CB2057856'' AS OBJECT (
  GEOM SDO_GEOMETRY,
  MEMBER FUNCTION GET_SDO_GEOM RETURN SDO_GEOMETRY DETERMINISTIC,
  MEMBER FUNCTION GET_WKB RETURN BLOB DETERMINISTIC,
  MEMBER FUNCTION GET_WKT RETURN CLOB DETERMINISTIC,
  MEMBER FUNCTION ST_CoordDim RETURN SMALLINT DETERMINISTIC,
  MEMBER FUNCTION ST_IsValid RETURN INTEGER DETERMINISTIC,
  MEMBER FUNCTION ST_SRID RETURN INTEGER,
  MEMBER FUNCTION ST_SRID(asrid INTEGER) RETURN ST_Geometry,
  STATIC FUNCTION FROM_WKT(wkt CLOB) RETURN ST_GEOMETRY DETERMINISTIC,
  STATIC FUNCTION FROM_WKT(wkt VARCHAR2) RETURN ST_GEOMETRY DETERMINISTIC,
  STATIC FUNCTION FROM_WKB(wkb BLOB) RETURN ST_GEOMETRY DETERMINISTIC,
  STATIC FUNCTION FROM_WKT(wkt CLOB, asrid INTEGER) 
            RETURN ST_GEOMETRY DETERMINISTIC,
  STATIC FUNCTION FROM_WKT(wkt VARCHAR2, asrid INTEGER) 
            RETURN ST_GEOMETRY DETERMINISTIC,
  STATIC FUNCTION FROM_WKB(wkb BLOB, asrid INTEGER) 
            RETURN ST_GEOMETRY DETERMINISTIC,
  STATIC FUNCTION FROM_SDO_GEOM(ageometry SDO_GEOMETRY) 
            RETURN ST_GEOMETRY DETERMINISTIC) NOT FINAL';
  exception when others then NULL;
  end;
end;
/

declare
begin
  begin
  EXECUTE IMMEDIATE '
  ALTER TYPE ST_GEOMETRY ADD
  MEMBER FUNCTION ST_IsEmpty RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Envelope RETURN ST_Geometry DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Boundary RETURN ST_GEOMETRY DETERMINISTIC,
  ADD MEMBER FUNCTION ST_GeometryType RETURN VARCHAR2 DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Buffer(d NUMBER) RETURN ST_Geometry DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Equals(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_SymmetricDifference(g2 ST_Geometry) 
              RETURN ST_Geometry DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Distance(g2 ST_Geometry) RETURN NUMBER DETERMINISTIC,
  ADD MEMBER FUNCTION ST_IsSimple RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Intersects(g2 ST_Geometry) 
        RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Relate(g2 ST_Geometry, PatternMatrix VARCHAR2) 
              RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Cross(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Disjoint(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Touch(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Within(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Overlap(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Contains(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Intersection(g2 ST_Geometry) 
            RETURN ST_Geometry DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Difference(g2 ST_Geometry) 
             RETURN ST_Geometry DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Union(g2 ST_Geometry) RETURN ST_Geometry DETERMINISTIC,
  ADD MEMBER FUNCTION ST_ConvexHull RETURN ST_Geometry DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Centroid RETURN ST_Geometry DETERMINISTIC 
    CASCADE';
  exception when others then null;
end;
end;
/

declare
begin
  begin
  EXECUTE IMMEDIATE '
  ALTER TYPE ST_GEOMETRY 
ADD MEMBER FUNCTION ST_SymDifference(g2 ST_Geometry)
              RETURN ST_Geometry DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Touches(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_Crosses(g2 ST_Geometry) RETURN Integer DETERMINISTIC,
  ADD MEMBER FUNCTION ST_GetTolerance RETURN NUMBER DETERMINISTIC
   CASCADE ';
  exception when others then null; 
end;
end; 
/



declare
begin

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_GEOMETRY_ARRAY 
OID ''4BA3494AB3EFE5B6E040578CB2057856''
AS VARRAY(1048576) OF ST_GEOMETRY';
  exception when others then NULL;
 end;


  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_DOUBLE_PRECISION_ARRAY 
OID ''4BA3494AB3F0E5B6E040578CB2057856''
AS VARRAY(1048576) OF DOUBLE PRECISION';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_Point
OID ''4BA3494AB3F1E5B6E040578CB2057856''  UNDER ST_GEOMETRY (
  CONSTRUCTOR FUNCTION ST_Point(xcoord DOUBLE PRECISION, 
           ycoord DOUBLE PRECISION) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_Point(xcoord DOUBLE PRECISION, 
            ycoord DOUBLE PRECISION, asrid INTEGER) RETURN SELF AS RESULT,
  OVERRIDING MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_X RETURN DOUBLE PRECISION DETERMINISTIC,
  MEMBER FUNCTION ST_X(xcoord DOUBLE PRECISION) RETURN ST_Point,
  MEMBER FUNCTION ST_Y RETURN DOUBLE PRECISION DETERMINISTIC,
  MEMBER FUNCTION ST_Y(ycoord DOUBLE PRECISION) RETURN ST_Point,
  MEMBER FUNCTION ST_ExplicitPoint 
            RETURN ST_DOUBLE_PRECISION_ARRAY DETERMINISTIC)';
  exception when others then NULL;
 end;


 begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_Point_Array 
OID ''4BA3494AB3F2E5B6E040578CB2057856''
AS VARRAY(1048576) OF ST_Point';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_CURVE
 OID ''4BA3494AB3F3E5B6E040578CB2057856'' UNDER ST_GEOMETRY (
  OVERRIDING MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_Points RETURN ST_Point_Array DETERMINISTIC,
  MEMBER FUNCTION ST_NumPoints RETURN INTEGER DETERMINISTIC,
  MEMBER FUNCTION ST_PointN(aposition INTEGER) RETURN ST_Point DETERMINISTIC,
  MEMBER FUNCTION ST_IsClosed RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_MidPointRep RETURN ST_Point_Array DETERMINISTIC,
  MEMBER FUNCTION ST_StartPoint RETURN ST_Point DETERMINISTIC,
  MEMBER FUNCTION ST_EndPoint RETURN ST_Point DETERMINISTIC,
  OVERRIDING MEMBER FUNCTION ST_IsSimple RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_IsRing RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_Length RETURN NUMBER DETERMINISTIC) NOT FINAL';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_CURVE_ARRAY 
OID ''4BA3494AB3F4E5B6E040578CB2057856''
AS VARRAY(1048576) OF ST_CURVE';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_SURFACE
OID ''4BA3494AB3F5E5B6E040578CB2057856'' UNDER ST_GEOMETRY (
  OVERRIDING MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_PointOnSurface RETURN ST_Point DETERMINISTIC,
  MEMBER FUNCTION ST_Area RETURN NUMBER DETERMINISTIC) NOT FINAL';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_SURFACE_ARRAY  
OID ''4BA3494AB3F6E5B6E040578CB2057856''
AS VARRAY(1048576) OF ST_GEOMETRY';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_CURVEPOLYGON 
OID ''4BA3494AB3F7E5B6E040578CB2057856'' UNDER ST_SURFACE (
  CONSTRUCTOR FUNCTION ST_CURVEPOLYGON(acurve ST_CURVE) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_CURVEPOLYGON(acurve ST_CURVE, acurvearray 
    ST_CURVE_ARRAY) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_CURVEPOLYGON(acurve ST_CURVE, asrid INTEGER) 
              RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_CURVEPOLYGON(acurve ST_CURVE, acurvearray 
             ST_CURVE_ARRAY, asrid INTEGER) RETURN SELF AS RESULT,
  MEMBER FUNCTION ST_ExteriorRing RETURN ST_Curve DETERMINISTIC,
  MEMBER FUNCTION ST_ExteriorRing(acurve ST_Curve) 
            RETURN ST_CurvePolygon DETERMINISTIC,
  MEMBER FUNCTION ST_InteriorRings RETURN ST_Curve_Array DETERMINISTIC,
  MEMBER FUNCTION ST_InteriorRings(acurvearray ST_Curve_Array) 
          RETURN ST_CurvePolygon DETERMINISTIC,
  MEMBER FUNCTION ST_NumInteriorRing RETURN INTEGER DETERMINISTIC,
  MEMBER FUNCTION ST_InteriorRingN(aposition INTEGER) 
     RETURN ST_Curve DETERMINISTIC
--  MEMBER FUNCTION ST_CurvePolyToPoly RETURN ST_Polygon
) NOT FINAL';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_LINESTRING 
OID ''4BA3494AB3F8E5B6E040578CB2057856''
UNDER ST_CURVE (
  CONSTRUCTOR FUNCTION ST_LINESTRING(apointarray ST_Point_Array) 
               RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_LINESTRING(apointarray ST_Point_Array, asrid INTEGER)
 RETURN SELF AS RESULT,
  OVERRIDING MEMBER FUNCTION ST_IsSimple RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_Points(apointarray ST_Point_Array) RETURN ST_LineString)';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_LINESTRING_ARRAY 
OID ''4BA3494AB3F9E5B6E040578CB2057856''
AS VARRAY(1048576) OF ST_LINESTRING';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_POLYGON
OID ''4BA3494AB3FAE5B6E040578CB2057856''
UNDER ST_CURVEPOLYGON (
  CONSTRUCTOR FUNCTION ST_POLYGON(alinestring ST_LineString) 
       RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_POLYGON(alinestring ST_LineString, 
      alinestringarray ST_LineString_Array) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_POLYGON(alinestring ST_LineString, asrid INTEGER) 
    RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_POLYGON(alinestring ST_LineString, 
   alinestringarray ST_LineString_Array, asrid INTEGER) RETURN SELF AS RESULT,

  OVERRIDING MEMBER FUNCTION ST_ExteriorRing RETURN ST_LineString DETERMINISTIC,
  OVERRIDING MEMBER FUNCTION ST_ExteriorRing(acurve ST_Curve) 
             RETURN ST_Polygon DETERMINISTIC,
  MEMBER FUNCTION ST_InteriorRingsP RETURN ST_LineString_Array DETERMINISTIC,
  MEMBER FUNCTION ST_InteriorRingsP(acurvearray ST_Curve_Array) 
     RETURN ST_Polygon DETERMINISTIC,
  OVERRIDING MEMBER FUNCTION ST_InteriorRingN(aposition INTEGER) 
             RETURN ST_LineString DETERMINISTIC,
  STATIC FUNCTION ST_BdPolyFromText(awkt CLOB) RETURN ST_Polygon DETERMINISTIC,
  STATIC FUNCTION ST_BdPolyFromText(awkt CLOB, asrid INTEGER) 
        RETURN ST_Polygon DETERMINISTIC,
  STATIC FUNCTION ST_BdPolyFromWKB(awkb BLOB) RETURN ST_Polygon DETERMINISTIC,
  STATIC FUNCTION ST_BdPolyFromWKB(awkb BLOB, asrid INTEGER) 
        RETURN ST_Polygon DETERMINISTIC)';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_POLYGON_ARRAY 
OID ''4BA3494AB3FBE5B6E040578CB2057856''
AS VARRAY(1048576) OF ST_POLYGON';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_GEOMCOLLECTION
OID ''4BA3494AB3FCE5B6E040578CB2057856'' UNDER ST_GEOMETRY (
  CONSTRUCTOR FUNCTION ST_GEOMCOLLECTION(ageometry ST_GEOMETRY) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_GEOMCOLLECTION(ageometryarray ST_GEOMETRY_ARRAY) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_GEOMCOLLECTION(ageometry ST_GEOMETRY, asrid INTEGER) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_GEOMCOLLECTION(ageometryarray ST_GEOMETRY_ARRAY, asrid INTEGER) RETURN SELF AS RESULT,

  OVERRIDING MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_GEOMETRIES RETURN ST_GEOMETRY_ARRAY DETERMINISTIC,
  MEMBER FUNCTION ST_GEOMETRIES(ageometryarray ST_GEOMETRY_ARRAY) RETURN ST_GEOMCOLLECTION) NOT FINAL';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_MULTIPOINT 
OID ''4BA3494AB3FDE5B6E040578CB2057856'' UNDER ST_GEOMCOLLECTION (
  CONSTRUCTOR FUNCTION ST_MULTIPOINT(apointarray ST_Point_Array) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_MULTIPOINT(apointarray ST_Point_Array, asrid INTEGER) RETURN SELF AS RESULT,

  OVERRIDING MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,
  OVERRIDING MEMBER FUNCTION ST_IsSimple RETURN Integer DETERMINISTIC)';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_MULTICURVE
OID ''4BA3494AB3FEE5B6E040578CB2057856'' UNDER ST_GEOMCOLLECTION (
  CONSTRUCTOR FUNCTION ST_MULTICURVE(acurvearray ST_CURVE_ARRAY) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_MULTICURVE(acurvearray ST_CURVE_ARRAY, asrid INTEGER) RETURN SELF AS RESULT,

  OVERRIDING MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,
  OVERRIDING MEMBER FUNCTION ST_IsSimple RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_Length RETURN NUMBER DETERMINISTIC,
  MEMBER FUNCTION ST_IsClosed RETURN Integer DETERMINISTIC) NOT FINAL';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_MULTISURFACE 
OID ''4BA3494AB3FFE5B6E040578CB2057856'' UNDER ST_GEOMCOLLECTION (
  CONSTRUCTOR FUNCTION ST_MULTISURFACE(asurfacearray ST_SURFACE_ARRAY) 
   RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_MULTISURFACE(asurfacearray ST_SURFACE_ARRAY, 
                  asrid INTEGER) RETURN SELF AS RESULT,

  OVERRIDING MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,
  MEMBER FUNCTION ST_PointOnSurface RETURN ST_Point DETERMINISTIC,
  MEMBER FUNCTION ST_Area RETURN NUMBER DETERMINISTIC) NOT FINAL';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_MULTILINESTRING
OID ''4BA3494AB400E5B6E040578CB2057856'' UNDER ST_MULTICURVE (
  CONSTRUCTOR FUNCTION ST_MULTILINESTRING(alinestringarray ST_LINESTRING_ARRAY)
          RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_MULTILINESTRING(alinestringarray ST_LINESTRING_ARRAY,
             asrid INTEGER) RETURN SELF AS RESULT)';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_MultiPolygon
OID ''4BA3494AB401E5B6E040578CB2057856'' UNDER ST_MULTISURFACE (
  CONSTRUCTOR FUNCTION ST_MultiPolygon(apolygonarray ST_POLYGON_ARRAY) 
           RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_MultiPolygon(apolygonarray ST_POLYGON_ARRAY,
            asrid INTEGER) RETURN SELF AS RESULT,

  STATIC FUNCTION ST_BdMPolyFromText(awkt CLOB) 
           RETURN ST_MultiPolygon DETERMINISTIC,
  STATIC FUNCTION ST_BdMPolyFromText(awkt CLOB, asrid INTEGER) 
           RETURN ST_MultiPolygon DETERMINISTIC,
  STATIC FUNCTION ST_BdMPolyFromWKB(awkb BLOB) 
           RETURN ST_MultiPolygon DETERMINISTIC,
  STATIC FUNCTION ST_BdMPolyFromWKB(awkb BLOB, asrid INTEGER) 
           RETURN ST_MultiPolygon DETERMINISTIC)';
  exception when others then NULL;
 end;

  begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_CIRCULARSTRING
OID ''4BA3494AB402E5B6E040578CB2057856'' UNDER ST_CURVE (
  CONSTRUCTOR FUNCTION ST_CIRCULARSTRING(apointarray ST_Point_Array) 
           RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_CIRCULARSTRING(apointarray ST_Point_Array,
            asrid INTEGER) RETURN SELF AS RESULT,

  MEMBER FUNCTION ST_Points(apointarray ST_Point_Array) 
           RETURN ST_CircularString)';
  exception when others then NULL;
 end;

 begin
    EXECUTE IMMEDIATE '
CREATE OR REPLACE TYPE ST_COMPOUNDCURVE
OID ''4BA3494AB403E5B6E040578CB2057856'' UNDER ST_CURVE (
  CONSTRUCTOR FUNCTION ST_COMPOUNDCURVE(acurve ST_CURVE) RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_COMPOUNDCURVE(acurvearray ST_CURVE_ARRAY) 
           RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_COMPOUNDCURVE(acurve ST_CURVE, asrid INTEGER) 
           RETURN SELF AS RESULT,
  CONSTRUCTOR FUNCTION ST_COMPOUNDCURVE(acurvearray ST_CURVE_ARRAY,
            asrid INTEGER) RETURN SELF AS RESULT,

  MEMBER FUNCTION ST_Curves RETURN ST_Curve_Array DETERMINISTIC,
  MEMBER FUNCTION ST_Curves(acurvearray ST_Curve_Array) RETURN ST_CompoundCurve,
  MEMBER FUNCTION ST_NumCurves RETURN INTEGER DETERMINISTIC,
  MEMBER FUNCTION ST_CurveN(aposition INTEGER) RETURN ST_Curve DETERMINISTIC)';
  exception when others then NULL;
 end;


end;
/


declare
BEGIN
  begin
    EXECUTE IMMEDIATE '
  ALTER TYPE SDO_GEOMETRY
  ADD MEMBER FUNCTION GET_WKB RETURN BLOB DETERMINISTIC,
  ADD MEMBER FUNCTION GET_WKT RETURN CLOB DETERMINISTIC,
  ADD MEMBER FUNCTION ST_CoordDim RETURN SMALLINT DETERMINISTIC,
  ADD MEMBER FUNCTION ST_IsValid RETURN INTEGER DETERMINISTIC,
  ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN CLOB,
            srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT,
  ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN VARCHAR2,
            srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT,
  ADD CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkb IN BLOB,
            srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT
  CASCADE';
   exception when others then NULL;
  end;
END;
/

commit;

declare
begin
   begin
     execute immediate ' PURGE TABLESPACE SYSTEM USER MDSYS  ';
     execute immediate ' PURGE TABLESPACE SYSAUX USER MDSYS  ';
           exception when others then NULL;
   end;
end;
/

GRANT EXECUTE ON MDSYS.ST_GEOMETRY         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_POINT         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_CURVE         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_SURFACE         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_CURVEPOLYGON         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_LINESTRING         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_POLYGON         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_GEOMCOLLECTION         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_MULTIPOINT         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_MULTICURVE         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_MULTISURFACE         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_MULTILINESTRING         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_MULTIPOLYGON         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_CIRCULARSTRING         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_COMPOUNDCURVE         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_GEOMETRY_ARRAY         to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_DOUBLE_PRECISION_ARRAY  to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_Point_Array  to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_CURVE_ARRAY  to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_SURFACE_ARRAY  to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_LINESTRING_ARRAY  to PUBLIC WITH GRANT OPTION;
GRANT EXECUTE ON MDSYS.ST_POLYGON_ARRAY  to PUBLIC WITH GRANT OPTION;