how auto generate number start from specific number the first 4 # include the current year in oracle 11g

Question

i have table which have id when iwnna to insert new record its by default generate new # increment by 1 how can i do it please the number is begin with year and serial for ex; 20130001,20130002,20130003 and so on , when the year is end then will start 20140001,20140002,20140003

Answer 1

Putting aside the question why you would want to do this.

The most straightforward approach is to create an Oracle SEQUENCE object, starting at the value you want to start with, increment of 1. As an example:

 CREATE SEQUENCE myseq START WITH 20130001 INCREMENT BY 1 NOCACHE ;

To make use of the sequence object to supply a value for a table on an INSERT, create a BEFORE INSERT trigger

CREATE TRIGGER mytable_trg_bi 
BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
   IF NEW.mycol IS NULL THEN
      SELECT myseq.NEXTVAL FROM DUAL INTO NEW.mycol;
   END IF;
END;
/

(It's been a while since I've worked with the Oracle syntax; some of the keywords might not be in the right order. But this is the normal pattern for assigning unique, system generated values to a column on an INSERT.

That part is easy.

The trickier part is getting the sequence to "jump" to a specific value.

There's a couple of approaches to doing that. One would be drop the sequence object and re-create a new object, with the same name, with a new starting value. But that's not very elegant, and fairly disruptive.

Another approach is to modify the increment value, select nextval to make it jump, and then set the increment back to 1.

As a rough outline of what that might look like:

DECLARE
  ln_val NUMBER;
BEGIN
  -- retrieve next value from sequence
  SELECT myseq.NEXTVAL FROM DUAL INTO ln_val;

  -- set increment so next call to nextval will "jump"
  EXECUTE IMMEDIATE
    'ALTER SEQUENCE myseq INCREMENT BY '|| 20140001 - 2 - ln_val ||' NOCACHE';

  -- this should return us 20140000
  SELECT myseq.NEXTVAL FROM DUAL INTO ln_val;

  -- reset increment back to 1
  EXECUTE IMMEDIATE
    'ALTER SEQUENCE myseq INCREMENT BY 1';

END;
/

Note that this approach of setting/resetting the current value of the sequence is subject to a race condition, if another session is pulling values from the SEQUENCE at the same time.