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)?

Solution 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

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

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