WHY THIS ORACLE DBMS_SCHEDULER JOB IS ONLY EXECUTED ONCE?

Question

I have an Oracle job defined as follows:

BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
    JOB_NAME => 'DBMS_JOB_RESYNC_REQUESTS',
    JOB_TYPE => 'PLSQL_BLOCK',
    JOB_ACTION => '
        BEGIN
          -- iterate over implicit cursor 
          FOR K IN (SELECT SCEN_ID
                      from TABLE1
                      WHERE STATUS = 2)
            LOOP           
              -- updating status and date
              UPDATE TABLE1
              SET STATUS = 3, LAST_UPDATED_DATE = SYSDATE
              WHERE SCEN_ID= K.SCEN_ID;
              
            END LOOP;
            COMMIT;
        END;',        
    START_DATE => systimestamp,
    REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=1;BYSECOND=0;',
    END_DATE => NULL,
    ENABLED => TRUE,
    COMMENTS => 'TESTING JOB'); 
END;

I have a table that is constantly being updating by an external application the STATUS column from 1 to 2 value, and I would like to create a job that change STATUS from 2 to 3 and also add the date when changes were made.

I don't know why my job runs only once since I have specified to run it every minute.

Am I doing something wrong?

Answer 1

You have an infinite loop in your code. Did you ever test the code?

Try this

BEGIN 
   
     UPDATE TABLE1
     SET STATUS = 3, 
     LAST_UPDATED_DATE = SYSDATE
     WHERE STATUS = 2;
          
     COMMIT;
END;