Re: Job starting problem

Wojtek Wawrzyniak wrote:
> > What does the view USER_JOBS tell you about this job? Is it
broken?
> > What are the values for NEXT_DATE and NEXT_SEC? Have you tried
> > increasing job_queue_processes?
> >
> >
> > David Fitzjarrell
> >
> I notice that just after restart database all existing jobs are
executed,
> but on the NEXT_DATE again nothing.. (after that BROKEN=N, the
FAILURES=0,
> NEXT_DATE / NEXT_SEC and LAST_DATE / LAST_SEC are set correctly,
> but does not change anymore)..
> Normally when I create new job (of course with commit) it has:
> BROKEN=N, the FAILURES=NULL, NEXT_DATE / NEXT_SEC points to the same
date as
> when job was created, for examle yesterday created job has
'2005-02-16
> 10:27:56' / '10:27:56' and this values does not change, fields
LAST_DATE /
> LAST_SEC are empty..
> The job_queue_processes param was set to 3,4 and 5 -does nothing..
>
> Wojtek Wawrzyniak

Apparently you are not explicitly setting your interval for your jobs. You need to do so, to allow them to reschedule themselves. An example is shown below:

declare

	--
	-- Job number
	--
	-- Returned by DBMS_JOB.SUBMIT()
	--
	jobid	number;
	start_dt varchar2(20);
begin
	--
	-- Obtain the next days date
	--
	select to_char(sysdate + 1, 'YYYY-MM-DD:')
	into start_dt
	from dual;
	--
	-- Add the time, which will be just past
	-- midnight
	--
	start_dt := start_dt||'00:01:00';
	--
	-- Submit the job to the queue
	--
	-- Supply the fraction of a day to shift
	-- SYSDATE to tomorrow, just past midnight
	--
	dbms_job.submit(jobid, 'vm_notify;',
	                to_date(start_dt, 'YYYY-MM-DD:HH24:MI:SS'),
			'sysdate + 1');
        --
        -- Commit the job
        --

        commit;

	--
	-- Report the job number returned by DBMS_JOB.SUBMIT()
	--
	dbms_output.put_line('Job  number: '||jobid);

end;
/

Note the submission of the interval, the last string in the parameter list.

David Fitzjarrell Received on Thu Feb 17 2005 - 10:00:22 CST