Considerations
When adding a table in FME’s Oracle reader, the table list picker may not display the expected tables or views. This can happen for several reasons, including permissions, incorrect reader selection, or missing metadata entries.
Why This Happens
Permission Issues
- FME requires SELECT access to display tables and views in the list.
- If a table is missing, verify that your user account has the necessary permissions.
Incorrect Oracle Reader Selection
Make sure you’re using the correct Oracle reader type based on your data:
- Oracle Autonomous Non-Spatial Object -> non-spatial tables
- Oracle Autonomous Spatial Object -> spatial tables
- Oracle Non-Spatial -> non-spatial tables
- Oracle Spatial GeoRaster -> Raster data in Oracle's Georaster format
- Oracle Spatial Object -> spatial tables
- Oracle Spatial Point Cloud -> Point Cloud data in Oracle's SDO_PC format
- Oracle Spatial Relational -> spatial tables in the older Oracle Spatial Relational data model
Missing Metadata Entries (For Spatial Tables)
- If using the Oracle Spatial Object reader, FME extracts spatial information from the MDSYS.SDO_GEOM_METADATA table.
- If your table is missing an entry in MDSYS.USER_SDO_GEOM_METADATA, it will not appear in the table list picker.
- To add metadata, see: Adding metadata entries for Oracle spatial tables and views.
Schema Visibility Issues
- By default, FME only shows schemas the user has access to.
- If a schema is missing, check permissions by using another Oracle client tool like SQL Developer with the same user credentials.
Filtering Large Schemas
- Use the Filter option to narrow the results by entering part of the table name.
- Select tables using the checkbox beside the returned results and continue searching for others.
Comments
2 comments
This is with Oracle 19c with a 12.2 client and FME 2022.2.
I can select tables in a FCOWNER schema with SQL Developer or Toad, but with FME Workbench using the Oracle Non-Spatial reader the list of tables does not include this schema. What specific grants do I need to have so that workbench will show this schema's tables?
What I do know is that none of these will allow you to use the reader to access items in another schema:
SELECT ANY TABLE
GRANT DBA TO user
GRANT SELECT (or also UPDATE, INSERT, DELETE) ON schema.table TO user
Apparently, you either you need to connect as the schema owner or use a proxy login as the schema owner. I don't understand what FME is looking for.
Please sign in to leave a comment.