Last updated on FEBRUARY 08, 2017

Applies to:

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
*/

Cause