Creating a sequence for a varchar2 field in Oracle

Question

I want to create a sequence for this varchar. It would have been easier had it been a number instead of varchar. In that case, I could do

seq_no := seq_no + 1;

But what can I do when I want to store next value in column as A0000002, when the previous value was A0000001 (to increment the number in the next varchar rowby 1)?

Manas

What is the outcome of A9999999 + 1 ? – Casmey

@APC: Your doubt, even though it is valid, my program might never need such huge values. – Manas

Answer 1

This can be done by

to_char(seq_no,'FM0000000')

your example can be done by creating sequence in oracle

create sequence seq_no  start with 1 increment by 1;

then

select 'A'||to_char(seq_no.nextval,'FM0000000') from dual;

Right now i have used in dual ..but place this

'A'||to_char(seq_no.nextval,'FM0000000')

in your required query ..this will create sequence as you mentioned

sqlfiddle

Impresa

Answer 2

Sequences are purely numeric. However, you need a trigger anyway, so simply adapt such trigger to insert the desired prefix:

CREATE OR REPLACE TRIGGER FOO_TRG1
    BEFORE INSERT
    ON FOO
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
BEGIN
    IF :NEW.FOO_ID IS NULL THEN
        SELECT 'A' || TO_CHAR(FOO_SEQ1.NEXTVAL, 'FM0000000') INTO :NEW.FOO_ID FROM DUAL;
    END IF;
END FOO_TRG1;
/
ALTER TRIGGER FOO_TRG1 ENABLE;

Deva

That "WHEN OTHERS THEN RAISE" seems a bit redundant there. – Millman

Answer 3

If you're able I'd actually use a virtual column as defined in the CREATE TABLE syntax. It makes it more easily extensible should the need arise.

Here's a working example.

SQL> create table tmp_test (
  2     id number(7,0) primary key
  3   , col1 number
  4   , seq varchar2(8 char) generated always as (
  5           'A' || to_char(id, 'FM0999999'))
  6      );

Table created.

SQL>
SQL> create sequence tmp_test_seq;

Sequence created.

SQL>
SQL> create or replace trigger tmp_test_trigger
  2    before insert on tmp_test
  3    for each row
  4  begin
  5
  6    :new.id := tmp_test_seq.nextval;
  7  end;
  8  /

Trigger created.

SQL> show errors
No errors.
SQL>
SQL> insert into tmp_test (col1)
  2   values(1);

1 row created.

SQL>
SQL> select * from tmp_test;

        ID       COL1 SEQ
---------- ---------- --------------------------------
         1          1 A0000001

Having said that; you would be better off if you did not do this unless you have an unbelievably pressing business need. There is little point to making life more difficult for yourself by prepending a constant value onto a number. As A will always be A it doesn't matter whether it's there or not.

Potentate

"As A will always be A it doesn't matter whether it's there or not. " Exactly so. This is a pretty pointless question. – Casmey

My first thought was that they were prepending 'A' in order to distinguish new records from old records migrated from a legacy system; and they didn't want to prime the sequence with the maximum id from the migrated data. Just my 2c :) – Cheddite

Answer 4

If the format is always a letter followed by 7 digits you can do:

sequence = lpad(substr(sequence,2,7)+1,7,'0')

Selfevident