tELTOracle Components, Update/ Insert Problems.

tELTOracle Components, Update/ Insert Problems.

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
Seventeen Stars

Re: tELTOracle Components, Update/ Insert Problems.

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.

Re: tELTOracle Components, Update/ Insert Problems.

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 Smiley Sad

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch