File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Oracle sequence.nextval Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle sequence.nextval" Watch "Oracle sequence.nextval" New topic

Oracle sequence.nextval

Ranch Hand

Joined: May 02, 2001
Posts: 48
I need to be able to get 1000 sequence.netval values from an Oracle database. Right now, the Java code loops through 1000 times and executes (by calling statement.executeQuery()) the following PreparedStatement 1000 times.

This takes about 1 minute and 15 seconds. I need to speed this up.
Is there a way I can send one statement to the database and get back 1000 values of nextval?
If I can't do that, any suggestion on how to speed it up any other ways?
This has to be threadsafe.
Thanks for any help.
sandy gupta
Ranch Hand

Joined: Jan 30, 2001
Posts: 228
Hi April,
Thats really very easy...
Instead of going to the DB every time, use increment parameter of the sequence to set the increment value to 1000. Then use the SEQ.nextval that u got and in the javacode use ++/+ 1 to increment in the loop a 1000 times.
I hope this will help


Ranch Hand

Joined: May 02, 2001
Posts: 48
Thanks, Sahil,
But I don't create the sequence. It's a pre-existing sequence that I just get the next number from. The increment value is set to one.
And actually, for clarification, I need to be able to get any number of nextval values from 2 to 4000. And it will vary from user to user and with each execution. I just used 1000 as an example.
I thought of doing something like the following pseudocode?

But (even if I could do this and I don't know I can), the problem with that would be that it wouldn't be thread-safe. If another thread grabbed sequence.nextval before it was set, there would be a conflict.
Thanks for making me think.
Mark Spritzler

Joined: Feb 05, 2001
Posts: 17276

use increment parameter of the sequence to set the increment value to 1000.

April I think what he was saying is that, you get the Nextval, then you use a special function that will automatically add 1000 to the sequence. That way you just told the sequence give me the next val and increment yourself by 1000.
You then loop through 1000 times to add 1 to the original number given to get all those values.
For instance
select mySeq.nextval from dual
something like
select mySeq.increment(1000) from dual
(mySeq.increment(1000) This is probably not the exact syntax, but it should be something just like it.)
The first query returns 101.
Then you loop 1000 times adding 1 to 101 till you finish at where the sequence is now at already.
I am not sure about thread safetyness here, However you can expect quick results.

Perfect World Programming, LLC - iOS Apps
How to Ask Questions the Smart Way FAQ
I agree. Here's the link:
subject: Oracle sequence.nextval
It's not a secret anymore!