Though Oracle Spatial can accomodate multiple geometry columns in a single table, many clients may find it easier to work with the TOP10NL data in a normalized form, that is, one geometry column per table, each related geometry table joining to a shared attribute table.

Databases are the best environment to normalize data, so there is nothing diffuclt in making normalized datasets within Oracle. A template based approach used for other writers is not as good for Oracle - we could use scripts or predefined views, but having parts of the process in a file form, whereas most of the data is database form, or making predefining views, which may become invalid when no tables will be presented in a database, looked like an unreliable way. We wanted to make the workspace (See attachment: gml2oracle.fmwt) self-contained.



Creating views for tables with multiple geometries is happening after both Oracle Spatial and Oracle_DB writers have finished their job.  The syntax for running multiple SQL statements from Workbench is not described well in our Help file, here is how it should look:

  BEGIN EXECUTE IMMEDIATE 'SQL_STATEMENT1'; EXECUTE IMMEDIATE 'SQL_STATEMENT2'; END;

The workspace creates all the tables and the views, which may look as following in Oracle Spatial Index Advisor:



These views are also accessible through Universal Viewer (you should type their names though, because they are not displayed in the list of available tables - SUPPORT.WEGDEELVIEW SUPPORT.WATERDEELVIEW SUPPORT.FUNCTIONEELGEBIEDVIEW etc):



Going back from Oracle to TOP10 is a simple FeatureMerge operation.


Note, that the attached examples will work with the Relay example file. Some of the feature types were not presented in that file, and they didn't get into the Oracle database and oracle2gml workspace (See attachment: oracle2gml.fmw). There may also be an issue with uppercase vs lowercase attributenames. So the attached examples illustrate the technique as applied to Oracle, and should be used with care - some modification may be required to work with another datasets.