Log DBMS_OUTPUT.Put_Line Output Into Table In Oracle With DBMS_OUTPUT.Get_Lines

by
Dbms_Output.Put_Line is a good way to debug PLSQL code easily by getting print the required values to track where the things are going wrong. But if you want to log that print data into a table to analyze it any time, you can do this with Dbms_Output.Get_Lines procedure.
dbms_output.put_line output to table with dbms_output.get_lines

The example is given below to log Dbms_Output.Put_Line print values into a table in Oracle. Below is the structure of the table which is used in this example named "outputlog" or you can create your own as per your requirement.

CREATE TABLE OUTPUTLOG
(
  CHAR_COL  VARCHAR2(1000 BYTE),
  PROCNAME  VARCHAR2(100 BYTE),
  LOG_DATE  DATE
);
And the following is the PLSQL anonymous block example to log the output:
DECLARE
   n      NUMBER := 100;
   vcol   DBMS_OUTPUT.chararr;
BEGIN
   DBMS_OUTPUT.enable (100000);
   --- do something here
   DBMS_OUTPUT.put_line ('first line');
   --- do somthing here 
   DBMS_OUTPUT.put_line ('second line');
   --- do something here
   DBMS_OUTPUT.put_line ('third line');
   --- get the output into vcol array
   DBMS_OUTPUT.get_lines (vcol, n);
   FOR i IN 1 .. n
   LOOP
      INSERT INTO outputlog (char_col, procname, log_date)
          VALUES (vcol (i), 'anonymous', SYSDATE);
   END LOOP;
   COMMIT;
END;
Note I assing the value of 100 to n variable and the number of output lines are only 3 so it will loop and record only 3 times because it is having only 3 rows in output, but if the output buffer is having rows more than 100 than it will log only 100 rows, so adjust this variable value accordingly.

Leave a Comment