Re: Job queue Question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 07 Jul 1999 20:06:05 GMT
Message-ID: <3788b191.25002091@inet16.us.oracle.com>

On Wed, 07 Jul 1999 19:31:48 GMT, amerar_at_ci.chi.il.us wrote:

>
>
>
>Hello,
>
>I put a job into the job queue with this command:
>
>VARIABLE jobno number;
>begin
> DBMS_JOB.SUBMIT(:jobno,
> 'cash_man.water_pull;',
> sysdate,
> 'sysdate + 18/24');
> commit;
>end;
>/
>
>I want the job to run everyday at 6:00pm.......this is not happening.
>Can someone help me out with the interval??

Sure. From the spec of dbms_job

<quote>

• Parameters are: --
• JOB is the number of the job being executed.
• WHAT is the PL/SQL procedure to execute.
• The job must always be a single call to a procedure. The
• routine may take any number of hardcoded parameters.
• Special parameter values recognized are:
• job: an in parameter, the number of the current job
• next_date: in/out, the date of the next refresh
• broken: in/out, is the job broken. The IN values is FALSE.
• Always remember the trailing semicolon.
• Some legal values of WHAT (assuming the routines exist) are
• 'myproc( ''10-JAN-82'', next_date, broken);'
• 'scott.emppackage.give_raise( ''JENKINS'', 30000.00);'
• 'dbms_job.remove( job);'
• NEXT_DATE is the date at which the job will next be automatically run,
• assuming there are background processes attempting to run it.
• INTERVAL is a date function, evaluated immediately before the job starts
• executing. If the job completes successfully, this new date is placed
• in NEXT_DATE. INTERVAL is evaluated by plugging it into the statement
• select INTERVAL into next_date from dual;
• INTERVAL must evaluate to a time in the future. Legal intervals include
• 'sysdate + 7' -- execute once a week
• 'NEXT_DAY(sysdate,''TUESDAY'')' -- execute once every tuesday
• 'null' -- only execute once
• If INTERVAL evaluates to null and a job completes successfully, then
• the job is automatically deleted from the queue. </quote>

So basically, your jobs is set to run every 18 hours. What you want is...

VARIABLE jobno number;
begin
  DBMS_JOB.SUBMIT(:jobno,

                  'cash_man.water_pull;',
                   trunc( sysdate ) + 18/24,
                  'sysdate');

  commit;
end;
/

This schedules the job to run at 6PM today and everyday after at 6PM.

hope this helps,

chris.

>
>Please cc a copy to email....
>
>Thanks,
>
>Arthur
>amerar_at_ci.chi.il.us
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 07 1999 - 15:06:05 CDT