One Star

[resolved] Oracle sequence while insert.

Hi,
Anybody has an experience to use oracle sequence while inserting. what is the way to get the sequence which is defined in oracle to use with TOS database components?
regards,
sebastianr
1 ACCEPTED SOLUTION

Accepted Solutions
Employee

Re: [resolved] Oracle sequence while insert.

Additional Columns are designed to handle db columns which needs instructions but where there are no datas from the row.
In your component schema, you have EMP_NAME.
Configure additional column like in the picture added.

Regards,
Michaël.
10 REPLIES
Employee

Re: [resolved] Oracle sequence while insert.

Hello,
Use the additional columns table.
Regards,
One Star

Re: [resolved] Oracle sequence while insert.

Dear Mhirt,

Using "Additional Columns" option, how can use oracle sequence? I dont want generate the numbers in TOS itself. I need to get the sequence which is defined in Oracle and increment the value while inserting.
For eg. insert into employee (EMP_SEQ_ID, EMP_NAME) values(EMP_SEQ.NEXTVAL, 'Sebastian');
Here i am using Oracle sequence EMP_SEQ. Same way i want it to be done.
If your reply addressed my query, how can it be achieved with "Additional Columns" option.

Thanks.
Employee

Re: [resolved] Oracle sequence while insert.

Additional Columns are designed to handle db columns which needs instructions but where there are no datas from the row.
In your component schema, you have EMP_NAME.
Configure additional column like in the picture added.

Regards,
Michaël.
One Star

Re: [resolved] Oracle sequence while insert.

Hello,
I have a problem using an oracle sequence to get value for my primary key.
I added an additional column but it doesn't work
there you ca find all screenshot.
The error is in english "sequence number not allowed here"
I hope that you can help me
Thx
One Star

Re: [resolved] Oracle sequence while insert.

OK I've changed the SQL expression in my tOracleOutput additional column to "select hibernate_sequence.nextval from dual" and now a new error seems to appear :"ORA-00936: emissing expression"
Is it better or worst?
One Star

Re: [resolved] Oracle sequence while insert.

I'm really sorry but my issue comes from my sql expression. In fact the right expression is "hibernate_sequence.nextval" and now all works fine
thx
One Star

Re: [resolved] Oracle sequence while insert.

I'm really sorry but my issue comes from my sql expression. In fact the right expression is "hibernate_sequence.nextval" and now all works fine
thx

Hello all,
when I type this, I've got the next error: java.sql.SQLException: ORA-02287: sequence number not allowed here
Please, could you help me.
Thanks in advance,
Gracia.
One Star

Re: [resolved] Oracle sequence while insert.

I'm really sorry but my issue comes from my sql expression. In fact the right expression is "hibernate_sequence.nextval" and now all works fine
thx

Hello all,
when I type this, I've got the next error: java.sql.SQLException: ORA-02287: sequence number not allowed here
Please, could you help me.
Thanks in advance,
Gracia.
I've found the problem: I can't use the sequence over a Key Field.
Thanks,
Gracia.
One Star

Re: [resolved] Oracle sequence while insert.

Hi,
this call of sequence work fine when you use the column name in upper format
One Star

Re: [resolved] Oracle sequence while insert.

So can we not do this with the primary key columns in the table? We are using Talend Data Mapping to map our XSD's to tables in our Oracle database and are trying to find out ways to get the primary keys and foreign keys.
thank you