[SOLVED] How to create alphanumeric sequence using date and sequence number

How to create alphanumeric sequence using date and sequence number

I want to create an alphanumeric sequence in oracle. Table name is rel_details it consists of four columns.

rel_id
rel_name
rel_modified_date
rel_desc

In rel_id i want to generate ID like REL230420151001

REL is a string , 23042015 is today's date, 1001 is a starting number.

How to create this type sequence.

Solution

If you are on 12c, then here is one way using IDENTITY column and VIRTUAL column.

Identity column was introduced in version 12c, and virtual column was introduced in version 11g.

SQL> CREATE TABLE t
  2            (
  3              ID NUMBER GENERATED ALWAYS AS IDENTITY
  4              START WITH 1000 INCREMENT BY 1,
  5              text VARCHAR2(50),
  6              dt DATE DEFAULT SYSDATE,
  7              my_text varchar2(1000) GENERATED ALWAYS AS (text||to_char(dt, 'DDMMYYYY')||ID) VIRTUAL
  8            );

Table created.

SQL>
SQL> INSERT INTO t(text) VALUES ('REL');

1 row created.

SQL>
SQL> SELECT text, my_text FROM t;

TEXT  MY_TEXT
----- ------------------------------
REL   REL230420151000

SQL>

I created identity column to start with 1000, you could customize the way you want.

There is one small trick about the VIRTUAL column. You will have to explicitly cast it as varchar2 with fixed size, else the implicit conversion will make it up to maximum size. See this for more details Concatenating numbers in virtual column expression throws ORA-12899: value too large for column