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
begin
  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');
      else
        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');
    else
      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.