I have an Oracle SDE table created from MS Integration Service. The table has columns with mixed upper and lower cases. Look like FME data write into this table only looks for upper cased column names and I got this ORA-00904: invalid identifier error for all columns. We won't want to change the column names to be all upper case. Is there a workaround for this?
Question
How to write data into Oracle SDE tables that have mix case column names?
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+26- Contributor
If table or attribute names are mixed case, you need to enter them in double quotes, like "Table".
+11- Supporter
If table or attribute names are mixed case, you need to enter them in double quotes, like "Table".
+26- Contributor
@erik_jan. Not sure what you mean. The table is already created (with double quotes on column names to make them case sensitive). My question is how to make FME workspace write data into this table as FME seems to match the upper case column names for Oracle/
But now that seems to get changed in:
I would send this to Support@Safe.com.
Seems to be unwanted behavior.
When creating the writer, make sure that you've un-checked the box for upper case column names:
For the table name, no use in putting it in quotation marks, the table name case is respected regardless.
Tested with FME 2017.1
+26- Contributor
When creating the writer, make sure that you've un-checked the box for upper case column names:
For the table name, no use in putting it in quotation marks, the table name case is respected regardless.
Tested with FME 2017.1
But in the FME 2017 Oracle Spatial writer that does not exist (only for columns):
Good catch @david_r,
But in the FME 2017 Oracle Spatial writer that does not exist (only for columns):
+26- Contributor
Yes, there is no such option for the table names, only for the columns, but according to my tests that isn't necessary since the tablenames are created with the exact case used in FME. Or am I misunderstanding something?
Even with this setting unchecked, when I leave the table name field it changes to uppercase. Sure at some older version it didn't.
Thanks for clearing that up!
+13- Safer
Hi @ag . Have you submitted this to Support?
Curious if you can confirm - "Oracle SDE" and want to ensure you are using an Oracle Writer or are you using an SDE or Geodatabase Writer? When I see 'SDE' I think Esri and thus SDE30 or Geodatabase Writers might be involved here.
+13- Safer
@ag, On the outside chance that this is essentially a non-spatial Oracle table you are writing to, you could use the SQLExecutor and build the insert statement in there, double quote the attribute names.
+11- Supporter
Sorry not making this clear, The writer is an ArcSDE Geodatabase in Oracle.This upper case column option is only avaliabke for Oracle non-spatial or Oracle spatial. Tried double quoting attribute names before data entering the writer, not working. Always this ORA-00904: invalid identifier error unless I recreated the feature class using all upper cased column names.