Posts

Showing posts from 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 …