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".
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,
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
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.
This is my screen shot when i run i have that RA-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
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.
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.
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
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.
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
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.
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
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.
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