Home » SQL & PL/SQL » SQL & PL/SQL » How to safely commit inside a cursor loop (merged by CM and JR)
How to safely commit inside a cursor loop (merged by CM and JR) [message #446058] Fri, 05 March 2010 06:15 Go to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Given the following example


cursor test_cursor is
 select * 
 from authors;

open test_cursor;

if (test_cursor%rowcount = 0) then
 raise no_data;
else
	close test_cursor;
	for test_cursor_rec in test_cursor
	loop
		update authors
		set id="21"
		commit;
	end loop
end if;



The above code example, selects everything into a cursor and loops through the cursor to update rows that are in the authors table. This is the same data that is on the cursor. I have been getting "snapshot too old" errors and reading about this error i concluded that the commit statement is causing this as i am making changes to the data which the cursor is using.

What is the alternative way to do this to avoid the "snapshot too old" errrors. ?

Thanks
Re: How to safely commit inside a cursor loop [message #446062 is a reply to message #446058] Fri, 05 March 2010 06:17 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Write it as a single update statement and don't bother using cursors at all. Then commit once at the end.
Re: How to safely commit inside a cursor loop [message #446070 is a reply to message #446058] Fri, 05 March 2010 06:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, your code is completely broken for a variety of reasons.
You could do this:

cursor test_cursor is
 select * 
 from authors;

  for test_cursor_rec in test_cursor loop
    update authors
    set id='21'

  end loop
  commit;


but the best solution would be:
  update authors
  set id='21'
  commit;
Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446080 is a reply to message #446058] Fri, 05 March 2010 06:46 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Sorry guys i seem to be having lots of problems submit a post. For some reason it keeps duplicating them even though i only clicked once this time.

Anyway the example i gave above is just a simple example to give an idea of the structure i am working with. The cursor is there because i have to loop through a table with about 2 million rows and update each of them.

I also cant commit at the end after the loop because if i do that i get errors with the UNDO tablespace. So i need to commit inside the cursor but to commit inside the cursor, the cursor become invalid with the snapshot too old errors.

Thanks
Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446081 is a reply to message #446080] Fri, 05 March 2010 06:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Going through the table one row at a time is the slowest, least efficient way of doing the update that you can get.

If an update on a 2 million row table breaks your undo tablespace, then you need to give your DBA a very hard slap.

The single SQL statement is the best way to go.
Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446082 is a reply to message #446058] Fri, 05 March 2010 07:00 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Well if you have to process each row first wouldnt you have to go through them all and update each one that needs updating?

Im not trying to update every row. I select all the rows, process them and update if necessary.

[Updated on: Fri, 05 March 2010 07:00]

Report message to a moderator

Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446084 is a reply to message #446082] Fri, 05 March 2010 07:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Going through the table in PL/SQL, reading one row at a time from a cursor, and then parsing and executing a seperate SQL statement for each row is, in almost every case, much slower then just doing a single UPDATE statement.

There are cases, where processing logic is very complex where it's not possible, but there are other things you can do in those cases.

It is very rare that you'll run out of UNDO space unless your undo tablespace is (by definition) too small/

If you give us a better idea about your actual problem, the help you get will be better.
Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446094 is a reply to message #446058] Fri, 05 March 2010 07:33 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Ok let me try to explain in much detail.

I've got a SQL*Loader script that runs every night. It loads about 2 million rows into a temporary table. The data that has been loaded needs to be validated before it can be processed. The validation is quite complex. Some examples of the validations are

- Valid dates and time (SQL Loader loads just txt)
- Valid product Codes
- Valid userid
- Valid user expiry dates
- Valid product descriptions

The validation itself is quite complex.

There is a cursor which selects everything in the temporary table and validates each row in that table. All of the validation is inside that cursor for loop and its about 2000 lines of validation code.

Now my main problem is that for each row that fails validation, it is needs to be marked as being not valid. The update is done within the cursor for loop and commited straight away.

When i run it the first time, i got the "ORA-01555: snapshot too old: rollback segment number with name "" too small" error. After investigation i found that the commit was causing this error. I found this out by disabling the update statement to see whether it will run to completion and so it did.

Obviously removing the update statement is not a solution. What i tried next is to commit outside the cursor for loop. This causes the "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO_RB1'"error. After some struggling I managed to convince the DBA to increase the UNDO tablespace to 1GB but that didnt help.

Now it looks like i am stuck with the commiting inside the cursor loop option. My question is, is there a safe way i can commit inside the cursor loop without causing the ORA-01555 error.

I found an example (on AskTom) of a possible solution but im not quite sure whether it the best way to do it (see below).

declare

  cursor eupd ( rowid_in rowid )
  is
  select *
  from emp e
  where rowid = rowid_in;

  eupdrec eupd%rowtype;

  commit_count integer := 1;
  commit_interval integer := 10000;

begin

  for erec in select e.rowid, e.*  from emp e;
  loop

     -- do lots of calculations or something here
    open eupd(erec.rowid);
    fetch eupd into eupdrec;

    update emp
    set something = something_else
    where current of  eupd;

     close eupd;

     commit_count := commit_count + 1;
 
     if commit_count >= commit_interval then
         commit_count := 1;
         commit;
     end if;

  end loop;

  commit;

end;

[Updated on: Fri, 05 March 2010 07:33]

Report message to a moderator

Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446095 is a reply to message #446058] Fri, 05 March 2010 07:43 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
Read up on BULK COLLECT.
Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446142 is a reply to message #446058] Fri, 05 March 2010 12:46 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
The bulk collect option is interesting but will require significant code changes so i cant use it.
Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446144 is a reply to message #446142] Fri, 05 March 2010 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ziggy25 wrote on Fri, 05 March 2010 10:46
The bulk collect option is interesting but will require significant code changes so i cant use it.



ROCK<=>ziggy25<=>Hard_Spot

You ask for solutions & then discard them.
Sad

So ENJOY struggling with the errors!
Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446151 is a reply to message #446058] Fri, 05 March 2010 14:00 Go to previous messageGo to next message
ziggy25
Messages: 206
Registered: July 2005
Senior Member
Sorry my response probably didnt use the best of words. The reason i cant make that kind of change is that i wont be able to get approval for a change that changes the design of the process. The only approval i have now is to fix it as a fault.

I know its sounds a bit silly but budget/resource allocation gets a bit too political that sometimes its best to avoid having to deal with.
Re: How to safely commit inside a cursor loop (merged by CM and JR) [message #446164 is a reply to message #446151] Fri, 05 March 2010 14:50 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
So you have basically a design that relies on flying pigs, but you are not allowed to replace the pigs with airplanes, you are just allowed to tweak the pigs. Very Happy

The only option to "tweak the pigs" at this moment I can think of without changing the process would be to perhaps split the 2 million rows input file into smaller files and process them one after the other one.

Previous Topic: Query to get port no
Next Topic: single select
Goto Forum:
  


Current Time: Thu Feb 06 17:05:30 CST 2025