Conditional PL/SQL Compilation in Oracle 10g R2

santoshjoshi's picture

Often, we want our PL/SQL code to output messages (using DBMS_OUTPUT) to debug a problem. This is mostly required during development, but may also come in handy if a program misbehaves in production. If the testing of your application is thorough, then you should never have an issue in the production environment. But, we all know that there are instances where we do want debug messages even in the production environment. Before Oracle 10g R2, this could not be done dynamically.

Oracle 10gR2 introduces the cool new feature of conditional PL/SQL compilation.
Here is how it works:

We insert our code in -

If ...
   then 
 ...
elseif
 ...
else
  ...
end if

as usual except you put a dollar sign ($) in front of the if, then, else and end statements. (Notice that we do not put the "if" in the "end" or "else". Here is an example:

CREATE OR REPLACE PROCEDURE Cond_Plsql
IS
  CURSOR c1 IS 
    SELECT *
    FROM   emp;
  
BEGIN
  $If $$Debug_Flag
  $Then
    dbms_Output.Put_Line( 'Now fetching emp record' );
  $End
  
  OPEN c1;
  
  $If $$Debug_Flag
  $Then
    dbms_Output.Put_Line( 'Now updating emp salary' );
  $End

  -- Logic TO UPDATE emp Salary

  CLOSE c1;
  
  dbms_Output.Put_Line('Finished updating emp salary');
END;
/

Notice that we have used a system defined value called debug_flag. Since it is a system-defined value, $$ precedes it.

If we do not have the value of debug_flag set in production, the execution of this procedure will yield the following output:

SQL> exec Cond_Plsql
Finished updating emp salary

PL/SQL procedure successfully completed.

In the development environment (or in production when we have to analyse an issue), we can execute the following:

alter session set plsql_ccflags = 'debug_flag:true';

alter procedure Cond_Plsql compile;

When we execute the procedure, we get:

SQL> exec Cond_Plsql
Now fetching emp record
Now updating emp salary
Finished updating emp salary

PL/SQL procedure successfully completed.

Besides user defined variables, constants can be used too.
Also, there is also a list of predefined Inquiry Directives: PLSQL_LINE, PLSQL_UNIT, PLSQL_CCFLAGS, PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_NATIVE_LIBRARY_DIR, PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT, PLSQL_OPTIMIZE_LEVEL, PLSQL_WARNINGS, and NLS_LENGTH_SEMANTICS

Lets look at an example with constants.

CREATE OR REPLACE PACKAGE Environment_Settings
IS
  My_Raise_Percent  CONSTANT PLS_INTEGER := 10;
END;

SQL> /

Package created.

Now a procedure is written based on this constant:

create or replace procedure salary_readjust
as
begin
$IF environment_settings.my_raise_percent = 10
$THEN
dbms_output.put_line('Now giving a  raise of 10%');
$ELSIF environment_settings.my_raise_percent = 20
$THEN
dbms_output.put_line('Now giving a raise of 20%');
$ELSE
dbms_output.put_line('Raise other than 10 or 20%');
$END
end;
SQL> /

Procedure created

Whe it is executed, we get:

SQL> exec salary_readjust
Now giving a  raise of 10%

PL/SQL procedure successfully completed.

Now lets say that we want to increase the salary level to 20%,then you modify the package:

CREATE OR REPLACE PACKAGE Environment_Settings
IS
  My_Raise_Percent  CONSTANT PLS_INTEGER := 20;
END;

SQL> /

Package created.

Interestingly, if you now exec salary_readjust, it knows the value of 20 even though we have not recompiled salary_readjust

SQL> exec salary_readjust
Now giving a raise of 20%

PL/SQL procedure successfully completed.

This is because, when we change the environment_settings package, Oracle knows that salary_readjust is dependent on it and makes it invalid. When we exec salary_readjust, it actually recompiles salary_readjust to make it valid and then executes it.

Other uses of Conditional Compilation

Another useful application of conditional compilation one that is very handy to programmers. As happens to any real-world programmer, you might start on a logic but cannot complete it because you have to go out asap or there is another fire to fight or you have a hot date! In that case, you wouldn't want your code to be checked in and compiled since you are not sure of the logic. So you use the $error ... $end construct.

CREATE OR REPLACE PROCEDURE Cond_Plsql
IS
  CURSOR c1 IS 
    SELECT *
    FROM   emp;
BEGIN
  
BEGIN
  $If $$Debug_Flag
  $Then
    dbms_Output.Put_Line( 'Now fetching emp record' );
  
  
  $End
  OPEN c1;
  
  
 $If $$Debug_Flag
  $Then
    dbms_Output.Put_Line( 'Now updating emp salary' );
  
  
  $End
  -- logic to update emp salary
  -- new logic for BOSS
  
  -- logic to update emp salary
  -- new logic for BOSS
    -- logic to update emp salary
  -- new logic for BOSS
  -- logic to update emp salary
  -- new logic for BOSS
  
  -- new logic for BOSS
    -- new logic for BOSS
  -- new logic for BOSS
   IF (c1.ROLE = ‘Manager’)
      $Error 'This piece of code is not complete' $End
   END IF;
  
  CLOSE c1;
  
  dbms_Output.Put_Line('Finished updating emp salary');
END;
/

Warning: Procedure created with compilation errors.

SQL> sho errors
Errors for PROCEDURE SALARY_READJUST:

ERROR
-----------------------------------------------------------------
PLS-00179: $ERROR: This piece of code is not complete

The DBMS_PREPROCESSOR package

If you look at your conditional PL/SQL source code in DBA_SOURCE or ALL_SOURCE tables, you will see the conditional directives in the output. If you want to see how the code looks like when compiled, Oracle provides you with the package DBMS_PREPROCESSOR.
It has two main procedures:

DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE—Retrieves the postprocessed
source and then displays it using DBMS_OUTPUT.PUTLINE.
DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE—Returns the post-processed source as
either a single string or a collection of strings.

Both procedures take either three parameters (object_type, schema, name of object) or just a string that has the whole procedure or function.
Please look at Oracle Documentation for details.

Lets look at an example of each procedure to see the output of our salary_readjust procedure.

begin
DBMS_PREPROCESSOR.print_post_processed_source ('PROCEDURE',scott','salary_readjust');
end;

The output is:

SQL> /
procedure salary_readjust
as
begin
dbms_output.put_line('Now giving a  raise of 10%');
end;

PL/SQL procedure successfully completed.

And here an example of DBMS_PREPROCESSOR.get_post_processed_source. It uses a defined data type of SOURCE_LINES_T of the package

DECLARE
  V_SOURCE  DBMS_PREPROCESSOR.SOURCE_LINES_T;
  L_ROW     PLS_INTEGER;
BEGIN
  V_SOURCE := DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE('PROCEDURE','goldtest__master','salary_readjust');
  
  L_ROW := V_SOURCE.FIRST;
  
  WHILE (L_ROW IS NOT NULL ) LOOP
    DBMS_OUTPUT.PUT_LINE(LPAD(L_ROW,3)
                         ||' - '
                         ||RTRIM(V_SOURCE(L_ROW),CHR(10)));
    
    L_ROW := V_SOURCE.NEXT(L_ROW);
  END LOOP;
END;
/

The output is

SQL> /
1 - procedure salary_readjust
2 - as
3 - begin
4 -
5 -
6 - dbms_output.put_line('Now giving a  raise of 10%');
7 -
8 -
9 -
10 -
11 -
12 -
13 - end;

PL/SQL procedure successfully completed.

DBMS_DB_VERSION

Another use of the conditional PL/SQL is to process pieces of code depending on which oracle version it is. The DBMS_DB_VERSION would be useful for this. Here is an example of code that uses conditional PL/SQL based on DBMS_DB_VERSION.

CREATE OR REPLACE PROCEDURE TEST_10g AS
BEGIN
  $IF $$debug_flag $THEN
    $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN
      DBMS_OUTPUT.put_line ('This is Oracle version EARLIER than 10.1');
      -- put pre Oracle 10 processing here
    $ELSE
      DBMS_OUTPUT.put_line ('This is Oracle version LATER than 10.1');
      -- put post Oracle 10 processing here

    $END

  $ELSE
    NULL;
  $END
END TEST_10g;

Please note that DBMS_DB_VERSION, in conjunction with conditional compilations only works with Oracle 10g R2 upwards. Pre-oracle 10 versions do not support conditional PL/SQL compilation.

Conclusion

Conditional PL/SQL Compilation is a very useful feature introduced in Oracle 10g R2. It can be extended to execute certain piece of code depending on conditions. For example, which OS is being used, etc.

Comments

Hello Santosh,

Good article! Thanks a lot... I was really looking for something like this! This helped me a lot.

Thanks,
Ameya