I’ve just hit (for the nth time) the limit in DBMS_OUTPUT.PUT_LINE that (prior to 10g) 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.

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.

Enjoy!

Matt
SQL script

This entry was posted on Wednesday, January 14th, 2009 at 1:17 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.