Home » SQL & PL/SQL » SQL & PL/SQL » how to retrieve a rollbacked data?
how to retrieve a rollbacked data? [message #260067] Fri, 17 August 2007 06:06 Go to next message
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 #260070 is a reply to message #260067] Fri, 17 August 2007 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not exist as it is rolled back.
Precise your fonctional issue, I'm sure you are taking it by the wrong side.

Regards
Michel
Re: how to retrieve a rollbacked data? [message #260100 is a reply to message #260070] Fri, 17 August 2007 07:44 Go to previous messageGo to next message
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 #260106 is a reply to message #260100] Fri, 17 August 2007 07:56 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
The purpose of a sequence is to be a unique identifier, not to be gapless.
Re: how to retrieve a rollbacked data? [message #260113 is a reply to message #260106] Fri, 17 August 2007 08:47 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

If you want a sequential data then don't use sequence, use max(col1)+1 or a table for containing the number.But it is a poor method and no one agreed to use this at all.

Cheers
Sanka
Re: how to retrieve a rollbacked data? [message #260123 is a reply to message #260100] Fri, 17 August 2007 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Explain why you need gapless numbers.
If you think about it, you'll see there is no business reason.

Regards
Michel
Re: how to retrieve a rollbacked data? [message #260128 is a reply to message #260113] Fri, 17 August 2007 09:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you can use a scheduled job running every n minutes to go round all the records that don't yet have a unique, gapless value assigned to them and give them one.
Re: how to retrieve a rollbacked data? [message #260143 is a reply to message #260128] Fri, 17 August 2007 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Prime directive in an efficient application design and development:

Thou shalt do nothing that is not absolutly indispensable

Each and every performances problem comes from a transgression of this rule.

Regards
Michel


Re: how to retrieve a rollbacked data? [message #260161 is a reply to message #260067] Fri, 17 August 2007 11:04 Go to previous messageGo to next message
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 #260168 is a reply to message #260161] Fri, 17 August 2007 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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....

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
Re: how to retrieve a rollbacked data? [message #260179 is a reply to message #260168] Fri, 17 August 2007 11:56 Go to previous messageGo to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4640
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 #260189 is a reply to message #260179] Fri, 17 August 2007 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
Re: how to retrieve a rollbacked data? [message #260192 is a reply to message #260187] Fri, 17 August 2007 12:37 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #260201 is a reply to message #260196] Fri, 17 August 2007 12:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.

I'd take a small example with my case.
I'm a professional and do my own accounting for my very small entreprise.
By the end of the fiscal year, I print the log of all operations (and other things), they have a sequential number (this is mandatory) partition by account and order by date but in fact I didn't register them in the real order (this should not be done but...).
After this print I destroy the accounting from my computer, the only remaining things are the reports I printed and this is the only thing that is necessary for income tax people.

Regards
Michel
Re: how to retrieve a rollbacked data? [message #260207 is a reply to message #260201] Fri, 17 August 2007 13:09 Go to previous messageGo to next message
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...
Re: how to retrieve a rollbacked data? [message #260233 is a reply to message #260207] Fri, 17 August 2007 14:15 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3225867788098

do a search for "hesitate" on the page.
Re: how to retrieve a rollbacked data? [message #260239 is a reply to message #260233] Fri, 17 August 2007 14:46 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Excellent Andrew. One of my favorite Asktom discussions. I did a quick search for it to post the link but couldn't find it. I couldn't think of the "vacant key query" search text.
Previous Topic: counting commas from numeric(amount)
Next Topic: Date Query
Goto Forum:
  


Current Time: Sun Dec 04 14:49:40 CST 2016

Total time taken to generate the page: 0.09131 seconds