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?
+18- Contributor
If table or attribute names are mixed case, you need to enter them in double quotes, like "Table".
+11- Contributor
erik_jan wrote:
If table or attribute names are mixed case, you need to enter them in double quotes, like "Table".
+18- Contributor
aguan wrote:
@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
+18- Contributor
david_r wrote:
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):
erik_jan wrote:
Good catch @david_r,
But in the FME 2017 Oracle Spatial writer that does not exist (only for columns):
+18- Contributor
david_r wrote:
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?
erik_jan wrote:
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!
+12- 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.
+12- 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- Contributor
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.
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.