Convert SDO_GEOMETRY’s sdo_point attribute to text

I have SDO_GEOMETRY objects in Oracle 18c:

select
    sdo_geometry(2002, null, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(1, 2, 3, 4)) as shape
from
    dual
union all
select
    sdo_geometry(2001, null, sdo_point_type(-79, 37, null), null, null) as shape
from
    dual

Output:
MDSYS.SDO_GEOMETRY(2002, NULL, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1), MDSYS.SDO_ORDINATE_ARRAY(1, 2, 3, 4))
MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(-79, 37, NULL), NULL, NULL)

In a query, I want to select the SDO_GEOMETRY’s sdo_point attribute as literal text (for concatenation purposes).

Example: (fails)

select
    'the geometry sdo_point attribute is: ' || a.shape.sdo_point
from
    (
    select
        sdo_geometry(2002, null, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(1, 2, 3, 4)) as shape
    from
        dual
    union all
    select
        sdo_geometry(2001, null, sdo_point_type(-79, 37, null), null, null) as shape
    from
        dual
    ) a    

--Desired output:
--'the geometry sdo_point attribute is: null'
--'the geometry sdo_point attribute is: (-79, 37, null)'

ORA-00932: inconsistent datatypes: expected CHAR got MDSYS.SDO_POINT_TYPE
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 2 Column: 37

I don’t know how to convert that object attribute to text. I’ve tried using the SDO_UTIL.TO_WKTGEOMETRY() function. But that only seems to work on a point geometry as a whole, not on the specific sdo_point attribute.

---

How can I select the SDO_GEOMETRY’s sdo_point attribute as text?

Answer

Just extract each X, Y and Z component part of the point and convert those to strings:

SELECT 'the geometry sdo_point attribute is: '
       || NVL2(
            a.shape.sdo_point,
            '('
            || COALESCE(TO_CHAR(a.shape.sdo_point.X), 'NULL') || ', '
            || COALESCE(TO_CHAR(a.shape.sdo_point.Y), 'NULL') || ', '
            || COALESCE(TO_CHAR(a.shape.sdo_point.Z), 'NULL')
            || ')',
            'NULL'
          ) AS description
FROM   (
  SELECT sdo_geometry(
           2002, null, null,
           sdo_elem_info_array(1, 2, 1),
           sdo_ordinate_array(1, 2, 3, 4)
         ) as shape
  FROM   DUAL
  UNION ALL
  SELECT sdo_geometry(2001, null, sdo_point_type(-79, 37, null), null, null)
  FROM  DUAL
) a

Which outputs:

DESCRIPTION
the geometry sdo_point attribute is: NULL
the geometry sdo_point attribute is: (-79, 37, NULL)

---

On later Oracle versions, you could convert the SDO_POINT to JSON and then translate the JSON to your format:

SELECT 'the geometry sdo_point attribute is: '
       || TRANSLATE(JSON_OBJECT(a.shape.sdo_point), '{}":XYZ', '()')
         AS description
FROM   (
  SELECT sdo_geometry(
           2002, null, null,
           sdo_elem_info_array(1, 2, 1),
           sdo_ordinate_array(1, 2, 3, 4)
         ) as shape
  FROM   DUAL
  UNION ALL
  SELECT sdo_geometry(2001, null, sdo_point_type(-79, 37, null), null, null)
  FROM  DUAL
) a

Which has the similar output to above.

db<>fiddle here