Search

My Oracle tables are not showing up in the table list

Liz Sanderson
Liz Sanderson

FME Version

Question

No tables show up in the FME table list picker. Why?

 

Answer

There can be a number of reasons why the tables or views don't show up in the table list picker of the Oracle reader:

 

Do you have permission to access the tables?

FME requires SELECT access to add a table or view to the table list.

Have you selected the correct Oracle reader?

  • Oracle Autonomous Non-Spatial Object for reading non-spatial tables
  • Oracle Autonomous Spatial Object for reading spatial tables
  • Oracle Non-Spatial for reading non-spatial tables
  • Oracle Spatial GeoRaster to read Raster data stored in Oracle's Georaster format
  • Oracle Spatial Object for reading spatial tables
  • Oracle Spatial Point Cloud to read Point Cloud data stored in Oracle's SDO_PC format
  • Oracle Spatial Relational for reading spatial tables in the older Oracle Spatial Relational data model
 
Screenshot 2024-05-03 at 12.52.06 PM.png


Do your spatial tables have entries in the metadata tables (MDSYS.USER_SDO_GEOM_METADATA)?

When looking for tables to add to the table list picker with the Oracle Spatial Object reader we access the MDSYS.SDO_GEOM_METADATA table to extract information such as the spatial extents and the SRID. If your spatial table or view doesn't have an entry in the MDSYS.USER_SDO_GEOM_METADATA table then it won't be included in the table list picker.  For more information on adding entries to the MDSYS.USER_SDO_GEOM_METADATA table see Adding metadata entries for Oracle spatial tables and views.

By default, FME will present the schemas that the user has access to. If you do not see the schema listed, there is a good chance permissions may not be set correctly.  Check with another Oracle Client tool, like SQL Developer, using the same user to verify.


OracleTablePicker2024.png

Use the Filter option to narrow the schemas by entering the table name you want to add.  Use the check box beside the returned table names to mark for selection, and continue searching for other tables.  This is very useful for large schemas.

Was this article helpful?

Comments

2 comments

Date Votes
  • Comment author
    Bryan Hall
    • Edited

    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?

    0
  • Comment author
    Bryan Hall

    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.

    0

Please sign in to leave a comment.