Oracle update sequence value

Question

Using Oracle 11, and using a query/queries (no stored procedure) I want to to update my sequencers value to:

 biggest primary key in table + 1

This is what I have but couldnt combine these two:

select max("ID") from  ACCOUNTS;    

ALTER SEQUENCE SEQ_ACCOUNTS INCREMENT BY "how to use here the max + 1";

You can't do this in a single query. You could execute a series of SQL statements. Or you could put that series of SQL statements in a stored procedure or anonymous PL/SQL block. — Justin Cave, Mar 28 '14 at 15:12
@Justin Cave it does not has to be single query, I just want it without a stored procedure — Spring, Mar 28 '14 at 15:13
Is an anonymous PL/SQL block acceptable? If so, just take the stored procedure and replace the `CREATE OR REPLACE PROCEDURE <> AS` with `DECLARE`. — Justin Cave, Mar 28 '14 at 15:14
@Justin Cave without using stored procedure is it that difficult to use a value/varaible I want in my query? it thought it is very simple with some variable assigmnets — Spring, Mar 28 '14 at 15:16
As I said, if an anonymous PL/SQL block is acceptable, you just need to make a trivial modification to the stored procedure to be able to use PL/SQL variables. — Justin Cave, Mar 28 '14 at 15:18
@Justin Cave I dont know what is a anonymous PL/SQL block neither how to write a oracle sql procedure — Spring, Mar 28 '14 at 15:18

score 3 · Accepted Answer · edited May 23 '17 at 10:31

An example of a stored procedure that resets a sequence value can be found in another StackOverflow thread. It sounds like an anonymous PL/SQL block would be acceptable to you just not a stored procedure. In that case, you can make some minor modifications...

declare
  l_current_max number;
  l_current_seq number;
  l_tmp         number;
begin
  select max( id )
    into l_current_max
    from accounts;

  select seq_accounts.nextval
    into l_current_seq
    from dual;

  -- Set the nextval of the sequence to 0
  execute immediate
    'alter sequence seq_accounts increment by -' || l_current_seq || ' minvalue 0';

  -- Fetch a value of 0
  execute immediate
    'select seq_accounts.nextval from dual' 
    into l_tmp;

  -- Set the nextval of the sequence to the current max
  execute immediate
    'alter sequence seq_accounts increment by ' || l_current_max || ' minvalue 0';

  -- Fetch the current max
  execute immediate
    'select seq_accounts.nextval from dual' 
    into l_tmp;

  -- Set the nextval of the sequence to the current max + 1
  execute immediate
    'alter sequence seq_accounts increment by 1 minvalue 0';
end;

You can do the same thing with a single step rather than setting the sequence to 0 and then to the current max in separate steps but I find it a bit clearer to do it this way.

@Spring There is no function to set the value of a sequence, so you have to do it in a roundabout way where you change the step size for the sequence. — Klas Lindbäck, Mar 28 '14 at 15:51

Oracle update sequence value

1 Answers1