Recover value of sequence in customSQL : insert ..

, ,
1

Hello,

I have this customSQL : insert into ADERTRANSCO values (?,?,SEQUENCE1.nextval) and i want to recover the value of the oracle sequence in the output file.

How do i procced ?

Thank

2

How Iā€™ve done this in the past:

  • Call select service to get SEQUENCE1.nextval
  • Call insert service, setting the appropriate field with the value retrieved

That way you have the value to use for other purposes.

3

Thank for your reply, but i donā€™t want to do this in 2 adapters service but only in 1.

I want to user the returning clause the SQL insert :

RETURNING expr [, expr ]ā€¦INTO data_item [, data_item ]ā€¦

like this

inert into tab(a,b) values (ā€˜xxxā€™,seq.nextval) returning(seq.currval) into yyy

1 Like
4

I know that using one service is desirable. I have not found a way to do so. The 2 services approach worked just fine for me so I didnā€™t pursue any further. Is there an objection to doing 2 services instead of just one other than preference?

I would think using seq.curval is risky because another thread may update it between your nextval and curval calls.

Perhaps you can reference the column that is populated with the seq value in the returning clause. But Iā€™m not even certain the JDBC adapter service (insert or custom) supports the returning clause. Perhaps it does and that will do the trick for you.

5

u can have a procedure which takes in the input does an insert and gives the sequence as the output.

6

What do you mean by ā€˜procedureā€™ ?

A pl/sql procedue ? If you meanā€™s flow service, i donā€™t know how to do put the sequence value in the output.

To reamon : iā€™m agree whith you : itā€™s not possible to using the returning clause of insert wtih a JDBC Adapter.

7

have a db personnel create a procedure, create an adapter service ā€œprocedure with signatureā€ and select the procedure from the dropdown. It will automatically give u the list of inputs and the outputs (seq no in our case).

8

thanks pilaire,
with solution is OK.

But I need to have a list of data in my input in addition to have the sequence in the output.

as TYPE G_TT_lg_CDMONTANT IS TABLE OF varchar2(100) INDEX BY BINARY_INTEGER;

iā€™m donā€™t see them.