How to use SQL to retrieve the Vertex values in an SDO_GEOMETRY Attribute

It turns out to be more complicated than you might think to simply retrieve the Vertices of an SDO_GEOMETRY attribute in Oracle Spatial using just SQL. The main problem is that this is an array of an unknown size, so the solution below is just for retrieving the Ordinate values for 1 record identified by a key and each record returned is one Ordinate in the list.

SELECT Round(COLUMN_VALUE,8) as pt
FROM TABLE( SELECT a.MyGeomAttr.SDO_ORDINATES
                    FROM MyTable a
                    WHERE MyKey=24974)

which will retrieve something like this (my example uses a 3D geometry so there are 3 value per vertex with Z being 0 in all of them):

I will go through why I did the things I did to make this work like I wanted below.

If you are using ODP.NET and you have Oracle Number values in the Ordinates that are larger than a .NET Decimal value (this is common for me), you must use Round (or truncate the value in someway) so that you don't get an Arithmetic Overflow when the Ordinate Value is loaded into a .NET decimal value. I am using the Round function to simply get 8 decimal points of precision). It turns out that the data I have uses Oriented Points and the computed vector coordinates use a large number of decimal places that .NET Decimals can't handle.

To get the Ordinate Values back as the Query results, you must use the TABLE function to convert the SDO_ORDINATES Array to records. See here for more info on the TABLE function. The key thing here is that the COLUMN_VALUE is not just me making up a name for my example, it is a keyword that lets you refer to the value the Table function returns (otherwise you can't run the results of the Table function through the Round function).

The alias "a" seems to be required or you get an error in the query.