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.