Markus Winand's Blog

Posts Tagged ‘pl/sql’

Pipelined Functions: Better Than DBMS_OUTPUT

In Portability on 2009-11-11 at 15:36

Every now and then I need some PL/SQL that prints something to the terminal. The traditional solution for this is DBMS_OUTPUT.PUT_LINE. If you used it before, you probably know that there are some obstacles:

  • Don’t forget to set serveroutput on
  • Don’t forget to set an appropriate buffer size (but there is still a absolute maximum)
  • Don’t wonder about missing blanks
  • No way to flush the output

In case you don’t know yet, the first three can be taken care of by issuing the following in SQL*Plus:

set serveroutput on size 1000000 format wrapped;

However, the absolute buffer size limit remains. The years have passed, and the limitations were accepted. Until I noticed that there is an alternative.