Create an ODCI list of SDO_GEOMETRY objects

Question

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 reason that you need to use one of the built-in collection types? You can (and almost certainly should) declare your own collection of `sdo_geometry` objects if you want to do something with a collection. — Justin Cave, Jun 12 '22 at 03:34

score 1 · Answer 1 · answered Jun 12 '22 at 03:48

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;
/

Is the main benefit to creating our own types the fact that, *"They can be given more informative names"*? — User1974, Jun 14 '22 at 04:35

score 1 · Accepted Answer · answered Jun 12 '22 at 08:12

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;

Create an ODCI list of SDO_GEOMETRY objects

2 Answers2

Linked