I’ve just hit (for the nth time) the limit in DBMS_OUTPUT.PUT_LINE that you can only have 1000000 characters in the output.  I know that that sounds a lot, but if you’re debugging complex workflows (with lots written out to the screen), and processing multiple flows in the same transaction, then this can easily be blown.  I know that in 10g, you can have an unlimited DBMS_OUTPUT buffer, but not everyone is using that version, or even using SQL*Plus.

So I “googled” (yes, it’s a verb!) the error that I was getting thrown, and (as ever) found this thread on AskTom.

Tom has provided a new PL/SQL package which replicates pretty much what DBMS_OUTPUT does by storing it in a PL/SQL table, and then has a view on top of it to retrieve (and delete) the data from the table.  You can then just select from the view to get the output back.

I’ve copied the code into the attached SQL file, just for ease:

DBMS_OUTPUT alternative

Enjoy!

Matt

This entry was posted on Saturday, October 11th, 2008 at 3:21 pm and is filed under Oracle, Technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.