Problem with Insert

, ,
1

Hi, I have a problem, My problem is know the value of the field ID (my field ID to create with one sequence of Oracle) of the record reciently inserted in the table, for example: insert one record then i want to know the value of ID quickly beacuse i need the value for manage in other service.

Know the value of the field ID of my record reciently inserted in my table of Oracle.

Thanks :frowning:

2

Can you elaborate your problem?? Unable to understand…

–Shaik

3

Since SQL insert statements do not return any values other than a status code, you will need to either:

  • write a stored procedure to perform the insert and return the unique ID generated from the sequence. Call the stored proc from a adapter service (stored procedure or stored procedure with signature template)
  • follow your insert with a query to find the newly inserted row using some other value or pseudo key and then get the ID from that row

M

4

Hi M,

I tried to do what you said, but system does not like the “;” I used to separate the two sql statements, I wonder what separator could be used to multiple sql statements.

5

The custom SQL template doesn’t support multiple statements, I believe (but may be wrong).

In the past I’ve done this with 2 separate adapter services:

  • Call to get the next sequence number.
  • Assign that seq to the record being inserted, then call insert.
6

Hi reamon,

one sql is enough
INSERT INTO WEB_METHOD_TEST(field_n FIELD_T, my_id) VALUES (?,CURRENT_TIMESTAMP,test_seq.nextval)

Thanks anyway.

7

How does that return the value of test_seq.nextval? (The question posed by the original poster.)

If we’re just exploring the ways to set a column using a sequence object, here are 3:

  • Use custom SQL template as adam319 notes above.
  • Use insert SQL template and specify seq.nextval as the value.
  • Define a trigger on the DB table to set the column to seq.netxtval.

But if the caller needs to know the value of the sequence then one of the other suggestions in previous posts would need to be used.

8

Sorry, I made a wrong statement in my last post.

I tried storedProcedureWithSignature and it works, wonder if you guys know which is more efficient compare to this method following:

  1. service A return the next value of the oracle sequence, e.g. 1111,

  2. service B use the 1111 as primary key to insert the row in table,

  3. put the value of 1111 as an output entry so it can be accessed by user.

9

Unless the volume of data is quite high, I wouldn’t worry about which is more efficient.