Monday, 16 July 2012

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;