Create an ODCI list of SDO_GEOMETRY objects

I recently learned about ODCI lists (in an answer from @MT0).

For example, an OdciVarchar2List:

select 
    sys.odcivarchar2list('a', 'b', 'c') as my_list
from 
    dual


MY_LIST                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
-------------------------------
ODCIVARCHAR2LIST('a', 'b', 'c')

---

Out of curiosity, is there a way to create a ODCI list of MDSYS.SDO_GEOMETRY objects?

I tried using ODCIObjectList:

select 
    sys.ODCIObjectList(
        sdo_geometry('point(10 20)'),
        sdo_geometry('point(30 40)'),
        sdo_geometry('point(50 60)')
    ) as my_list
from 
    dual

But I got an error, suggesting that ODCIObjectList isn't meant for sdo_geometry objects:

ORA-00932: inconsistent datatypes: expected SYS.ODCIOBJECT got MDSYS.SDO_GEOMETRY
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 3 Column: 9

is there a way to create a ODCI list of MDSYS.SDO_GEOMETRY objects?

No, the ODCI types are built-in types belonging to the SYS schema and there is no ODCI list that can hold SDO geometry objects.

You either create your own collection type; see Justin Cave's answer.

Or you need to look for a different (non-ODCI) built-in collection type.

---

To find the existing collection types, you can use:

SELECT *
FROM   ALL_TYPES
WHERE  TYPECODE  = 'COLLECTION'

To find a list of all the built-in collections.

The SDO objects are defined in the MDSYS schema and you can filter to just those using:

SELECT *
FROM   ALL_TYPES
WHERE  OWNER     = 'MDSYS'
AND    TYPECODE  = 'COLLECTION'

Which contains 92 collection types; one of which is the SDO_GEOMETRY_ARRAY type which happens to do exactly what you want.

---

Therefore, if you do not want to create your own type, you can use:

SELECT MDSYS.SDO_GEOMETRY_ARRAY(
         sdo_geometry('point(10 20)'),
         sdo_geometry('point(30 40)'),
         sdo_geometry('point(50 60)')
       )
FROM   DUAL;

or, more simply:

SELECT SDO_GEOMETRY_ARRAY(
         sdo_geometry('point(10 20)'),
         sdo_geometry('point(30 40)'),
         sdo_geometry('point(50 60)')
       )
FROM   DUAL;