Oracle 10g : PL/SQL : Conditional Compilation

If you are a pl/sql developer and if you looking for best practices on unit testing, this is for you.

Usually we run into situations where we need to debug some code, but we don't want to be running the debug code in production. Do we ???

Oracle 10g provides a beautiful feature - conditional compilation. (Like #ifdef in C).

Let's see an example.

Create a test procedure ...

create or replace procedure test_proc
as
begin
dbms_output.put_line( 'Debug 1' );
dbms_output.put_line( 'Debug 2' );
$IF $$debug_code $THEN
dbms_output.put_line( 'Debug 3' );
dbms_output.put_line( 'Debug 4' );
$END
dbms_output.put_line( 'Debug 5' );
end;

set echo on;
set serveroutput on;

Let us suppose that we are running this procedure on our dev/qa environment.

Then, you have to run the following command :
------------------------------------------------------------------------------------
SQL> alter procedure test_proc compile plsql_ccflags = 'debug_code:true' reuse settings;

Procedure altered

SQL>
------------------------------------------------------------------------------------

Now let's run the procedure and see what the output is ....
------------------------------------------------------------------------------------
SQL> exec test_proc;

Debug 1
Debug 2
Debug 3
Debug 4
Debug 5

PL/SQL procedure successfully completed

SQL>
------------------------------------------------------------------------------------

Let's suppose that we are all set to go to production and we need to deploy this procedure on production and we don't want all the debugging.

Then, execute the command :

------------------------------------------------------------------------------------
SQL> alter procedure test_proc compile plsql_ccflags = 'debug_code:false' reuse settings;

Procedure altered

SQL>
------------------------------------------------------------------------------------
That's it. Let's see the output of the procedure, if we run it on production.

------------------------------------------------------------------------------------
SQL> exec test_proc;

Debug 1
Debug 2
Debug 5

PL/SQL procedure successfully completed

SQL>
------------------------------------------------------------------------------------
So, on production when the debug is disabled, you might have noticed that the statements "Debug 3" and "Debug 4" did not print.

Plan B : If you don't want to pollute your code, you can you Log4PLSQL.

Long Live "Tom Kyte".

Good Luck !!

r-a-v-i