I want to start putting up snippets on occasion, often so I can find the code myself, but also for the world. So here’s the first in the series.

Promotion of databases isn’t a big deal when overwriting the whole thing, except for the permission specifics and a few other details. Unfortunately promotion is usually piecemeal. Going from development to production, refreshing portions of a development environment, or providing a major update to a client site involves a tangled one-time SQL script. One complication is that bulk loading often ignores sequences, leaving the Oracle sequence values out of date.

Unlike MySql where auto incrementing columns automatically adjust when new records are inserted with a hard-coded ID, Oracle uses an external Sequence object. I find there is often a need right after doing the promotion to reset the sequence to the tables’ max ID +1. There are lots of solutions on the web for resetting a sequence to 1 or 0 but not much on this particular practical application. The procedure below makes this easy to fix, just add the calls to the procedure at the end of the promotion script for each table.

call reset_sequence('seq_mytable',
 'select (max(mytable_id)+1) start_num from mytable');

Just substitute the name of your sequence, the name of the ID field, and the name of the table.

If your sequences are named based on your tables and IDs are named consistently then you can even write a script to generate the reset_sequence calls based on an export of the table names or from the sequence names. I do a partial auto-build on my projects that use this procedure.

CREATE OR REPLACE PROCEDURE reset_sequence (
seq_name IN VARCHAR2, startvalue_sql IN VARCHAR2) AS

cval   INTEGER;
inc_by VARCHAR2(25);
startvalue INTEGER;

BEGIN
-- Resets a sequence. Used when refreshing an environment or promoting changes.
--
-- Some example calls:
-- call reset_sequence('seq_mytable', 'select (max(mytable_id)+1) start_num from mytable');
-- call reset_sequence('seq_mytable', 'select 1 from dual');

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' ||seq_name||' MINVALUE 0';

  EXECUTE IMMEDIATE startvalue_sql
  INTO startvalue;
  IF startvalue is null THEN
     startvalue := 1;
  END IF;

  EXECUTE IMMEDIATE 'SELECT ' || seq_name ||'.NEXTVAL FROM dual' INTO cval;

  cval := cval - startvalue + 1;
  IF cval < 0 THEN
    inc_by := ' INCREMENT BY ';
    cval:= ABS(cval);
  ELSE
    inc_by := ' INCREMENT BY -';
  END IF;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || inc_by || cval;

  EXECUTE IMMEDIATE 'SELECT ' ||seq_name ||'.NEXTVAL FROM dual' INTO cval;

  EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' INCREMENT BY 1';

END reset_sequence;

I’ve found this is also useful when a rogue developer manually adds a bunch of rows without using a sequence, causing all the users to get error messages. Fun.

Also: I found a nice site to do basic reformatting of code so it looks good in a blog. I’d like to have color/bold/italic highlighting based on the language so I may switch over to the Google code prettify but PL/SQL isn’t explicitly listed as a supported language; I’ll try it next time.