| Creating a Custom Project ID [message #628337] |
Mon, 24 November 2014 04:11  |
 |
NMcS
Messages: 2
Registered: November 2014
Location: N. Ireland
|
Junior Member |
|
|
|
Hi I am new to using APEX and I have got my first project to do within work however I am trying to assess if one of the requirements can be done or not. The requirement states that a Project ID must be automatically generated in a ADXYYZZZ format where ADX will never change, YY is the current Year and ZZZ is a number incrementing from 001. The user will create a project and and it will automatically generate an ID such as "ADX14005" which will then populate drop down lists where the user can select one of the projects. Does anybody know if this is doable and if so could you give me some idea as to how I would implement this?
|
|
|
|
| Re: Creating a Custom Project ID [message #628338 is a reply to message #628337] |
Mon, 24 November 2014 05:03  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
A sequence would be a good choice if you can afford gapes (i.e. 001, 002, 008, 020, ... instead of 001, 002, 003, 004, ...). If not, MAX + 1 is what you really need. It works OK in a single-user environment, but in a multi-user environment it'll fail sooner or later because two users will simultaneously fetch the same MAX, but one of them will commit first and the very moment another users tries to commit, he'll get uniqueness violation (I guess that project ID must be unique, right?).
Do you want to restart the sequence every year so you'll have 14001, 14002, ... 14087, 15001, 15002, ... or do you NOT care about that? I guess you do, because - what do you plan to do when number of projects reaches 999?
Anyway: an additional table in your schema might do the job. It would keep YEAR information and CURRENT SEQUENCE, having just one row per year (so you'd have 14 + 001 in there when there's just one project. When there's 20th project, you'd keep 14 + 020 in the table). In 2015, you'd have two records:
14 + 087 (87 projects in 2014)
15 + 001 (the first project in 2015)
You'd create a function which returns the next value. It would be a PRAGMA AUTONOMOUS TRANSACTION (as you need to perform UPDATE and COMMIT it, but you don't want that COMMIT to affect other transactions). Also, lock the table (so that other users won't be able to mess it up). Lock won't last long, only a few miliseconds so other users won't even notice that it is locked.
So you'd, basically,
- check whether a record for current year (SYSDATE?) exists. If not, INSERT it. If it exists, UPDATE it with MAX + 1.
- COMMIT (which will automatically release lock)
- return newly created sequence number for the current year
That's it, more or less ...
[Updated on: Mon, 24 November 2014 05:05] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
