how to generate sequence values through a function in oracle

Question

for financial year 2018-2019 sequence name should generate like this

when financial year will complete on 31 st March 2019 then old sequence will drop automatically and new sequence will generate automatically

output
190001
190002
190003
190004
190005
190006  so on

you have to create a Job, that will drop a seq and create new one — hotfix, Sep 05 '18 at 11:23
You should try something first by yourself. If you encounter specific issue, then it will be relevant to ask a question here. Read [ask], and be sure SO is not a code generating facility. — J. Chomel, Sep 05 '18 at 11:47

score 0 · Answer 1 · answered Sep 05 '18 at 11:28

The scope of your problem is not completely clear, but here is a possible solution:

You can isolate the year's digits by dividing the year by 100 (i.e. 2018 -> 18),
If your year-break is, say, March 31st, (such that March 30th 2018 will still be considered in 2017), you can subtract the number of days between March 31st and January 1st from the current date, get the year of the resulting date and then apply the computation of "1" above,
The running number would be the number of days between your current date and the previous March 31st,
Once you have these, your number would be:

Sequence = Year-Suffix * 10000 + Sequence

where Year-Suffix is calculated in 1 and Sequence is calculated in 3.

Hope this gives you an idea of how to resolve your problem.

1 Answers1