topic Re: [resolved] Always get ora-00984 error when executing in Talend Studio

[resolved] Always get ora-00984 error when executing

Anonymous — Thu, 12 Apr 2012 13:31:43 GMT

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?

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Thu, 12 Apr 2012 13:35:27 GMT

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

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Fri, 13 Apr 2012 02:55:24 GMT

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

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Fri, 13 Apr 2012 03:38:44 GMT

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

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Fri, 13 Apr 2012 05:10:26 GMT

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!

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Fri, 13 Apr 2012 06:32:54 GMT

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

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Fri, 13 Apr 2012 07:05:32 GMT

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

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Fri, 13 Apr 2012 07:29:41 GMT

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!

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Fri, 13 Apr 2012 07:33:38 GMT

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.

Re: [resolved] Always get ora-00984 error when executing

Anonymous — Fri, 13 Apr 2012 08:02:44 GMT

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