Creating a sequence in Oracle that the Finance people are happy with
-
Creating a sequence in Oracle is simple enough. Creating one that the Finance people accept... slightly less so.
The issue is that these numbers must be sequential, no matter what. And whilst Oracle (or SQL in general) sequences guarantee that, there could be some issue like an exception in code, earthquake, meteor strike, Judgement Day, anything that will cause the next sequence value not to be inserted into the database. And that means a gap, and that means we're hiding something, and that means trouble.
I can't really think of anything better than acquiring an exclusive lock on the table, taking the max ID, increasing it by 1 and using that as the next sequence value. But maybe there's a better way of doing this?
-
big no no according to experts : rolling our own sequence object.
so my suggestion is to have pseudo number column that will have the sequence finance people are happy with (no gaps) and a sequence object that will be used by oracle. simple solutions exist for most problems.Edit: Keyword:most
-
so my suggestion is to have pseudo number column that will have the sequence finance people are happy with (no gaps) and a sequence object that will be used by oracle
God help me, I agree with @Nagesh.
I am officially a RWTF. :(
-
-
Your finance people are morons.
Evidence: It requires more hacking of the database to implement guaranteed non-skipping ids than to leave the default sequence implementation the hell alone.
They should be more concerned about their data if you do manage to make it work that way, because that probably means you could have been inserting your own data everywhere in the middle.
-
God help me, I agree with @Nagesh.
I am officially a RWTF. :(
Everybody agree with common-sense. This is @commonsense speaking.
-
-
New sockpuppet in 3...
-
Someone create @commonsense quickly.
-
Am I late to the party?
-
Was just trying, but said it was taken. So who grabbed @commonsense?
EDIT: and there they are.
-
Am I late to the party?
No you are just in time. Just as @boomzilla 4cast, @commonsense is here.
Only question is are you my sckpuppet or @keith?
-
So who grabbed @commonsense?
Oh lord the boy/girl just entered and you're already grabbing him/her?
-
-
! Harassment!!
-
Your finance people are morons.
Well... it's actually the auditor hired by the VAT (Value Added Tax) department. And whether it's difficult to implement is not really a concern to a non-technical person – and they're right.
-
But maybe there's a better way of doing this?
Oh, that's obvious. First, you buy MAXVALUE network cards and reprogram them to have MAC addresses ranging from 0 to MAXVALUE-1. Then you have your insert routine find the first working network card, obtain its MAC address, insert the record, and zap the card after a successful insert.
For bonus points, invent a device smashing the card with a hammer
-
And whether it's difficult to implement is not really a concern to a non-technical person
Cue Blakeyrant....It's not about how difficult it is to implement. It's about the fact that hacking something to try to implement what they want in this case is going to be MORE BROKEN THAN JUST LEAVING IT THE HELL ALONE. Usually the point of the auditor is to make things LESS broken. If they can't understand, after explaining to them how the system works, that less broken is better... well that is why there is a general disdain for auditors because they're next to useless for actually making sure things are secure.
-
Clearly TRWTF is every dbms ever though, for sequence IDs not guaranteeing that they won't skip, amiright?
How about you just create a garbage table for foreign keying a garbage field labeled AUDITOR_WANTS_AN_ID that has their "unique sequential non-skipping #" in it, use the real sequence ID for what it's intended to be used for, creating an ID for that table. Then join it back out to your real table. That way you don't have to lock your whole table to try to prevent your financial records from getting hosed.
-
Also, that's the point of the logs - to show that you didn't DELETE the missing id, that it was a burned SEQUENCE # on a failed INSERT. If you don't have those logs, it doesn't matter shit what you do to hack around any of this, the auditor is only making the data less trustworthy, which defeats the point.
-
Just one question, why isn't this in the I-Hate-Oracle category?
-
I-Hate-Oracle category
Because the same thing happens if you fail an insert on MSSQL, and I think a good number of other databases that have ID/Sequence generators? Of all the things to hate Oracle for, this would be one of the less wtf-y ones.
-
just create a garbage table for foreign keying a garbage field labeled AUDITOR_WANTS_AN_ID
If this is what your original post is about, a non-ID sequence, then ignore what I've said. I just read the topic to mean they want the actual ID on which you have put your keys to be forced into fitting their rule, not just a sequential numeric descriptor for audit purposes only to fit their rule.
-
Yes, it's exactly that. My first reaction was "they're all barking mad", too, but that's from an engineer's point of view.
The question "why is there no number six?" is valid. There's still quite a bit of VAT fraud in this country, and they take it seriously these days (at least a bit more serious than the time when you saw dozens of people chatting outside the VAT offices every time you drove by, until a massive corruption scandal within the department erupted).
-
The question "why is there no number six?" is valid.
It's a shame it's too late to use UUIDs instead. Since you'd issue them non-sequentially, you'd get rid of the questions entirely; there's nothing quite like an obviously random string for stopping people from reading things into internal identifiers that shouldn't be thought about.
Still, it's definitely too late if you're already dealing with auditors. Most auditors can cope with any business process as long as it stays exactly the same. You need a transaction log that records each of those IDs even if the rest of the transaction is aborted. (You could also use a simple row count provided no rows ever get deleted from that table.)
-
As far as I understand, in finance, you're required to use a numerically incrementing value.
-
But that gives away secret information to the
enemyauditors!
-
-
Maybe a dumb thought since I don't do Oracle, but can you make a view?
Sql server syntax
Select *, row_number() over (partition by 1 order by id) as rownumThis way you use guaranteed unique and display guaranteed in sequential row ids that don't change due to failures unless you back load, in which you argue that's it's proper place.
You can always change the order by to fit your fancy.
-
The question "why is there no number six?" is valid.
True.
@darkmatter said:that's the point of the logs - to show that you didn't DELETE the missing id, that it was a burned SEQUENCE # on a failed INSERT.
Even more true!
@Keith said:As far as I understand, in finance, you're required to use a numerically incrementing value.
There is NO guidance in GAAP or IASB requiring numerically increasing numbers for invoices, or any other documents (our system uses MMDDYxxAAAAz where xx is a "store code", AAAA is in [A-Z]{4} (1) and z is in (ε|[A-Z]) for when a document is "split").Unless the gov't says otherwise (looking at you !!!)...
(1) This is "cleaned up" to prevent the 4-letter usual suspects... and ε stands for ""…