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";
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.
CREATE OR REPLACE PROCEDURE <<procedure name>> ASwithDECLARE.