Hi,

I have a cursor which loops through a table and does some processing.
Towards the end of the loop I update another table and I want to COMMIT each
record in turn. I'm getting a fetch out of sequence message when i try to
run this and believe it is because I'm trying to commit within the cursor
loop. Any suggestions on how I can get round this would be greatly
appreciated. An outline of what I'm trying to do is shown below.

Thanks in advance.

PROCEDURE update_rec
IS
--
CURSOR c_record
IS
SELECT
FROM
WHERE;
--
processed_rec c_record%ROWTYPE;
--
BEGIN
FOR r_record IN c_record LOOP
--
BEGIN
--
process record
--
write to another table
--
-- if I get this far I want to commit individual records
COMMIT;
EXCEPTION
ROLLBACK;
handle any exceptions raised in the loop
END LOOP;
--
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
handle any exceptions outside the loop
END;

On Mon, 28 Aug 2006 10:26:47 GMT, "PAUL MADDSION" <60_pm@blueyonder.co.uk>
wrote:

>I have a cursor which loops through a table and does some processing.
>Towards the end of the loop I update another table and I want to COMMIT each
>record in turn. I'm getting a fetch out of sequence message when i try to
>run this and believe it is because I'm trying to commit within the cursor
>loop. Any suggestions on how I can get round this would be greatly
>appreciated. An outline of what I'm trying to do is shown below.
>
>PROCEDURE update_rec
>IS
>--
>CURSOR c_record ...
>--
>processed_rec c_record%ROWTYPE;
>--
>BEGIN
> FOR r_record IN c_record LOOP
>--
> BEGIN
....
>-- if I get this far I want to commit individual records
> COMMIT;
> EXCEPTION
> ROLLBACK;
> handle any exceptions raised in the loop
> END LOOP;

The commit in the loop is dodgy practice. But it could be the rollback that's
finishing you off:

http://www.oracle.com/technology/ora...o54asktom.html

Or you're using FOR UPDATE in which case it'll fail straight away.

--
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Thanks for the advice.

Yes, I was using a for update in my cursor. I wanted to mark the
successfully processed records so that I could delete them all in one go at
the end of the procedure. I suppose the way round this is to delete them as
I go (once I know processing have been successful) and issue a commit every
x number of records.

Paul

"Andy Hassall" <andy@andyh.co.uk> wrote in message
news:50r5f21jl0sdln9sesov1mk89t7d145c33@4ax.com...
> On Mon, 28 Aug 2006 10:26:47 GMT, "PAUL MADDSION" <60_pm@blueyonder.co.uk>
> wrote:
>
>>I have a cursor which loops through a table and does some processing.
>>Towards the end of the loop I update another table and I want to COMMIT
>>each
>>record in turn. I'm getting a fetch out of sequence message when i try to
>>run this and believe it is because I'm trying to commit within the cursor
>>loop. Any suggestions on how I can get round this would be greatly
>>appreciated. An outline of what I'm trying to do is shown below.
>>
>>PROCEDURE update_rec
>>IS
>>--
>>CURSOR c_record ...
>>--
>>processed_rec c_record%ROWTYPE;
>>--
>>BEGIN
>> FOR r_record IN c_record LOOP
>>--
>> BEGIN
> ...
>>-- if I get this far I want to commit individual records
>> COMMIT;
>> EXCEPTION
>> ROLLBACK;
>> handle any exceptions raised in the loop
>> END LOOP;
>
> The commit in the loop is dodgy practice. But it could be the rollback
> that's
> finishing you off:
>
> http://www.oracle.com/technology/ora...o54asktom.html
>
> Or you're using FOR UPDATE in which case it'll fail straight away.
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

On Mon, 28 Aug 2006 22:05:08 GMT, "PAUL MADDSION"
<60_pm@blueyonder.co.uk> wrote:

>Thanks for the advice.
>
>Yes, I was using a for update in my cursor. I wanted to mark the
>successfully processed records so that I could delete them all in one go at
>the end of the procedure. I suppose the way round this is to delete them as
>I go (once I know processing have been successful) and issue a commit every
>x number of records.
>
>Paul

