0

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Tapajyoti Giri
  • 39
  • 2
  • 2
  • 13

3 Answers3

1

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

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • @TapajyotiGiri No problem, use an explicit sequence object, and rest you know what to do. Of course, virtual column is ruled out too. However, the logic remains same. All the best. – Lalit Kumar B Apr 23 '15 at 13:45
0

if I were you, I wouldn't bother storing such a sequence in a column; I would store the columns containing the relevant information separately and then either have a virtual column that concatenates them together or do the concatenating in a view.

Boneist
  • 22,910
  • 1
  • 25
  • 40
0

Check this , you may not able to create seq , but you can use select as below.

create sequence mysec minvalue 0 start with 10001 increment by 1 nocache;

select 'REL'||to_char(sysdate,'DDMMYYYY')||mysec.nextval from dual;

Ramki
  • 453
  • 2
  • 7
  • create sequence mysec minvalue 0 start with 10001 increment by 1 nocache; select 'REL'||to_char(sysdate,'DDMMYYYY')|| mysec.nextval from dual; – Ramki Apr 23 '15 at 11:24