Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in SQL?-CodePudding

Oracle 18c:

I can extract the startpoint X coordinate from an SDO_GEOMETRY using SHAPE.SDO_ORDINATES(1) in a custom PL/SQL function:

with 
function startpoint_x(shape in sdo_geometry) return number 
is
begin
  return 
  shape.sdo_ordinates(1); 
end;

select
  startpoint_x(shape) as startpoint_x
from
  (select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape 
   from dual)

STARTPOINT_X
------------
           1

But if I try do that purely in an SQL query, I get an error:

select
  (shape).sdo_ordinates(1) as startpoint_x
from
  (select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape 
   from dual)

ORA-00904: "MDSYS"."SDO_GEOMETRY"."SDO_ORDINATES": invalid identifier

For what it's worth, if I were to remove the (1) and instead select the entire sdo_ordinates attribute, then that would work:

select
  (shape).sdo_ordinates as ordinates 
from
  (select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape 
   from dual)

ORDINATES
------------------------
SDO_ORDINATE_ARRAY(1, 2)

db<>fiddle

But of course, that's not what I want. I want to get the startpoint X coordinate as a number.

Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in an SQL query?

CodePudding user response：

Why does SHAPE.SDO_ORDINATES(1) work in PL/SQL, but not in an SQL query?

Because the syntax of extracting collection elements by index is not supported in SQL. It is not just SDO objects but any collection:

SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c')(1) FROM DUAL;

Outputs:

ORA-03001: unimplemented feature

and:

SELECT l.list(1)
FROM   (SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c') AS list FROM DUAL) l;

Outputs:

ORA-00904: "L"."LIST": invalid identifier

(Which, I think means that it is trying to parse it as a function but the error message is less helpful/obvious than the previous one.)

There are methods of getting the value but it is more complicated as you need to dereference the entire collection using a table collection expression and then filter to get the desired row:

SELECT (
         SELECT COLUMN_VALUE
         FROM   TABLE(s.shape.sdo_ordinates)
         FETCH FIRST ROW ONLY
       ) as startpoint_x
FROM  (
  select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape from dual
) s

db<>fiddle here

CodePudding user response：

My guess is that varrays and table collections have to be treated as tables, and SQL (3) doesn't support them in native SQL.

CodePudding user response：

See if such a workaround helps ...

This is what you have and it works:

SQL> select
  2    (shape).sdo_ordinates as ordinates
  3  from
  4    (select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape
  5     from dual);

ORDINATES
------------------------------------------------------------
SDO_ORDINATE_ARRAY(1, 2, 3, 4, 5, 6)

This is what you tried, but it doesn't work:

SQL> select
  2    (shape).sdo_ordinates(1) as startpoint_x
  3  from
  4    (select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape
  5     from dual);
  (shape).sdo_ordinates(1) as startpoint_x
                       *
ERROR at line 2:
ORA-00904: "MDSYS"."SDO_GEOMETRY"."SDO_ORDINATES": invalid identifier

And this is a workaround:

SQL> select
  2    sdo_geom.sdo_min_mbr_ordinate(shape, 1) as startpoint_x
  3  from
  4    (select sdo_geometry('linestring(1 2, 3 4, 5 6)') as shape
  5     from dual);

STARTPOINT_X
------------
           1

SQL>