NOTICE: Appericiate all the answers, thanks but we already have a sequence.. and can't use the UNIQUE contraints because some items need to have duplicates.. I need to handle this using PLSQL somehow, so based on some criteria (using an if statement) i need to ensure there is no duplicates for that.. And just to confirm, these identifiers are very customized with different types of string.. And for each set of string, we have a number taht counts up only for that string (STR-STR-####) and we have like hundreds of these STR-STR combinations and for each combination we have a #### that counts up... And on top of these some STR-STR combinations are allowed to have duplicates.. So we CAN'T use UNIQUE CONTRAINTS, not can we use PRIMARY KEY as it's not a simple number and on top of that we do have a PRIMARY KEY assigned to the each item.. These Identifiers are for the users and not for the database management.
When a user creates an item, based on some creteria, we give a certain number to the item.. In the save function, a function is called to prepare the first initial string, then the table where the numbers are kept is scanned and the next available 4 digit number for that particular string is assigned.
Now there is some issues with this, there is about 1000 lines of codes between getting the next number and the commit to the database. The issue is when 2 people create an item with the same criteria within seconds of each other, sometimes the same number is issued.
What i've done is that just before the commit, i check the database for the number and if it exist, i call the function to get the next available number again...
Even though this code decreased the chances of a duplictation, if i save 2 items exactly at the same time, i still get a duplicate number..
Anyone know any way to avoid duplicates and drop the chances of a duplicate number to a 0?
EDIT1: We already have a primary key.. This identifier is a special string, and very customized, therefore it woudn't be possible to just use a number that counts up from 0
EDIT2: There is also some cases where we need duplicates.. this is very minor (maybe about 10 different item tracks use duplicates) so before i do my checks for duplicates before the commit with an if statment, so if the item saves dosn't belong to one of the systems that is supposed to have duplicates then, i skip the check...
EDIT 3: We are using PL/SQL here
EDIT 4: I guess this problem was very specific and i didn't quite convey it so.. Although there were many answers, noone of them really captured my problem.. Regardless,i solved the problem and added below as my answer..
I almost hate to suggest this, but since the approach that you settled on was pretty lousy then the one I'm going to show here is pretty good in comparison. Forgive the sarcasm, but when you have this many users telling you that your approach in general is wrong you ought to take that into consideration.
Somewhere you are probably running a SQL like this:
SELECT MAX(DENORMALIZED_FIELD)
INTO BADLY_NAMED_VARIABLE
FROM POORLY_ORGANIZED_TABLE
WHERE DENORMALIZED_FIELD LIKE 'ABC-XYZ%';
Then later you're probably split the variable using SUBSTR, parse the second part into a NUMBER, increment it, and build a new variable with the new code.
What you can do is add the FOR UPDATE clause to the SELECT statement, placing a lock on the records in question. Sure, you're not actually updating them, but by your definition you have to serialize the operation. This is a non-performant, unscalable, and dirty way to get what you want, but it should work. Check the Oracle docs to see all the implications.