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

CodePudding user response：

Declaring your own collection type is pretty easy and definitely the way to go. Being able to use the ODCI collection types can be handy if you happen to be in a situation where you really need to use a collection but you're not allowed to declare your own collection type for some reason (i.e. you're using a read-only database). In general, though, it makes vastly more sense to declare your own collection types (which can be given more informative names and which you can then manage just like all the other objects in the schema.

create or replace type sdo_geometry_nt is table of sdo_geometry;
/

declare
  l_points sdo_geometry_nt := sdo_geometry_nt( sdo_geometry( 'point(10 20)' ),
                                               sdo_geometry( 'point(30 40)' ),
                                               sdo_geometry( 'point(50 60)' ));
begin
  for i in 1..l_points.count
  loop
    -- do something
    null;
  end loop;
end;
/

CodePudding user response：

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;