Home » SQL & PL/SQL » SQL & PL/SQL » Rollback
Rollback [message #290445] Fri, 28 December 2007 23:28 Go to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Hi,
In my procedure, I am getting a cursor and I am inserting that datas to another table using for loop.Consider 100 records in the cursor. I am giving commit for every 25 records. If the procedure throws exception for 55th record, What is the status of that record?...

Please advice.

Thanks in advance.

the procedure is attached.
Re: Rollback [message #290446 is a reply to message #290445] Fri, 28 December 2007 23:35 Go to previous messageGo to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Hi,
You have given commit for every 25th record.so,the records will keeps on insering with commit.So some exceptions come at 55th record because of the only 50 records will get commited and at 55th record it will come out of that block.others are rollbacked.
Re: Rollback [message #290448 is a reply to message #290446] Fri, 28 December 2007 23:41 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Thank you Subbu. Smile
So the the status of 55th record processed will be 'N'?

I gave a sample case.
Can you please read the file, I have attached.

[Updated on: Fri, 28 December 2007 23:45]

Report message to a moderator

Re: Rollback [message #290449 is a reply to message #290446] Fri, 28 December 2007 23:42 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

search for EXCEPTIONS table or DBMS_ERRLOG


regards,
Re: Rollback [message #290474 is a reply to message #290445] Sat, 29 December 2007 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aaargh! Don't you see my answer (http://www.orafaq.com/forum/mv/msg/94730/290087/102589/#msg_290087) to one of your previous posts?
This is bad PL/SQL.
If something is sure is that you WILL have many problems and not only performance ones.

Regards
Michel

Re: Rollback [message #290475 is a reply to message #290449] Sat, 29 December 2007 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@dhananjay

EXCEPTIONS clause and table are for bulk insert not a row per row one.

Regards
Michel
Re: Rollback [message #290478 is a reply to message #290474] Sat, 29 December 2007 01:57 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Yes Michel.
Thanks a lot.
I need to fix all the bugs.
Please do help me to fix this rollback problem first.
Give me an idea to commit upto the previous data and changing the status of that data?

I need to give commit upto 54th data and I have to set processed='N' for 55th data. (for example)
But now the procedure will commit that 50 only.

[Updated on: Sat, 29 December 2007 02:01]

Report message to a moderator

Re: Rollback [message #290480 is a reply to message #290475] Sat, 29 December 2007 01:58 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member

yes Michel i am aware of that.my intention was instead of row by row inserting ,just use sql and log the offending rows using either of the features.may be i should have made it clear in my previous post.


regards,
Re: Rollback [message #290484 is a reply to message #290478] Sat, 29 December 2007 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You want to do this then commit every row.

Of course, this should be stupid but not much more than committing every 25 rows (or what else).
Why do you care about the 51 to 54th rows that are rolled back. Restart at 51 instead of 55, what's the problem?

Regards
Michel


Re: Rollback [message #290486 is a reply to message #290484] Sat, 29 December 2007 02:22 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Yes. Smile
But I am actually doing that commit for 500.
In that case I should consider that rollbacked rows.
Right?
So, giving commit for every row is the only solution?

[Updated on: Sat, 29 December 2007 04:47]

Report message to a moderator

Re: Rollback [message #290494 is a reply to message #290486] Sat, 29 December 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

In that case I should consider that rollbacked rows.
Right?

Wrong, 25, 500, 1000 or what else this is the same.
You should consider to do it in a whole.
This is transaction.
Either you do the whole thing, either you do nothing. This is your business need, doesn't it?

Regards
Michel
Re: Rollback [message #290495 is a reply to message #290494] Sat, 29 December 2007 02:44 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
No Michel.
I need to give commit upto 54.
Need to set that 55 as Failed.
Re: Rollback [message #290498 is a reply to message #290495] Sat, 29 December 2007 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you don't need it.
You designed your program is such way you are now stuck with that problem.
But you don't need it.
Show me your business need saying you have to commit the 54th row.
The business need is do the whole job or nothing. full stop.

Change the program.

Regards
Michel



Re: Rollback [message #290499 is a reply to message #290498] Sat, 29 December 2007 02:57 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Yes. I have to change the program.
While getting exception, My design wont work.
My need is to give commit upto 54.
Need to set that 55 as Failed.

Actually, what will happen in case of exception is rollback and the processed rows will be in the same status that is 'N'.
What I want to do is, set the processed as 'F' in case of exception. So that that row wont be taken again.

Thanks a lot for you effort Michel.

[Updated on: Sat, 29 December 2007 03:32]

Report message to a moderator

Re: Rollback [message #290503 is a reply to message #290499] Sat, 29 December 2007 03:43 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Instead of giving rollback, lets keep the ID__ in another variable. In case of exception, it will come to exception part and executing this.
In ID, the corresponding row id will be there.

EXCEPTION
-- Handle the Exception. For time being Handle only OTHERS
WHEN OTHERS THEN
UPDATE SECCORE3
SET PROCESSED = 'F'
WHERE ID__ = ID;
COMMIT;

This will set the current row processed as 'F' and commit the before processed rows.
Is that okay?

[Updated on: Sat, 29 December 2007 03:44]

Report message to a moderator

Re: Rollback [message #290504 is a reply to message #290503] Sat, 29 December 2007 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The whole has to be redesigned starting with the way you get IDs.
Patching this is useless, there will always be holes.
For instance, think if 2 different processes access your max(id) in concurrent transactions.

Regards
Michel
Re: Rollback [message #290505 is a reply to message #290504] Sat, 29 December 2007 04:25 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Please check this file.
In this I am getting the current cursor value in ID.
In case of exception, It will execute the
UPDATE SECCORE3
SET PROCESSED = 'F'
WHERE ID__ = ID;
COMMIT;
So, the current row will be updated as, F
and the remaining rows that are not commited also commited.
Is this okay?
Re: Rollback [message #290506 is a reply to message #290505] Sat, 29 December 2007 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Is this okay?

No.

Regards
Michel
Re: Rollback [message #290507 is a reply to message #290506] Sat, 29 December 2007 04:29 Go to previous messageGo to next message
vanathi
Messages: 74
Registered: December 2007
Location: India
Member
Please have a look on that file and say...
Sad
Re: Rollback [message #290508 is a reply to message #290507] Sat, 29 December 2007 04:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already said and repeated, re-read my posts.

Regards
Michel
Re: Rollback [message #290513 is a reply to message #290507] Sat, 29 December 2007 05:34 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
check this link.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:546822742166

Even after reading the above link if you decide to implement the existing logic then all I can say is Good Luck.

Regards

Raj
Re: Rollback [message #290516 is a reply to message #290513] Sat, 29 December 2007 05:52 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Even if he has all the luck of the word for eternity, he will face many problems and not just for fetch across commit.
This procedure is doomed to failure.
All the program is doomed to failure (see the link on the other topic).

Regards
Michel
Previous Topic: EXECUTE IMMEDIATE
Next Topic: Create table for other users
Goto Forum:
  


Current Time: Sat Dec 03 22:38:40 CST 2016

Total time taken to generate the page: 0.11007 seconds