Home » SQL & PL/SQL » SQL & PL/SQL » Infinite Cursor Loop
Infinite Cursor Loop [message #237090] Fri, 11 May 2007 19:25 Go to next message
kuda.oracleapps@gmail.com
Messages: 10
Registered: May 2007
Junior Member
Hello...I have written the following procedure add_bonuses that should update the salary of any employees where department_id = 50. It seems the procedure loops infinitely when I execute it. Can anyone tell me how to solve this problem. Below is the sample code.

create or replace procedure add_bonuses is
v_department_id employees.department_id%type;

cursor cursor_bonus is
select department_id from employees
where department_id = 50;

begin
open cursor_bonus;
loop
fetch cursor_bonus into v_department_id;
exit when cursor_bonus%notfound;
update employees
set salary = salary + 536
where department_id = v_department_id;
end loop;
close cursor_bonus;
end add_bonuses;

Please help...
Re: Infinite Cursor Loop [message #237091 is a reply to message #237090] Fri, 11 May 2007 19:53 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
1) Please read & follow the #1 STICKY post at the top of the forum for posting guidelines.
2) INFINITE loops are next to impossible to code.
I suspect that you are waiting on a mutex because another session has started to modify some object & has not yet issued a COMMIT or ROLLBACK.
Re: Infinite Cursor Loop [message #237105 is a reply to message #237090] Sat, 12 May 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To add to Ana answer.

Your procedure is functionaly wrong: you don't do what you think you do.

Regards
Michel
Re: Infinite Cursor Loop [message #237122 is a reply to message #237090] Sat, 12 May 2007 05:20 Go to previous message
psix666
Messages: 51
Registered: April 2007
Location: Azerbaijan
Member

Why do you select department_id if then you write it???
check this:
cursor cursor_bonus is
select department_id from employees
where department_id = 50;


it always got only 1 string in cursor. If table employees contains department_id = 50 then your cursor always returns next row
department id
50


Isn't it?

May be you want to do this?
create or replace procedure add_bonuses is
begin
  update employees
  set salary = salary + 536
  where department_id = 50;

  commit;
end add_bonuses;


Or better one:
create or replace procedure add_bonuses (p_bonus number, p_dep_id number)is
begin
  update employees
  set salary = salary + p_bonus
  where department_id = p_dep_id;

  commit;
end add_bonuses;
/

begin
  add_bonuses(536,50);
end;

[Updated on: Sat, 12 May 2007 05:22]

Report message to a moderator

Previous Topic: Skip in sequence
Next Topic: newbie problems with plsql...:)
Goto Forum:
  


Current Time: Wed Dec 07 16:25:37 CST 2016

Total time taken to generate the page: 0.11648 seconds