I?m working on a job which simply gets datas from 3 Oracle tables, and put transformed records in another Oracle Table. Since the Input and Output are on the same db, I preferred to use tELT components instead of the classics ETL components. INSERT, works perfectly with high performance (1?000?000 records in less than 40-50 seconds). In Order to do the insert, I previously run a job which truncate the table and then call the job which inserts records. Of course, since we have W_NUMERO_PRATICA as Primary Key, If I try to run the insert job, obviously gives an error because you cannot duplicate records The problem comes out when I try the Update/Insert. As I see on Talend?s manual, in order to do the update/insert you must use MERGE options on tELTOracleOutput ( see tELTOracleOutput1,2,3.jpg). I can?t understand problem?and how to fix it? ORA-00904 ? ?STRING? INVALID IDENTIFIER on oracle manual, have this definition : ?Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word.? Thanks For your Help
You have to include the column W_NUMERO_PRATICA also in the update section. If you do not do this, the ELT component will not add SQL code for it and this is the reason because the implicit created view does not contains this field and the update statement using this view does not find this column. For your second exception: you should find the check the statement you use to write and there you find the word STRING as column name - which is not allowed, because it is a protected keyword in Oracle (and most known other databases). To get more help, please provide more settings of the tOracleRow component.
Hi, Thank you for your reply and for your help.. i just tried what you suggest (including that column in update) but error still.. about oracle exception, is the one that talend provide while im trying to update/insert...but as you can see there is no tRow and no column named 'STRING' i don't know what to do