Skip to main content
  • EN

I want to write features to an Oracle database. Some featuretypes have geometry others are non-spatial. Tables are created by the writer, schema is dynamic and read from a FME schema txt file.

If I use Oracle Spatial Object writer ALL features get a GEOM attribute and are treated as if they are spatial.

So I tried to optimize the workflow and use a second writer - an Oracle non-spatial writer - for the non-spatial features.

This improved performance considerably, as I have 50% non-spatial features.

Problem:

Oracle Spatial Object Writer ALWAYS creates tables with UPPERCASE names (and converts them if necessary) while Oracle non-spatial writer ALWAYS creates the tables as they are in the controlling attribute.

In this way I cannot use the same schema definition file for both writers.

Anything I missed out on ?

Just a hunch, have you tried unchecking the option "Uppercase Column Names" when creating the Oracle Spatial Object writer?

From the docs:

If this parameter is checked, the writer automatically makes the column names uppercase, and disallows characters in the column names that require quoting within Oracle. This parameter is only applicable when generating a writer.

Just a hunch, have you tried unchecking the option "Uppercase Column Names" when creating the Oracle Spatial Object writer?

From the docs:

If this parameter is checked, the writer automatically makes the column names uppercase, and disallows characters in the column names that require quoting within Oracle. This parameter is only applicable when generating a writer.

Yes, it doesn't affect the tablename.

Yes, it doesn't affect the tablename.

I just checked with FME 2018 and 2019, and indeed you're right...

I just checked with FME 2018 and 2019, and indeed you're right...

We're going to check into this and see what can be done -- longer term we will fix this, but it may be more involved than a quick fix would allow. Sorry about the inconvenience but thanks for pointing it out.

We're going to check into this and see what can be done -- longer term we will fix this, but it may be more involved than a quick fix would allow. Sorry about the inconvenience but thanks for pointing it out.

Good to hear. For now I will write everything with Oracle Spatial writer.

Thinking back on non-spatial problems with GeoPackage it is like a Deja Vue ;-)

Good to hear. For now I will write everything with Oracle Spatial writer.

Thinking back on non-spatial problems with GeoPackage it is like a Deja Vue ;-)

Oracle Spatial has a requirement that spatial table and column names must be all-caps, which is why our Oracle Spatial Object writer has the restriction on all-caps table names.

Doc reference: https://docs.oracle.com/en/database/oracle/oracle-database/18/spatl/spatial-datatypes-metadata.html#GUID-76097710-5E16-4A2F-8DCA-6BCC2C6E4429 (There is a note just above this link that suggests non-all-caps table names might be possible, just strongly unrecommended; however I was not able to make this work while testing Oracle 12c yesterday.)

We lifted this restriction for our Oracle non-spatial writer some years ago as it wasn't needed. However, I can see how the resulting inconsistency causes pain. A number of users would like an *option* to make the Oracle non-spatial (and other database) writers force uppercase (or perhaps lowercase) table names, similar to what is currently possible for column names. That's something we're looking into for the future.

Oracle Spatial has a requirement that spatial table and column names must be all-caps, which is why our Oracle Spatial Object writer has the restriction on all-caps table names.

Doc reference: https://docs.oracle.com/en/database/oracle/oracle-database/18/spatl/spatial-datatypes-metadata.html#GUID-76097710-5E16-4A2F-8DCA-6BCC2C6E4429 (There is a note just above this link that suggests non-all-caps table names might be possible, just strongly unrecommended; however I was not able to make this work while testing Oracle 12c yesterday.)

We lifted this restriction for our Oracle non-spatial writer some years ago as it wasn't needed. However, I can see how the resulting inconsistency causes pain. A number of users would like an *option* to make the Oracle non-spatial (and other database) writers force uppercase (or perhaps lowercase) table names, similar to what is currently possible for column names. That's something we're looking into for the future.

Thanks Paul for the insight. I fully agree.

As a sidenote: Esri ArcCatalog is in trouble with small letter/mixed case Oracle tablenames. They are listed, but if I try to access/drop them is says something like "table is not existing".

So optional forcing to UPPERCASE would really help, as long as a dynamic schema definition would still work ;-)

Thanks Paul for the insight. I fully agree.

As a sidenote: Esri ArcCatalog is in trouble with small letter/mixed case Oracle tablenames. They are listed, but if I try to access/drop them is says something like "table is not existing".

So optional forcing to UPPERCASE would really help, as long as a dynamic schema definition would still work ;-)

> ... as long as a dynamic schema definition would still work

 

 

Indeed! That is part of the challenge here. From your comment, I suspect you're aware that the Oracle writers' "Uppercase Column Names" setting doesn't work in dynamic schema mode. That's tracked by our FMEENGINE-47925.

I think the best case would be to fix that issue, and also make a similar option for table names. However, we don't have a concrete timeline for this work at this time.

> ... as long as a dynamic schema definition would still work

 

 

Indeed! That is part of the challenge here. From your comment, I suspect you're aware that the Oracle writers' "Uppercase Column Names" setting doesn't work in dynamic schema mode. That's tracked by our FMEENGINE-47925.

I think the best case would be to fix that issue, and also make a similar option for table names. However, we don't have a concrete timeline for this work at this time.

My current understanding and experience is:

Oracle Spatial Objects writer will force tablenames to UPPERCASE and dynamic schema still works even if lower-/mixed-case tablenames are used in the schema file. This situation works fine for me.

Oracle non-spatial writer does not change table names.

When I tried to UPPER the tablenames myself my schemafile no longer worked.

I do not have issues with attribute names, as they are always UPPER in my application. I see that these open up a set of problems I wasn't even aware of.

My current understanding and experience is:

Oracle Spatial Objects writer will force tablenames to UPPERCASE and dynamic schema still works even if lower-/mixed-case tablenames are used in the schema file. This situation works fine for me.

Oracle non-spatial writer does not change table names.

When I tried to UPPER the tablenames myself my schemafile no longer worked.

I do not have issues with attribute names, as they are always UPPER in my application. I see that these open up a set of problems I wasn't even aware of.

@mhab: Again, fully agree.

I should also mention for you and others: If you're in a dynamic scenario and you want to uppercase the table names (e.g. with Oracle non-spatial), there is a simple workaround.

I made a Sqlite database with lowercase table names, and was able to use this workaround to automatically copy these table to Oracle (non-spatial) with uppercase table names.

I still hope to offer a proper solution to all of this. Timing yet to be determined.

Reply


Community Stats

30,159
Posts
114,480
Replies
37,806
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.