Home » SQL & PL/SQL » SQL & PL/SQL » close and re-open cursor for every 20 records (10g)
close and re-open cursor for every 20 records [message #401290] Sun, 03 May 2009 19:11 Go to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
Hi,
i want to Close and re-open cursor for every 20 records

do anyone have any idea about how to write this cursor??

thanks
jillu
Re: close and re-open cursor for every 20 records [message #401291 is a reply to message #401290] Sun, 03 May 2009 20:21 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>do anyone have any idea about how to write this cursor??
Do you have any idea why you refuse to follow Posting Guidelines?
http://www.orafaq.com/forum/t/88153/0/

Re: close and re-open cursor for every 20 records [message #401299 is a reply to message #401290] Sun, 03 May 2009 23:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
nastyjillu wrote on Mon, 04 May 2009 02:11
Hi,
i want to Close and re-open cursor for every 20 records

do anyone have any idea about how to write this cursor??

thanks
jillu

Your question is meaningless. A cursor "exists" between open and close. You have to detail your issue.

Regards
Michel
Re: close and re-open cursor for every 20 records [message #401400 is a reply to message #401299] Mon, 04 May 2009 10:43 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
Ok. given below is the detailed requirement that i was given.

i need to write a cursor for update. coding rules are given below

Special Coding rules:
a) Use explicit Open, Fetch, Close cursor commands, not a FOR loop.
b) Commit on every record.
c) Close and re-open cursor every 20 records.
d) Exception block inside the loop, must recognize Oracle error ORA-00054, report on these records, rollback, then continue with next record. For other errors, report, rollback and RAISE.

do closing and opening cursor for every 20 records improve performance?? or decrease performance??

