ALLINSIGHT

Home of the AlmostImplementedException

Oracle: Multiple call of nextval for sequences

Today we will make a short trip to the world of Oracle. In my current project I have to do a lot with a Oracle database and some days ago I had to programmatically fill a table based on a predefined sequence for primary key. Sound simple, right? This sequence has an increment by 1000 and I had to insert ca. 180,000 rows programmatically. So i need to call nextval for the sequence 180 times. One way is to call nextval each time, after I inserted 1000 rows.

But to make it more comfortable, I will get all nextvals at once. The magic word is “connect by level”. So I will execute the following query:

Now I get 180 times the nextval. 1, 1001, 2001, etc.
The “Connect By Level” functionality is very helpful if you need to get values multiple times.
The following snippet will hopefully make it more clear. I select MAX(ROWNUM) from the table DUAL. At the first call I will get a 1, because there is only one row in the result. But because I virtually call the query 1000 times, the number of rows will raise up to 1000. I save this value in the variable “myIndex” and output it afterwards. The Output will be: “Index: 1000”

Hope you enjoy this snippet. Have a great sunday!

Share :

, , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *