APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn

 

Oracle SQL

Print
13
Mar

Reset sequence values to align with table

Written by Richard Weug. Posted in Oracle SQL

Migrating data between environments sometimes requires the need to update the sequence next value. Here's a pice of code that might help when you need to alter certain sequence values.

 
I can't remember where I adopted this code, but I've had it for a while now and I've improved it a little.
 
create or replace procedure reset_seq
  (p_seq_name  IN VARCHAR2
  ,p_new_value IN NUMBER DEFAULT NULL ) IS
  l_val number;
begin
  execute immediate
  'select ' || p_seq_name || '.nextval from dual' INTO l_val;
 
  l_val := -l_val+COALESCE(p_new_value,0);
 
  --debug( 'alter sequence ' || p_seq_name || ' increment by ' || l_val ||' minvalue 0');
  execute immediate
  'alter sequence ' || p_seq_name || ' increment by ' || l_val || ' minvalue 0';
 
  execute immediate
  'select ' || p_seq_name || '.nextval from dual' INTO l_val;
 
  execute immediate
  'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/
 
So you can reset a sequence manually using
exec reset_seq('my_seq', 117)
Take it a little further and you can make it dependent on the current value in your table.
 
declare
 l_id number;
begin
  select max(my_id)+1 -- replace with relevant pk column
  into l_id
  from my_table; -- just replace with relevant table
 
  reset_seq('my_seq', l_id); -- replace relevant sequence name
end;
/
 
To take this even further to treat an entire schema, you could do something like this.
 
 
declare
 v_id number;
begin
  FOR r_rec IN (
    select table_name
           -- mapping sequence to table caught me out at first, but luckily we had a good standard in our table comment descriptions.
          ,(select substr(comments,14,4) from all_tab_comments c where c.table_name = t.table_name and t.owner=c.owner) seq
    from all_tables t
    where owner = 'SAGE'
    order by table_name
  ) LOOP
 
    execute immediate 'select max(id)+1 from '||r_rec.table_name into v_id;
    dbms_output.put_line(r_rec.table_name||' id:'||v_id||'; seq:'||r_rec.seq||'_id_seq');
 
    reset_seq(r_rec.seq||'_id_seq', v_id); 
  end loop;
end;
/
 
Hopefully it might be useful for you one day.
 
Scott

Original article : http://www.grassroots-oracle.com/2013/03/reset-sequence-values-to-align-with.html