Oracle java exception when trying to use NVL()

Question

I have the following SQL statement in Oracle that is giving me some headache. I am trying to return an empty geometry if the value held in the Oracle table is null however it simply fails with the following error:

The Error

*

ORA-29532: Java call terminated by uncaught Java exception: java.lang.NullPointerException ORA-06512: at "MDSYS.SDO_UTIL", line 2421 ORA-06512: at "MDSYS.SDO_UTIL", line 2443 ORA-06512: at "MDSYS.SDO_GEOMETRY", line 36

*

The Code

select CLUSTER_ID, 
       NUM_POINTS, 
       FEATURE_PK, 
       A.CELL_CENTROID.SDO_POINT.X, 
       A.CELL_CENTROID.SDO_POINT.Y, 
       A.CLUSTER_CENTROID.SDO_POINT.X, 
       A.CLUSTER_CENTROID.SDO_POINT.Y,
       TO_CHAR (A.CLUSTER_EXTENT.GET_WKT ()),  
       TO_CHAR (A.CELL_GEOM.GET_WKT ()), 
       A.CLUSTER_EXTENT.SDO_SRID, 
       TPHS_PHASE_ID 
from (SELECT CLUSTER_ID, 
             NUM_POINTS, 
             FEATURE_PK, 
             SDO_CS.transform (CLUSTER_CENTROID, 4326) cluster_centroid, 
             SDO_CS.TRANSFORM(NVL(CLUSTER_EXTENT, MDSYS.SDO_GEOMETRY(2001,4326 ,MDSYS.SDO_POINT_TYPE(NULL,NULL,NULL),NULL,NULL)),4326) CLUSTER_EXTENT , 
             SDO_CS.transform (CELL_CENTROID, 4326) cell_centroid, CELL_GEOM FROM              
             V_CLUSTER_1000M) a  
 LEFT JOIN  RWOL_TMA_ROADWORKS 
 ON a.FEATURE_PK = RWOL_TMA_ROADWORKS.TPHS_PHASE_ID   
 where sdo_filter( A.CELL_GEOM, SDO_CS.transform(mdsys.sdo_geometry(2003,4326, NULL, mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(-25.43623984375,44.257784519021, 21.62918984375, 60.752403080295)),81989)) = 'TRUE'

Anybody got any idea what is going wrong here? I am no Oracle dev so any help is appreciated as well as an explanation.

CSharpened CSharpened · Accepted Answer · 2012-08-20T11:04:08

The problem here was caused by the line

TO_CHAR (A.CLUSTER_EXTENT.GET_WKT ()),

This line is attempting to get a well known text item from the returned column and because we do not supply a valid one it is failing. Strange error really.

Oracle java exception when trying to use NVL()

1 Answers