## Oracle Sequences Create & Use

### 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.