Display value of attribute of type SDO_GEOMETRY

The problem itself is caused by the fact that you are trying to retrieve a complex object that is a custom Oracle object which should be handled with its own mapping library. The SDO_GEOMETRY type as used here in you example statement:

SDO_GEOMETRY(   2003,   NULL,   NULL,   SDO_ELEM_INFO_ARRAY(1,1003,3),   SDO_ORDINATE_ARRAY(1,1, 5,7))

is part of the Oracle Spatial package and relies on custom objects. These cannot be read as easily as basic SQL columns like VARCHAR or NUMBER, that is why your getString("shape") call yields no results.

The solution is to use the JGeometry package provided by Oracle that is designed exactly for this purpose:

A Java class that maps Oracle Spatial's SQL type MDSYS.SDO_GEOMETRY. Supports only Oracle JDBC Driver version 8.1.7 or higher. Provides basic access functions to the geomeries stroed in Oracle Spatial database.

With this library on classpath you would be able to use the custom Java mappings to manipulate your shape object:

/// reading a geometry from databaseResultSet rs = statement.executeQuery("SELECT shape FROM testGeo");STRUCT st = (oracle.sql.STRUCT) rs.getObject(1);//convert STRUCT into geometryJGeometry j_geom = JGeometry.load(st);

You should use JGeometry class in the Oracle Spatial Java package for this. You can call the spatial type, like:

// Read a geometry from the DB.ResultSet rs = statement.executeQuery("SELECT shape FROM testGeo where name='cola_a'");STRUCT stGeo = (oracle.sql.STRUCT) rs.getObject(1);JGeometry jGeo = JGeometry.load(stGeo);// Use jGeo to fetch the required data.

If you need to write the Geometry back to Database, you can try this:

 // Write a geometry back to the DB. PreparedStatement ps = connection.prepareStatement("UPDATE testGeo set shape=? where name='cola_a'"); STRUCT stGeo = JGeometry.store(jGeom, connection); ps.setObject(1, stGeo); ps.execute();

Note: Refer the JGeometry documentation for the usage of the class APIs.

You can use something like

select g.geoid, g.name, go.column_valuefrom testgeo g, table(g.shape.SDO_ORDINATES) go;

but you will get one row for each number in the shape.