Inserting DBMS_OUTPUT from wrapped procedures into a table

This tip is courtesy +V. Kapoor from http://www.foxinfotech.in. Thank you, you saved my day.

I had a situation where the database procedure SRW is wrapped and I needed to log the dbms_output.put_line output produced by the package into a table. Thanks to the tip provided, I was able to do so.

I had to call the following procedure

dbms_output.get_lines(vcol, n);

This procedure is intended to retrieve an array of lines from buffer. This means if the buffer is not cleared it will be available in the array (out parameter 1). The second parameter is the number of lines in array returned by the procedure.

To declare the OUT variables required by this procedure do the following:

n number;
vcol dbms_output.chararr;

Simply put together the code will be as follows:

DECLARE

   n      NUMBER := 100;

   vcol   DBMS_OUTPUT.chararr;

BEGIN

   srw.start_debugging;

   srw.run_report(...);

   srw.stop_debugging;

   --- get the output into vcol array

   DBMS_OUTPUT.get_lines (vcol, n);

   FOR i IN 1 .. n LOOP

      INSERT INTO log_table (char_col, procname, log_date) VALUES (vcol (i), USER, SYSDATE);

   END LOOP;

   COMMIT;

END;