Run oracle query (SQL) every 30 seconds and once count equals 8 it should proceed further and finish the job

Question

I was looking for a solution to my problem on the internet, however I could not find fit for purpose solution. Can some please help and guide me with some examples.

In Oracle em console I have a job that runs for at least for 1 hour and during the job running process it will update few tables in Oracle.

Basically I want SQL query to run every 1 minute and once count equals 8 it should stop and move ahead....

TABLE_NAME: TASK - This table will get updated during 1 hour processing

ID TYPE_ID VALUE
1  12      TEST1
2  13      TEST2
3  14      TEST3
4  15      TEST4
5  16      TEST5

Appreciate your help.

Answer 1

You could create a job and schedule it using DBMS_SCHEDULER. All your logic could reside in a PL/SQL procedure and then schedule the procedure to execute at desired intervals.

For example,

SQL> BEGIN
  2    DBMS_SCHEDULER.create_job (
  3      job_name        => 'test_job',
  4      job_type        => 'PLSQL_BLOCK',
  5      job_action      => 'BEGIN my_job_procedure; END;',
  6      start_date      => SYSTIMESTAMP,
  7      repeat_interval => 'freq=0; byminute=0; bysecond=30;',
  8      end_date        => NULL,
  9      enabled         => TRUE,
 10      comments        => 'Job defined entirely by the CREATE JOB procedure.');
 11  END;
 12  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT JOB_NAME, ENABLED FROM DBA_SCHEDULER_JOBS where job_name ='TEST_JOB'
  2  /

JOB_NAME                 ENABL
--------------------     -----
TEST_JOB                 TRUE

SQL>

The above job would start per your SYSTIMESTAMP, and then execute the procedure my_job_procedure every 30 seconds.

See more examples here.