How to get a list of features from Oracle via FME?

Question

I have a table in Oracle containing only the names of other tables of varying feature types, which I'd like to process (i.e.export to csv). this task is to be scheduled via FME Server. the feature types to work on are only known at runtime. Therefore a test like Mapperz suggested would not work in this situation.

There are several examples how to do this based on names in text files or using cx_oracle (see http://fmepedia.safe.com/articles/How_To/Pass-a-List-of-Tables-or-Layers-to-an-FME-Reader-using-Python-Scripted-Parameters).

My situation is that cx_oracle isn't availabe and a text file won't do it as entries will change frequently (but the latter approach via scripted parameter is actually working fine).

The link above states in the beginning that: (...) For many situations, using an FME reader to read the table list and then the FeatureReader transformer to read the data may be a better approach than the one described below. (..)

I tried to follow this approach, which is also described by Mark Irland FME Features to read parameter, set up the reader for the table with the names of the other tables and connected it to a FeatureReader. It is configured to query the feature types specified in the attribute below (which is set to a concatenated attribute as described at "Feature Type List" in http://blog.safe.com/2011/05/fmeevangelist78/ or in another test case to the relevant column of the table ). All ports are then connected to a shapefile-writer.

The result is that the entries of the table with the names are output via the initiator port and the features (with geometries) themselves to the 'other' port. As result the geometries are shown in the inspector but aren't contained in the shapefile.

I also tried exposing fme_feature_type on the reader then taking it on the FeatureReader, but this makes only appear the feature names in the .dbf file with no geometries at all.

the hole thing is quite related to How to get a list of features from FME? Python? and Mark Ireland answer, and might be only causing troubles because Oracle is involved.

I also checked: http://fmepedia.safe.com/articles/How_To/How-to-Read-All-Feature-Classes-from-Multiple-ESRI-Geodatabases / http://screencast.com/t/5OjFTrkiZL: this approach won't work with oracle as you have to specify credentials.

so how could I get the FeatureReader to query the table names and put each in different shapefiles?

Unfortunatly the workbench code I used cannot be pasted here because of size limitation.

Added Image shows the workbench used: note in the lower left corner the concatenated result based on which the FeatureReader is supposed to query the database

Version information: #! LAST_SAVE_BUILD="FME(R) 2013 SP2 (20130605 - Build 13499 - WIN64)"

Answer 1

More an additional Comment (with an image) than Answer

Same Oracle Spatial Connection - Same TABLE - but using two TESTERs to define FeatureType

and then write the to visualiser (or Shapefiles)

Answer 2

I have one sql creator querying usertable information, extract the tablenames i want (using regexp etc) and write it to an attribute. Then i link ta SQL executor and have it read the selected tables dynamically. TABLE_NAME is attribute holding the desired tables.

Example.

SQL Creator:

select  d0.naam, ut.num_rows, ut.table_name, 
        REGEXP_REPLACE(ut.table_name,'SDO_OBJECT(\d*)','\1') as OBJECTCODE,
        REGEXP_REPLACE(d0.tabel,'def(\d*)','\1') as defOBJECTCODE
from user_tables ut,
      def0 d0
where ut.table_name LIKE 'SDO_OBJECT%'
and ut.num_rows >0
and REGEXP_REPLACE(ut.table_name,'SDO_OBJECT(\d*)','\1') = REGEXP_REPLACE(d0.tabel,'def(\d*)','\1')

Create attribute "TABLE_NAME" written to attribute "TABLE_NAME" and then SQL executor. (if you need to concatenate databasename. If i do the concatenate in the (below) sql i get an error. So i attributecreator first)

"select ID, GGI_ID, GEOMETRY from @Value(TABLE_NAME)

You can add a shape writer with fanout.