[resolved] Always get ora-00984 error when executing

[Anonymous]

Hi, all:
I tried to write data to an oracle DB with tOracleOutput component. Attach is the definition of component.
However all actions are failed with below error:
ORA-00984: column not allowed here
Below is an output from tLogRow, showing my example data:
BUY|BRLUSD173|03/02/2012|03/02/2012|8650000.0|0.0
Can anyone help to look into this?

[Anonymous]

Hi,
Can you upload any image of your job and definition of the components you used in your job, so that it will be easy to understand the problem
Thanks and Regards,
Pavan

[Anonymous]

Please see attached images.
Pic 1 shows the job definition and tOracleOutput Component.
Pic 2 shows the definition of tConertType
Pic 3 shows my database table definition.
Thanks

[Anonymous]

Hi
Usually the ORA-00984 error is caused by wrong query.
Add a tJava in this job and type in the following code.

((String)globalMap.get("tOracleOutput_1_QUERY"))

Is there anything wrong with this query?
Or just show it to us.
Regards,
Pedro

[Anonymous]

Hi, Pedro:
I think the problem should related to two Date columns. I did some testing as below:
1. Remove Record_Date and Settle_Date columns from input file.
2. Recursively remove these two columns from subsequent component, that means now INSERT SQL statement would not contain these two columns.
Then it works and I can insert data with the rest four columns. Below I attached the updated definition.
My question is how to output Date type/format data into Oracle db.
Thanks!

[Anonymous]

Hi
Please send me an email attached this export job.
I will test it for you. Maybe this is due to data pattern.
Regards,
Pedro

[Anonymous]

Hi,
You can do your job in the below way which i thought would be simple for you to handle

tFileInputDelimited_1(Main) ----> tMap_1(Main) ----> tOracleOutput_1

In tMap_1 double click on the component and click auto map, after you click auto map all your input columns will be mapped to your output columns(tOracleOutput_1). After doing this you have to do small changes for the date columns in the right hand side of tMap ie,. tOracleOutput_1, clickon the row1.record_date you will see a edit button, click on it an expression builder will be opened, in that paste the below mentioned expression
TalendDate.formatDate("mm/dd/yyyy", TalendDate.parseDate("MM/DD/YYYY",row1.record_date))
The same process should be done for row1.settle_date.

This should work for you, but make sure that you dont have any null values for the any of the columns, if there are null values you need to handle it in different way
Thanks and Regards,
Pavan

Please see attached images.
Pic 1 shows the job definition and tOracleOutput Component.
Pic 2 shows the definition of tConertType
Pic 3 shows my database table definition.
Thanks

[Anonymous · Accepted solution]

Hi, Pavan:
I got your point, and also the design point of talend to oracle db.
Normally I thought if a column is defined as Date type, then its' the responsibility of tOracleOutput component to convert it in correct format and then write back to DB. Now I realized we have to manually set the output format with a pattern of "yyyy-MM-dd HH:mm:ss", then tOracleOutput component will do the right thing. Actually Date type is only a type of Talend. When they are output to a special DB, only users know which DB is the destination and what format is the best for such DB. I guess the design is originated from this.
So the problem is resolved, thanks very much!

[Anonymous]

Also I think we need a manual for such questions, like a FAQ. I believe many persons would take it for granted that tOracleOutput would be the owner of such format conversion, and other format involved issues.

[Anonymous]

Hi,
Initially i thought the same way as u did, but i used to get the errors later i googled and learnt that we should implicitly convert the data types. Yes it would be great if the converting part is done automatically, but sure talend team is going to implement this functionality.
Thanks and Regards,
Pavan