Check Your Subroutines - We are delivering our latest release to internal test today. Had a code review yesterday. Many issues were found. We are fixing the highest priority probl...
Weird DBMS_OUTPUT Behavior
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.