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