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

Question

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?

---

Somewhat related: Get X & Y coordinates from GEOM_SEGMENT_START_PT()

Answer 1

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

Answer 2

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

Answer 3

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>