Hi Simon:
Thanks for the information. I have read your article as well as your replay to this post. I am only intrested in records with sdo_elem_info(1, 4,1). But after I modify your query and run it, I am receiving so many null value for the etype. Is there any way to use the Table function in the where clause of the select statement?
thanks again for you advise on this.
Thanks, MK

So, if you want to query an SDO_ELEM_INFO triplet with the current implementation you have to normalise it through pivoting to be able to do so.

(It would have been nice that the SDO_ELEM_INFO_ARRAY had then been defined as CREATE OR REPLACE TYPE SDO_Elem_Info_Set_Type AS TABLE OF SDO_Elem_Info_Type; but it wasn't so what we have to work with is what has been given to us.)

I don't really recommend this method as its quite easy to overload your memory settings with a large geometry, but if you know your varrays to compare are modest in size and/or you have a lot of memory to spare, you can flatten any varrays within an object into XML easily and then directly compare the resulting CLOBs to each other. I use this type of thing when doing tests where function A version 1 produces SDOs and then function A version 2 should produce the same SDOs.

"I don't really recommend this method as its quite easy to overload your memory settings with a large geometry"

What overloading occurs? What memory settings are affected?

Sdo_Elem_Info's for most of the sdo_geometries I have seen over the past 10+ years don't have large sdo_elem_info_arrays (and pivoting/unpivoting and disaggregating a collection via a table function is well handled by the query optimizer): I could have suggested a pipelined function but for most questioners on this forum I try not to suggest functions/procedures as PL/SQL can be scary for some people.

I mean if you take an SDO GEOMETRY with a 1/2 million vertices which are packed tightly in a SDO_ORDINATE_ARRAY VARRAY and then try the XML flattening trick, you end up with one copy in memory as the XML object and a second copy in memory as the output CLOB (and won't the original VARRAY also be in memory during part of this time?). The XML/XML AS CLOB data structures are much larger then the original arrays. I've seen my smaller machines run out of memory doing this kind of serialization.

If the idea is to ONLY compare the SDO_ELEM_INFO_ARRAYs, you'd need a different object to stick the VARRAY into to allow the flattening. Its doubtful that would ever overload things but that object wouldn't be particularly useful for anything else.

Hmm, I am not sure how you can do much with Oracle Spatial without PL/SQL.