Sunday, February 26, 2012

What are these dollar signs doing in my code ?

I've had the good fortune, in my current role, to be doing a fair amount of PL/SQL development in 11gR2. Boy, it feels good to be actually using up-to-date versions. Being picky, we are on Apex 4.0, rather than 4.1 but that's not a biggie.

One item I've found myself using more and more are the $$PLSQL_UNIT and $$PLSQL_LINE inquiry directives. I think they are great for logging.

Here's a handy example

create or replace procedure fizz_buzz as
  for i in 1..15 loop
    if i/2 = trunc(i/2) then
      if i/3 = trunc(i/3) then
        dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Fizz Buzz');
        dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Fizz');
      end if;
    elsif i/3 = trunc(i/3) then
      dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Buzz');
      dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - '||i);
    end if;
  end loop;
end fizz_buzz;

When executed, you get a "poor man's trace" of the path the process takes through the logic.

SQL> exec fizz_buzz
FIZZ_BUZZ(13) - 1
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(13) - 5
FIZZ_BUZZ(6) - Fizz Buzz
FIZZ_BUZZ(13) - 7
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(13) - 11
FIZZ_BUZZ(6) - Fizz Buzz
FIZZ_BUZZ(13) - 13
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz

By automatically substituting in the actual program name (eg the package name) and the line, you don't have to worry that refactoring the code is going to mess up any debug or logging logic. Refactoring is a fancy name for taking chunks of code and moving them into specialised procedures and functions so you can reuse them. Most developers do refactoring but may not know the name of it, or even that it has a name. Mostly you only need to know the buzzwords at interviews.

So that is what those $$ directives are doing in my code.


Gert Poel said...

I use this also for logging.

But as we put everything in packages, it should return package name and procedure/function name instead of only the package name.

For what purpose did they leave out the procedure/function name?
Overloaded procedures?

And yes, you've got the package name/line number combination to figure out, but it's nicer to see the package procedure too.

Damir Vadas said...

Very interesting ... didn't know that until now.
Thank you for info.

Damir Vadas said...

I'm sorry to say that this feature exists from 10gR2 ...

Now it is more surprise to me ...

Simon Hoskins said...

Always knew there must be a way of doing this but never found it... only read this post as I recognised your name from Bristol Poly... Thanks.