Tuesday, March 19, 2013


I've been scratching my eyes out lately trying to reverse engineer some lots of PL/SQL.

One thing I've seen a lot of is calls to dbms_output.put_line. Fortunately, I've seen some dbms_application_info.set_module and other system calls too. But back to that first one.

1. When I used dbms_output, I would typically only use it in development. Once done, I would remove all calls to it, test and promote to QA. It would never survive the trip to production.
2. Typically, when I used it in development, I would tire of typing out d b m s _ o u t p u t . p u t _ l i n e so I would either a, create a standalone procedure or create a private procedure inside the package, something like this (standalone version).
  dbms_output.put_line( p_text );
END p;
Easy. Then, in the code, I would simply use the procedure p all over the place...like this:
  l_start_time date;
  l_end_time date;
  l_start_time := sysdate;
  p( 'l_start_time: ' || l_start_time );

  --do some stuff here
  --maybe add some more calls to p

  l_end_time := sysdate;
  p( 'l_end_time: ' || l_start_time );

Since the procedure is 84 characters long, I only have to use the p function 4 times to get the benefit. Yay for me...I think. Wait, I like typing.


Steve said...

You also forgot to mention that when moving to production, you only need to comment out one line of code and replace with a simple null; (in the P procedure)!

Stew said...

Along these lines, I stole something like this from Steven Feuerstein and created a packaged function debug_log.print(). Then I can turn the output on/off with a call to a package function (debug_log.dbg() or debug_log.nodbg()). It defaults to being turned off.

Then in my Oracle IDE of choice (PL/SQL Developer), I created a code template with a shortcut of "pr". To add it to my code, I just type pr, then Ctrl-J and voila! (I'm good at typing but don't like to when I don't need to.)

p.s. It's nice to see you have fans from Zaire! :-)

p.p.s. I have a gallon of this year's Vermont's finest with your name on it in our deep freeze, if your offer is still open???

oraclenerd said...


I've used the packaged variety myself. The past few years though, I haven't done a whole lot of PL/SQL so didn't mention it here. You're right though, lots of good ways to go.

re: p.s.
Zaire! Yeah, the Google/Blogspot spam filters aren't what they used to be. I've probably not turned something on.

re: p.p.s.


Unknown said...

First, stop using standalone procedures. Always use packages from the jump. Actually there isn't a second. (hugs)

Unknown said...

First, stop using standalone procedures. Always use packages from the jump. Actually there isn't a second. (hugs)