1

I have a procedure below, which loops through a cursor does some logic. I have put FOR UPDATE NOWAIT on the cursor to lock my records set, in case someone from another session wants to update the same set.

But there may be a scenario where the records I have selected in my cursor, is locked by someone as well, in which case, I want to simply log the locked record in a log table and continue to the next record in the loop.

PROCEDURE test(p_id IN NUMBER)
IS

  CURSOR cur_test IS
  SELECT emp_id,
         emp_name
  FROM  
         EMP
  FOR UPDATE NOWAIT;

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

BEGIN
  FOR r_cur_test  IN cur_test 
  LOOP
    BEGIN

      --do something

    EXCEPTION
      WHEN row_locked THEN
        --log locked record in log table
        log_lock('Record is locked');  
        COMMIT; 
    END;      
  END LOOP;

  --call log function to log a successful run

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN  
    --Unsuccessful completion of the run.Trap all unhandled exceptions.
    --log error in error table    
    log_lock('Process exited with error');   
    ROLLBACK;

END;

for testing, I open session 1 and executed 

select * from EMP FOR UPDATE NOWAIT

and in session 2, I executed

begin
  test(1);
end;

it always went to the OTHERS exception, as a result of which, the procecure just terminated without continuing looping through the cursor.

Can anyone give me some advice? Thanks a lot

William Mu
  • 115
  • 5
  • 16
  • 2
    Well, what is the exception in the when others block?? This is a mess. – OldProgrammer Nov 28 '16 at 02:34
  • There is more code after the cursor for loop and the exception in the when others block is for that. – William Mu Nov 28 '16 at 02:49
  • 2
    So, what is the exception??? If you cannot provide all relevant information, how do you expect someone to help you? – OldProgrammer Nov 28 '16 at 03:14
  • Sorry, I should have been more specific. The exception in the OTHERS block is to handle all unexpected exceptions. – William Mu Nov 28 '16 at 03:32
  • 2
    Are you sure that the exception is getting raised when you are trying to lock the records. I would suggest removing the exception block all together for a while and observe what exception is getting raised. – phonetic_man Nov 28 '16 at 03:53
  • Yes, I am sure. I logged a hard-coded message in a temp table. I have updated the above code accordingly. Thanks – William Mu Nov 28 '16 at 04:00
  • I think the OTHERS exception gets raised at the start of the cursor for loop, as it's locked by session 1. So it never got into the for loop, and the row_locked exception never gets raised. – William Mu Nov 28 '16 at 04:35
  • 2
    @WilliamMu if you weren't hiding the error message in your exception block, you might actually stand a chance of being able to debug your procedure. Instead, you've replaced useful error message information with, effectively, "ooh, an error occurred. I'm not gonna tell you what it was, though!". If you're going to log an error message to a table, at least make sure it contains the `SQLERRM` and/or a `RAISE`, otherwise it's pretty pointless recording the fact that an error happened. – Boneist Nov 28 '16 at 08:57
  • So any exception other than ORA-000054 drops through to the outer 'Others' handler and is logged as 'Process exited with error' with no other information. If you want to know what the mystery exception is then obviously don't hide it. I am not sure what the question is. – William Robertson Nov 28 '16 at 11:32
  • Thank you guys for all the suggestions. I will give it a go – William Mu Nov 28 '16 at 22:20

1 Answers1

2

When you do open cursor for update this statement works in scenario: at first run select query and set lock for all records and after then do fetch operations.

PROCEDURE test(p_id IN NUMBER)
IS

  CURSOR cur_test IS
  SELECT emp_id,
         emp_name,
         rowid as row_id
  FROM  
         EMP;

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

  v_sql varchar2(4000) := 'SELECT 1 FROM EMP t WHERE rowid = :row_id FOR UPDATE NOWAIT';
  c int;
  n int;
BEGIN
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, v_sql, dbms_sql.native);

  FOR r_cur_test  IN cur_test 
  LOOP
    BEGIN
      dbms_sql.bind_variable (c, 'row_id', i.row_id);
      n := dbms_sql.execute(c);

      --do something

    EXCEPTION
      WHEN row_locked THEN
        --log locked record in log table
        log_lock('Record is locked');  
        COMMIT; 
    END;      
  END LOOP;

  dbms_sql.close_cursor(c);

  --call log function to log a successful run

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN  
    --Unsuccessful completion of the run.Trap all unhandled exceptions.
    --log error in error table    
    log_lock('Process exited with error');   
    dbms_sql.close_cursor(c);
    ROLLBACK;

END;
SkyWalker
  • 494
  • 2
  • 7