02-28-2018 11:16 PM
When I try to create a new feature class (via GeoMEdia -> Feature Classes), GeoMedia tries to insert into SDO_GEOM_METADATA_TABLE:
GMDatabase:repareStatement - pSQL: 'insert into MDSYS.SDO_GEOM_METADATA_TABLE (...)
Database Error Number: 942
Database Error Message: ORA-00942: Tabelle oder View nicht vorhanden freshData()
Then it goes with MDSYS.USER_SDO_GEOM_METADATA
Is this "by design" ? Why are we trying to access SDO_GEOM_METADATA_TABLE instead of going directly to USER_view?
RGDS Cezary Zielinski
Solved! Go to Solution.
03-06-2018 01:56 PM - edited 03-06-2018 01:57 PM
I suspect that you are able to create your feature class but see the ORA-00942 in logging only and are curious about the error in your log file.
As you are aware, Oracle has it's own MDSYS metadata that must be populated for spatial feature tables; GeoMedia also has it's own set of metadata stored in the GDOSYS schema. When features are created by GeoMedia (Feature Class Definition for example), GeoMedia will populate both Oracle's and GeoMedia's metadata. It is by design that GeoMedia will first try to populate the MDSYS metadata directly using the MDSYS.SDO_GEOM_METADTA_TABLE. It then has fall back logic to try populating the MDSYS metadata via the USER_SDO_GEOM_METADATA.
Find below an extract from the GeoMedia Oracle Object Model (GOOM) Package quick reference guide that may shed more light on this subject.
7. ORACLE METADATA – SETTING THE MBR AND TOLERANCE FOR GEOMETRIES
All columns of type SDO_GEOMETRY require entries in Oracle's spatial metadata table: SDO_GEOM_METADATA_TABLE which is stored in MDSYS. This table is used to store the spatial tolerance and spatial extents (minimum bounding rectangle – MBR) of each spatial column in the database. This information is used for indexing and analysis of the geometry data.
Oracle provides access to this metadata table via 2 views:
Normally, applications update Oracle's metadata via USER_SDO_METADATA, which is writable, and access the metadata via ALL_SDO_GEOM_METADATA, which is read-only (as is its parent table). You can get around this restriction by granting insert privilege in MDSYS.SDO_GEOM_METADATA_TABLE to the user (or to PUBLIC) that requires the capability. This is not recommended but may be necessary in cases where you want a user to create tables in the master schema.
GeoMedia requires all feature classes (even those that are views) to have entries in the MDSYS.SDO_GEOM_METADATA_TABLE. During a connection, it will read from ALL_SDO_GEOM_METADATA to get the information it requires. When running a GeoMedia command that creates a table in Oracle, GeoMedia will first try to populate the MDSYS.SDO_GEOM_METADATA_TABLE and if that fails, it will next try to populate USER_SDO_GEOM_METADATA. If you are connected as a user that does not own the table you are creating and proper privileges have not been established, the process will fail. All columns of type SDO_GEOMETRY require Oracle metadata. This includes views (a GeoMedia requirement, not Oracle).