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 - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem 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;