PL/SQL Execute immediate exception handling inside for loop

Question

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;

score 1 · Answer 1 · answered Sep 08 '17 at 18:24

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;

PL/SQL Execute immediate exception handling inside for loop

1 Answers1