Skip to main content
  • EN

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?

If table or attribute names are mixed case, you need to enter them in double quotes, like "Table".

If table or attribute names are mixed case, you need to enter them in double quotes, like "Table".

@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/

 

@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/

 

I was pretty sure you could do this in the past:

 

 

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

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

Good catch @david_r,

 

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):

 

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?
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.

 

 

Even with this setting unchecked, when I leave the table name field it changes to uppercase. Sure at some older version it didn't.

 

 

Aha, I see now. You're right, the tablename is forced to upper case when using the Oracle Spatial writer. If you're using the Oracle Non-Spatial writer, you can use mixed case table names.

 

Thanks for clearing that up!
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.

 

@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.

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


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.