It would be helpful if Oracle could support extracting collection elements by index in SQL:
Example:
SELECT SYS.ODCIVARCHAR2LIST('a', 'b', 'c')(1) FROM DUAL;
Error:
ORA-03001: unimplemented feature
------------------------------------------------------------------------------------------
@MT0 described it well in a Stack Overflow post: (https://stackoverflow.com/a/72571112/5576771)
"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"
------------------------------------------------------------------------------------------
Could Oracle make it easier to extract collection elements by index in SQL?