Re: Flushing the dbms_output.put

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: Fri, 07 Apr 2000 18:53:05 GMT
Message-ID: <8claqa$8kl$1@nnrp1.deja.com>

That's a limitation of DBMS_OUTPUT. Flushing the buffer is not the same as displaying its contents...

There are several tricks like using UTL_FILE or DBMS_PIPE to monitor transaction progress, but what you should really be considering is using commits in your process. If you are performing 'piddly' updates and it is taking long enough for you to be attempting to monitor it, think of how much rollback you must be sucking up for your process! Also an error on the last record will cause all of the others to be rolled back.

The key is to process them in blocks (aka COMMIT every 1000 rows in your case) with a means by which you can determine where to restart where you left off in case of an error.

Be careful about COMMITing inside an open cursor loop, or you will get a SNAPSHOT TOO OLD error. Create a cursor that selects the first 1000 rows that have not been processed (based on some logic you determine). Open it, loop through it, close it, commit your changes, and reopen it (gets the next 1000) until all records are processed. Here's an example:

DECLARE
CURSOR c IS

    SELECT...
    FROM...
    WHERE...logic to select records that still needs to be processed

    AND ROWNUM < 1001
    FOR UPDATE;   bRecordsProcessed BOOLEAN;
BEGIN
  LOOP
    bRecordsProcessed := FALSE;
    FOR r IN c
    LOOP

      bRecordsProcessed := TRUE;
      -- process records here

    END LOOP;
    IF bRecordsProcessed THEN
      COMMIT;
    ELSE

      EXIT;
    END IF;
  END LOOP;

END; Monitor it from another session by selecting a count of the records remaining using the similar logic as you used in your cursor above. Remember that the count will decrease in multiples of 1000 since you will only see changes from another session after commits.

If you can't come up with some logic for determining whether a row has already been updated or not, you can add a processedflag to the table and flip it as the record gets processed, or you can create a driving (temporary) table with the PK of the table being updated and a processed flag and use that to keep track. Depends on your situation...

Hope that helps,
Michael J. Ort

In article <8cf3l0$cof$1_at_nnrp1.deja.com>, ekophyne_at_my-deja.com wrote:
> Hi
> I'm using Oracle7 Server Release 7.3.4.4.0 - Production
> With the distributed, replication and parallel query options
> PL/SQL Release 2.3.4.4.0 - Production on HP Unix.
>
> I'm using a CURSOR FOR loop in an anonymous block to perform some
> fiddly updates to a table. I'm keeping count of the number of
> iterations of the loop in a PLS_INTEGER variable.
>
> I'd like to use dbms_output.put_line to give me a loop count every
1000
> rows. However, I don't see any output until the whole process
finishes.
> That is to say, the output buffer is not flushed until the block
exits.
>
> I've tried putting the dbms_output statement inside its own BEGIN &
END
> block, but this has no apparent effect.
> I've also tried toggling dbms_output.enable/disable. (According to my
> Steve Feurstein book, dbms_output.disable should force a flush...) but
> this seems not to work either.
>
> Has anyone found a solution?
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 07 2000 - 13:53:05 CDT

Re: Flushing the dbms_output.put_line Buffer...