How to safely commit inside a cursor loop (merged by CM and JR) [message #446058] |
Fri, 05 March 2010 06:15  |
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 #446070 is a reply to message #446058] |
Fri, 05 March 2010 06:28   |
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   |
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 #446084 is a reply to message #446082] |
Fri, 05 March 2010 07:05   |
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   |
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 #446151 is a reply to message #446058] |
Fri, 05 March 2010 14:00   |
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  |
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. 
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.
|
|
|