How to create a sequence and advance it without PL/SQL

Liquibase Development
1

Hello people!

We have been working with Liquibase in Oracle for a few months now. Usually it’s simple stuff that we already have a handle on but recently we’ve run into a little problem trying to get off the beaten path.
We are trying to create a sequence and then advance it to the maximum value of a certain table. We wanted to avoid using PL/SQL so we are trying to do something like this:

--liquibase formatted sql

--changeset author:1
CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1;

--changeset author:2
SELECT my_sequence.NEXTVAL FROM dual CONNECT BY LEVEL <= (SELECT MAX(field) FROM my_table);

We know that this approach is not the most optimal in terms of performance but we don’t expect the number of rows returned to be very high either. The sql works correctly but in the context of liquibase we observe that the sequence only advances 10 positions. Is there any limitation that we have not seen?,would it be better to use PL/SQL?

Thank you very much in advance for your comments.

Best regards

2

I’d recommend using a PL/SQL anonymous block to do this.

3

Thank you for your response. In the end that is what I have done.

1 Like
4

You don’t need to use nextval from the sequence to achieve this.

SELECT my_sequence.NEXTVAL FROM dual CONNECT BY LEVEL <= (SELECT MAX(field) FROM my_table);

could be replaced by the following plsql statement. It gives the same performance whether max(field) is 1000 or 10 millions.

This syntax is supported at least by oracle 12, not sure about older oracle versions.

declare
 maxval number;
begin
  SELECT MAX(field) into maxval FROM my_table ;
  execute immediate 'alter sequence my_sequence restart start with ' || maxval;
end;
2 Likes
5

Thanks for the reply Helene. That is precisely what we have done.