Home » SQL & PL/SQL » SQL & PL/SQL » Reset table data after a process
icon7.gif  Reset table data after a process [message #259048] Tue, 14 August 2007 05:06 Go to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Hi All,

I work on SQRs(Structured Query Reports) in PeopleSoft. There are processing SQRs which manipulate data inside tables (say setting/resetting flags)based on some conditions.

Testing of such SQRs is a real pain in the neck since they alter the underlying data everytime they run. Much of our time is wasted in resetting the test data again.

I wanted to know what is the best way of resetting test data back to previous state.

Currently we blindly run back end update or insert any rows what we want. I really don't like it since they don't conform to data integrity requirements.

So I take back up of the tables getting altered and restore it after my process runs.

Recently I learned that Oracle gives up to 9 undo DML steps/options and i tried to find more about it. I learnt that for every SQL fired on the Database, an exactly reverse SQL is automatically generated by Oracle. This generated SQL is used to undo the up to 9 DML statements.

I tried to find more on this but unfortunately I was unable to comprehend more on that.

So will you help me understand if i can use this technique in my SQR problem because i understand there are some prerequisites for that.

Can you suggest any better remedy for my resetting of table data fast and simple (say writing some DB Scripts)?

If this description of my problem may be insufficient/ unclear, please let me know. I will try to give more explanation if i can.

If posting in wrong section,please accept my apologies.

Regards,
Nilesh.
Re: Reset table data after a process [message #259133 is a reply to message #259048] Tue, 14 August 2007 08:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I've never heard of Oracle generating back-out SQL I'm afraid.
Re: Reset table data after a process [message #259141 is a reply to message #259048] Tue, 14 August 2007 09:06 Go to previous messageGo to next message
cbruhn2
Messages: 41
Registered: January 2007
Member
Hi theevildevil,

I'm not quite sure about
Quote:
Recently I learned that Oracle gives up to 9 undo DML steps/options and i tried to find more about it. I learnt that for every SQL fired on the Database, an exactly reverse SQL is automatically generated by Oracle. This generated SQL is used to undo the up to 9 DML statements.

I haven't heard about that before.

You might be interested at looking at new flashback features of Oracle.
With this you will be able to take the database back in a previous state.
Works with Oracle10g Enterprise Edition.
There are also possibilities of just flashing back some tables.
Please look at
http://www.oracle.com/technology/deploy/availability/htdocs/Flashback_Overview.htm
Best regards
Carl Bruhn
Re: Reset table data after a process [message #259148 is a reply to message #259048] Tue, 14 August 2007 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Recently I learned that Oracle gives up to 9 undo DML steps/options and i tried to find more about it. I learnt that for every SQL fired on the Database, an exactly reverse SQL is automatically generated by Oracle. This generated SQL is used to undo the up to 9 DML statements.

This does not exist.
Post/explain in details what you learn maybe you didn't clearly understand.

Regards
Michel
Re: Reset table data after a process [message #259553 is a reply to message #259048] Wed, 15 August 2007 23:46 Go to previous messageGo to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Hey Carl/Michel,

Sorry for the delay in replying but I was away on holiday.

Thanks for the info.

I will get back to you with details ASAP.

Thanks once again for the info.

Regards
Nilesh
Re: Reset table data after a process [message #259555 is a reply to message #259048] Wed, 15 August 2007 23:52 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
My quick read of V11 Doc indicates it can be done.
If you are serious, then dig into the new Doc. set.
Please do NOT ask me to do your job for you, unless or until I get part of your paycheck.
Re: Reset table data after a process [message #259562 is a reply to message #259048] Thu, 16 August 2007 00:15 Go to previous messageGo to next message
theevildevil
Messages: 10
Registered: February 2007
Junior Member
Hey anacedent,
Thanks for letting me know that there is a slight chance for that.
and dont worry about the work, if i dont happen to get the thing working, i will do as you say. Smile just kiddin.
Thanks.
@Carl/Michel: I will get back with more details.

Regards
Nilesh Chhajed
Re: Reset table data after a process [message #259566 is a reply to message #259048] Thu, 16 August 2007 00:22 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
I have not tried, but the rumor is Oracle is not yet accepting Service Requests involving V11; but it can't hurt to try an SR.
Previous Topic: inserting multiple email id into one column via stored procedure
Next Topic: Count without time stamps
Goto Forum:
  


Current Time: Wed Dec 07 10:42:47 CST 2016

Total time taken to generate the page: 0.22936 seconds