Edit D:\app\Administrator\product\11.2.0\dbhome_1\apex\images\doc\advnc_backplsql.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <title>Using a Process to Implement Background PL/SQL</title> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1" /> <meta name="date" content="2009-04-14T16:11:32Z" /> <meta name="robots" content="noarchive" /> <meta name="doctitle" content="Using a Process to Implement Background PL/SQL" /> <meta name="relnum" content="Release 3.2" /> <meta name="partnum" content="E11947-01" /> <link rel="copyright" href="dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="stylesheet" href="dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" /> <link rel="contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="up" href="preface.htm" title="Home" type="text/html" /> <link rel="up" href="advnc.htm" title="Advanced Programming Techni..." type="text/html" /> <link rel="up" href="advnc_plsql.htm" title="Running Background PL/SQL" type="text/html" /> <link rel="up" href="advnc_backplsql.htm" title="Using a Process to Implemen..." type="text/html" /> <link rel="prev" href="advnc_sys_updates.htm" title="Previous" type="text/html" /> <link rel="next" href="advnc_web_services.htm" title="Next" type="text/html" /> </head> <body> <p id="BREADCRUMBING"><a href="preface.htm" title="Home">Home</a> > <a href="advnc.htm" title="Advanced Programming Techni...">Advanced Programming Techni...</a> > <a href="advnc_plsql.htm" title="Running Background PL/SQL">Running Background PL/SQL</a> > Using a Process to Implemen...</p> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr valign="bottom"> <td align="left"></td> <td align="center"><a href="advnc_sys_updates.htm"><img src="dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="advnc_web_services.htm"><img src="dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> <p><a id="BABHIHBG" name="BABHIHBG"></a></p> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h1><a name="HTMDB25881" id="HTMDB25881"></a>Using a Process to Implement Background <a id="sthref2245" name="sthref2245"></a><a id="sthref2246" name="sthref2246"></a>PL/SQL</h1> <p>The following example runs a PL/SQL job in the background for testing and explanation:</p> <pre xml:space="preserve" class="oac_no_warn"> 001 BEGIN 002 FOR i IN 1 .. 100 LOOP 003 INSERT INTO emp(a,b) VALUES (:APP_JOB,i); 004 IF MOD(i,10) = 0 THEN 005 APEX_PLSQL_JOB.UPDATE_JOB_STATUS( 006 P_JOB => :APP_JOB, 007 P_STATUS => i || 'rows inserted'); 008 END IF; 009 APEX_UTIL.PAUSE(2); 010 END LOOP; 011 END; </pre> <p>In this example, note that:</p> <ul> <li> <p>Lines 002 to 010 run a loop that inserts 100 records into the <code>emp</code> table.</p> </li> <li> <p><code>APP_JOB</code> is referenced as a bind variable inside the VALUES clause of the INSERT, and specified as the <code>P_JOB</code> parameter value in the call to <code>UPDATE_JOB_STATUS</code>.</p> </li> <li> <p><code>APP_JOB</code> represents the job number which will be assigned to this process as it is submitted to <code>APEX_PLSQL_JOB</code>. By specifying this reserved item inside your process code, it will be replaced for you at execution time with the actual job number.</p> </li> <li> <p>Note that this example calls to <code>UPDATE_JOB_STATUS</code> every ten records, INSIDE the block of code. Normally, Oracle transaction rules dictate updates made inside code blocks will not be seen until the entire transaction is committed. The <code>APEX_PLSQL_JOB.UPDATE_JOB_STATUS</code> procedure, however, has been implemented in such a way that the update will happen regardless of whether the job succeeds or fails. This last point is important for two reasons:</p> <ol> <li> <p>Even if your status shows "100 rows inserted," it does not mean the entire operation was successful. If an error occurred at the time the block of code tried to commit, the <code>user_status</code> column of <code>APEX_PLSQL_JOBS</code> would not be affected because status updates are committed separately.</p> </li> <li> <p>Updates are performed autonomously. You can view the job status before the job has completed. This gives you the ability to display status text about ongoing operations in the background as they are happening.</p> </li> </ol> </li> </ul> </div> <!-- class="sect2" --> <!-- class="sect1" --> <!-- Start Footer --> <div class="footer"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="86%" /> <col width="*" /> <tr> <td align="left"><span class="copyrightlogo">Copyright © 2003, 2009, Oracle and/or its affiliates. All rights reserved.</span><br /> <a href="dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td> <td align="center"><a href="advnc_sys_updates.htm"><img src="dcommon/gifs/leftnav.gif" alt="Previous" /><br /> <span class="icon">Previous</span></a> </td> <td align="center"><a href="advnc_web_services.htm"><img src="dcommon/gifs/rightnav.gif" alt="Next" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </div> <!-- class="footer" --> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de