Creating Oracle sequence that starts with alphanumeric

Question

I want to create sequence to start with character inv and increment by 1

The values to be

INV01
INV02
INV03  
etc...

CREATE SEQUENCE invoice_nun
START WITH "INV"
INCREMENT BY 1

Answer 1

Only integer valued sequences can be created.

So the statement must be:

CREATE SEQUENCE invoice_nun
  START WITH 1
  INCREMENT BY 1;

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

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

You can create a function to simulate a sequence returning appropriate string values

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

you can now do

select next_invoice_nun 
  from dual;

The sequence as defined above uses some default values. This is documented in the Database SQL Language Reference. It is equivalent to the following statement

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

You should be aware of the following:

1) If a transaction fetches a sequence value and rolls back then the sequence value is lost. So if you do the following:

-- 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;

the invoice idsINV00000001andINV00000003are inserted in theinvoicestable but the invoice idINV00000002` is lost because the statement that fetched it was rolled back

2) If an instance crashes all sequences that are in the cache of the instance are lost. In your example the default value for cache is used which is 20. So if the instances crashes at most 20 sequence values can be be get lost. an alter native is to use the keyword NOCYCLEif you create the sequence but this will bring performance penalties.

3) If you are on a RAC system sequence number does not represent the order of fetching the statement. So it is possible that the first statement gets the id INV00000021 and the second statement gets the id INV00000001 if the second statement is executed on a different instance than the first statement. This is because the instance fetched the first 20 sequences numbers in its cache and the other instance fetched the second 20 sequences numbers in its cache. The first statement is executed on the instance that fetched the second 20 sequence numbers. You can use the ORDER keyword to avoid this but this again will bring performance penalties

So one can avoid 2) and 3) for the price of performance penalties but there is no way to avoid 2).

Answer 2

Oracle only provides numeric sequences, but you can construct your identifiers by converting to a string, e.g. 'INV' || TO_CHAR(invoice_num.NEXTVAL,'fm00')

Mind you, you might need more than 2 digits, depending on how many records you're expecting.