ORA-00054: Resource busy

[waitecj]

DB = 8.1.7
OS = Win NT

I am writing some code to insert rows based on values already in a table. I have written something like:

------------------------------------------------------

create or replace procedure close_IFC_IFU as

cursor cursor_case_number is
select case_number
from case_t
where substr(case_number, 1, 8) = to_char(sysdate, 'YYYYMMDD')
and error_fg in('IFC', 'IFU')
and orbs_flag='I'
for update NOWAIT;

current_case_number case_t.case_number%TYPE;
max_adjno case_adjust_t.adj_adjno%TYPE;
cases_count number(5):=00000;
case_locked number(5):=00000;

LOCKED_OUT EXCEPTION;
PRAGMA EXCEPTION_INIT(LOCKED_OUT, -54);

BEGIN

open cursor_case_number;

delete from temp_adjustsmade;
delete from temp_casestodo;

loop

current_case_number:=NULL;
fetch cursor_case_number into current_case_number;
exit when cursor_case_number%NOTFOUND;

select max(adj_adjno) into max_adjno from case_adjust_t where
case_number = current_case_number;

if max_adjno=1 then

insert into temp_casestodo
select * from case_adjust_t where case_number = current_case_number;

insert into temp_adjustsmade
(fields)
select 'values from this case'
from temp_casestodo a
where a.case_number=current_case_number;

----

update case_t set
status_fg='C',
status_dt= to_char(sysdate, 'YYYYMMDD'),
case_balance=0,
researcher_id='CBUSS4'
where case_number=current_case_number;

insert into case_comment_t
(OID, CASE_NUMBER, DATE_TIME, OPR_ID, CASE_COMMENT)
values
(0, current_case_number, to_char(sysdate, 'YYYYMMDDHH24MMSSSS'), 'CBUSS4', 'DO NOT REOPEN');

cases_count:=cases_count+1;

else

cases_count:=cases_count;

end if;

end loop;

EXCEPTION
WHEN LOCKED_OUT THEN
case_locked:=case_locked+1;

insert into case_adjust_t
select * from temp_adjustsmade
where adj_adjno=2;

insert into IFC_IFD_close_audit
(close_date, number_closed, locked_count)
values
(to_char(sysdate, 'YYYYMMDD'), cases_count, case_locked);

end;
/

-----------------------------------------------------------

What I am trying to do is say; if one of the rows in my cursor is locked (someone else is doing something with this row), which is identified by ORA-00054, then skip this row and continue to insert the rest of the rows.

What the code above is doing is raising the exception when the cursor opens and exiting the procedure, rather than analysing each row and raising the exception just on the rows that are locked.

I know there is something fundimentally wrong with what I am doing, but I can't think of how to do this without declaring the cursor and thus raising the error too early. Anyone have any ideas?

[waitecj]

Apologies for the bad formatting...

[jmodic]

You are doing it like this:

...
begin
  open cursor
  loop
    fetch
    ....
  end loop;
exception
  when ..... then ....
end;
....

So whenever a predefined exception occurs, your code is exiting the loop and hanles the exception end then finishes. What you need to do is move your exception handling part INTO THE LOOP, something like this:

...
begin
  open cursor
  loop
    begin
      fetch
      ....
    exception
      when ..... then ....
    end;
  end loop;
end;
....

And BTW, as far as I can see you never closes your cursor_case_number cursor! Start using implicit cursors instead of explicit cursors.

[waitecj]

Thanks for your help..

The problem is, the exception occurs when I open the cursor, which is outside the loop in my (and your) example.
Is there a way of doing this (without the explicit cursor?), so that I can check for locked rows (ORA-00054) per row within a loop rather than just at the point in time that the cursor is opened as in my example?

[jmodic]

Ah, sorry, I forgot about the FOR UPDATE bit in a cursor. So you are correct, the locked rows are detected when you open the cursor and in this case there is no way you can handle this only for the locked rows and process all the others. When you want to lock rows with the SELECT FOR UPDATE it's all or nothing: either you get all rows locked for yourself or you must wait untill all the locks from other users are released.

[waitecj]

Gotcha, thanks.

Perhaps there is another way around this without using the FOR UPDATE? Obviously without it, no error is raised, and therefore no exception would be triggered. Bit Catch 22 really.

If anyone can think of an alternative way to skip locked rows from a cursor, please let me know.

jmodic - thanks for your responses.