Re: Stored Procedure Help

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Tue, 18 Feb 2003 12:26:50 +0300
Message-ID: <b2suc9$2t2$1@babylon.agtel.net>

I would recommend avoiding commits inside a loop over cursor for a number of good reasons, all of which were discussed many times over. Instead, I'd recommend to use DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS() and V$SESSION_LONGOPS view for tracking the progress. It's pretty trivial:

DECLARE

  l_rindex      pls_integer := -1;
  l_slno         pls_integer;
  l_rowsdone number := 0;
  l_total         number;

BEGIN
  SELECT COUNT(1) INTO l_total
    FROM EMPLOYEES_DTL A, EMPLOYEES B

                           WHERE A.NO_EMP_NUSER3 IS NOT NULL
                           AND A.NO_EMP = B.NO_EMP
                           AND B.NM_LST_UPDT != 'EGATE';

• initialize new row in V$SESSION_LONGOPS DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS( rindex => l_rindex, slno => l_slno, op_name => 'My long operation', totalwork => l_total, units => 'rows');

 FOR c IN (<cursor>) LOOP

• now process rows ... ...
• and for each processed row do l_rowsdone := l_rowsdone + 1; DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS( rindex => l_rindex, slno => l_slno, sofar => l_rowsdone); END LOOP;

You will be able to track the progress by inspecting V$SESSION_LONGOPS like this:

SELECT
   SOFAR||' '||UNITS||' out of total '||TOTALWORK||' processed so far.' "Progress",    TO_CHAR((SOFAR/TOTALWORK)*100,'009D99')||'% done.' "Percentage"  FROM V$SESSION_LONGOPS
 WHERE OPNAME = 'My long operation';

from any session.

Corrections and additions welcome.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E4D1CC8.FBC2DEB6_at_exesolutions.com...


> Aaron Rouse wrote:

> 

> > Thanks, Andrew's select statement is working since I tested it outside

> > of the stored procedure.  However when running the SP it ends up

> > hanging just like my prior one did.  We are running 8i here, I believe

> > 8.1.7

> 

> Inside the cursor loop put the following code:

> 

> i := i+1;

> INSERT INTO audit_tab

> VALUES

> (TO_CHAR(i) || '-' || SAPORG_NEW);

> COMMIT;

> 

> Watch the audit table from another session. That will answer the question

> as to wheher it is running or hanging.

> 

> Daniel Morgan

> 

Received on Tue Feb 18 2003 - 03:26:50 CST