SQL & PL/SQL

Hello,

I am trying to handle an exception and to resume execution of my pl/sql block. From what I have read, the way to do this is to create a sub-block (BEGIN/END), containing the pl/sql code that can potentially cause an error and to place the exception code in that sub-block. The problem is that it is not working. The error is not getting handled at all.

Here is my code (simplified for clarity):

DECLARE
-- identify indexes that need rebuilding
CURSOR c_rebuild_index IS
...
BEGIN
FOR cur_row IN c_rebuild_index LOOP
IF height > 5 THEN
BEGIN -- BEGIN SUB-BLOCK
EXECUTE IMMEDIATE 'ALTER INDEX '||cur_row.owner||'.'||cur_row.index_name||' REBUILD ONLINE';
EXCEPTION WHEN OTHERS THEN NULL;
END; -- END SUB-BLOCK
END IF;
END LOOP;
END;
/

Here is what I get:

ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at line 46

However, when I do not use a sub-block, the exception is handled, but execution is terminated. So for example:

BEGIN
FOR cur_row IN c_rebuild_index LOOP
IF height > 5 THEN
EXECUTE IMMEDIATE 'ALTER INDEX '||cur_row.owner||'.'||cur_row.index_name||' REBUILD ONLINE';
END IF;
END LOOP;
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

Thanks,
Mark T.