| how to retrieve a rollbacked data? [message #260067] |
Fri, 17 August 2007 06:06  |
shweta.bansal
Messages: 4
Registered: August 2007
|
Junior Member |
|
|
Hi,
I have a problem,lt us suppose there is a scenario wherein we generate a sequence 1,2.....so on for entries in a database.
Now,in case,we need to rollback after the 3rd record,then a scenario happens in which we have all the records(1,2 and then 4th to....) except the 3rd.
How can we retrieve the 3rd record in such a case?
Please reply.
Thanks..
|
|
|
|
|
|
| Re: how to retrieve a rollbacked data? [message #260100 is a reply to message #260070] |
Fri, 17 August 2007 07:44  |
shweta.bansal
Messages: 4
Registered: August 2007
|
Junior Member |
|
|
sorrie for the disconnect.
Actually the case is such that in case a particular transaction is rolled back ,then the sequence number '5' (for example)generated for that particular transaction will be skipped and the next successful transaction will have a sequence number 6.
so the sequence will look like 1,2,3,4,6,7..
but that is not desired.
if u could please suggest a solution for the same.
Thanks..
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to retrieve a rollbacked data? [message #260161 is a reply to message #260067] |
Fri, 17 August 2007 11:04 |
jaagira
Messages: 9
Registered: August 2007
|
Junior Member |
|
|
I don't know what original poster has reason for gapless numbers..But I have seen business needs past with similar requirement. It was AUDITOR who use to scold for missing PO/INVOICE id's..and Accounting department has to to pull their hairs to justify that its CODE issue...But they(auditors) don't like that...they want justification for every missing no's for PO's/Invoices etc and want to stop that completely for future...
Remember IT is just tool but business is most important...It doesn't matter whats good for performance, but matters most what business need is....
|
|
|
|
|
|
| Re: how to retrieve a rollbacked data? [message #260179 is a reply to message #260168] |
Fri, 17 August 2007 11:56 |
jaagira
Messages: 9
Registered: August 2007
|
Junior Member |
|
|
| Quote: |
I 100% agree.
Almost always business does not need gapless numbers just IT because it seems easier to manage consecutive numbers.
The only cases I saw is for legal reason in accounting as you said.
But only reports must have consecutive numbers (at least in all the countries I worked) not internal storage...
So you can always create a paper report/screen display with external consecutive numbers and internal non consecutive numbers. You only generate consecutive numbers for report.
Oracle provides rownum/row_number function for this.
Regards
Michel
|
I am not sure how can you keep paper numbers gapless as well. For Internal or external numbers, we have to use sequence.., so its boils down to use of Oracle sequence.
In my case(its old story), I remember that AUDITOR told us that they don't care if INVOICE/PO cancelled but they want each and every number counted.(They need paper trail of each numbers.., missing numbers means something shady in business for them) So we modified system(as well as old data) to work that way..In short, if sequence would be selected as last part of transaction and commited immideiately..., if USER changes his mind after that, they have to cancel that INVOICE/PO., No rollback once INVOICE/PO created....
|
|
|
|
| Re: how to retrieve a rollbacked data? [message #260187 is a reply to message #260179] |
Fri, 17 August 2007 12:27 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
| jaagira wrote on Fri, 17 August 2007 12:56 |
I am not sure how can you keep paper numbers gapless as well. For Internal or external numbers, we have to use sequence.., so its boils down to use of Oracle sequence.
.
|
Then you are going to have to stop using an Oracle sequence. You cannot guarantee a gapless sequence.
|
|
|
|
|
|
| Re: how to retrieve a rollbacked data? [message #260192 is a reply to message #260187] |
Fri, 17 August 2007 12:37 |
jaagira
Messages: 9
Registered: August 2007
|
Junior Member |
|
|
| joy_division |
Then you are going to have to stop using an Oracle sequence. You cannot guarantee a gapless sequence.
|
So whats alternative then .? Do you have anything better than what we implemented? You use sequence or your own generated numbers(supposedly from custom table etc), you have to make sure that they don't get duplicated or gapless on multiuser enviorments..What else design would make that happen? and how that would be better than NOT using sequence??, elaborate more.
|
|
|
|
| Re: how to retrieve a rollbacked data? [message #260196 is a reply to message #260189] |
Fri, 17 August 2007 12:40 |
jaagira
Messages: 9
Registered: August 2007
|
Junior Member |
|
|
| Michel Cadot wrote on Fri, 17 August 2007 12:34 | | Quote: | I am not sure how can you keep paper numbers gapless as well.
|
You use rownum/row_number when generating the report. The number does not exists in the database.
Regards
Michel
|
Oh no...that won't work...INVOICE/PO numbers are kind of INVXXX001 etc..(XXX could combination company,dept,budget code etc)and they get printed on PO, same goes for INVOICES numbers...AUDITORS care about those numbers not some rownum generated at runtime
|
|
|
|
|
|
| Re: how to retrieve a rollbacked data? [message #260207 is a reply to message #260201] |
Fri, 17 August 2007 13:09 |
jaagira
Messages: 9
Registered: August 2007
|
Junior Member |
|
|
| Michel Cadot wrote on Fri, 17 August 2007 12:51 | Auditors don't dump the database, they see what the report says.
You can generate numbers/tags as you want with the row_number function.
|
For my experience,(And that company was/is very big enterprise steelcompany with atleast few $B+ in revenue..just to give an idea), AUDITORS did care about numbers printed on INVOICE/PO(which is not rownum, but company specific tags+Unique sequential numbers ). And they don't like any INVOICE/PO numbers missing atall...and they insisted on that...
And they are not looking in DB but verifying what printed on INVOICE/PO copy..., which I don't beleive in any company will be a runtime number but it will be permenent data stored in DB with unique identifier...
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
