Skip to main content
  • EN

Problem:

FME apparently only looks in the MDSYS.USER_SDO_GEOM_METADATA table for metadata on Oracle spatial data. When it can't find this, it currently falls back to assuming data is in two dimensions. This is not ideal given the rapid increase in use of 3/4D data.

FME advice currently (not updated since v2015.1) is to manually create the metadata in the USER_SDO_GEOM_METADATA table. With the release of Oracle 23ai, this is something that's no longer advised because Oracle now creates the metadata itself. This also introduces further problems when the tabe owner updates their metadata, but the user copy then won't be updated.

This appears to be an assumption by FME that assumes that the database connection user is then automatically the schema/table owner

Proposal:

Any data using the Oracle Spatial reader should continue to first search MDSYS.USER_SDO_GEOM_METADATA and then progress to MDSYS.ALL_SDO_GEOM_METADATA should there be no valid metadata This would support this user-schema database setup, but additionally maintain any user-defined metadata where this is required.

​Hi ​@joey_geoloog ,

Investigating this request further. Could you share a set of steps to get into a situation where FME has this problem? It would also be helpful to know what the username, schema name, and table name are in this scenario – is the user name the same or different to the table’s schema?

Thank you

Hi ​@PierreAtSafe ,

I think most of the info you need is documented in this other post: Oracle Spatial reader unable to access metadata | Community. I’ll make it more generic here.

If I connect to the database as user JOEY and select data from JOEY.TABLE, then the metadata will be correctly loaded, because JOEY.TABLE is owned by JOEY and the metadata will be present in MDSYS.USER_SDO_GEOM_METADATA when accessed by user JOEY, because that view is controlled by the active session user. FME sees the metadata, all OK.

If I connect as user PIERRE and select data from JOEY.TABLE, then because user PIERRE is not the owner of schema JOEY, there is no entry in MDSYS.USER_SDO_GEOM_METADATA for that table. FME complains there is no metadata available. This is where FME should then look into MDSYS.ALL_SDO_GEOM_METADATA where there is indeed an entry for JOEY.TABLE.

Linked forum post above has real world examples with the info from the database visible, let me know if you need further info.

Thanks, Joey

New→Open

Community Stats

31,824
Posts
120,871
Replies
39,458
Members

Latest FME

Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.