Creating a sequence for a varchar2 field in Oracle

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

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

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 errorsNo 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.