topic Re: [resolved] tOracle output - sequence insert in Talend Studio

[resolved] tOracle output - sequence insert

Anonymous — Sat, 16 Nov 2024 13:35:23 GMT

Hi,
I am trying to insert a bunch of columns into an Oracle table from tMap, along with an oracle sequence.
I have the following process:
tFileInputDelimited connected to tMap component connected to tOracleOutput.
In tMap, I mapped out all the columns from the delimited file.
In tOracleOutput, I went into Advanced Settings, and defined an Oracle sequence giving it a name and an sql expression:
vrtx_lexnex_ap_invoices_int_s.nextval, and specified the refence column that I want the sequence to be inserted into.
I am getting an error on tOracleOutput (Component Error).
Do I need to do something in tMap, so that tOutputOracle knows about the sequence name.
Please let me know,
Thanks,
Boris

Re: [resolved] tOracle output - sequence insert

Anonymous — Thu, 28 Jan 2010 04:05:10 GMT

Hello
You can see the example on 1488.
Best regards
shong

Re: [resolved] tOracle output - sequence insert

Anonymous — Thu, 28 Jan 2010 13:03:44 GMT

Hi Shong,
I followed the example and still getting an error.
Do I need to define this column in tMap somehow? Or is the only thing I need to do is define it in additional columns in tOracleOutput?
In additional columns, after "Before", whic column do I put in as reference column?
I am stuck in my development project, and your help would be greatly appreciated.
Thanks,
Boris

Re: [resolved] tOracle output - sequence insert

Anonymous — Thu, 28 Jan 2010 14:09:31 GMT

Hello

I am stuck in my development project, and your help would be greatly appreciated.

What's your error message? You don't show me what's wrong, it is a simple question but I can' t help you without any info about your job!!! I think you should upload some screenshots of your job, so I could know what are doing and what you are doing wrong,
Best regards
shong

Re: [resolved] tOracle output - sequence insert

Anonymous — Thu, 28 Jan 2010 14:12:53 GMT

Just to clarify here's exactly what I did in tOracleOutput:
I have a column invoice_interface_id, and i need to insert an Oracle sequence into it.
In advanced columns I specify:
name: "invoice_interface_id"
SQL expression:"vrtx.vrtx_lexnex_ap_invoices_int_s.nextval"
Position: Before
Reference column: invoice_interface_id
in tMap, I created a column: invoice_interface_id, and left Expression as blank.
If I remove invoice_interface_id column from tMap, in tOracleOutput, Reference_column invoice_interface_id disappears from the option window.
What do I need to do?

Re: [resolved] tOracle output - sequence insert

Anonymous — Thu, 28 Jan 2010 14:16:03 GMT

The error I get is:
ORA-00957: duplicate column name

Re: [resolved] tOracle output - sequence insert

Anonymous — Thu, 28 Jan 2010 14:18:22 GMT

Hello
What's your table structure in your oracle db?
Best regards
shong

Re: [resolved] tOracle output - sequence insert

Anonymous — Thu, 28 Jan 2010 14:26:28 GMT

You don't need define invoice_interface_id column on tMap, remove it.
In advanced columns specify:
name: "invoice_interface_id"
SQL expression:"vrtx.vrtx_lexnex_ap_invoices_int_s.nextval"
Position: Before
Reference column: the column after invoice_interface_id in your real table(for example, there are three columns in your table:
invoice_interface_id,name,age
the reference column is name here)

Re: [resolved] tOracle output - sequence insert

Anonymous — Thu, 28 Jan 2010 14:38:42 GMT

Hi Shong,
I found the issue. I removed the column from tMap, and renamed everything correctly in tOracleOutput.
Thanks for helping out.
Boris

Re: [resolved] tOracle output - sequence insert

_AnonymousUser — Tue, 30 Aug 2011 18:10:50 GMT

You don't need define invoice_interface_id column on tMap, remove it.
In advanced columns specify:
name: "invoice_interface_id"
SQL expression:"vrtx.vrtx_lexnex_ap_invoices_int_s.nextval"
Position: Before
Reference column: the column after invoice_interface_id in your real table(for example, there are three columns in your table:
invoice_interface_id,name,age
the reference column is name here)

If i select the toracleoutput action on data to Insert or Update, it throws an error stating it requires Key. If i want to do insert or update how do i do it.

Re: [resolved] tOracle output - sequence insert

Anonymous — Wed, 31 Aug 2011 07:35:19 GMT

If i select the toracleoutput action on data to Insert or Update, it throws an error stating it requires Key. If i want to do insert or update how do i do it.

For update action, you need to set one column as key on the schema of tOracleOutput.
Best regards
Shong

Re: [resolved] tOracle output - sequence insert

Anonymous — Tue, 30 Jul 2019 22:08:10 GMT

For update action, you need to set one column as key on the schema of tOracleOutput.
Best regards
Shong

Hi Shong,

I have a question related to this topic and need the answer.
If my target table uses the sequence numbers generated by Oracle Sequence as a primary key, how to accomplish insert/update on tOracleOutput?

Did you mean I should use an alternate key on the schema of tOracleOutput to do insert/update?

Thanks!