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?
How to write data into Oracle SDE tables that have mix case column names?
Userlevel 2
+16
- Contributor
- 2180 replies
-
9 October 2017
If table or attribute names are mixed case, you need to enter them in double quotes, like "Table".
+4
- Contributor
- 132 replies
-
9 October 2017
If table or attribute names are mixed case, you need to enter them in double quotes, like "Table".
Userlevel 2
+16
- Contributor
- 2180 replies
-
9 October 2017
@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.
Userlevel 4
- 8177 replies
-
10 October 2017
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
Userlevel 2
+16
- Contributor
- 2180 replies
-
10 October 2017
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):
Userlevel 4
- 8177 replies
-
10 October 2017
Good catch @david_r,
But in the FME 2017 Oracle Spatial writer that does not exist (only for columns):
Userlevel 2
+16
- Contributor
- 2180 replies
-
10 October 2017
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?
Userlevel 4
- 8177 replies
-
10 October 2017
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!
+11
- Safer
- 373 replies
-
11 October 2017
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.
+11
- Safer
- 373 replies
-
11 October 2017
@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.
+4
- Contributor
- 132 replies
-
11 October 2017
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
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.