Oracle 18c:

I can get SDO_ORDINATE_ARRAY values from SDO_GEOMETRY objects:

create table test_table (shape sdo_geometry);

insert into test_table (shape) values (sdo_geometry('linestring(10 20, 30 40, 50 60)'));
insert into test_table (shape) values (sdo_geometry('linestring(70 80, 90 100)'));
insert into test_table (shape) values (sdo_geometry('linestring(110 120, 130 140, 150 160, 170 180)'));

select
    (shape).sdo_ordinates as sdo_ordinate_array
from
    test_table


SDO_ORDINATE_ARRAY
------------------
MDSYS.SDO_ORDINATE_ARRAY(10, 20, 30, 40, 50, 60)
MDSYS.SDO_ORDINATE_ARRAY(70, 80, 90, 100)
MDSYS.SDO_ORDINATE_ARRAY(110, 120, 130, 140, 150, 160, 170, 180)

db<>fiddle

For what it's worth, this is what the definition of the MDSYS.SDO_ORDINATE_ARRAY type looks like in SQL Developer:

create or replace TYPE SDO_ORDINATE_ARRAY AS VARRAY(1048576) OF NUMBER  

Using a function, I want to convert the SDO_ORDINATE_ARRAYs to the built-in VARRAY datatype.

Reason: I want to experiment with storing the ordinates in a non-spatial Function-Based Index. If I understand correctly, only built-in datatypes are supported by FBIs, not Oracle-supplied types or abstract datatypes like SDO_ORDINATE_ARRAY or SDO_GEOMETRY.

• Idea: Support function-based indexes on Abstract datatypes.
• Function-based spatial indexes don't help me in this case either, since I want to utilize the index using the SELECT list of a query, not in a spatial operation in the WHERE clause. Why can't a spatial index be invoked by the SELECT list, yet a non-spatial index can?

For example, if I were to try to create the following FBI, it would fail:

create index sdo_ordinates_idx on test_table ((shape).sdo_ordinates);

Error:

Error starting at line : 12 in command -
create index sdo_ordinates_idx on test_table ((shape).sdo_ordinates)
Error report -
ORA-02327: cannot create index on expression with datatype ADT