4

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
user3463885
  • 99
  • 1
  • 4
  • 9
  • "Basically I want SQL query to run every 1 minute and once count equals 8 it should stop and move ahead...." What SQL? Count of what? What if it never exactly equals 8 but goes above 8 (i.e. jumps from 7 to 9+)? What should it move ahead to do? – Anthony Grist Jun 01 '15 at 11:10
  • Hi Anthony, Count will be of ID and count will not exceed 8. I have some if blocks with jUnit assertions so I want to do assertions only when count reaches 8. – user3463885 Jun 01 '15 at 15:41

1 Answers1

4

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Thank you Lalit. Actually this is part of the test so I won't be allowed to create a procedure.. – user3463885 Jun 01 '15 at 11:23
  • @user3463885 No problem, create an anonymous PL/SQL block, thus you do not create any database object, you just execute the logic you want. – Lalit Kumar B Jun 01 '15 at 11:31