Weird DBMS_OUTPUT Behavior

I had to do some unit testing today. Before running the test, I determined the expected outputs. Unfortunately the actual output differed. Had to fail the test. Then I needed to debug the issue. I stuck some DBMS_OUTPUT statement in the middle of my package. However the output did not show up in the report.

Initially I thought maybe it was not my code running. I dropped the procedure and the report errored out. It was my code running. Then I thought it was the positioning of my DBMS_OUTPUT statements. Nope. My statements were not getting into the report. Very strange. I started to get spooked. I thought it might have something to do with the capitalization of the word DBMS_OUTPUT. Yeah, I know. Nonsense. No that was not the cause.

There was only one thing to do. Trace this output line by line until I know what is up. The report is written a little funny. There is a UNIX Korn shell script which calls a Perl script which executes a SQL script that makes a call to my procedure. The Perl script was somehow grabbing the output of the SQL*Plus execution. That's when I saw it. Only certain output was being captured and put into the report. You had to stick a sentinel value in the output to get it into the report. The rest was skipped over.

Order was restored. But for future reference, I might stick the output in a procedure. I can make the procedure put the sential value in the output. Then I can comment the heck out of this and explain the hack.