autoIncrement of oracle sequence with talend

One Star

autoIncrement of oracle sequence with talend

I have a quastion can i generate the next value of an oracle sequence ,  to insert into a primary key of a table ,with talend in the composant troaclerow , or what should i do 

thanks for help 
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

Yes you can. Simply do not set the value of the primary key field NOT in the schema - also remove it from the schema what probably cause the schema to be build-in, instead use the advanced settings of the tOracleOutput and set here an additional field (your primary key field) and set as sql expression "your_sequence.nextval".
One Star

Re: autoIncrement of oracle sequence with talend

Thanks Jlolling for your reply
i willl try this, juste a question can i change format date of TalendDate.getCurrentDate(); to this format dd/mm/yyyy HH:mi:ss
Thanks,
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

Thementioned function returns a Date object. A pattern is only needed if a Data have to be converted from or to a textual representation.
One Star

Re: autoIncrement of oracle sequence with talend

Thanks jlolling for your help ,
It works Thanks,
just a question, can i generate the sequence with toraclerow ?
or it should be a toracleoutput and my user oracle should have a privilége to select the sequence 
Thanks for your help,
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

Only the row components can execute such statements like create sequence...
One Star

Re: autoIncrement of oracle sequence with talend

I have my sequence that autoincrement my primary key of the table, 
juste question how to do in the toraclerow that generate the next value of my sequence ?
One Star

Re: autoIncrement of oracle sequence with talend

hi,
i have in the table other column i juste want to auto increment the primary key 
I create a sequence with name : kpi.id_seq_http_trace;
when I try this query in toraclerow :
"INSERT INTO  KPI.HTTP_TRACE(ID_HTTP_TRACE)
 values ("   kpi.id_seq_http_trace.NEXTVAL    
+")"
it does'nt work 
and when i try with these components: it does'nt work also :
tfixedFlowInput---tmap---toracleoutput 
and ithe advancedSettings of toracleoutput I met 
the name: "seq_value"
SQL expression: "KPI.ID_SEQ_HTTP_TRACE.NEXTVAL"
Position : Before
and finally the reference column : id_http_trace
but it does'nt work because i have an error 
Thanks for your great help 
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

To create a sequence is only necessary once and not all the time a job runs. Thats because I usually do this in separate SQL scripts which creates the tables and all the database objects needed.
You have obviously misunderstood me. You do not need to use a explicit insert statement in the tOracleRow component. To insert a key based on the sequence it is quite enough what I told you for the tOracleOuput component - I though you got it.
You question was "generate a sequence" and I probably misunderstood you: generate a new value of the sequence is done with the method nextval of the sequence, nothing else is necessary. 
Every time you call my_sequence.nextval the sequence generates a new value.
If you want create (not generate) a sequence, do it once in a SQL script.
One Star

Re: autoIncrement of oracle sequence with talend

This is my screen shot : i make a job with these component 
but it does'nt work 
can you explain to me how to generate a next val of a sequence in the toracleoutpout and in the toraclerow 

 
Thanks,
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

Tell what does not work. Please post the error message.
One Star

Re: autoIncrement of oracle sequence with talend

This is my screen shot  
 
 
when i run i have that Smiley SurprisedRA-00957: duplicate column name
what should i put in the advanced Settings of the toraceOutPut in the additional columns to generates the next val of the sequence
Thanks jlolling
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

I bet you have forget to remove the ID_HTTP_TRACE in the schema - as I suggested in my first post.
Of course you will get this error when you simply add this column the additional columns without removing it from the schema.
One Star

Re: autoIncrement of oracle sequence with talend

If I delete the id of the table from the schema in the component , I can't add it in the refrenced Column 
how to do it please 
Thanks
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

What you mean with "referenced Column" ?
In your first picture in the post#12 you have added this column name as a String with the correct SQL expression - why this does not work anymore?
You have done everything right, only you forgot to delete the column from the schema.
One Star

Re: autoIncrement of oracle sequence with talend

in the additional column i should put the refrenced column 
and when i delete the id of the table from the schema it propose for me in selected item the other column of the table 

how to do please 
Thanks
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

Aahh sorry, OK this reference column is actual nothing else than the column where the new column have to set before or after in the list of columns. You do not have to take care on this. It doesn't matter on which position your additional ID column is located in the generated insert statement.
One Star

Re: autoIncrement of oracle sequence with talend

When i run i have this error :
ORA-01400: cannot insert NULL into ("KPI"."HTTP_TRACE"."ID_HTTP_TRACE")
ORA-01400: cannot insert NULL into ("KPI"."HTTP_TRACE"."ID_HTTP_TRACE")
also i put in the SQL expression : "KPI.ID_SEQ_HTTP_TRACE.NEXTVAL"
Then i would insert the next value of the sequence in the column Id of the table 
help please 
Thanks
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

Please post also the schema here as screenshot and again the advanced settings. There must something wrong here. The method I explained to you is very common for Talend.
One Star

Re: autoIncrement of oracle sequence with talend

the schema without after i delete the id in the main
 
and in the additional colmun i should put the name , the sql expression , the position and the refrence column 
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

You have changed your component configuration to be wrong. 
You are attempt to write the value from the sequence in a column called seq_value ? I have no idea why do you did that.
Please take a look to the picture in your post#16 here is everything OK!
The reference column does not matter, please ignore it.
One Star

Re: autoIncrement of oracle sequence with talend

I change the name of the column name becaue the primary key of my table has the name : ID_HTTP_TRACE
so i put in the additional column the name of the column that should be the primary key 
and in the refrenced column the same 
what's wrong with my configuration please 
Seventeen Stars

Re: autoIncrement of oracle sequence with talend

Obviously I am unable to explain it to you. 
I assume: You have a table with the primary key column ID_HTTP_TRACE which you want to fill with the value of a sequence - that it what you explained so far. Now you tell me you want to use a new column for the primary key called seq_values ok but the primary key column will not filled this way -> you get the error.
OK: If you want to fill the column ID_HTTP_TRACE with the value from the sequence - hell - than do it by adding exactly this column as additional column in the component. You have to use it as additional column because only this way you can bring the sequence into the game for this column.
Go to the advanced setting of tOracleOutput and set as name "ID_HTTP_TRACE" and keep the SQL expression and ignore what ever you can read as reference Column and damned this will work.
One Star

Re: autoIncrement of oracle sequence with talend

Mr jlolling 
in the script table the primary key is id_http_trace , forget the seq_value is just i try to understant the componenet,
so if i put in the additional column 
the name of the column the same the refrenced column , i have this error : ORA-00957: duplicate column name 
and if i put what ever in the refrenced column i have the error that it can't be null 
how can do it , or how can i do it with the toracleRow 
Thanks