Xref: alice comp.databases.oracle.server:56406
Path: alice!news-feed.fnsi.net!WCG!newsfeed.berkeley.edu!newsfeed.stanford.edu!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail
From: clbeck@us.oracle.com (Christopher Beck)
Newsgroups: comp.databases.oracle.server
Subject: Re: Job queue Question
Date: Wed, 07 Jul 1999 20:06:05 GMT
Organization: Oracle Corp.
Lines: 96
Message-ID: <3788b191.25002091@inet16.us.oracle.com>
References: <7m09v5$m3u$1@nnrp1.deja.com>
Reply-To: clbeck@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: inet16.us.oracle.com 931377998 19620 138.1.120.248 (7 Jul 1999 20:06:38 GMT)
X-Complaints-To: usenet@inet16.us.oracle.com
NNTP-Posting-Date: 7 Jul 1999 20:06:38 GMT
X-Newsreader: Forte Agent 1.5/32.451

On Wed, 07 Jul 1999 19:31:48 GMT, amerar@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@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@us.oracle.com
Reston, VA.
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation