In the below PL/SQL code, TABLE_ONE holds table name tname , column name cname and rowid rid. The For loop fetches records from TABLE_ONE and updates column cname in table tname for the record with row id rid. But if the record to be updated in tname is locked then the for loop gets stuck and no further records from TABLE_ONE are processed. Ideally, the script to ignore the records for which update failed and proceed further. Please advise what could be the issue.
BEGIN
FOR c IN (SELECT * FROM TABLE_ONE a )
LOOP
DECLARE
TNAME varchar2(30);
CNAME varchar2(30);
RID ROWID;
X number;
updt_stmt varchar2(300);
BEGIN
BEGIN
TNAME := c.TNAME;
CNAME := c.CNAME;
RID := c.RID;
DBMS_OUTPUT.PUT_LINE( TNAME || '=>' || CNAME);
updt_stmt := 'UPDATE ' || TNAME || ' SET ' || CNAME || ' = ''123'' WHERE ROWID like ''%' || RID || '%''';
EXECUTE IMMEDIATE updt_stmt;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR');
END;
END;
END LOOP;
END;
Untested, but I think you could first try to lock the record with FOR UPDATE and by specifying NOWAIT cause a failure if some other transaction was active. You could then catch this exception and skip processing. Here is an untested example:
DECLARE
x ROWID;
resource_busy EXCEPTION;
PRAGMA EXCEPTION_INIT(resource_busy,
-00054);
BEGIN
FOR c IN (SELECT *
FROM table_one a)
LOOP
DECLARE
tname VARCHAR2(30);
cname VARCHAR2(30);
rid ROWID;
x NUMBER;
updt_stmt VARCHAR2(300);
BEGIN
BEGIN
tname := c.tname;
cname := c.cname;
rid := c.rid;
dbms_output.put_line(tname || '=>' || cname);
BEGIN
EXECUTE IMMEDIATE 'SELECT rowid FROM ' || tname ||
' WHERE rowid = :x FOR UPDATE NOWAIT'
INTO x
USING rid;
EXCEPTION
WHEN resource_busy THEN
dbms_output.put_line('Record locked; try again later.');
CONTINUE;
END;
updt_stmt := 'UPDATE ' || tname || ' SET ' || cname ||
' = ''123'' WHERE ROWID like ''%' || rid || '%''';
EXECUTE IMMEDIATE updt_stmt;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('ERROR');
END;
END;
END LOOP;
END;