Top 5 Recent Articles

ARTICLES CATEGORIES

CASE Statements and SDO_GEOMETRY

Over the past week I have been having a conversation with a customer around problems with using CASE WHEN THEN ELSE END constructs in SQL for turning WKT into SDO_GEOMETRY objects on INSERT.

Now the customer was doing this sort of comparison for correctly processing WKT data values in an external file into SDO_GEOMETRY and was getting a wrong result forcing him to change the way he inserted the data.

The error he was generating was trying to construct a geometry with a NULL WKT value as follows:

 SELECT sdo_util.from_wktgeometry(CAST(NULL AS VARCHAR2(100))) AS geom
   FROM dual;
 Error starting at line 260 IN command:
 SELECT sdo_util.from_wktgeometry(CAST(NULL AS VARCHAR2(100))) AS geom FROM dual
 Error report:
 SQL Error: ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.SQL.SQLException
 ORA-06512: at "MDSYS.SDO_UTIL", line 187
 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
 *Cause:    A Java exception OR error was signaled AND could NOT be
            resolved BY the Java code.
 *Action:   MODIFY Java code, IF this behavior IS NOT intended.

Now, he thought that he could catch this by doing this:

 DECLARE
   v_wkt  VARCHAR2(100) := NULL;
   v_geom sdo_geometry;
 BEGIN
   SELECT CASE v_wkt WHEN NULL THEN NULL ELSE sdo_util.from_wktgeometry(v_wkt) END INTO v_geom FROM dual;
 END;
 /
 Error starting at line 253 IN command:
 DECLARE
   v_wkt  VARCHAR2(100) := NULL;
   v_geom sdo_geometry;
 BEGIN
   SELECT CASE v_wkt WHEN NULL THEN NULL ELSE sdo_util.from_wktgeometry(v_wkt) END INTO v_geom FROM dual;
 END;
 Error report:
 ORA-29532: Java CALL TERMINATED BY uncaught Java exception: java.SQL.SQLException
 ORA-06512: at "MDSYS.SDO_UTIL", line 187
 ORA-06512: at line 5
 29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
 *Cause:    A Java exception OR error was signaled AND could NOT be
            resolved BY the Java code.
 *Action:   MODIFY Java code, IF this behavior IS NOT intended.

That is, he thought the case statement would catch the null WKT error but it doesn’t: the same error is being generated. Why is this?

The reason is the way the CASE statement does the comparison when it hits a null value for the variable.

 SELECT CASE NULL WHEN NULL THEN 'NULL' ELSE 'NOT NULL' END AS RESULT  FROM DUAL;
 RESULT
 --------
 NOT NULL
 -- The above fails because the CASE NULL WHEN NULL is executing a comparison on two NULLs as in the following "null = null" predicate.
 SELECT 1 FROM dual AS RESULT WHERE NULL = NULL
 UNION ALL
 SELECT 2 FROM dual AS RESULT WHERE NULL IS NULL;
 RESULT
 ------
 2

In other words, you simply can’t compare nulls using an equality operator. The CASE WHEN form actually executes a equals (*) and not an *IS NULL.

There are ways of making this form of CASE statement work and that is by using the NVL function as follows:

 SET serveroutput ON
 DECLARE
   v_wkt1 VARCHAR2(100) := NULL;
   v_wkt2 VARCHAR2(100) := 'POINT(90 90)';
   v_display SDO_GEOMETRY;
 BEGIN
   dbms_output.put_line('select case v_wkt2 when null ... works fine because a comparison with a null never happens....');
   SELECT CASE v_wkt2 WHEN NULL THEN NULL ELSE sdo_util.from_wktgeometry(v_wkt2) END INTO v_display FROM dual;
   dbms_output.put_line('RESULT: ' || SDO_UTIL.TO_WKTGEOMETRY(v_display));
   dbms_output.put_line('case NVL(v_wkt1,''NULL'') when ''NULL'' .... works because, via NVL, we end up comparing two strings rather than two NULLs using incorrect NULL=NULL predicate....');
   SELECT CASE NVL(v_wkt1,'NULL') WHEN 'NULL' THEN NULL ELSE sdo_util.from_wktgeometry(v_wkt1) END INTO v_display FROM dual;
   dbms_output.put_line('RESULT: ' || CASE WHEN v_wkt1 IS NULL THEN 'NULL' ELSE SDO_UTIL.TO_WKTGEOMETRY(v_display) END);
   dbms_output.put_line('case when v_wkt1 is null then null.... works because this is the proper way to compare nulls ie using NULL IS NULL predicate.');
   SELECT CASE WHEN v_wkt1 IS NULL THEN NULL ELSE sdo_util.from_wktgeometry(v_wkt1) END INTO v_display FROM dual;
   dbms_output.put_line('RESULT: ' || CASE WHEN v_wkt1 IS NULL THEN 'NULL' ELSE SDO_UTIL.TO_WKTGEOMETRY(v_display) END);
   dbms_output.put_line('case v_wkt1 when null then null.... throws exception even because you can''t compare nulls this way.');
   SELECT CASE v_wkt1 WHEN NULL THEN NULL ELSE sdo_util.from_wktgeometry(v_wkt1) END INTO v_display FROM dual;
   EXCEPTION
      WHEN OTHERS THEN
          dbms_output.put_line('Exception: ' || SQLERRM);
 END;
 /
 anonymous block completed
 SELECT CASE v_wkt2 WHEN NULL ... works fine because a comparison WITH a NULL never happens....
 RESULT: POINT (90.0 90.0)
 CASE NVL(v_wkt1,'NULL') WHEN 'NULL' .... works because, via NVL, we END up comparing two strings rather than two NULLS USING incorrect NULL=NULL predicate....
 RESULT: NULL
 CASE WHEN v_wkt1 IS NULL THEN NULL.... works because this IS the proper way TO compare NULLS ie USING NULL IS NULL predicate.
 RESULT: NULL
 CASE v_wkt1 WHEN NULL THEN NULL.... throws exception even because you can't compare nulls this way.
 Exception: ORA-29532: Java call terminated by uncaught Java exception: java.sql.SQLException
 ORA-06512: at "MDSYS.SDO_UTIL", line 187

The CASE Statement The CASE statement is a mix of programming constructs. The first is when you use the following form:

 CASE <value>
      WHEN <constant> THEN <result>
      WHEN <constant> THEN <result>
      ...
     ELSE <value>
  END

Here you are using the traditional form of a switch statement as in languages like Java etc.

In this, the switching value can only be an instance of a variable’s possible range of fixed values. So, numbers, strings, booleans and enumeration types are supported.

The second of a CASE statement is in the form of a set of nested IF statements…

 IF ( clause ) THEN
   <result>
 ELSIF ( clause ) THEN
   <result>
 ELSE
   <result>
 END IF

That is:

 CASE WHEN <variable> <operator> <value> THEN <result>
      WHEN <variable> <operator> <value> THEN <result>
      ...
      ELSE <result>
  END

This is used in the anonymous block above here:

 SELECT CASE WHEN v_wkt1 IS NULL THEN NULL ELSE sdo_util.from_wktgeometry(v_wkt1) END INTO v_display FROM dual;

This is always safe for testing if a variable/column etc is NULL.

Comparing SDO_GEOMETRY Objects

This leads to a discussion about how to use Oracle OBJECTs like SDO_GEOMETRY in CASE statements. Can it be done? Well, yes it can if the TYPE declares a comparison operation called an ORDER MEMBER FUNCTION. SDO_GEOMETRY does not declare such a comparison operator as part of its type so one must use other methods (see later).

But, if it did have one how would it work?

As an exercise in creating a type I created the MBR type as part of my packages many years ago. As part of the type I declared and implemented an order member function as follows

 CREATE OR REPLACE TYPE MBR AS OBJECT (
    MinX  NUMBER,
    MinY  NUMBER,
    MaxX  NUMBER,
    MaxY  NUMBER,
    -- ================== Constructors
    --
    Constructor FUNCTION MBR
                RETURN SELF AS RESULT,
    Constructor FUNCTION MBR(            p_geometry  IN MDSYS.SDO_GEOMETRY,
                                         p_tolerance IN NUMBER )
                RETURN SELF AS RESULT,
    Constructor FUNCTION MBR(            p_MBR IN MBR )
                RETURN SELF AS RESULT,
 ....
   ORDER Member FUNCTION Evaluate(p_other IN MBR)
                RETURN PLS_Integer
 );
 -- The Order Member Function Evaluate is declared in the Type body as....
 --
 CREATE OR REPLACE TYPE BODY MBR
 AS
 .....
   -- @function  : Evaluate
   -- @version   : 1.0
   -- @precis    : Returns value that can be used to company two MBRs in an expression of type MBR < MBR
   -- @return    : Computed number.
   -- @returntype: Number
   -- @history   : SGG August 2006 - Original Coding
   --
   ORDER Member FUNCTION Evaluate(p_other IN MBR)
                RETURN PLS_Integer
   IS
   BEGIN
     IF (MinX < p_other.MinX) THEN
       IF (MinY <= p_other.MinY) THEN
          RETURN -1;
       ElsIf (MinY = p_other.MinY) THEN
          RETURN -1;
       END IF;
     ElsIf (MinX = p_other.MinX) THEN
       IF (MinY < p_other.MinY) THEN
          RETURN -1;
       ElsIf (MinY = p_other.MinY) THEN
          RETURN 0;
       END IF;
     ELSE
       RETURN 1;
     END IF;
   END Evaluate;
 END MBR;
 /

Now you can implement any sort of processing in this method to compare the two MBRs – I have done it to return -1,0,1.

