Systems Engineering and RDBMS

Archive for March 30th, 2007

Conditional Compilation in 10gR2

Posted by decipherinfosys on March 30, 2007

Conditional compilation is a new feature in 10gR2’s PL/SQL – it essentially makes the compiler ignore or recognize code. Let’s take an example first and then we will mention the advantages of this feature:

SQL> create or replace procedure test
2 as
3 begin
4 $IF $$debug_test $THEN
5 dbms_output.put_line( ‘My debug test code’ );
6 dbms_output.put_line( ‘is put right here.’ );
7 $END
8 dbms_output.put_line( ‘My actual code starts here’ );
9 end;
10 /
Procedure created.

Now, let’s execute it:

SQL> exec test
My actual code starts here
PL/SQL procedure successfully completed.

The debug code was completely ignored in the execution above. Now, by enabling the “debug_test” variable, we can enable that debug code – do note that debug_test is my naming, it doesn’t have to be called that, you can define your own variables.

SQL> alter procedure test compile
2 plsql_ccflags = ‘debug_test:true’ reuse settings;
Procedure altered.

SQL> exec test
My debug test code
is put right here.
PL/SQL procedure successfully completed.

This helps in code instrumentation by enabling or disabling the debug code at will. It also helps in writing code that can work across versions so if you want to use the same code but want to take advantage of some neat feature in version 10gR2, you can compile one set of code for say 9iR2 and the other one for 10gR2 – without a need for two code sets. This helps those vendor companies who provide solutions to their clients who can chose to implement the code in either version.

Posted in Oracle | 1 Comment »

Autotrace enhancement in 10gR2

Posted by decipherinfosys on March 30, 2007

In 10gR2, autotrace uses DBMS_XPLAN to display the explain plans.  In prior versions, one had to use the DBMX_XPLAN packae to get the same output.  This gives much more detailed information to the end users – especially important is the addition of the predicates at the bottom of the plan which clearly show the step at which Oracle’s optimizer is applying them.

Posted in Oracle | Leave a Comment »