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;