SQL CREATE SEQUENCE based on a combination of date and counter

Question

I am using Oracle SQL:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0 
PL/SQL Release 12.1.0.2.0 - Production                                                    0 
CORE    12.1.0.2.0  Production                                                            0 
TNS for Linux: Version 12.1.0.2.0 - Production                                            0 
NLSRTL Version 12.1.0.2.0 - Production

I need help figuring out how to create a sequence for a key field below:

I have a table with a field named MY_ID.

MY_ID needs to be automatically generated using sequence when a record is inserted.

The rule for the sequence is a combination of a string prefix, an counter increment by 1 per day and reset at midnight, and the date.

for example: on Sept 10, we inserted 2 records, then the MY_ID should be:

PREFIX_01_20170910
PREFIX_02_20170910

on Sept 11 :

PREFIX_01_20170911
PREFIX_02_20170911
PREFIX_03_20170911

on Sept 12, the whole table might look like this

PREFIX_01_20170910
PREFIX_02_20170910
PREFIX_01_20170911
PREFIX_02_20170911
PREFIX_03_20170911
PREFIX_01_20170912

so far, all i can do with the sequence is increment by 1 regardless of the date:

CREATE SEQUENCE SEQ_MY_TABLE INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCACHE;

and the trigger:

create or replace TRIGGER MY_TABLE_TRG 
BEFORE INSERT ON MY_TABLE
FOR EACH ROW
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF INSERTING AND :NEW.MY_ID IS NULL THEN
      SELECT SEQ_MY_TABLE .NEXTVAL INTO :NEW.MY_ID FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;

Thank you!

score 3 · Answer 1 · answered Sep 28 '17 at 16:48

You can probably dream up a scheme to generate this PREFIX_nn_date key that will appear to work in your development environment, but I'm going to give you a bit of unwanted advice: don't waste your time on it. Make your primary key a simple NUMBER column, populate it from a sequence, and move on. Once your code hits production, where many more users will be banging on your table simultaneously, your carefully crafted scheme to generate that PREFIX_nn_date key is likely to fail - and the more band-aids you throw at it to fix the problems the worse it's going to become.

Best of luck.

Thanks Bob for your advise. However in this situation, only <= 20 records will be inserted per year, i think it should be fine. — Luke Yang, Sep 28 '17 at 17:17

score 2 · Answer 2 · answered Sep 28 '17 at 16:58

You can modify the TRIGGER as follows. Since you need each digit in the sequence as 01,02,03 attached to your prefix, I have used fm specifier in TO_CHAR with '00'. If the total inserts per day exceeds 99 , you need to use fm000

create or replace TRIGGER MY_TABLE_TRG 
BEFORE INSERT ON MY_TABLE
FOR EACH ROW
DECLARE
BEGIN
  <<COLUMN_SEQUENCES>>
  BEGIN
    IF INSERTING AND :NEW.MY_ID IS NULL THEN
      SELECT 'PREFIX_'||TO_CHAR(SEQ_MY_TABLE.NEXTVAL,'fm00')||'_'||TO_CHAR(SYSDATE,'YYYYMMDD') INTO :NEW.MY_ID FROM SYS.DUAL;
    END IF;
  END COLUMN_SEQUENCES;
END;

Note: To suggest a good practice, I do not recommend this to be used as your PRIMARY KEY. It would be better to simply make the sequence PRIMARY KEY in all your application code while populating the records.

but this trigger does not reset SEQ_MY_TABLE.NEXTVAL right? if I insert 1 records today, I will get PREFIX_01_20170927 , If i insert another record tomorrow I will get PREFIX_02_20170928, is there a way to reset the NEXTVAL so that i will get PREFIX_01_20170928 tomorrow? — Luke Yang, Sep 28 '17 at 17:19
No to do that call the reset procedure described in this link at 12:00 am from scheduler. https://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle — Kaushik Nayak, Sep 28 '17 at 17:24

score 2 · Answer 3 · answered Sep 28 '17 at 17:04

Following up on Bob Jarvis's answer, you can always create your string when you need it. Here is a simple example.

with records as (
select 1 id, to_date('20170901', 'yyyymmdd') theDate
from dual
union
select 2 id, to_date('20170901', 'yyyymmdd') theDate
from dual
union
select 3 id, to_date('20170902', 'yyyymmdd') theDate
from dual

)

select 'prefix_' ||  
to_char(theDate, 'yyyymmdd') || '_' || 
to_char( rank() over (partition by theDate order by id)) prefix
from records

returns:

prefix_20170901_1                                        
prefix_20170901_2                                        
prefix_20170902_1

I don't do that much oracle work, but if you are going to do this repeatedly, you might want to incorporate this logic into a function or view.

score 1 · Answer 4 · answered Sep 28 '17 at 17:19

1

The simpliest way is to recreate sequence every midnight via job. But using sequences is not a good idea. I think this ID is important for you, but sequence can cache some values, some values can be missing. So you'll get:

PREFIX_01_20170910
PREFIX_02_20170910
PREFIX_04_20170910
PREFIX_07_20170910

... and so on. For example you had "cache 10", you inserted 2 records and quited, or did rollback, or something else.

Use just number for increment field and calc this fake ID.

answered Sep 28 '17 at 17:19

usbo

131
1
7

you are right! this is not a reliable method. I should probably generate this ID in the back end by query the table and count the record that has current date , and append prefix and date to generate that ID instead. – Luke Yang Sep 28 '17 at 17:34

score 0 · Answer 5 · answered Sep 29 '17 at 06:33

you can create function like below to get new id and use it in the insert query.

CREATE OR REPLACE FUNCTION F_GETID (P_DT IN VARCHAR2) RETURN VARCHAR2
IS
V_NEW_ID VARCHAR2(50);
BEGIN
SELECT 'PREFIX_' || COUNT(*)+1 ||'_' || P_DT INTO V_NEW_ID FROM MY_TABLE 
WHERE MY_ID LIKE   'PREFIX%'||P_DT;
RETURN V_NEW_ID;
END;

then

 insert into my_table(my_id , ...) values(F_GETID('20170927'),...);

SQL CREATE SEQUENCE based on a combination of date and counter

5 Answers5