Saturday, September 12, 2009

how to find locked row

CREATE OR REPLACE FUNCTION find_locked_rows (v_rowid ROWID, table_name VARCHAR2)
RETURN ROWID IS
x NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'Begin Select 1 into :x from '
|| table_name
|| '
where rowid =:v_rowid for update nowait; Exception
When Others Then :x:=null; End;'
USING OUT x, v_rowid;

ROLLBACK;

IF x = 1 THEN
RETURN v_rowid;
ELSIF x IS NULL THEN
RETURN NULL;
END IF;
END;

No comments: