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
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 idsINV00000001
and
INV00000003are inserted in the
invoicestable but the invoice id
INV00000002` 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 NOCYCLE
if 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).