Create an Oracle sequence that begins with an alphanumeric

Question

I want to create a sequence to start with a character invand increment by 1

Values

INV01
INV02
INV03  
etc...

CREATE SEQUENCE invoice_nun
START WITH "INV"
INCREMENT BY 1

Answer 1

Only integer meaningful sequences can be created.

Therefore, the statement should be:

CREATE SEQUENCE invoice_nun
  START WITH 1
  INCREMENT BY 1;

You can convert the selected value to a string and add the appropriate prefix.

select 'INV'||to_char(invoice_nun.nextval,'FM09999999') 
  from dual;

You can create a function to simulate a sequence that returns the corresponding string values

create or replace function next_invoice_nun return varchar2
  as
  begin
  return('INV'||to_char(invoice_nun.nextval,'FM09999999') );
  end;
/ 

now you can do

select next_invoice_nun 
  from dual;

, , . SQL..

CREATE SEQUENCE invoice_nun
  CACHE 20
  NOORDER
  START WITH 1
  INCREMENT BY 1;

:

1) , . , :

-- fetch invoice_id INV00000001
insert into invoices(invoice_id,...) values (next_invoice_nun,...);   
commit;
-- fetch invoice_id INV00000002
insert into invoices(invoice_id,...) values (next_invoice_nun,...);
rollback;
-- fetch invoice_id INV00000003
insert into invoices(invoice_id,...) values (next_invoice_nun,...);
commit;

INV00000001 and INV00000003 are inserted in the - table but the invoice id INV00000002` , , ,

2) , , . , 20. , 20 , . alter native - NOCYCLE, , .

3) RAC, . , , id INV00000021, id INV00000001, , . , 20 , 20 . , 20 . ORDER, ,

, 2) 3) , 2).

Answer 2

Oracle , , . 'INV' || TO_CHAR(invoice_num.NEXTVAL,'fm00')

, , , .