Procedure Buffer overflow

Question

I have following procedure which is filling up null values in a column. The procedure works fine if i have very small set of data. But data that i am targettign on is about 3 billions of record. Just having this script tested on 1 Million record threw these execptions.

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "DBNAME.PRBACKFILLI", line 39
ORA-06512: at line 2

After having a little bit digging, I realized that DBMS_OUTPUT.PUT_LINE prints output at the end of the procedure. Now the thing is we want debugging info, what should we do?

CREATE OR REPLACE PROCEDURE PRBACKFILL (str_dest IN VARCHAR2)  AS 
  CURSOR cr_pst_ IS
    select id, seq from TABLE_ where ID is null;

  TYPE t_id_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE t_seq_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  a_id   t_id_array;
  a_seq  t_seq_array;
  i_bulk_limit  NUMBER := 1000;
BEGIN
  OPEN cr_pst_;
  LOOP
    FETCH cr_pst_
    BULK COLLECT INTO a_id, a_seq LIMIT i_bulk_limit;


    FOR i IN 1..a_id.count LOOP
      a_id(i) := Floor(a_seq(i)/10000000000000);
    END LOOP;

    FORALL i IN 1 .. a_id.count
      UPDATE TABLE_
      SET ID = a_id(i)
      WHERE SEQ = a_seq(i);

      COMMIT;
      DBMS_OUTPUT.PUT_LINE ('COMMITED '||i_bulk_limit||' records');
    EXIT WHEN cr_pst_%NOTFOUND;
  END LOOP; -- main cursor loop
  CLOSE cr_pst_;

  DBMS_OUTPUT.PUT_LINE ('Backfill completed gracefully!');

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No more records to process');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('errno: '||TO_CHAR(SQLCODE)||' Msg: ' || SQLERRM);              
END PRBACKFILL;
.
/
sho err;

Answer 1

First off, you would normally not use DBMS_OUTPUT for logging. It would generally make far more sense to write the data to a log table particularly if your logging procedure was defined as an autonomous transaction so that you could monitor the log data while the procedure was running. DBMS_OUTPUT is only going to be displayed after the entire procedure has finished executing at which point it's generally somewhat pointless.

Related to that first point, relying on DBMS_OUTPUT to indicate to the caller that there has been some sort of exception is a very poor practice. At a minimum, you'd want to re-raise the exception that was thrown so that you'd get the error stack in order to debug the problem.

Second, when you enable output, you have to specify the size of the buffer that DBMS_OUTPUT can write to. It appears that you've declared the buffer to be 20,000 bytes which is the default if you simply

SQL> set serveroutput on;

You can change that by specifying a size but the maximum size is limited to 1,000,000 bytes

SQL> set serveroutput on size 1000000;

If you plan on updating 3 billion rows in 1000 row chunks, that's going to be far too small a buffer. You're going to generate more than 60 times that amount of data with your current code. If you are using 10.2 both on the client and on the server, you should be able to allocate an unlimited buffer

SQL> set serveroutput on size unlimited;

but that is not an option in earlier releases.

Finally, are you certain that you need to resort to PL/SQL in the first place? It appears that you could do this more efficiently by simply executing a single UPDATE

UPDATE table_
   SET id = floor( seq/ 10000000000000 )
 WHERE id is null;

That's much less code, much easier to read, and will be more efficient than the PL/SQL alternative. The only downside is that it requires that your UNDO tablespace be large enough to accommodate the UNDO that is generated but updating a single column from NULL to a non-NULL numeric value shouldn't generate that much UNDO.