chap12_2.html

Oracle资料大集合

</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>
<p>
<b><font size="+1">12.2	How do I匰ubmit scheduled jobs with DBMS_JOB?</font></b>

<p>
<B>Problem</B>
<p>
In our application, I have some processes that need to be run regularly to update data and perform calculations. The operating system can schedule jobs, but we want our database applications to be operating system independent. How do I schedule programs within Oracle?
<p>
<B>Technique</B>
<p>
The DBMS_JOB package allows stored procedures to be run periodically, providing a means to manage background batch processes. The DBMS_JOB package contains procedures to control job scheduling. Table 12.1 lists the procedures embodied in the DBMS_JOB package.

<p>

Table 12.3 Procedures in the DBMS_JOB package

<p>

<table bgcolor="cdcdcd" cellpadding="3 cellspacing="3">

<TR>
<td>Procedure</td>
<td>Description</td>
</TR>

<TR>
<td>ISUBMIT</td>
<td>Submits a new job with a specified job number.</td>
</TR>

<TR>
<td>SUBMIT</td>
<td>Submits a new job. Returns a unique job number.</td>
</TR>

<TR>
<td>REMOVE</td>
<td>Removes an existing job from the job queue.</td>
</TR>

<TR>
<td>CHANGE</td>
<td>Changes one or more job attributes.</td>
</TR>

<TR>
<td>WHAT</td>
<td>Changes the program on which the job is to run.</td>
</TR>

<TR>
<td>NEXT_DATE</td>
<td>Changes when an existing job is to be run.</td>
</TR>

<TR>
<td>INTERVAL</td>
<td>Changes the interval that the job runs.</td>
</TR>

<TR>
<td>BROKEN</td>
<td>Sets the broken flag. Terminates scheduling of a job.</td>
</TR>

<TR><td>RUN</td>
<td>Executes a job immediately (even if it is broken).</td></TR>

<TR>
<td>USER_EXPORT</td>
<td>Produces the text of a call to re-create the specified job.</td>
</TR>
</table>
<p>
The SUBMIT procedure is used to send a job to the queue along with parameters specifying how often it should run. Each job submitted to the queue is given a unique job number for identification. Alternatively, the ISUBMIT procedure is used to submit a job with a specified job number. To remove a job from the queue, use the REMOVE procedure, but a running job cannot be stopped with the REMOVE procedure.

<p>

The WHAT, NEXT_DATE, and INTERVAL procedures are used to alter a single characteristic of a job, while the CHANGE procedure allows changes to more than one job characteristic in a single call. The BROKEN procedure can be used to mark the job status as broken and the job will not be run again. You can use the RUN procedure in the DBMS_JOB package to force a job to run immediately (even a broken job). If the job completes successfully, the job status is marked as not broken. In order to mark the job as broken again, the BROKEN procedure has to be called explicitly. Jobs currently in the queue can be viewed by querying the USER_JOBS data dictionary view. Important columns of the USER_JOBS view are listed in Table 12.4.

<p>

Table 12.4 Important columns in the USER_OBJECTS view
<p>
<table cellpadding=3 cellspacing=3 border=0 bgcolor="cdcdcd">
<TR><td>Column</td>
<td>Description</td></TR>

<TR><td>JOB</td>
<td>Job number.</td></TR>

<TR><td>LOG_USER</td>
<td>USER who submitted the job.</td></TR>

<TR><td>PRIV_USER</td>
<td>USER whose privileges apply to the job.</td></TR>

<TR><td>SCHEMA_USER</td>
<td>Schema used.</td></TR>

<TR><td>LAST_DATE</td>
<td>Date when this job was last successfully run.</td></TR>

<TR><td>LAST_SEC</td>
<td>Same as LAST_DATE.</td></TR>

<TR><td>THIS_DATE</td>
<td>Date when this job started running.</td></TR>

<TR><td>THIS_SEC</td>
<td>Same as THIS_DATE.</td></TR>

<TR><td>NEXT_DATE</td>
<td>Date when the job will next run.</td></TR>

<TR><td>NEXT_SEC</td>
<td>Same as NEXT_DATE.</td></TR>

<TR><td>TOTAL_TIME</td>
<td>Seconds spent by the system running this job.</td></TR>

<TR><td>BROKEN</td>
<td>If Y, this job will not be run.</td></TR>

<TR><td>INTERVAL</td>
<td>A date function to calculate NEXT_DATE.</td></TR>

<TR><td>FAILURES</td>
<td>Number of times job failed after the last successful run.</td></TR>

<TR><td>WHAT</td>
<td>PL/SQL code that the job runs.</td></TR>
</table>
<p>

<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. CHP12_5.SQL, shown in <A HREF="image/12oht05.gif" TARGET="_blank">Figure 12.5</A>, creates a table and a stored procedure used in this How-To. The stored procedure is executed using the DBMS_JOB package and inserts a record into the table each time it runs.
<p>
The script creates a table containing a date column. The stored procedure created inserts a record with the date and time into the table each time it runs. Note the COMMIT statement at the end of the stored procedure, which enables you to determine when the stored procedure was last executed by querying the table.
<p>
<B>2.</B>	Run CHP12_6.SQL file in SQL*Plus. The PL/SQL code contained in the file schedules the stored procedure to run every other minute throughout the day. The results of the operation are shown in <A HREF="image/12oht06.gif" TARGET="_blank">Figure 12.6</A>.
<p>
Line 2 declares a variable to be passed the job number as an OUT parameter from the SUBMIT function. Lines 4 through 9 call the DBMS_JOB.SUBMIT procedure to schedule the procedure. The first parameter, shown in line 5, is an OUT parameter that returns the job number. Unique job numbers are generated from the SYS.JOBSEQ sequence. The second parameter (string for WHAT), shown in line 6, specifies the procedure scheduled to run. The third parameter (value for NEXT_DATE), shown in line 7, is the date and time at which the job will next run.
<p>
The value of the parameter is the current date and time, plus one minute. If the value of the parameter is before SYSDATE, the job will never begin running. The fourth parameter (string for INTERVAL), shown in line 8, specifies a date function to calculate the time for the next execution of the job. This date function is evaluated before the job starts running and each time thereafter when the job completes successfully. The new date and time is then used as a value for NEXT_DATE. To execute a job only once, use 憂ull