Show Full SDO_DIM_ARRAY in Oracle SQL Developer

Question

In Oracle SQL Developer, when I run this query:

SELECT DIMINFO FROM USER_SDO_GEOM_METADATA;

I get results that look like this:

MDSYS.SDO_DIM_ARRAY([MDSYS.SDO_DIM_ELEMENT],[MDSYS.SDO_DIM_ELEMENT])

I don't want this collapsed version. I want the entire array printed out.

I'm pretty sure I did this about a week ago, but for the life of me, I cannot remember how.

Version: SQL Developer 3.2.20.09

Answer 1

Under Preferences, Database, Advanced, try enabling the "Display Struct Data In Grid" option. Does that help?

Answer 2

The best answer seems to be to join the array cast to the source table so that it expands each element of the array to a different row:

SELECT META.TABLE_NAME, META.COLUMN_NAME, META.SRID, DIM.*
FROM USER_SDO_GEOM_METADATA META, TABLE(META.DIMINFO) DIM;

The results look something like this:

TABLE_NAME    COLUMN_NAME    SRID    SDO_DIMNAME   SDO_LB      SDO_UB      SDO_TOLERANCE
TABLE1        GEOM           3857    X             -20037700   20037700    0.1
TABLE1        GEOM           3857    Y             -20037700   20037700    0.1
TABLE2        GEOM           3857    X             -20037700   20037700    0.1
TABLE2        GEOM           3857    Y             -20037700   20037700    0.1