Oracle Illustrated


February 21, 2010  6:29 AM

Migrating from 9i to 11g – Conditional compilation



Posted by: Lakshmi Venkatesh

This feature is available from : Oracle 10g

This feature enables you to customize your PL/SQL code.

Major advantages –
(1)Helps to enable debugging in development environment and switch it off in LIVE environment
(2)Helps to utilize the latest functionality with the latest DB release and disable the new features against the older release. This enables to compile the code in older versions of Oracle even with the new feature in place ! The benefit is that one source code with version specific new feature can be applied to any database version.

How does it work? -
It is implemented by setting compiler / directive commands in the source code. Following are the directives used
A) Selection directives
Use the $IF directive to evaluate expressions and determine which code should be included or avoided.

B) Inquiry directives
Use the $$identifier syntax to refer to conditional compilation flags. These inquiry directives can be referenced within an $IF directive or used
independently in your code.

C) Error directives
Use the $ERROR directive to report compilation errors based on conditions evaluated when the preprocessor prepares your code for compilation.

Reference : http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#BABIHIHF

Example – Using Conditional compilation selection directives

Evaluates static expression to determine which text should be included in the compilation.

Form of selection directive –

$IF boolean_static_expression $THEN text
[ $ELSIF boolean_static_expression $THEN text ]
[ $ELSE text ]
$END

Example :

set serveroutput on size 100000
BEGIN
$IF DBMS_DB_VERSION.VER_LE_9_2 $THEN
DBMS_OUTPUT.PUT_LINE( ‘Dummy message – this code will not work in this db release’);
$ELSE
DBMS_OUTPUT.PUT_LINE (‘Release ‘ || DBMS_DB_VERSION.VERSION || ‘.’ ||
DBMS_DB_VERSION.RELEASE || ‘ is supported.’);
COMMIT;
$END
END;
/

Oracle 10g

Dummy message – this code will not work in this db release
PL/SQL procedure successfully completed.

Oracle 11g

Release 11.1 is supported.
PL/SQL procedure successfully completed.

Or we can write something like this -
set serveroutput on size 100000
BEGIN
$IF DBMS_DB_VERSION.VER_LE_9_2 $THEN
— use this code
$ELSE
— use this code
$END
END;
/

Example – Using conditional compilation Inquiry directives

Inquiry directives enables to use values set against PLSQL_CCFLAGS. Form is

inquiry_directive ::$$id

Example 1 – Debug

ALTER SESSION SET PLSQL_CCFLAGS = ‘debug_flag:true, trace_level_val:10′;

SET SERVEROUTPUT ON SIZE 10000

BEGIN
$IF $$debug_flag AND $$trace_level_val >= 10
$THEN
DBMS_OUTPUT.PUT_LINE (‘The tracing level is set to 10 or higher’);
$END
NULL;
END;
/

Oracle 10g
The tracing level is set to 10 or higher

PL/SQL procedure successfully completed.

Oracle 11g
The tracing level is set to 10 or higher

PL/SQL procedure successfully completed.

Example 2 – Setting a Common value that can be used across all programs

ALTER SESSION SET PLSQL_CCFLAGS = ‘debug_flag:true, trace_level_val:10, max_days:100′;

Note: The above example is to show how to maintain multiple values and use certain values for certain programs.

DECLARE
v_days number := 200;
BEGIN
IF v_days >= $$max_days THEN
DBMS_OUTPUT.PUT_LINE(‘The value of v_days ‘ || v_days ||’ greater than ‘ || $$max_days);
ELSE
DBMS_OUTPUT.PUT_LINE(‘The value of v_days ‘ || v_days ||’ lesser than ‘ || $$max_days);
END IF;
END;
/

Oracle 10g
The value of v_days 200 greater than 100

PL/SQL procedure successfully completed.

Oracle 11g
The value of v_days 200 greater than 100

PL/SQL procedure successfully completed.

Note: In the above example we did not use $IF / $THEN or $END because, we are not using BOOLEAN expression.

The following information (corresponding to the values in the USER_PLSQL_OBJECT_SETTINGS data dictionary view) is available via inquiry directives:

$$PLSQL_DEBUG - Debug setting for this compilation unit
$$PLSQL_OPTIMIZE_LEVEL - Optimization level for this compilation unit
$$PLSQL_CODE_TYPE - Compilation mode for the unit
$$PLSQL_WARNINGS - Compilation warnings setting for this compilation unit
$$NLS_LENGTH_SEMANTICS - Value set for the NLS length semantics

Example – Using conditional compilation Error directives

$ERROR raises user defined error. Form is

$ERROR ” $END

set serveroutput on size 100000
BEGIN
$IF DBMS_DB_VERSION.VER_LE_9_2 $THEN
$ERROR ‘Dummy message – this code will not work in this db release’ $END
$ELSE
DBMS_OUTPUT.PUT_LINE (‘Release ‘ || DBMS_DB_VERSION.VERSION || ‘.’ ||
DBMS_DB_VERSION.RELEASE || ‘ is supported.’);
COMMIT;
$END
END;
/

Oracle 10g
$ERROR ‘Dummy message – this code will not work in this db release’ $END
*
ERROR at line 3:
ORA-06550: line 3, column 3:
PLS-00179: $ERROR: Dummy message – this code will not work in this db release

Oracle 11g
Release 11.1 is supported.

PL/SQL procedure successfully completed.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: