• Post Reply Bookmark Topic Watch Topic
  • New Topic

Oracle - insert auto_seq  RSS feed

 
Ranch Hand
Posts: 247
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

Is there a way to get back in one query the auto inserted number for a query.
i.e.

At the moment I use 2 queries
INSERT INTO Blah (ID) VALUES (my_seq.NEXTVAL)

then:
select my_seq.currval from dual

Thanks
 
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could write a Stored Procedure to do this. It would still really be two SQL statements, but from the calling code's point of view it would only be one call.
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Kris Reid:
Hi

Is there a way to get back in one query the auto inserted number for a query.
i.e.

At the moment I use 2 queries
INSERT INTO Blah (ID) VALUES (my_seq.NEXTVAL)

then:
select my_seq.currval from dual

Thanks



The only way I can think of to do it in one call is with a stored procedure.

However, if your goal is really to avoid the concurrency problems that the above approach has, you can merely shuffle things around a bit:


Each thread will retrieve a unique value from the sequence and return it to your Java code, where you can then do a normal insert.
 
Kris Reid
Ranch Hand
Posts: 247
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks guys!
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!