-
ORA-00054: Resource busy
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?
-
Apologies for the bad formatting...
-
You are doing it like this:
Code:
...
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:
Code:
...
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
Last edited by waitecj; 10-29-2004 at 10:23 AM.
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|