Tuesday, March 19, 2013

dbms_output.put_line

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).
CREATE OR REPLACE
PROCEDURE p( p_text IN VARCHAR2 ) 
IS
BEGIN
  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;
begin
  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 );

end;
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.

5 comments:

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...

@Stew

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.

Absolutely!

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)