Question

How to dynamically retrieve all tables with all features

fme_hans
Rank
Badge +3

Hi,

 

I'm trying to get all the attributes of all spatial tables in an Oracle schema.

For this I start with a SQLCreator. It runs a query that gets table names from user_objects. The SQL creator exposes TABLE_NAME. I connect the Result port to a FeaureReader. I set this up as follows:

Feature Types to Read: TABLE_NAME.

,Generic> Port Attributes to Expose: *

When I run the workspace, I see the correct number of records coming out in the < Generic> port. However, not per attribute. In the log I see that a select is performed with all attributes. (Select schema_name.coloumn_a, schema_name.coloumn_b, schema_name.coloumn_c etc.).

When I connect an attribute exploder to the generic port I see all attributes with content but then they are shown separately and no longer per record.

Is it possible to dynamically retrieve all tables and then show all attributes of all these tables per record?

 

SQLCreator 

FeatureReader 

4 replies

ebygomm
Rank
Userlevel 1
Badge +21

Is there a reason you want to do this in FME rather than just writing a sql statement to return the column names?

This will give you column names so should be fairly straightforward to adapt your existing sql that returns the table names to return column and table name

SELECT table_name, column_name
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'

 

nielsgerrits
Rank
Userlevel 6
Badge +33

Is there a reason you want to do this in FME rather than just writing a sql statement to return the column names?

This will give you column names so should be fairly straightforward to adapt your existing sql that returns the table names to return column and table name

SELECT table_name, column_name
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'

 

Like @ebygomm​ I think you should do it in SQL when you can. Faster and easier.

 

But if you want to do this using a FeatureReader you should read the schema (FeatureReader Parameter Features To Read = Schema Features) and connect to the Schema outputport. Then you have for each table a schema feature with all column names and types in a list.

fme_hans
Rank
Badge +3

I need to export shape files per table. That can't be done with SQL.

nielsgerrits
Rank
Userlevel 6
Badge +33

I need to export shape files per table. That can't be done with SQL.

Then the only thing you need to do is create a dynamic writer.

  • Set the FeatureReader to Single Output Port.
  • Create a FeatureWriter.
  • Connect the schema outputport and the generic outputport to the same FeatureWriter inputport.
  • In the FeatureWriter check "Dynamic Schema Definition". Now Shapefile Name = fme_feature_type, Schema Sources = "Schema From Schema Feature".
  • Go to tab User Attributes. Attribute Definition = "Dynamic" and remove existing attributes.

Now run the workspace.

Be aware, shapefile column name length is max 10 characters. Column names in oracle are longer this won't work well.

Reply


Community Stats

28,723
Posts
109,511
Replies
36,559
Members
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.