thanks
jillu
Re: close and re-open cursor for every 20 records [message #401401 is a reply to message #401290] Mon, 04 May 2009 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>do closing and opening cursor for every 20 records improve performance?? or decrease performance??
YES


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Re: close and re-open cursor for every 20 records [message #401403 is a reply to message #401400] Mon, 04 May 2009 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
c) Close and re-open cursor every 20 records.

As I said this is meaningless and you didn't detailed your need just detailed what you want to do.

Regards
Michel
Re: close and re-open cursor for every 20 records [message #401412 is a reply to message #401403] Mon, 04 May 2009 18:15 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
Michel,

i heard that closing and opening cursor for few records will improve the performace. i might be wrong. but i was given a task to do so. you can see the requirement which i gave before.

if there is no improvement in the performance .then my leader wouldnot have asked me to close and re-open cursor.

hope this is clear. iam trying hard to explain my question.

Re: close and re-open cursor for every 20 records [message #401413 is a reply to message #401290] Mon, 04 May 2009 18:46 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>if there is no improvement in the performance .then my leader wouldnot have asked me to close and re-open cursor.
You need to ask your leader for a code template that you can modify to meet the new requirements.

How do you plan on avoiding the reprocessing records previously UPDATEd on second & subsequent OPEN?

[Updated on: Mon, 04 May 2009 19:00]

Report message to a moderator

Re: close and re-open cursor for every 20 records [message #401419 is a reply to message #401412] Mon, 04 May 2009 20:50 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
can i assume that you need multiple cursor opening and closing with different set of select statement? for example
cursor c1 is select * from temp1 where id between 1 to 20
cursor c2 is select * from temp1 where id between 21 to 40


Re: close and re-open cursor for every 20 records [message #401423 is a reply to message #401290] Mon, 04 May 2009 22:10 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>b) Commit on every record.
Row by row is slow by slow.
Commit on every record. is an ideal way to create ORA-01555 error
Re: close and re-open cursor for every 20 records [message #401433 is a reply to message #401419] Tue, 05 May 2009 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
dr.s.raghunathan wrote on Tue, 05 May 2009 03:50
can i assume that you need multiple cursor opening and closing with different set of select statement? for example
cursor c1 is select * from temp1 where id between 1 to 20
cursor c2 is select * from temp1 where id between 21 to 40




How would that scale on millions of records?
Re: close and re-open cursor for every 20 records [message #401460 is a reply to message #401433] Tue, 05 May 2009 02:00 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
i just want to know whether he is interested in opening multiple cursor and is his query related aspects. May be wrong example i quoted. I faced the problem of using the same cursor name even after closing. To have better understanding of OP's interest I just tried to lead him. That's all

[Updated on: Tue, 05 May 2009 02:02]

Report message to a moderator

Re: close and re-open cursor for every 20 records [message #401570 is a reply to message #401460] Tue, 05 May 2009 10:13 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
Quote:
Raghunathan : can i assume that you need multiple cursor opening and closing with different set of select statement?


No, i can have only one cursor.

Quote:
Swan: How do you plan on avoiding the reprocessing records previously UPDATEd on second & subsequent OPEN?


thats another issue i get here. to avoid this i have to skip the records which were updated previously by making use of rowcount.
i wrote an example procedure which may work according to my requirement. given below is the code which doesnt update records but just print records.it closes and re-opens cursor for every 2 records:

create or replace procedure datafix.xxemp1 ( p_deptno in number,p_sal out number,p_ename out varchar2 ) 
is
m number;
cursor c is
select sal,ename 
from emp2
where deptno=p_deptno;

begin
m:=0;
open c;
loop
fetch c into p_sal,p_ename;   
     
exit when c%notfound;

if c%rowcount>m then
dbms_output.put_line('cur count is :  ' ||c%rowcount);
dbms_output.put_line(p_sal||'  '||p_ename);
m:=m+1;
if mod(m,2)=0 then
close c;
dbms_output.put_line('cur closed');
open c;
dbms_output.put_line('cur opened');
end if;
end if;
end loop;
close c;
end xxemp1;


above procedure works fine in this case, but is it an efficient and correct way of writing a code??

thanks
jillu

[Updated on: Tue, 05 May 2009 10:23] by Moderator

Report message to a moderator

Re: close and re-open cursor for every 20 records [message #401574 is a reply to message #401570] Tue, 05 May 2009 10:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i wrote an example procedure which may work according to my requirement. given below is the code which doesnt update records but just print records.it closes and re-opens cursor for every 2 records:

Put something that is NOT oversimplified and we will tell you how to do it efficiently.

Regards
Michel

[Updated on: Tue, 05 May 2009 10:26]

Report message to a moderator

Re: close and re-open cursor for every 20 records [message #401575 is a reply to message #401570] Tue, 05 May 2009 10:34 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
nastyjillu wrote on Tue, 05 May 2009 16:13

above procedure works fine in this case, but is it an efficient and correct way of writing a code??



No.
There's nothing efficient or correct about closing and reopening a cursor every x records, whether x is 2, 20 or 2000.

If you have to use a cursor open it once.

Quote:

thats another issue i get here. to avoid this i have to skip the records which were updated previously by making use of rowcount.


You'll struggle to make that work and it'll be brutally inefficient, because it implies refetching rows that have already been updated.
You need a better way of identifying the rows you need to update.


Your earlier stated requirements don't make a lot of sense.
Committing after every update is a bad idea.

this:
Quote:

d) Exception block inside the loop, must recognize Oracle error ORA-00054, report on these records, rollback, then continue with next record. For other errors, report, rollback and RAISE.


seems dubious - I assume it's the cursor (declared as FOR UPDATE NOWAIT) that's going to kick out this error?
If so this isn't going to happen because you'll get the error on the cursor OPEN.

Why don't you go back to first principles and tell us what all this is supposed to achieve?
Re: close and re-open cursor for every 20 records [message #401588 is a reply to message #401575] Tue, 05 May 2009 12:09 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
cookiemonster :
Quote:
Why don't you go back to first principles and tell us what all this is supposed to achieve?


We are trying to do historical data conversion:

Quote:
You'll struggle to make that work and it'll be brutally inefficient, because it implies refetching rows that have already been updated.
You need a better way of identifying the rows you need to update


i dont have to refetch the previous rows. you will understand if you see below cursor:

CURSOR cur_archive_rec is                

select id from table_A
where id between 1000 and 15000 
and id>last_id
order by id
for update NOWAIT;


in the above code, we assign id value to last_id variable in every loop(at the end). that way whenever the cursor is re-opened it doesnt fetch the previously fetched records as the select query has new set of records each time.

hope this gives you a clear idea.

as you said closing and re-opening is very inefficient, shall i tell me leader not to use such code??

i wrote below code to update table_A , but i got and error:

error is::

PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

   begin case declare else elsif end exit for goto if loop mod
   null pragma raise return select update while with
   <an identifier> <a double-quoted delimited-identifier>
   <a bind variable> << close current delete fetch lock insert
   open rollback savepoint set sql execute commit forall merge
   pipe


Code is given below:

declare
last_id number;
l_cntr number;
l_actual_end_date  table_B.actual_end_date%TYPE;
l_completed_date   table_B.completed_date%TYPE; 
resource_busy EXCEPTION;
PRAGMA EXCEPTION_INIT (resource_busy, -54);
l_id number;  

CURSOR cur_archive_rec is              

select id from table_A  
where id between 293201 and 293219 
and id>last_id 
order by id
for update NOWAIT; 

begin
last_id :=0;
l_cntr :=0;                      
open cur_archive_rec ;
loop

fetch cur_archive_rec into l_id;
exit when cur_archive_rec%notfound;
select actual_end_date,completed_date into l_actual_end_date ,l_completed_date  
from table_B where w_id=l_id and id=0; 
if cur_archive_rec%rowcount>l_cntr then

update table_A
set end_date=l_actual_end_date,
close_date=l_completed_date,
seq=500,
status_code='ABC',
state='CONVERT'
where current of cur_archive_rec ;

commit;

EXCEPTION

            WHEN resource_busy
           THEN
dbms_output.put_line('Resource is buzy');
dbms_output.put_line('Records that didnot update are db_id:'||l_id);
rollback;
exit;  
     rollback;
    RAISE;

l_cntr:=l_cntr+1;
last_id:= l_id; 
if mod(l_cntr,2)=0 then  -- for every 2 records
close cur_archive_rec;

open cur_archive_rec;

l_cntr:=0;  -- this resets counter for every 2 records
end if;
end if;

end loop;
close cur_archive_rec;
end;


i was not able to solve that PLS-00103 error.

thanks
jillu

[Updated on: Fri, 08 May 2009 04:12] by Moderator

Report message to a moderator

Re: close and re-open cursor for every 20 records [message #401589 is a reply to message #401588] Tue, 05 May 2009 12:22 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
i asked my leader about why they want to close and re-open cursor even though its inefficient,

she said:
we will be updating records when databse is UP and the table we are updating would be most used table.

if we dont close and re-open cursor then it throws "Cursor is too old" error. and it happend with my leader many a times.

this is the main reason.

thanks
jillu
Re: close and re-open cursor for every 20 records [message #401590 is a reply to message #401589] Tue, 05 May 2009 12:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>then it throws "Cursor is too old" error. and it happend with my leader many a times.
There is no such error.
It could be "too many cursors open", which means, your code has issues ( connection pooling?)

May be, it is "snapshot too old".
What you are trying to do is the most recommended method to induce "snapshot too old".

[Updated on: Tue, 05 May 2009 12:35]

Report message to a moderator

Re: close and re-open cursor for every 20 records [message #401613 is a reply to message #401590] Tue, 05 May 2009 14:22 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
What you are trying to do is the most recommended method to induce "snapshot too old".


mahesh, do you mean closing and re-opening cursor many a times will produce "snapshot too old" error???

Re: close and re-open cursor for every 20 records [message #401616 is a reply to message #401613] Tue, 05 May 2009 14:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
No.
Just noticed that BlackSwan has already mentioned the same.
Quote:
>b) Commit on every record.
Row by row is slow by slow.
Commit on every record. is an ideal way to create ORA-01555 error




And
>>closing and re-opening cursor many a times
is totally useless.
Re: close and re-open cursor for every 20 records [message #401617 is a reply to message #401616] Tue, 05 May 2009 16:32 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
closing and re-opening cursor many a times
is totally useless.


but i have no other option. i have to do this way.

i have other question regarding same code.

i want to have 'Exception' block inside the loop,
must :
-recognise oracle error ORA-00054
-report on these records
-rollback
-then continue with next record.

for Other errors: report,rollback and Raise

is it possible to write exception inside the loop?? iam trying but got below error, which i think i mention earlier.anyway for your convinience its given below:

PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:

   begin case declare exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> <<
   close current delete fetch lock insert open rollback
   savepoint set sql execute commit forall merge pipe
ORA-06550: line 88, column 1:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the follo


code for exception is given below:

declare
resource_busy EXCEPTION;
PRAGMA EXCEPTION_INIT (resource_busy, -54);

--in the loop i have below exception block

EXCEPTION

            WHEN resource_busy
           THEN
dbms_output.put_line('Resource is buzy');
dbms_output.put_line('Records that didnot update are db_id:'||l_db_id);
rollback;
exit;  --when resource is buzy ,it will show msg and exit-when others then
when others then      
         rollback;
                RAISE;


Re: close and re-open cursor for every 20 records [message #401618 is a reply to message #401617] Tue, 05 May 2009 16:41 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
If you want help from these people, you really, REALLY need to start paying attention to what they are asking.
It's very simple, what you are being asked to do. Supply us with what you are trying to do. Not with how your boss wants you to do it (oh and "We are trying to do historical data conversion:" is about as close as something that is not close at all)
Quote:
but i have no other option. i have to do this way.

No you don't.
Quote:
i have other question regarding same code.
Let's deal with the first bit first eh?
One more time What are you trying to do?
Re: close and re-open cursor for every 20 records [message #401619 is a reply to message #401290] Tue, 05 May 2009 16:42 Go to previous messageGo to next message
magnetic
Messages: 324
Registered: January 2003
Senior Member

would work easyly with this thought

counter:=0;
open cursor;
fetch cursor;
while cursor%found loop
while counter<=20 loop
counter:=counter+1;
-- put your code like inserts rerds here and commit it --
fetch cursor;
exit when counter=20 or cursor%notfound;
end loop;
counter :=0;
if cursor%notfound then
close cursor;
else
fetch cursor;
end if;
exit when cursor%notfound;
end loop;
--
if cursor%isopen then
close cursor;
end if;

Re: close and re-open cursor for every 20 records [message #401620 is a reply to message #401619] Tue, 05 May 2009 16:51 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Did you bother to read any of the previous replies? Committing within a loop is a monumentally poor decision. Besides, your 'code' does not even do what the OP has asked.
Please go and read the posting guidelines as to how to post 'code'
Re: close and re-open cursor for every 20 records [message #401621 is a reply to message #401290] Tue, 05 May 2009 17:12 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Post DDL for tables.
Post DML for test data.

Post expected/desired results.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330
Re: close and re-open cursor for every 20 records [message #401627 is a reply to message #401621] Tue, 05 May 2009 19:22 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
pablolee,
Please go and read the posting guidelines as to how to post 'code' 

i read post guide and wrote the code accordingly. please let me know if i made any mistake .

If you want help from these people, you really, REALLY need to start paying attention to what they are asking.


i clearly mentioned what i wanted to do. what more i can explain? i dont understand. i even told you why i want to close and re-open cursor which is justified.


Did you bother to read any of the previous replies?

i was working on some other things , how can you expect me to look for your replies within few hours of time??

magnetic, where are you re-opening cursor??

Re: close and re-open cursor for every 20 records [message #401630 is a reply to message #401627] Tue, 05 May 2009 19:51 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Seems you did not realize that Pablolee's last post was in response to Magnetic.

[Updated on: Tue, 05 May 2009 19:51]

Report message to a moderator

Re: close and re-open cursor for every 20 records [message #401636 is a reply to message #401630] Tue, 05 May 2009 20:59 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
yes.might be. but he didnot mention name. as i started this topic,i thought it was meant to me.

--sorry if i was wrong.
Re: close and re-open cursor for every 20 records [message #401641 is a reply to message #401290] Tue, 05 May 2009 21:43 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
Never confuse movement with progress.
This thread has lots of movement, but no discernible progress.

nastyjillu,
You need to keep in mind the first rule about holes.
The first thing you should do when you find yourself in a hole, is to stop digging.

You've been told by many folks here (& by Tom Kyte) that your approach is flawed.

If you insist on proceeding, I hope that the code will complete the weekly data load before 7 days elapse.

Read, Fire, Aim!
Re: close and re-open cursor for every 20 records [message #401748 is a reply to message #401627] Wed, 06 May 2009 06:06 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
@nastyjillu One post replied to you, the other replied to Magnetic. You can tell which is which by looking at the top of the post, it states exactly which post is being replied to.

Quote:
clearly mentioned what i wanted to do.
No you didn't. What is it that you are trying to do with your data something along the lines of

"I want to update rows in table x where they match condition ab and cd, here is a valid taest case ...
create table and insert statements to generate a valid test case"
Re: close and re-open cursor for every 20 records [message #401767 is a reply to message #401290] Wed, 06 May 2009 07:00 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
@nastyjillu

You need to read up on exception handling, try here

If you want to handle exceptions inside a loop you need to put a BEGIN and END inside the loop to go with the exception handler.

You need to understand that the rules you've been given CAN'T work.

Here's a couple of obvious problems:
Quote:

a) Use explicit Open, Fetch, Close cursor commands, not a FOR loop.
b) Commit on every record.
c) Close and re-open cursor every 20 records.
d) Exception block inside the loop, must recognize Oracle error ORA-00054, report on these records, rollback, then continue with next record. For other errors, report, rollback and RAISE.



You can't trap ORA-00054 inside the loop becuase you won't get that error inside the loop. As I previously pointed out this error is raised when you open the cursor.

Observe

session 1
Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

SQL> create table lock_test as select rownum a, rownum b from dual connect by level < 100;

Table created.

SQL> update lock_test set b = 10 where a = 5;

1 row updated.

SQL> 

I've locked the row where a=5.
Session 2:
SQL> set serveroutput on;

SQL> DECLARE
  2  
  3    CURSOR c1 IS
  4    SELECT a, b
  5    FROM lock_test
  6    WHERE a BETWEEN 1 and 20
  7    FOR UPDATE OF b NOWAIT;
  8    
  9    r1 c1%ROWTYPE;
 10    
 11  BEGIN
 12  
 13    dbms_output.put_line('start');
 14    OPEN c1;
 15    dbms_output.put_line('opened cursor');
 16    LOOP
 17      FETCH c1 INTO r1;
 18      dbms_output.put_line('fetched');
 19      EXIT WHEN c1%NOTFOUND;
 20      
 21    END LOOP;
 22    CLOSE c1;
 23    dbms_output.put_line('end');
 24    
 25  END;
 26  /
start
DECLARE
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 4
ORA-06512: at line 14


Gets as far as the open statement then errors out.
You can't check for ORA-00054 on a row by row basis because it's not reported on a row by row basis.

2nd problem:
Commiting every record is incompatible with rule 4.
To check for record locks you need to declare a cursor as FOR UPDATE NOWAIT. A cursor in this form locks all the records it's trying to select.
COMMIT apart from saving your changes releases all locks you may have - which destroys your cursor leading to a fetch out of sequence error.

Observe:
session 1 again
SQL> rollback;

Rollback complete.

SQL> set serveroutput on
SQL> DECLARE
  2  
  3    CURSOR c1 IS
  4    SELECT a, b
  5    FROM lock_test
  6    WHERE a BETWEEN 1 and 20
  7    FOR UPDATE OF b NOWAIT;
  8    
  9    r1 c1%ROWTYPE;
 10    
 11  BEGIN
 12  
 13    dbms_output.put_line('start');
 14    OPEN c1;
 15    dbms_output.put_line('opened cursor');
 16    LOOP
 17      FETCH c1 INTO r1;
 18      dbms_output.put_line('fetched');
 19      EXIT WHEN c1%NOTFOUND;
 20      
 21      UPDATE lock_test
 22      SET b = r1.b + 1
 23      WHERE a = r1.a;
 24      
 25      COMMIT;
 26      
 27    END LOOP;
 28    CLOSE c1;
 29    dbms_output.put_line('end');
 30    
 31  END;
 32  /
start
opened cursor
fetched
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 17


SQL> SELECT * from lock_test where a between 1 and 20;

         A          B
---------- ----------
         1          2
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
         9          9
        10         10
        11         11

         A          B
---------- ----------
        12         12
        13         13
        14         14
        15         15
        16         16
        17         17
        18         18
        19         19
        20         20

20 rows selected.

SQL> 


1 row got updated then it crashed and died.

So, as has been previously suggested by me and others you really explain what you're trying to achieve with all this so we can help you come up with a better solution, because your team leaders rules can't work.
Re: close and re-open cursor for every 20 records [message #401837 is a reply to message #401590] Wed, 06 May 2009 11:08 Go to previous messageGo to next message
corral
Messages: 1
Registered: February 2009
Location: Madrid
Junior Member
I think I've understood what happens. A long running cursor is prone to ORA-01555 errors (snapshot too old), as BlackSwan and Mahesh said. Among other possible reasons, the error may arise if the cursor reads one row which another process updated and committed time ago, but after the cursor was opened. To fetch the row through the cursor, Oracle must get a consistent view of the row at the time the cursor was opened. The undo information required for this consistent view of the row is no longer in the undo tablespace. Due to the high activity, it was reused by another transaction. Since that change was committed, Oracle may reuse their undo space.

For this to happen, the query (the cursor) must take a long time, so it requires getting consistent blocks which were modified several seconds ago. Also, there must be a high activity of new transactions being committed, so the undo headers and blocks are frequently reused. And last, the undo tablespace is configured too small to avoid all this.

Closing the cursor after n rows and reopening for a new set of rows will reduce the time the cursor is open up to the point that every time it does a consistent get it finds the necessary undo info, thus preventing ORA-01555 errors. But this may be achieved with more than 20 rows; let's say 100,000. Since reopening a cursor has a cost, better do it less frequently.
Re: close and re-open cursor for every 20 records [message #402142 is a reply to message #401837] Thu, 07 May 2009 18:32 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
i solved the issue. code is given below :

declare              
last_id         number;
l_end_date      date;
l_comp_date     date; 
l_db_id         number;
l_id            number;
count_recs      number;
total_recs_read number;

cursor cursor_1 is
select id from table_A 
where id between 29320 and 135912
and id>last_id 
order by id;

begin

last_id:=0;
count_recs:=0;
total_recs_read:=0;

open cursor_1 ;

loop
fetch cursor_1 into l_db_id;

if cursor_1%found then
total_recs_read:=total_recs_read+1;
end if;

exit when cursor_1%notfound;

select end_date,comp_date into l_end_date ,l_comp_date  
from table_B
where w_id=l_db_id and d_id=0; 

/*below code updates table and has exception block*/

declare
resource_busy EXCEPTION;
PRAGMA EXCEPTION_INIT (resource_busy, -54);

cursor cursor_2 is
select 1 from table_A 
where id=l_db_id
for update nowait;

begin
open cursor_2;
loop
fetch cursor_2 into l_id;
exit when cursor_2%notfound;

update  table_A    
set end_date=l_end_date,
w_close_date=l_comp_date,
w_seq=100,
w_status_code='XYZ',
w_state='ABC' ,
modify_date=sysdate,
modify_user_id='USR'
where current of cursor_2  ;
 
commit;            --commits for every record
  exit;
  
end loop;
close cursor_2;
  exception
when resource_busy then
rollback;
raise;
dbms_output.put_line('id which didnot get updated is :'||l_db_id);
when others then 
     rollback;
      raise;
      dbms_output.put_line('id which didnot get updated is :'||l_db_id);

   end;

/*update code finished here*/

last_id:=l_db_id;

if cursor_1%rowcount>20 then  --this closes and re-opens cursor for every 20 records
close cursor_1; 
open cursor_1;  
end if;    

count_recs:=count_recs+1;
end loop;        

if cursor_1%isopen then
close cursor_1;
end if;
commit;

dbms_output.put_line('total records read is: '||total_recs_read);   
dbms_output.put_line('total records updated is: '||count_recs);

exception
when others then
rollback;
raise;
dbms_output.put_line('the db_id which didnot get updated is: '||l_db_id);

end;


thanks
jillu
Re: close and re-open cursor for every 20 records [message #402198 is a reply to message #402142] Fri, 08 May 2009 01:14 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
nastyjillu
commit;            --commits for every record


./fa/2115/0/ Cool!
Re: close and re-open cursor for every 20 records [message #402239 is a reply to message #402142] Fri, 08 May 2009 03:07 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
nastyjillu wrote on Fri, 08 May 2009 00:32
i solved the issue.[/code]


Nope, what you have done is compound the issue. Why would you come to a forum and refuse to take the advice given to you? Seems like a bit of a waste of time. Oh well, on your head be it.
Re: close and re-open cursor for every 20 records [message #402249 is a reply to message #402198] Fri, 08 May 2009 03:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Littlefoot wrote on Fri, 08 May 2009 08:14
nastyjillu
commit;            --commits for every record


./fa/2115/0/ Cool!
http://www.orafaq.com/forum/fa/449/0/

Nastyjillu, I'm glad you're not on my team.

MHE
Re: close and re-open cursor for every 20 records [message #402273 is a reply to message #401290] Fri, 08 May 2009 06:12 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I thought you wanted processing to continue after a ora-00054?
because that code won't do that.

You don't seem to have quite grasped exception handling.

The inner exception when others is pointless as any errors it traps will be trapped by the outer exception when others as well.

The exception when resource_busy is pointless as it stands since it does exactly the same thing as the when others (I suspect you meant it to do something different).

The dbms_output's after the raises will never be executed.

If ID is unique on tableA then the inner loop is pointless.

And of course this will be horribly slow.
Re: close and re-open cursor for every 20 records [message #402340 is a reply to message #402273] Fri, 08 May 2009 18:54 Go to previous messageGo to next message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
cookiemonster
Quote:
I thought you wanted processing to continue after a ora-00054?
because that code won't do that.


if i get -54 error, the control goes to exception which is in inner block. and it comes out of the innser block and now the control is in outer block and it behaves normal.

Quote:
The inner exception when others is pointless as any errors it traps will be trapped by the outer exception when others as well.


yes, yor are right. i changed it.


Quote:
The dbms_output's after the raises will never be executed.


yes, you are right. i changed it before i saw your information.

Quote:
If ID is unique on tableA then the inner loop is pointless.


yes,i think so. you want me to remove the inner loop and just have fetch statement??

Quote:
And of course this will be horribly slow.


i know. there is no other way. i had to do this.otherwise i wouldnot have done.


pablolee

Quote:
Nope, what you have done is compound the issue. Why would you come to a forum and refuse to take the advice given to you? Seems like a bit of a waste of time. Oh well, on your head be it.


its not waste of time. there was so much of knowledge sharing. if this is compound, please modify that code and reply with a simplified code which does the same thing.even i too need that.
i hope you know what i wanted to do. please reply with he modified code.

littlefoot
Quote:
Cool!


iam surprised.everyone is against me. and you said cool. is it positive word or ?????

thanks
jillu

[Edit MC: to quote use QUOTE tags and not CODE ones]

[Updated on: Sat, 09 May 2009 00:40] by Moderator

Report message to a moderator

Re: close and re-open cursor for every 20 records [message #402376 is a reply to message #402340] Sat, 09 May 2009 11:03 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
nastyjillu
iam surprised.everyone is against me. and you said cool. is it positive word or ?????

I'm afraid that you got it wrong ... There's really no reason for anyone to be against someone else on this Forum. People are just trying to show you how things should be done, what programming techniques you should avoid as they might lead into problems, but ... it seems that you just don't want to listen.

The same goes for me; committing every record in a loop is stupid (pardon my French). The fact that it *might* work once or twice simply doesn't prove that this is a way to go. However, as I have concluded that you are doing it your way, I had nothing else to say but cool. Perhaps I should have said nothing, but attached this instead: ./fa/3518/0/

Good luck with the script; you might need it!
Re: close and re-open cursor for every 20 records [message #402387 is a reply to message #402376] Sat, 09 May 2009 14:35 Go to previous message
nastyjillu
Messages: 211
Registered: February 2009
Senior Member
littlefoot

ya. but i had to commit for every record. thats what was told to me. i have no other option.

iam trying to learn new things and this forum helps a lot.

there are more then 500 views on this topic. many people get to learn new things. iam happy about it.

thanks
jillu
Previous Topic: Global temporary table
Next Topic: Oracle rows displays not in same order
Goto Forum:
  


Current Time: Sat Dec 03 18:08:50 CST 2016

Total time taken to generate the page: 0.05211 seconds