A Query Involving Column MDSYS.SDO_GEOMETRY, WITH Alias AS, And A Self Join Returns NULL In The CLOB
(Doc ID 1608759.1)
Last updated on MARCH 01, 2019
Applies to:
Oracle Spatial and Graph - Version 11.2.0.2 and laterInformation in this document applies to any platform.
Symptoms
A query involving column MDSYS.SDO_GEOMETRY, WITH alias AS, and a self Join returns NULL in the CLOB generated using geometry.get_wkt() member function
These are the steps that show the problem
drop table ner_geoloc;
create table ner_geoloc
(tkufa number(5), geoloc mdsys.sdo_geometry);
insert into ner_geoloc
values (2100, mdsys.sdo_geometry(2001,8307,SDO_POINT_TYPE(1,1,NULL),NULL,NULL));
insert into ner_geoloc
values (2103, mdsys.sdo_geometry(2001,8307,SDO_POINT_TYPE(1,1,NULL),NULL,NULL));
commit;
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'NER_GEOLOC',
'GEOLOC',
MDSYS.SDO_DIM_ARRAY(
MDSYS.SDO_DIM_ELEMENT('Longitude', -180, 180, .05),
MDSYS.SDO_DIM_ELEMENT('Latitude', -90, 90, .05)
),
8307
);
commit;
spool val.txt
DROP TABLE val_results;
CREATE TABLE val_results (sdo_rowid ROWID, result varchar2(1000));
CALL SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT('NER_GEOLOC','GEOLOC','VAL_RESULTS');
col result format a40
SELECT * from val_results;
spool off
-- validates ok
-- This query works ok:
SELECT h.tkufa, h.geoloc.get_wkt() geo_clob
FROM ner_geoloc h
ORDER BY tkufa desc;
/*
TKUFA GEO_CLOB
---------- ------------------------------
2103 POINT (1.0 1.0)
2100 POINT (1.0 1.0)
*/
-- This query works ok:
SELECT row_number() OVER (ORDER BY tkufa desc) line_no, tkufa, geo_clob, DBMS_LOB.GETLENGTH(geo_clob) l
FROM
(
SELECT h.tkufa, h.geoloc.get_wkt() geo_clob
FROM ner_geoloc h
ORDER BY tkufa desc
) hs;
/*
LINE_NO TKUFA GEO_CLOB L
---------- ---------- ------------------------------ ----------
1 2103 POINT (1.0 1.0) 15
2 2100 POINT (1.0 1.0) 15
*/
-- This query works ok:
WITH s AS
(
SELECT row_number() OVER (ORDER BY tkufa desc) line_no, tkufa, geo_clob, DBMS_LOB.GETLENGTH(geo_clob) l
FROM
(
SELECT h.tkufa, h.geoloc.get_wkt() geo_clob
FROM ner_geoloc h
) hs
)
SELECT n.geo_clob, n.l nl ,DBMS_LOB.GETLENGTH(n.geo_clob) l2
FROM s n
ORDER BY n.tkufa desc;
/*
GEO_CLOB NL L2
------------------------------ ---------- ----------
POINT (1.0 1.0) 15 15
POINT (1.0 1.0) 15 15
*/
-- This query returns NULL CLOB when doing the self Join
WITH s AS
(
SELECT row_number() OVER (ORDER BY tkufa desc) line_no, tkufa, geo_clob, DBMS_LOB.GETLENGTH(geo_clob) l
FROM
(
SELECT h.tkufa, h.geoloc.get_wkt() geo_clob
FROM ner_geoloc h
) hs
)
SELECT n.geo_clob, n.l nl , DBMS_LOB.GETLENGTH(n.geo_clob) l2
FROM s o, -- old
s n -- new
WHERE o.tkufa=n.tkufa
ORDER BY n.tkufa desc;
/*
GEO_CLOB NL L2
------------------------------ ---------- ----------
15
15
*/
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |