ORA-00054: Resource busy
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-00054: Resource busy

  1. #1
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612

    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?

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Apologies for the bad formatting...

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    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
  •  



Click Here to Expand Forum to Full Width