topic Re: Exception in component tOracleOutput_1 ORA-00904: : invalid identifier in Talend Studio

Exception in component tOracleOutput_1 ORA-00904: : invalid identifier

Anonymous — Sat, 16 Nov 2024 14:07:16 GMT

Hi,
I have a job with a tFileInputDelimited and a tOracleOutput.
I'm getting :
Exception in component tOracleOutput_1
java.sql.SQLException: ORA-00904: : invalid identifier
Note : there is no fieldname returned between the semicolons.
I started with a more complex schema, then used a simple schema with only 2 fields. I have put everything in uppercase, tablename, fieldnames.
I tried in the tOracleOutput "Drop table if exists and create","Create table if not exists", I created the table in advance in Oracle.
I checked that the fieldnames and table name are not longer than 30 Chars and don't contain unallowed characters.
I kept them as string type.
The connection to the DB is working (I can inspect it)
Nothing helps.
Any Idea what the reason could be ?

Re: Exception in component tOracleOutput_1 ORA-00904: : invalid identifier

youssef2 — Tue, 16 Dec 2008 15:09:00 GMT

Hi,
Which version of TOS & Oracle are you using? Try the solution shown in the screenshot below.
Regards,
Youssef

Re: Exception in component tOracleOutput_1 ORA-00904: : invalid identifier

Anonymous — Tue, 16 Dec 2008 17:04:13 GMT

Wow,
Thanks for the tip, It worked.
Just weird that it is not documented and that you have to change the schema manually.
I also don't understand in tOracleOutput "Advanced settings" -> "Convert columns and Table to uppercase" doesn't seem to work, or may be I don't know how to use it properly, because I had to uppercase myself.
TOS version
Version: 3.0.2
Build id: r20205-20081119-1100
ORACLE version :
10g (should be 10.0.2) with service name (couldn't have it working with Oracle with SID, it was complaining about oracle.jdbc.ClassNotFoundexception for oracle.jdc.driver.OracleDriver. I tried CLASSPATH,PATH, placing the jar in miscellaneous places, ... nothing helped)
This product has a lot of useful features. Very interesting one.
Thanks a lot.

Re: Exception in component tOracleOutput_1 ORA-00904: : invalid identifier

Anonymous — Sun, 21 Dec 2008 20:46:10 GMT

Hi,
The previous resolution is working, but rather as a workaround, because in the DB it inserts quotes around the value.
After researching, I found that the ORA-00904: : Invalid identifier is appearing when you change the column name going to the DB.
Creating the MetaData Schema (See Appended Img):
Point 1-2 : The input File is "Value1";"Value2";... using this setting, I remove the quotes around the value
Point 3-4 : The first line of the Input File is the Column name So use it for the column name.
Bug ?
Selecting Set heading as column name when creating the metadata schema is properly creating the schema with custom headers names, but after, when using it as input to tOracleOutput it creates ORA-00904: : invalid identifier when the Db column name is different from column0, ...
I couldn't find a way to set the Db column name other than column0, ... as soon as the the Db column is names column X it works.

Re: Exception in component tOracleOutput_1 ORA-00904: : invalid identifier

Anonymous — Tue, 03 Mar 2009 15:28:54 GMT

Ok,
Now I have figured out why I got the error.
The Oracle error is generated on field names which are reserved words. Those needs to be enclosed in double quotes.
I had 2 columns in my schema order and operation.
If I simply use those names as Db Column names, Oracle isn't happy and generated the error.
So the reason why we need to surround those names with double quotes.
The escape character is \. So if you write \"order\" it will be sent to the Oracle as "order".
That's for the explanation.