Rem $Header: sdo/admin/sdotypes.sql /main/46 2009/03/23 15:39:50 jxyang Exp $ Rem $Header: sdo/admin/sdotypes.sql /main/46 2009/03/23 15:39:50 jxyang Exp $ Rem Rem sdotypes.sql Rem Rem Copyright (c) 1997, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdotypes.sql - Specification of SDO Object Types Rem Rem DESCRIPTION Rem Defines the SDO Object Type(s) for the Spatial Cartridge version 8.1 Rem onwards Rem Rem NOTES Rem The constants used for array sizes may change. Rem Rem MODIFIED (MM/DD/YY) Rem jxyang 03/12/09 - make sdo_geo_addr functions deterministic Rem sravada 11/18/08 - add Geometry Array Rem sravada 12/20/06 - add new constructor for geocoder type Rem jxyang 09/08/06 - use alter type to add matchVector to sdo_geo_addr Rem jxyang 07/13/06 - add matchvector to sdo_geo_addr Rem zzhang 02/03/06 - add a new type sdo_string2_array Rem wexu 01/09/06 - add SDO_RANGE/SDO_RANGE_ARRAY Rem sravada 06/13/05 - remove purge dba_recyclebin Rem sravada 08/18/04 - bug 3834721 Rem sravada 08/06/04 - change to dba_recyclebin Rem sravada 07/28/04 - fix alter type issues Rem mhorhamm 05/19/04 - Change parameter name from asrid to srid Rem sravada 11/10/03 - fix reload bug Rem jxyang 10/30/03 - remove dca from sdo_geo_addr Rem jxyang 10/20/03 - change sdo_geo_addr field length Rem rkothuri 02/28/03 - remove geometry_key in sdo_region Rem rkothuri 02/03/03 - change sdo_ridpair Rem jxyang 02/26/03 - add ID to SDO_GEO_ADDR Rem jxyang 01/20/03 - sdo_geo_addr change Rem jxyang 01/15/03 - add match_code for sdo_geo_addr Rem jxyang 12/17/02 - add long/lat to sdo_geo_addr Rem rkothuri 10/05/02 - add regaggr_set Rem rkothuri 09/17/02 - add sdo_region_Set Rem syuditsk 08/26/02 - removing SDO_Geometry entry from SDO_gcdrAddr Rem syuditsk 08/22/02 - changing type of lat and long to NUMBER Rem syuditsk 08/15/02 - adding defn of SDO_gcdrAddr type for geocoding Rem syuditsk 08/15/02 - adding defn of type keywordArray Rem syuditsk 08/07/02 - adding SDO_geo_addr type for address parsing Rem qxie 04/17/02 - add number and string array types Rem rkothuri 04/22/02 - add sdo_ridset Rem sravada 10/10/00 - update type method signatures Rem sravada 09/22/00 - make functions deterministic Rem sravada 07/25/00 - Rem sravada 07/10/00 - add type methods Rem rkothuri 08/26/99 - add sdo_mbr type Rem jsharma 09/11/98 - Add OID and Timestamp Rem sravada 09/15/98 - add SRID Rem pfwang 08/23/98 - Limit dim object to 4 dimensions max Rem gvincent 08/04/98 - take out drops, make it all create or replace Rem pfwang 05/22/98 - Remove SDO_DIMNUM from SDO_DIM_ELEMENT object Rem pfwang 05/14/98 - Linearize elem_info_array, add point Rem ranwei 01/20/98 - replace SDO_GID with ROWID in object interface Rem jsharma 04/20/98 - Cleanup comments Rem ranwei 01/20/98 - remove SDO_GID in object definition Rem jsharma 12/22/97 - Add sdo_dim_element and sdo_dim_array types Rem jsharma 12/18/97 - Specification of SDO Object types Rem jsharma 12/18/97 - Created Rem Rem ************************************************************************** Rem THE FUNCTIONS, PACKAGES AND TYPES SUPPLIED BY THIS PACKAGE AND ITS EXTERNAL Rem INTERFACE ARE RESERVED BY ORACLE AND ARE SUBJECT TO CHANGE IN FUTURE Rem RELEASES. THIS FILE MUST NOT BE MODIFIED BY THE CUSTOMER. DOING SO Rem COULD CAUSE INTERNAL ERRORS AND SECURITY VIOLATIONS IN THE DBMS. Rem ************************************************************************** Rem Rem It is assumed we are connected as MDSYS. Rem Rem Type SDO_POINT Rem Rem Description: Rem Rem This type represents a simple point up to 3D. Rem Rem Members: Rem X, Y, Z dimension coordinates. Z value is optional. Rem Rem Comments: Rem Rem It is used to store a simple point as a geometry so that Rem the complexity of an elem_info_array and ordinates array Rem is avoided. SDO interprets this field according to the Rem value in the SDO_ELEM_INFO_ARRAY and SDO_ORDINATES. If Rem both varrays are NULL, then the point is taken as the Rem geometry (a point element) represented by this SDO_GEOMETRY. Rem If the two varrays are not NULL, then this field is ignored Rem by SDO. However user can still populate it for their Rem particular application. Rem declare begin begin execute immediate ' CREATE TYPE SDO_POINT_TYPE TIMESTAMP ''1998-09-09:16:00:00'' OID ''3796C48378FB407AE03400400B407D5F'' AS OBJECT ( X NUMBER, Y NUMBER, Z NUMBER) '; exception when others then NULL; end; end; / show errors; Rem Rem Type SDO_ORDINATE_ARRAY Rem Rem Description: Rem Rem This type is an array of varying length Oracle NUMBER Rem Rem Members: Rem None. Rem Rem Comments: Rem Rem It is used to store the ordinates that describe a geometry Rem and is used in conjunction with another array that indicates the Rem array offsets (starting from 1) at which the sequence of ordinates Rem for a particular geometric element start. Thus the 1st ordinate Rem for the 1st element is at offset 1. In PLSQL this cooresponds to an Rem array index 1, i.e. sdo_ordinates[1], and in an OCI program it Rem corresponds to an array index 0, i.e. Rem sdo_geometry_cstruct.sdo_ordinates[0] Rem Rem declare begin begin execute immediate 'CREATE TYPE SDO_ORDINATE_ARRAY TIMESTAMP ''1998-09-09:16:00:00'' OID ''3796C48378FC407AE03400400B407D5F'' AS VARRAY(1048576) OF NUMBER '; exception when others then NULL; end; end; / show errors; Rem Rem Type SDO_ELEM_INFO_ARRAY Rem Rem Description: Rem Rem This type is an array of sdo_elem_info_object with one entry Rem per geometric element whose ordiantes are stored in the Rem sdo_ordinate_array Rem Rem Members: Rem NUMBER. Rem Rem Comments: Rem Rem The implicit ordering of an ARRAY does not imply any containment Rem or heirarchical relationship among the elements. Rem These relationships are explicitly indicated by the SDO_ETYPE. Rem Rem The current value 1048576 may increase or decrease in the production Rem version of these types Rem Rem Numbers are grouped into 3s when SDO scans through the list. This Rem means the count of the actual varray has to be a multiple of 3. Rem The triplets contain the metadata for interpreting the ordinates in Rem an sdo_ordinate_array for a particular element. Rem Rem Each triplet has the following meaning: Rem sdo_starting_offset, sdo_etype, sdo_interpretation,[repeating...] Rem Rem sdo_starting_offset - The array offset of the first ordinate of an Rem element. Offset values start at 1. That is, an Rem offset value 1 indicates that ordinates start at Rem the 1st position in the array. This corresponds to Rem array index 0 in a C structure and 1 in PLSQL Rem VARRAY variable. Rem sdo_etype - The Element TYPE. Can be any supported Rem instantiable primitive OGIS or SQL/MM geometric Rem type. Rem sdo_interpretation - Indicates how a consecutive sequence of coordinate Rem pairs is connected. That is, by straight line Rem segments, circular or elliptical arcs, or some Rem bezier curve or b-spline interpolation. Rem Rem Comments: Rem sdo_etype: The actual value stored here is an SDO specific numeric Rem code and not an OGIS or other standards bodys code Rem However there is a one-to-one mapping between SDO codes Rem and their codes. Rem Rem declare begin begin execute immediate 'CREATE TYPE SDO_ELEM_INFO_ARRAY TIMESTAMP ''1998-09-09:16:00:00'' OID ''3796C48378FD407AE03400400B407D5F'' AS VARRAY (1048576) of NUMBER '; exception when others then NULL; end; end; / show errors; Rem Rem Type SDO_GEOMETRY Rem Rem Description: Rem Rem This is a generic SDO GEOMETRY type that is used to represent all Rem supported primitive geometry types and collections such as Rem points, lines, simple polygons, and polygons with holes. Rem Rem Members: Rem SDO_GID - A numeric primary key for a SDOGEOM table Rem This attribute has been removed Rem SDO_GTYPE - A Geometry Type identifier. It is an SDO specific Rem numeric value that has a one-to-one mapping to Rem values identifying supported OGIS and SQL/MM Rem geometry types Rem SDO_POINT - Stores a point, as descirbed above Rem SDO_ELEM_INFO - The control information for interpreting the values Rem stored in SDO_ORDINATES Rem SDO_ORDINATES - An array of ordinate values for the elements Rem comprising the Geometric Object Rem Comments: Rem Rem sdo_ordinates: The dimensionality of the object is determined by Rem metadata in a layer_sdodim table. For a 2-D object, Rem the ordinates are interpreted as an ordered sequence Rem of coordinate pairs. Similarly for a 3-D object they are Rem interpreted as an ordered sequence of coordinate Rem triples. 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 stmt varchar2(2000); begin begin stmt := ' DROP TYPE BODY SDO_GEOMETRY '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP MEMBER FUNCTION GET_WKB RETURN BLOB DETERMINISTIC CASCADE '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP MEMBER FUNCTION GET_WKT RETURN CLOB DETERMINISTIC CASCADE '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP MEMBER FUNCTION ST_CoordDim RETURN SMALLINT DETERMINISTIC CASCADE '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP MEMBER FUNCTION ST_IsValid RETURN INTEGER DETERMINISTIC CASCADE '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN CLOB, '|| ' srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT CASCADE '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN CLOB, '|| ' asrid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT CASCADE '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN VARCHAR2, '|| ' srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT CASCADE '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkt IN VARCHAR2, '|| ' asrid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT CASCADE '; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkb IN BLOB, '|| ' srid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT CASCADE'; execute immediate stmt; exception WHEN others then NULL; end; begin stmt := ' ALTER TYPE SDO_GEOMETRY '|| ' DROP CONSTRUCTOR FUNCTION SDO_GEOMETRY(wkb IN BLOB, '|| ' asrid IN INTEGER DEFAULT NULL) RETURN SELF AS RESULT CASCADE'; execute immediate stmt; exception WHEN others then NULL; end; end; / 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 'CREATE TYPE SDO_GEOMETRY TIMESTAMP ''1998-09-09:16:00:00'' OID ''3796C48378FE407AE03400400B407D5F'' AS OBJECT ( SDO_GTYPE NUMBER, SDO_SRID NUMBER, SDO_POINT SDO_POINT_TYPE, SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, SDO_ORDINATES SDO_ORDINATE_ARRAY) ' ; exception when others then NULL; end; end; / show errors; Rem Now create the types for the sdodim and other tables declare begin begin execute immediate 'CREATE TYPE SDO_DIM_ELEMENT TIMESTAMP ''1998-09-09:16:00:00'' OID ''3796C48378FF407AE03400400B407D5F'' AS OBJECT ( SDO_DIMNAME VARCHAR(64), SDO_LB NUMBER, SDO_UB NUMBER, SDO_TOLERANCE NUMBER ) ' ; exception when others then NULL; end; end; / show errors; declare begin begin execute immediate 'CREATE TYPE SDO_DIM_ARRAY TIMESTAMP ''1998-09-09:16:00:00'' OID ''3796C4837900407AE03400400B407D5F'' AS VARRAY(4) OF SDO_DIM_ELEMENT '; exception when others then NULL; end; end; / show errors; Rem Type SDO_VPOINT_TYPE Rem Rem Description: Rem Rem This type represents a simple point up to 64D. Rem Rem Members: Rem Varray of coordinates. Rem Rem Comments: Rem Rem It is used to represent the corner points in an SDO_MBR type. declare begin begin execute immediate 'CREATE TYPE SDO_VPOINT_TYPE TIMESTAMP ''1999-08-26:11:14:00'' OID ''5300427C0DBE4C3BE03400400B401354'' AS VARRAY(64) OF NUMBER '; exception when others then NULL; end; end; / show errors; Rem Type SDO_MBR Rem Rem Description: Rem Rem This type represents an axis-aligned Minimum Bounding Rectangle. Rem upto 64-dimensions. Rem Rem Rem Members: Rem Rem Lower_left: a varray of coordinates to represent the Rem lower-left corner of an MBR. Rem Upper_left: a varray of coordinates to represent the Rem upper-right corner of an MBR. Rem Rem Rem Comments: Rem Rem It is used to store a bounding rectangle whose sides are Rem aligned with the dimension axes. It is useful for representing Rem n-dimensional data where n >4. Current SDO_GEOMETRY type only Rem supports upto 4 dimensions. Rem declare begin begin execute immediate 'CREATE TYPE SDO_MBR TIMESTAMP ''1990-08-26:11:25:00'' OID ''5300427C0DBF4C3BE03400400B401354'' AS OBJECT ( LOWER_LEFT MDSYS.SDO_VPOINT_TYPE, UPPER_RIGHT MDSYS.SDO_VPOINT_TYPE) '; exception when others then NULL; end; end; / show errors; Rem Type SDO_ROWIDPAIR Rem Rem Description: Rem Rem This type represents a pair of Rowids Rem Rem Comments: Rem Rem It is used to return child rowids in Spatial Joins. Rem declare begin begin execute immediate 'CREATE TYPE SDO_ROWIDPAIR AS OBJECT ( rowid1 varchar2(24), rowid2 varchar2(24)) '; exception when others then NULL; end; end; / show errors; Rem Type SDO_ROWIDSET Rem Rem Description: Rem Rem This type represents a set of Rowid Pairs Rem Rem Comments: Rem Rem It is used to return child rowids in Spatial Joins. Rem declare begin begin execute immediate 'CREATE TYPE SDO_ROWIDSET AS TABLE OF SDO_ROWIDPAIR '; exception when others then NULL; end; end; / show errors; Rem Type SDO_SMPL_GEOM Rem Rem Description: Rem Rem This type represents a simplified geometry along with Rem area/len changes due to simplification Rem Rem Comments: Rem Rem declare begin begin execute immediate 'CREATE TYPE SDO_SMPL_GEOMETRY TIMESTAMP ''2002-10-14:18:00:44'' OID ''ACFF3AFF726D33B9E0340003BA0FE0FF'' AS OBJECT ( orig_area number, cur_area number, orig_len number, cur_len number, geometry mdsys.sdo_geometry) '; exception when others then NULL; end; end; / show errors; Rem Type SDO_REGION Rem Rem Description: Rem Rem This type represents a cluster region Rem Rem Comments: Rem Rem declare begin begin execute immediate 'CREATE TYPE SDO_REGION TIMESTAMP ''2002-10-01:12:41:49'' OID ''ABF53DECBB3C565BE0340003BA0FE0FF'' AS OBJECT ( id number, geometry mdsys.sdo_geometry) '; exception when others then NULL; end; end; / show errors; Rem Type SDO_REGIONSET Rem Rem Description: Rem Rem This type represents a set of regions Rem Rem Comments: Rem Rem It is used to return cluster regions in Spatial Clustering. Rem declare begin begin execute immediate 'CREATE TYPE SDO_REGIONSET TIMESTAMP ''2002-10-01:12:46:44'' OID ''ABF54553D965565FE0340003BA0FE0FF'' AS TABLE OF SDO_REGION '; exception when others then NULL; end; end; / show errors; Rem Type SDO_REGAGGR Rem Rem Description: Rem Rem This type represents a aggregate for a spatial region Rem Rem Comments: Rem Rem declare begin begin execute immediate 'CREATE TYPE SDO_REGAGGR AS OBJECT ( region_id varchar2(24), geometry mdsys.sdo_geometry, aggregate_value number) '; exception when others then NULL; end; end; / show errors; Rem Type SDO_REGAGGRSET Rem Rem Description: Rem Rem This type represents a set of aggregate objects Rem Rem Comments: Rem Rem It is used to return aggregates in spatial analysis. Rem declare begin begin execute immediate 'CREATE TYPE SDO_REGAGGRSET AS TABLE OF SDO_REGAGGR '; exception when others then NULL; end; end; / show errors; Rem Type SDO_NUMBER_ARRAY Rem Rem Description: Rem Rem This type is an array of varying length Oracle NUMBER Rem Rem Members: Rem None. Rem Rem Comments: Rem declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_NUMBER_ARRAY TIMESTAMP ''2001-12-06:13:00:00'' OID ''9476CC705B331B06E0340800209D3EE1'' AS VARRAY(1048576) OF NUMBER '; exception when others then NULL; end; end; / show errors; Rem Type SDO_STRING_ARRAY Rem Rem Description: Rem Rem This type is an array of varying length Oracle STRING Rem Rem Members: Rem None. Rem Rem Comments: Rem declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_STRING_ARRAY TIMESTAMP ''2001-12-06:13:00:00'' OID ''94C8417294383551E0340800209D3EE1'' AS VARRAY(1048576) OF VARCHAR2(32) '; exception when others then NULL; end; end; / Rem Type SDO_STRING2_ARRAY Rem Rem Description: Rem Rem This type is an array of varying length Oracle STRING, Rem the maximum size of string is 1024. Rem Rem Members: Rem None. Rem Rem Comments: Rem declare begin begin begin execute immediate 'drop type SDO_STRING2_ARRAYSET force'; execute immediate 'drop type SDO_STRING2_ARRAY force'; exception when others then NULL; end; execute immediate 'CREATE OR REPLACE TYPE SDO_STRING2_ARRAY TIMESTAMP ''2006-02-03:13:00:00'' oid ''0CB4F536D6E5DCE0E040578C45072612'' AS VARRAY(2147483647) OF VARCHAR2(4096) '; execute immediate 'CREATE OR REPLACE TYPE SDO_STRING2_ARRAYSET TIMESTAMP ''2006-02-03:13:00:00'' oid ''0CD69D620FD31EE0E040578C45075DC6'' AS VARRAY(2147483647) OF SDO_STRING2_ARRAY '; exception when others then NULL; end; end; / show errors; Rem Rem Alter the Geometry type to add a Method Get_Gtype() Rem Rem Rem Rem 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; / ALTER TYPE SDO_GEOMETRY REPLACE AS OBJECT ( SDO_GTYPE NUMBER, SDO_SRID NUMBER, SDO_POINT SDO_POINT_TYPE, SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, SDO_ORDINATES SDO_ORDINATE_ARRAY, MEMBER FUNCTION GET_GTYPE RETURN NUMBER DETERMINISTIC, MEMBER FUNCTION GET_DIMS RETURN NUMBER DETERMINISTIC, MEMBER FUNCTION GET_LRS_DIM RETURN NUMBER DETERMINISTIC); show errors; Create or Replace type body SDO_GEOMETRY IS MEMBER FUNCTION GET_GTYPE RETURN NUMBER IS gtype integer; BEGIN gtype := sdo_gtype MOD 100; return gtype; END; MEMBER FUNCTION GET_DIMS RETURN NUMBER IS dims INTEGER; BEGIN dims := sdo_gtype/1000; return dims; END; MEMBER FUNCTION GET_LRS_DIM RETURN NUMBER IS lrs_dim integer; BEGIN lrs_dim := sdo_gtype/100; lrs_dim := lrs_dim MOD 10; return lrs_dim; END; END; / REM ************************** REM SDO_keywordArray type defn REM ************************** declare begin begin execute immediate 'create or replace type mdsys.SDO_keywordArray oid ''A8451C3873756645E0340003BA0F2101'' as VARRAY(10000) of VARCHAR2(9000) '; exception when others then NULL; end; end; / show errors; grant execute on mdsys.SDO_keywordArray to public; create or replace public synonym SDO_keywordArray for mdsys.SDO_keywordArray; REM **************************************************** REM Definition of SDO_geo_addr type for address parsing: REM **************************************************** declare begin begin execute immediate 'create or replace type SDO_geo_addr oid ''A7A2F4EC0EE626C6E0340003BA0F2101'' as object ( id NUMBER, addresslines SDO_KEYWORDARRAY, placeName VARCHAR2(200), streetName VARCHAR2(200), intersectStreet VARCHAR2(200), secUnit VARCHAR2(200), settlement VARCHAR2(200), municipality VARCHAR2(200), region VARCHAR2(200), country VARCHAR2(100), postalCode VARCHAR2(20), postalAddonCode VARCHAR2(20), fullPostalCode VARCHAR2(40), poBox VARCHAR2(100), houseNumber VARCHAR2(100), baseName VARCHAR2(200), streetType VARCHAR2(20), streetTypeBefore VARCHAR2(1), streetTypeAttached VARCHAR2(1), streetPrefix VARCHAR2(20), streetSuffix VARCHAR2(20), side VARCHAR2(1), percent NUMBER, edgeId NUMBER, errorMessage VARCHAR2(20), matchcode NUMBER, matchmode VARCHAR2(30), longitude NUMBER, latitude NUMBER, CONSTRUCTOR FUNCTION SDO_GEO_ADDR RETURN SELF AS RESULT DETERMINISTIC, CONSTRUCTOR FUNCTION SDO_GEO_ADDR(country VARCHAR2, lines SDO_KEYWORDARRAY, matchmode VARCHAR2) RETURN SELF AS RESULT DETERMINISTIC, CONSTRUCTOR FUNCTION SDO_GEO_ADDR(country VARCHAR2, matchmode VARCHAR2, street VARCHAR2, settlement VARCHAR2, municipality VARCHAR2, region VARCHAR2, postalcode VARCHAR2) RETURN SELF AS RESULT DETERMINISTIC) '; exception when others then NULL; end; end; / show errors; begin begin execute immediate 'alter type sdo_geo_addr add attribute(matchVector VARCHAR2(20)) cascade'; exception WHEN others then NULL; end; begin execute immediate ' alter type sdo_geo_addr add Constructor FUNCTION SDO_GEO_ADDR( id NUMBER, addresslines SDO_KEYWORDARRAY, placeName VARCHAR2, streetName VARCHAR2, intersectStreet VARCHAR2, secUnit VARCHAR2, settlement VARCHAR2, municipality VARCHAR2, region VARCHAR2, country VARCHAR2, postalCode VARCHAR2, postalAddonCode VARCHAR2, fullPostalCode VARCHAR2, poBox VARCHAR2, houseNumber VARCHAR2, baseName VARCHAR2, streetType VARCHAR2, streetTypeBefore VARCHAR2, streetTypeAttached VARCHAR2, streetPrefix VARCHAR2, streetSuffix VARCHAR2, side VARCHAR2, percent NUMBER, edgeId NUMBER, errorMessage VARCHAR2, matchcode NUMBER, matchmode VARCHAR2, longitude NUMBER, latitude NUMBER) RETURN SELF as RESULT DETERMINISTIC CASCADE' ; exception WHEN others then NULL; end; end; / create or replace type body sdo_geo_addr as CONSTRUCTOR FUNCTION SDO_GEO_ADDR RETURN SELF AS RESULT AS BEGIN return ; END ; CONSTRUCTOR FUNCTION SDO_GEO_ADDR( country VARCHAR2, lines SDO_KEYWORDARRAY, matchmode VARCHAR2) RETURN SELF AS RESULT AS BEGIN SELF.id := 1 ; SELF.country := country ; SELF.addresslines := lines ; SELF.matchmode := matchmode ; return ; END ; CONSTRUCTOR FUNCTION SDO_GEO_ADDR( country VARCHAR2, matchmode VARCHAR2, street VARCHAR2, settlement VARCHAR2, municipality VARCHAR2, region VARCHAR2, postalcode VARCHAR2) RETURN SELF AS RESULT AS BEGIN SELF.id := 1; SELF.country := country ; SELF.matchmode := matchmode ; SELF.streetname := street ; SELF.settlement := settlement ; SELF.municipality := municipality ; SELF.region := region ; SELF.postalcode := postalcode ; RETURN ; END; CONSTRUCTOR FUNCTION SDO_GEO_ADDR( id NUMBER, addresslines SDO_KEYWORDARRAY, placeName VARCHAR2, streetName VARCHAR2, intersectStreet VARCHAR2, secUnit VARCHAR2, settlement VARCHAR2, municipality VARCHAR2, region VARCHAR2, country VARCHAR2, postalCode VARCHAR2, postalAddonCode VARCHAR2, fullPostalCode VARCHAR2, poBox VARCHAR2, houseNumber VARCHAR2, baseName VARCHAR2, streetType VARCHAR2, streetTypeBefore VARCHAR2, streetTypeAttached VARCHAR2, streetPrefix VARCHAR2, streetSuffix VARCHAR2, side VARCHAR2, percent NUMBER, edgeId NUMBER, errorMessage VARCHAR2, matchcode NUMBER, matchmode VARCHAR2, longitude NUMBER, latitude NUMBER) RETURN SELF AS RESULT AS BEGIN self.id := id; self.addresslines := addresslines; self.placeName := placeName; self.streetName := streetName; self.intersectStreet := intersectStreet; self.secUnit := secUnit; self.settlement := settlement; self.municipality := municipality; self.region := region; self.country := country; self.postalCode := postalCode; self.postalAddonCode := postalAddonCode; self.fullPostalCode := fullPostalCode; self.poBox := poBox; self.houseNumber := houseNumber; self.baseName := baseName; self.streetType := streetType; self.streetTypeBefore := streetTypeBefore; self.streetTypeAttached := streetTypeAttached; self.streetPrefix := streetPrefix; self.streetSuffix := streetSuffix; self.side := side; self.percent := percent; self.edgeId := edgeId; self.errorMessage := errorMessage; self.matchcode := matchcode; self.matchmode := matchmode; self.longitude := longitude; self.latitude := latitude; RETURN; END; end; / show errors ; grant execute on mdsys.SDO_geo_addr to public; create or replace public synonym SDO_geo_addr for mdsys.SDO_geo_addr; declare begin begin execute immediate ' create or replace type SDO_ADDR_ARRAY as VARRAY(1000) of SDO_GEO_ADDR '; exception when others then NULL; end; end; / grant execute on mdsys.SDO_ADDR_ARRAY to public ; create or replace public synonym SDO_ADDR_ARRAY for mdsys.SDO_ADDR_ARRAY ; Rem Type SDO_RANGE Rem Rem Description: Rem Rem This type defines a range consisting of two Oracle NUMBER boundaries Rem Rem Members: Rem None. Rem Rem Comments: Rem declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_RANGE TIMESTAMP ''2006-01-09:10:00:00'' OID ''09F4AEC78336F5BAE040578CAF05395A'' AS OBJECT (lb NUMBER, ub NUMBER)'; exception when others then NULL; end; end; / show errors; Rem Type SDO_RANGE_ARRAY Rem Rem Description: Rem Rem This type defines an array of varying length of SDO_RANGE elements Rem Rem Members: Rem None. Rem Rem Comments: Rem declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_RANGE_ARRAY TIMESTAMP ''2006-01-09:10:00:00'' OID ''09F4AEC7833BF5BAE040578CAF05395A'' AS VARRAY(1048576) OF SDO_RANGE'; exception when others then NULL; end; end; / show errors; Rem Type SDO_GEOMETRY_ARRAY Main use as Transient type declare begin begin execute immediate 'CREATE OR REPLACE TYPE SDO_GEOMETRY_ARRAY TIMESTAMP ''2008-11-18:10:54:01'' OID ''5BF8B4041C603EAEE040578CB2051995'' AS VARRAY(10485760) OF SDO_GEOMETRY '; exception when others then NULL; end; begin execute immediate ' create or replace public synonym SDO_GEOMETRY_ARRAY for MDSYS.SDO_GEOMETRY_ARRAY'; end; end; / grant execute on mdsys.SDO_GEOMETRY_ARRAY to public ;