Re: Fetch out of sequence in cursor
- From: DA Morgan <damorgan@xxxxxxxxx>
- Date: Wed, 30 Aug 2006 11:39:55 -0700
oradbamohan@xxxxxxxxx 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@xxxxxxxxxxxxxxxx
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.
- References:
- Fetch out of sequence in cursor
- From: PAUL MADDSION
- Re: Fetch out of sequence in cursor
- From: oradbamohan@xxxxxxxxx
- Fetch out of sequence in cursor
- Prev by Date: Re: DBMS is not supported in your current installation
- Next by Date: Re: ORA-12631: Username retrieval failed
- Previous by thread: Re: Fetch out of sequence in cursor
- Next by thread: sum with null value
- Index(es):
Relevant Pages
|
|
