Wednesday, March 12, 2008


Always check out the original article at for latest comments, fixes and updates.

Anyone who needs a multi-line version of DBMS_OUTPUT.PUT_LINE?

This is one of my favorite swiss knife utilities, a handy procedure called PUT_LINES that allows you to forget the limitation of 255 characters per line of DBMS_OUTPUT.PUT_LINE.

Download put_lines.sql source file.

The idea is to split a multi-line source string (p_str) into chunks of the specified maximum length (p_chunk), possibly at any existing newline (p_newline) or at any breaking character (p_breaks) like spaces (default), commas, semicolons, etc., whichever comes last.
A hard break at the chunk length will occur only if no suitable breaking character has been found in the second half of each line, but you can make this threshold bigger or smaller as desired (p_threshold).

In other simple terms, i am trying to split the string in a user-friendly format.

For the sake of simplicity, I've embedded a private function called RIGHTMOST as comments in the declaration section of PUT_LINES.
You can either make this function independent if you deem that it could be useful in other situations (as i think) or simply uncomment it to keep it private and compile PUT_LINES without external references.


Nigel said...

I use something very similar - but don't forget that DBMS_OUTUT.PUT_LINE handles strings up to 32k from version 10gR2 (see PSOUG

Byte64 said...

Thanks for pointing this out Nigel,
however i like to have something that works on every oracle version from 8i upwards, i still a few customers running on top of 8.1.7 and 9.2.


Jornica said...

Another alternative for dbms_output.put_line is myputline written by Steven Feuerstein. See the Oracle Magazine article Tracing lines for a link to the source.

With kind regards,


yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles