Trying to make a job to be executed after each 1 minute,but its not working?

Question

I have made this job,that should be executed in an interval of 1 minute,but it's not working. When I use execute dbms_job.run(2); it gets executed. printe is a procedure Please suggest!

BEGIN
    DBMS_JOB.SUBMIT  (
         job =>:job_no,
             WHAT=>'printe;',--Procedure 
             next_date=>sysdate+1/24*60,
             interval=>'sysdate+1/24*60'
          );
    commit;
END;

Electroencephalogram

How are you checking if the procedure is run? What does it do? – Vine

That interval looks wrong. If you want it to execute every minute, shouldn't the interval be 'SYSDATE + ((1 / 24) / 60)'? As specified I think it'll repeat every 60 hours. – Dehumanize

Answer 1

Try next_date = sysdate + (1/24/60) and interval = (1/24/60)...

Twoply

Answer 2

Here is a simple job.

SQL> create table log1 (ts timestamp)
  2  /

Table created.

SQL> create or replace procedure printe as
  2  begin
  3      insert into log1 values (systimestamp);
  4      commit;
  5  end;
  6  /

Procedure created.

SQL>

So the first thing is to submit it with both the start time and interval correctly specified. If you cannot remember how many minutes there are in a day (1440) it is a good idea to use brackets. Let's compare submitting the job with your date specifications ...

SQL> var job_no number
SQL> BEGIN
   2     DBMS_JOB.SUBMIT
   3      (
   4      job =>:job_no,
   5      WHAT=>'printe;',--Procedure
   6      next_date=>sysdate+1/24*60,
   7    interval=>'sysdate+1/24*60'
   8    );
   9    commit;
  10  END;
  11  /

 PL/SQL procedure successfully completed.

 SQL> print job_no

     JOB_NO
 ----------
         71

 SQL>

... with brackets to assert precedence ...

SQL> BEGIN
  2     DBMS_JOB.SUBMIT
  3      (
  4      job =>:job_no,
  5      WHAT=>'printe;',--Procedure
  6      next_date=>sysdate+1/(24*60),
  7    interval=>'sysdate+1/(24*60)'
  8    );
  9    commit;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> print job_no

    JOB_NO
----------
        72

SQL>

Clearly job 71 has not run and isn't going to run for some time yet:

SQL>    select job, what, last_date, next_date, interval
  2  from user_jobs
  3  where job in (71,72)
  4  /

   JOB WHAT         LAST_DATE            NEXT_DATE            INTERVAL
------ ------------ -------------------- -------------------- -----------------
    71 printe;                           05-MAY-2010 17:35:34 sysdate+1/24*60
    72 printe;      03-MAY-2010 05:44:42 03-MAY-2010 05:45:34 sysdate+1/(24*60)

SQL>

Monitoring job 72 ....

SQL> select * from log1
  2  /

TS
-------------------------------------------------------------------
03-MAY-10 05:43:39.250000
03-MAY-10 05:44:42.296000

SQL>

So, if this still isn't working for you, what should you be doing? The first thing is to check whether the database is configured to run jobs at all. You will need DBA access for this.

SQL> select value
  2  from v$parameter
  3  where name='job_queue_processes'
  4  /

VALUE
-------------------------
1000

SQL>

If I remember correctly, in Oracle 9i the default value for this parameter is 0. It needs to be set to some non-zero value for jobs to run.

And if that isn't the problem you need to check for error messages in the alert log. The background_dump_dest directory may also have some .trc files produced by a failing job.

Longhand

Along with job_queue_processes, there's also job_queue_interval, which is how often the database wakes up and looks for jobs to be run. You'll need it to be less than 60 (seconds). – Cockerel

I dont know what exactly the reason is but in my case, the job is running as expected for some time and then it just doesnt at the next minute. I am using oracle 9i and the job is scheduled to run every minute. – Hinze

@Hinze - really not sure what you're expecting me to do. It's hard enough to diagnose intermittent errors in my own system where I have full access. there's no chance of doing anything with some random remote system with no information. – Longhand

All I can say is, a job which runs every minute seems like a bad architectural decision. You probably need a constantly running background process which sleeps for a minute then wakes up and does something. Maybe it polls a message stack (table) and executes a subroutine, sort of a queuing solution. – Longhand