Current Oracle Spatial Blog Articles
Unpacking user_sdo_geom_metadata’s diminfo object.
Objects in Oracle require special handling when querying.
Firstly, Oracle says that, when querying something that contains an object, and you want to access one of the object’s elements (or fields), use of a table alias in a DML query is required.
So, let’s say we want to discover all possible geometry types being stored in a table/sdo_geometry column pair (we will use: my_table/shape) we would do the following (note the use of the table alias gd3):
That’s pretty straight forward. But let’s now apply this to the diminfo column of the user_sdo_geom_metadata view. Naively we could write:
But on execution we get the above error.
What you need to do is to use a TABLE function to unpack the elements of the diminfo structure as follows:
And to access all elements in the diminfo structure you can do this:
I have create a report for use in Oracle’s SQL Developer that generates a report showing each of the individual elements in USER_SDO_GEOM_METADATA. It can be downloaded using this link.