将SDO_几何学的sdo_point属性转换为文本

Question

我在Oracle18c中有SDO_GEOMETRY对象：

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)

在查询中，我希望选择SDO_几何学的sdo_point属性作为文字文本(为了连接目的)。

示例：(失败)

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

我不知道如何将对象属性转换为文本。我尝试过使用SDO_UTIL.TO_WKTGEOMETRY()函数。但这似乎只适用于点几何整体，而不适用于特定的sdo_point属性。

如何选择SDO_几何学的sdo_point属性作为文本？

Answer 1

下面是我不久前编写的一个函数，它以文本格式写出SDO_GEOMETRY类型的内容，就像SQLPLUS使用的那样：

create or replace function sdo_format
  (geom sdo_geometry)
  return varchar2
is
  output_string varchar2(32767);
  MAX_LENGTH number := 3980;
begin
  if geom is null then
    return NULL;
  end if;
  -- Initialyze output string
  output_string := 'SDO_GEOMETRY(';
  -- Format SDO_GTYPE
  output_string := output_string || geom.sdo_gtype;
  output_string := output_string || ', ';
  -- Format SDO_SRID
  if geom.sdo_srid is not null then
    output_string := output_string || geom.sdo_srid;
  else
    output_string := output_string || 'NULL';
  end if;
  output_string := output_string || ', ';
  -- Format SDO_POINT
  if geom.sdo_point is not null then
    output_string := output_string || 'SDO_POINT_TYPE(';
    output_string := output_string || geom.sdo_point.x || ', ';
    output_string := output_string || geom.sdo_point.y || ', ';
    if geom.sdo_point.z is not null then
      output_string := output_string || geom.sdo_point.z || ')';
    else
      output_string := output_string || 'NULL)';
    end if;
  else
    output_string := output_string || 'NULL';
  end if;
  output_string := output_string || ', ';
  -- Format SDO_ELEM_INFO
  if geom.sdo_elem_info is not null then
    output_string := output_string || 'SDO_ELEM_INFO_ARRAY(';
    if geom.sdo_elem_info.count > 0 then
      for i in geom.sdo_elem_info.first..geom.sdo_elem_info.last loop
        if i > 1 then
          output_string := output_string || ', ';
        end if;
        output_string := output_string || geom.sdo_elem_info(i);
      end loop;
    end if;
    output_string := output_string || ')';
  else
    output_string := output_string || 'NULL';
  end if;
  output_string := output_string || ', ';
  -- Format SDO_ORDINATES
  if geom.sdo_ordinates is not null then
    output_string := output_string || 'SDO_ORDINATE_ARRAY(';
    if geom.sdo_ordinates.count > 0 then
      for i in geom.sdo_ordinates.first..geom.sdo_ordinates.last loop
        exit when length(output_string) > MAX_LENGTH;
        if i > 1 then
          output_string := output_string || ', ';
        end if;
        output_string := output_string || geom.sdo_ordinates(i);
      end loop;
      if length(output_string) > MAX_LENGTH then
        output_string := output_string || ' <...>';
      end if;
    end if;
    output_string := output_string || ')';
  else
    output_string := output_string || 'NULL';
  end if;
  output_string := output_string || ')';
  -- output_string := output_string || ' [' || length(output_string) || ']';
  -- Done - return formatted string
  return output_string;
end;

注意:它以VARCHAR2的形式返回输出，并有一个硬编码限制为4000个字符:它为无法打印的序号编写<...>。

如果数据库已被设置为使用长字符串，则可以将该限制提高到32767 (实际上稍微少了一点，比如32760)。或者更简单:修改它以使用CLOBs。这是留给读者的练习。