I'm not sure why you don't want to commit once. If you commit every n
records, you break up a logical transaction into multi physical ones
*and* you increase the chance of the dratted ora-1555 error ('snapshot
too old')

--
Sybrand Bakker, Senior Oracle DBA

PAUL MADDSION wrote:
> Hi,
>
> I have a cursor which loops through a table and does some processing.
> Towards the end of the loop I update another table and I want to COMMIT each
> record in turn. I'm getting a fetch out of sequence message when i try to
> run this and believe it is because I'm trying to commit within the cursor
> loop. Any suggestions on how I can get round this would be greatly
> appreciated. An outline of what I'm trying to do is shown below.
>
> Thanks in advance.
>
> PROCEDURE update_rec
> IS

Unless you have a version prior to 9i dump the cursor loop and
use BULK COLLECT and FORALL. No loops. and put your COMMIT as
the last item before the exception handler. Commits embedded
in loops are a very bad practice and the specific cause your
your current pain.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Hi,

declare
cursor c1 is select * from emp1;
c_rec c1%ROWTYPE;
begin
open c1;
loop
fetch c1 into c_rec;
exit when c1%notfound;
-- here you use the your 'update' and 'commit' statements
-- it wont come from the endof file encounter.
dbms_output.put_line('the emp rec..'||c_rec.ename);
end loop;
close c1;
end;



PAUL MADDSION wrote:
> Hi,
>
> I have a cursor which loops through a table and does some processing.
> Towards the end of the loop I update another table and I want to COMMIT each
> record in turn. I'm getting a fetch out of sequence message when i try to
> run this and believe it is because I'm trying to commit within the cursor
> loop. Any suggestions on how I can get round this would be greatly
> appreciated. An outline of what I'm trying to do is shown below.
>
> Thanks in advance.
>
> PROCEDURE update_rec
> IS
> --
> CURSOR c_record
> IS
> SELECT
> FROM
> WHERE;
> --
> processed_rec c_record%ROWTYPE;
> --
> BEGIN
> FOR r_record IN c_record LOOP
> --
> BEGIN
> --
> process record
> --
> write to another table
> --
> -- if I get this far I want to commit individual records
> COMMIT;
> EXCEPTION
> ROLLBACK;
> handle any exceptions raised in the loop
> END LOOP;
> --
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK;
> handle any exceptions outside the loop
> END;

oradbamohan@gmail.com wrote:
> Hi,
>
> declare
> cursor c1 is select * from emp1;
> c_rec c1%ROWTYPE;
> begin
> open c1;
> loop
> fetch c1 into c_rec;
> exit when c1%notfound;
> -- here you use the your 'update' and 'commit' statements
> -- it wont come from the endof file encounter.
> dbms_output.put_line('the emp rec..'||c_rec.ename);
> end loop;
> close c1;
> end;
>
>
>
> PAUL MADDSION wrote:
>> Hi,
>>
>> I have a cursor which loops through a table and does some processing.
>> Towards the end of the loop I update another table and I want to COMMIT each
>> record in turn. I'm getting a fetch out of sequence message when i try to
>> run this and believe it is because I'm trying to commit within the cursor
>> loop. Any suggestions on how I can get round this would be greatly
>> appreciated. An outline of what I'm trying to do is shown below.
>>
>> Thanks in advance.
>>
>> PROCEDURE update_rec
>> IS
>> --
>> CURSOR c_record
>> IS
>> SELECT
>> FROM
>> WHERE;
>> --
>> processed_rec c_record%ROWTYPE;
>> --
>> BEGIN
>> FOR r_record IN c_record LOOP
>> --
>> BEGIN
>> --
>> process record
>> --
>> write to another table
>> --
>> -- if I get this far I want to commit individual records
>> COMMIT;
>> EXCEPTION
>> ROLLBACK;
>> handle any exceptions raised in the loop
>> END LOOP;
>> --
>> EXCEPTION
>> WHEN OTHERS THEN
>> ROLLBACK;
>> handle any exceptions outside the loop
>> END;

If I may:

PROCEDURE update_rec IS

TYPE myarray IS TABLE OF <table_name>%ROWTYPE;
l_data myarray;

CURSOR c_record IS
SELECT *
FROM <table_name>
WHERE <some_condition>;

BEGIN
OPEN c_record;
LOOP
FETCH c_record BULK COLLECT INTO l_data LIMIT 500;

FORALL i IN 1..l_data.COUNT
UPDATE STATEMENT ...;

EXIT WHEN r%NOTFOUND;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END update_rec;
/

I like to discourage legacy cursor loop single-row processing.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org