DBMS_OUTPUT manipulation and filtering in SQL*Plus
Small script to filter and manipulate the DBMS_OUTPUT in PL/SQL from SQL*Plus
CLEAR SCREEN; SET LONG 1000000000 ; SET SERVEROUTPUT ON; VARIABLE outputclob CLOB ; VARIABLE maxlines NUMBER; --set the max lines EXECUTE :maxlines :=10; DECLARE --array to save the output lines into outtab dbms_output.chararr; outstr VARCHAR2(255); BEGIN -- initialize the output clob dbms_lob.createtemporary (:outputclob, TRUE); -- add some lines to the output buffer, added some "special" lines to filter on dbms_output.put_line('special 1'); dbms_output.put_line('nothing special 2'); dbms_output.put_line('special 3'); dbms_output.put_line('nothing special 4'); dbms_output.put_line('special 5'); dbms_output.put_line('nothing special 6'); --get the output lines into the local array dbms_output.get_lines(outtab, :maxlines);--maxlines is changed here if there are fewer rows -- note that this string will not be part of the output clob, set serveroutput on will pick it up dbms_output.put_line('maxlines after:'||:maxlines); --loop through the array and build up the output clob FOR i IN 1 .. :maxlines LOOP outstr := outtab(i); -- filter the output IF INSTR(outstr,'special') = 1 THEN --modify the output dbms_lob.writeappend(:outputclob, length(outstr||chr(10)||'my message'||chr(10)), outstr||chr(10)||'my message'||chr(10)); END IF; END LOOP; END; / print outputclob;