Oracle Sequences

Sometimes in the middle of the night you will be working on a conversion effort and you will need to populate a field with a sequence of values.  This is easily done using Excel but if you are dynamically loading data you may want to have a more flexible feature.  Oracle sequences have that ability.

Create A Sequence:

CREATE SEQUENCE adjustment_fix START WITH 1001 INCREMENT BY 1 NOCACHE NOCYCLE;

So this sequence will start are 1001 and each additional next value will by greater by 1 — so 1002, 1003, 1004….etc.  I had an oddball situation where I needed an 18 digit number that increased by 10000000. So in my case it was:

CREATE SEQUENCE adjustment_fix START WITH 300000000010000000 INCREMENT BY 10000000 NOCACHE NOCYCLE;

Now that you have the sequence, you need to populate the rows with the sequence.  So I created a temporary table and loaded all the values in the table.  Then I had another field for the sequence number.  I tried to insert the sequence while doing the insert into the staging table of the values I needed staged for the adjustment but it whined and complained.  So I did the insert and then did an update of the rows:

SQL:    UPDATE STAGING_TABLE_4_ADJ SET ADJ_SEQ = adjustment_fix.nextval;

BAM!  Now I have 2200 rows of adjustment data with a unique sequence number on each row so that I can load the staging data tables.