Database Software

Hi,

I'm getting the above error when running a SDO_Within_Distance query, SDO_Geom_Metadata is populated correctly for the table, so why would the error be thrown that indicates it's missing?

Here's my reproducable test case. The query that shows the error looks a bit basic and not very useful, but has been stripped down to the simplest form that generates the error. Any help appreciated.



Environment = Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
OS = Oracle Enterprise Linux Server release 5.2 (Carthage) 2.6.18 92.el5 (32-bit)

Here's my repeatable test-case which generates the error...

-- Tablespaces used for test case...
-- Event_ID_01_TS
-- Event_ID_02_TS
-- Event_1_TS
-- Event_2_TS
-- Event_Default_TS

CONNECT Sys/...@OEL5DB AS SysDBA

DROP USER TEST_USER CASCADE;
CREATE USER TEST_USER IDENTIFIED BY x DEFAULT TABLESPACE Users TEMPORARY TABLESPACE Temp ACCOUNT UNLOCK;

GRANT CONNECT TO TEST_USER;
GRANT RESOURCE TO TEST_USER;
GRANT CREATE TABLE TO TEST_USER;

CONNECT TEST_USER/x@OEL5DB

CREATE TYPE Partition_type AS OBJECT
(
ID INTEGER,
eventDate Timestamp(3),
eventLocation MDSys.SDO_Geometry
)
/

-- Range Partitioned Object Table, uses Hash-Partitioned Global PK index
CREATE TABLE Event_Table
OF Partition_Type (PRIMARY KEY (ID) USING INDEX
GLOBAL PARTITION BY HASH (ID) PARTITIONS 2
STORE IN
(
Event_ID_01_TS,
Event_ID_02_TS
)
STORAGE
(
INITIAL 1M PCTINCREASE 0 NEXT 1M
) NOLOGGING
)
OBJECT IDENTIFIER IS PRIMARY KEY
PARTITION BY RANGE(eventDate)
(
PARTITION Event_Part_1 VALUES LESS THAN (To_Date('07-Feb-2012','DD-Mon-YYYY')) TABLESPACE Event_1_TS,
PARTITION Event_Part_2 VALUES LESS THAN (To_Date('08-Feb-2012','DD-Mon-YYYY')) TABLESPACE Event_2_TS,
PARTITION Event_Default VALUES LESS THAN (MAXVALUE) TABLESPACE Event_Default_TS
)
PCTFREE 0
STORAGE (INITIAL 1M
PCTINCREASE 0
NEXT 1M);

ALTER TABLE Event_Table ADD CONSTRAINT Event_Table_CC1
CHECK (eventDate IS NOT NULL AND
eventLocation IS NOT NULL AND
eventLocation.SDO_Point.X IS NOT NULL AND
eventLocation.SDO_Point.Y IS NOT NULL AND
eventLocation.SDO_Point.Z IS NULL);

CREATE INDEX Event_Table_I1 ON Event_Table (eventDate)
LOCAL
(
PARTITION Event_Part_1 TABLESPACE Event_1_TS PCTFREE 0 STORAGE (INITIAL 1M PCTINCREASE 0 NEXT 512K),
PARTITION Event_Part_2 TABLESPACE Event_2_TS PCTFREE 0 STORAGE (INITIAL 1M PCTINCREASE 0 NEXT 512K),
PARTITION Event_Default TABLESPACE Event_Default_TS PCTFREE 0 STORAGE (INITIAL 1K PCTINCREASE 0 NEXT 1K)
)
TABLESPACE Event_Default_TS STORAGE (INITIAL 1K PCTINCREASE 0 NEXT 1K) NOLOGGING PARALLEL;

INSERT INTO User_SDO_Geom_Metadata
(TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES
(
'Event_Table',
'eventLocation',
SDO_DIM_ARRAY
(
SDO_DIM_ELEMENT('Longitude',-180,180,1),
SDO_DIM_ELEMENT('Latitude', -90, 90,1)
),4326);

-- Create partitioned spatial index...
CREATE INDEX Event_Table_SI1
ON Event_Table(eventLocation)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS
('Layer_gType=POINT sdo_rtr_pctfree=0 Tablespace=Event_Default_TS Initial=1K PctIncrease=0 Next=1K')
LOCAL
(
PARTITION Event_Part_1 PARAMETERS ('Layer_gType=POINT sdo_rtr_pctfree=0 Tablespace=Event_1_TS Initial=1M PctIncrease=0 Next=512K'),
PARTITION Event_Part_2 PARAMETERS ('Layer_gType=POINT sdo_rtr_pctfree=0 Tablespace=Event_2_TS Initial=1M PctIncrease=0 Next=512K'),
PARTITION Event_Default PARAMETERS ('Layer_gType=POINT sdo_rtr_pctfree=0 Tablespace=Event_Default_TS Initial=1K PctIncrease=0 Next=1K')
) PARALLEL;

ALTER TABLE Event_Table DROP PARTITION Event_Default;

-- Create some test data...
declare
event partition_type;
BEGIN

DELETE Event_Table;

-- 1st Partition data...
event := new Partition_Type (1, To_Date('06-Feb-2012 12:00:00','DD-Mon-YYYY HH24:MI:SS'),
SDO_Geometry
(2001, 4326,SDO_Point_Type(10, 10, Null), Null,Null));
INSERT INTO Event_Table VALUES event;

event := new Partition_Type (2, To_Date('06-Feb-2012 13:00:00','DD-Mon-YYYY HH24:MI:SS'),
SDO_Geometry
(2001, 4326,SDO_Point_Type(20, 20, Null), Null,Null));
INSERT INTO Event_Table VALUES event;

-- 2nd Partition data...
event := new Partition_Type (3, To_Date('07-Feb-2012 14:00:00','DD-Mon-YYYY HH24:MI:SS'), -- sysdate,
SDO_Geometry
(2001, 4326,SDO_Point_Type(30, 30, Null), Null,Null));
INSERT INTO Event_Table VALUES event;

event := new Partition_Type (4, To_Date('07-Feb-2012 15:00:00','DD-Mon-YYYY HH24:MI:SS'), -- sysdate,
SDO_Geometry
(2001, 4326,SDO_Point_Type(40, 40, Null), Null,Null));
INSERT INTO Event_Table VALUES event;

COMMIT;

END;
/

---------------------------------------------------------------------------------------------------------------
-- now run sql that produces error...
SELECT E1.ID, E2.ID
FROM (SELECT E.EventLocation,
E.ID,
E.EventDate
FROM Event_Table E) E1,
Event_Table E2
WHERE (SDO_Within_Distance(E2.EventLocation, E1.EventLocation, 'distance=1000 unit=meter') = 'TRUE')
AND (E2.EventDate BETWEEN (E1.EventDate - NumToDSInterval(1,'Minute'))
AND (E1.EventDate + NumToDSInterval(1,'Minute')));

SELECT E1.ID, E2.ID
*
ERROR at line 1:
ORA-13268: error obtaining dimension from USER_SDO_GEOM_METADATA
ORA-06512: at "MDSYS.MD", line 1723
ORA-06512: at "MDSYS.MDERR", line 8
ORA-06512: at "MDSYS.SDO_3GL", line 1097
---------------------------------------------------------------------------------------------------------------


Running the query without the EventDate predicate and just the SDO_Within_Distance clause runs without the error, so it's unclear as to where the error message is pointing to.

Regards,
Mark