Now we can use the Evaluate method as follows:

 SELECT           MBR(337900, 5429000, 338900, 5430000).Evaluate(MBR(337950, 5429050, 338950, 5430500)) AS mbrvalue,
        CASE WHEN MBR(337900, 5429000, 338900, 5430000).Evaluate(MBR(337950, 5429050, 338950, 5430500)) = 0
             THEN 'EQUALS'
             ELSE 'UNEQUAL'
         END AS testMBR
  FROM DUAL;
 MBRVALUE               TESTMBR
 ---------------------- -------
 -1                     UNEQUAL
 .
 SELECT 'TRUE' AS RESULT
   FROM dual
  WHERE MBR(337900, 5429000, 338900, 5430000) = MBR(337950, 5429050, 338950, 5430500);
 RESULT
 ------
 .
 SELECT           MBR(337900, 5429000, 338900, 5430000).Evaluate(MBR(337900, 5429000, 338900, 5430000)) AS mbrvalue,
        CASE WHEN MBR(337900, 5429000, 338900, 5430000).Evaluate(MBR(337900, 5429000, 338900, 5430000)) = 0
             THEN 'EQUALS'
             ELSE 'UNEQUAL'
         END AS testMBR
  FROM DUAL;
 MBRVALUE               TESTMBR
 ---------------------- -------
 0                      EQUALS
 .
 -- But you can even do this!
 SELECT CASE MBR(337900, 5429000, 338900, 5430000)
             WHEN MBR(337900, 5429000, 338900, 5430000)
             THEN 'EQUALS'
             ELSE 'UNEQUAL'
         END AS testMBR
  FROM DUAL;
 TESTMBR
 -------
 EQUALS
 .
 SELECT 'TRUE' AS RESULT
   FROM dual
  WHERE MBR(337900, 5429000, 338900, 5430000) = MBR(337900, 5429000, 338900, 5430000);
 RESULT
 ------
 TRUE

Now that is super cool!

However, Oracle did not implement any ORDER MEMBER function for SDO_GEOMETRY so you can’t do this:

 SELECT CASE sdo_geometry('POINT(90 90)', 4326)
             WHEN sdo_geometry('POINT(90 90)',4326)
             THEN 'EQUALS'
             ELSE 'UNEQUAL'
         END AS geomCompare
   FROM dual;
 .
 Error starting at line 290 IN command:
 SELECT CASE sdo_geometry('POINT(90 90)', 4326)
             WHEN sdo_geometry('POINT(90 90)',4326)
             THEN 'EQUALS'
             ELSE 'UNEQUAL'
         END AS geomCompare
   FROM dual
 Error at Command Line:290 COLUMN:12
 Error report:
 SQL Error: ORA-22901: cannot compare VARRAY OR LOB attributes OF an object TYPE
 22901. 00000 -  "cannot compare nested table or VARRAY or LOB attributes of an object type"
 *Cause:    Comparison OF nested TABLE OR VARRAY OR LOB attributes OF an
            object TYPE was attempted IN the absence OF a MAP OR ORDER
            method.
 *Action:   define a MAP OR ORDER method FOR the object TYPE.
 .
 -- Nor can you do this
 --
 SELECT CASE WHEN sdo_geometry('POINT(90 90)', 4326) = sdo_geometry('POINT(90 90)',4326)
             THEN 'EQUALS' 
             ELSE 'UNEQUAL'
         END AS geomCompare
   FROM dual;
 .
 Error starting at line 297 IN command:
 SELECT CASE WHEN sdo_geometry('POINT(90 90)', 4326) = sdo_geometry('POINT(90 90)',4326)
             THEN 'EQUALS'
             ELSE 'UNEQUAL'
         END AS geomCompare
   FROM dual
 Error at Command Line:297 COLUMN:17
 Error report:
 SQL Error: ORA-22901: cannot compare VARRAY OR LOB attributes OF an object TYPE
 22901. 00000 -  "cannot compare nested table or VARRAY or LOB attributes of an object type"
 *Cause:    Comparison OF nested TABLE OR VARRAY OR LOB attributes OF an
            object TYPE was attempted IN the absence OF a MAP OR ORDER
            method.
 *Action:   define a MAP OR ORDER method FOR the object TYPE.

To compare two geometries one must convert them to WKT CLOBs or use the SDO_GEOM.RELATE function (Spatial only – or see my JTS replacement).

 SELECT CASE sdo_geom.relate(sdo_geometry('POINT(90 90)',4326),'EQUAL',sdo_geometry('POINT(90 90)',4326),0.05)
             WHEN 'FALSE' THEN 'UNEQUAL'
             WHEN 'EQUAL' THEN 'EQUAL'
             ELSE sdo_geom.relate(sdo_geometry('POINT(90 90)',4326),'EQUAL',sdo_geometry('POINT(90 90)',4326),0.05)
         END AS geoCompare
   FROM dual;
 .
 GEOCOMPARE
 ----------
 EQUAL

I hope this is of interest to someone out there.