Why does (SHAPE).SDO_ORDINATES(1) syntax fail, but (SHAPE).ST_PointN(1) succeeds?

Oracle 18c:

In a related question, we determined:

The syntax of extracting collection elements by index is not supported in SQL.

So a query that uses this syntax will fail: (shape).sdo_ordinates(1),

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

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

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

However, I have a query that is similar (different datatype) that succeeds when I use seemingly similar syntax: (shape).st_pointn(1).

select
  (shape).st_pointn(1) as startpoint
from
  (select treat(st_geometry.from_wkt('Linestring(1 2, 3 4, 5 6)',26917) as st_linestring) as shape
   from dual)

Result:
MDSYS.ST_POINT(MDSYS.SDO_GEOMETRY(2001, 26917, MDSYS.SDO_POINT_TYPE(1, 2, NULL), NULL, NULL))

Source: Why do we need to Treat() MDSYS.ST_GEOMETRY as ST_LINESTRING to use ST_PointN(1)?

Why does (SHAPE).SDO_ORDINATES(1) fail, but (SHAPE).ST_PointN(1) succeeds?

Solution

[TL;DR]

SDO_ORDINATES is a collection attribute of the MDSYS.SDO_GEOMETRY data type.

ST_POINTN is a member function of (a super-type of) the MDSYS.ST_LINESTRING data type.

When you use:

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

Then it the SQL engine processes the syntax as a call to a member function in the form of object_type.member_function(argument) and there is no SDO_ORDINATES member function of the SDO_GEOMETRY data type and the output is:

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

Because there is no SDO_ORDINATES member function on the MDSYS.SDO_GEOMETRY object.

If instead, you use:

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

Then the SQL engine processes the syntax as (object_type.collection_attribute)(index) and the output is:

ORA-03001: unimplemented feature

Because extracting collection elements is not supported in SQL.

Finally:

select treat(
         st_geometry.from_wkt('Linestring(1 2, 3 4, 5 6)',26917)
         as st_linestring
       ).st_pointn(1)
from   dual

Returns an ST_LINESTRING object type and then you call the ST_POINTN member function with the argument 1. This works because there is a ST_POINTN member function declared on MDSYS.ST_CURVE which is the super-type of MDSYS.ST_LINESTRING.

You can see the object's source using:

select text
from   all_source
where  type  = 'TYPE'
and    owner = 'MDSYS'
and    name  = 'ST_LINESTRING'
ORDER BY line

and then, for it's parent:

select text
from   all_source
where  type  = 'TYPE'
and    owner = 'MDSYS'
and    name  = 'ST_CURVE'
ORDER BY line

Which includes the declaration:

 MEMBER FUNCTION ST_PointN(aposition INTEGER) RETURN ST_Point DETERMINISTIC,

db<>fiddle here