CODEWITHSUNDEEP
sqloracle-databaseoracle-apexsequence
YOUCEF sami
YOUCEF sami

Reputation: 29

Creating specific value sequence in oracle

I need to create an oracle sequence with specific values

FOUR0001, FOUR0002, FOUR0003.....

the increment must be in order.

Upvotes: 0

Views: 221

Answers (3)

YOUCEF sami
YOUCEF sami

Reputation: 29

I've created a sequence starting with 210 ( because i already ahve 209 records) then created the trigger bellow 

CREATE OR REPLACE trigger BIU_FRS  
  before insert or update on FOURNISSEUR             
  for each row 
begin  
  if :NEW.FRS_NUM is null then
    select ('FOUR'||lpad(four_seq.nextval,4,'0')) into :NEW.FRS_NUM from dual;
  end if;
  if inserting then
        :new.created    := localtimestamp;
        :new.created_by := nvl(wwv_flow.g_user,user);
    end if;
    :new.updated    := localtimestamp;
    :new.updated_by := nvl(wwv_flow.g_user,user);
end;

thank you @Kaushik Nayak and @Diego Souza

Upvotes: 0

Diego Souza
Diego Souza

Reputation: 548

You could create a sequence: 

create sequence SEQ_NAME ...;

and then to create a trigger to feed the field automatically:

CREATE OR REPLACE TRIGGER INS_TABLENAME
  before insert on TABLENAME
  for each row

BEGIN

  if :new.FIELD_NAME is null then
    :new.FIELD_NAME := 'FOUR'||lpad(SEQ_NAME.nextval,4,'0');
  end if;

END;

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

First create a simple sequence

create sequence my_seq ; --start with 1 increment by 1

In your application code / table where you use the sequence to store the data, use something like this

INSERT INTO yourtab (col1) VALUES( 'FOUR'||lpad(my_seq.nextval,4,'0'));

Upvotes: 2

Related Questions