Conditional compilation in Oracle PL/SQL Page - 2

Page 2 of 3

Author: Saurabh Gupta Published on: 28th Jun 2011

Conditional compilation in Oracle PL/SQL

2.3. Error directive

The error directive $ERROR $END handles the error situation in compilation conditions. If the program doesn’t fits for compilation in any of the database environments, its creation can be restricted by raising compilation errors. The program would be created in INVALID state and the compilation error would be held up with USER_ERRORS dictionary view, until it is resolved. It can be displayed using SHOW ERRORS command in SQL. Oracle associates a generic message number PLS-00179 with the compilation errors.

Example Syntax [3]

Sample Code

$ERROR varchar2_static_expression $END

3. DBMS_DB_VERSION

It is an Oracle supplied package, introduced in Oracle 10g Release 2, to hold the persistent and static constants to denote the database versions and its release number. The constants can be referenced externally to get information about the current version, current release, and applicable database version. For demonstration, See the table below shows the values of the constants as applicable to Oracle 11g release 2.

Values of the constants
Constant Attributes	Type	Value
VERSION	CONSTANT PLS_INTEGER	11
RELEASE	CONSTANT PLS_INTEGER	2
VER_LE_9_1	CONSTANT BOOLEAN	FALSE
VER_LE_9_2	CONSTANT BOOLEAN	FALSE
VER_LE_9	CONSTANT BOOLEAN	FALSE
VER_LE_10_1	CONSTANT BOOLEAN	FALSE
VER_LE_10_2	CONSTANT BOOLEAN	FALSE
VER_LE_10	CONSTANT BOOLEAN	FALSE
VER_LE_11_1	CONSTANT BOOLEAN	TRUE
VER_LE_11	CONSTANT BOOLEAN	TRUE

4. Illustration of Conditional compilation

We take up the previous scenario where a support team maintains a testing database in 10g version and development database in 11g version. A new customization requires a new procedure creation.

Result caching feature was introduced in Oracle 11g release. To make use of the feature, programmers design a single procedure and impose conditions upon the compiler to evaluate selected code applicable for their database version.

A function P_GET_SAL has been created which returns the salary of an employee. For 11g environment, it must declare a cursor with result cache feature. On the other hand, for 10g environment, the cursor should be a normal one. Check the compilation conditions in the below function code. It uses DBMS_DB_VERSION constant to get the database version.

Sample Code

CREATE OR REPLACE FUNCTION P_GET_SAL (P_EMPID NUMBER)
RETURN NUMBER
IS
L_SAL NUMBER;
$IF DBMS_DB_VERSION.VERSION > 11 $THEN
CURSOR C IS
SELECT /*+ RESULT_CACHE */ SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID;
$ELSE
CURSOR C IS
SELECT SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID;
$END
BEGIN
OPEN C;
FETCH C INTO L_SAL;
CLOSE C;
RETURN L_SAL;
END;
/
FUNCTION created.

Testing the object level compilation parameters – Object level compilation flags can be set to read important information about the object. They are used by the inquiry directives within the compilation conditions.

Before that, you will need to check current setting of compilation flags.

Sample Code

SQL> SELECT PLSQL_CCFLAGS FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = P_GET_SAL;
PLSQL_CCFLAGS
-----------------------

Include a compilation flag TESTING in the session and recompile the function P_GET_SAL using the session compilation flags.

Sample Code

SQL> ALTER SESSION SET PLSQL_CCFLAGS = TESTING:TRUE;

From now onwards, all the objects created in the current session would inherit the same compilation flags. I shall create a function F_GETSAL_USING_COMP_PARAM, which uses the TESTING flag. Note the usage of inquiry directive.

Sample Code

SQL> CREATE FUNCTION F_GETSAL_USING_COMP_PARAM(P_EMPID NUMBER)
2 RETURN NUMBER
3 IS
4 L_SAL NUMBER;
5 $IF $$TESTING $THEN
6 CURSOR C IS
7 SELECT /*+ RESULT_CACHE */ SALARY
8 FROM EMPLOYEES
9 WHERE EMPLOYEE_ID = P_EMPID;
10 $ELSE
11 CURSOR C IS
12 SELECT SALARY
13 FROM EMPLOYEES
14 WHERE EMPLOYEE_ID = P_EMPID;
15 $END
16 BEGIN
17 OPEN C;
18 FETCH C INTO L_SAL;
19 CLOSE C;
20 RETURN L_SAL;
21* END;
FUNCTION created.

Inquiry directives can even make use of static packaged variables while defining the compilation conditions. I shall create a package PKG_COND_COMPILE to declare the statics constants.

Sample Code

CREATE OR REPLACE PACKAGE PKG_COND_COMPILE IS
$IF DBMS_DB_VERSION.VERSION >= 11 $THEN
PARAM_TESTING CONSTANT BOOLEAN := TRUE;
$ELSE
PARAM_TESTING CONSTANT BOOLEAN := FALSE;
$END
END;

Note that the packaged variable must be a static constant, else it would raise exception ‘PLS-00174: a static boolean expression must be used’. Refer the below function which uses the package variables.

Sample Code

CREATE OR REPLACE FUNCTION P_GET_SAL (P_EMPID NUMBER)
RETURN NUMBER
IS
L_SAL NUMBER;
$IF PKG_COND_COMPILE.PARAM_TESTING $THEN
CURSOR C IS
SELECT /*+ RESULT_CACHE */ SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID;
$ELSE
CURSOR C IS
SELECT SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMPID;
$END
BEGIN
OPEN C;
FETCH C INTO L_SAL;
CLOSE C;
RETURN L_SAL;
END;
/
FUNCTION created.

Demonstrating error directive: An error number can be included under compilation flags using ALTER SESSION command as below.

Sample Code

SQL> ALTER SESSION SET PLSQL_CCFLAGS = 'TESTING:TRUE,DEV:FALSE,ERR:83949';
Session altered.

The function below F_CHECK_ERR checks the compilation flag TESTING to return 1 when it is true. If its creation has to be restricted in all other databases, it must raise compilation error upon execution. Check the function below.

Sample Code

CREATE OR REPLACE FUNCTION F_CHECK_ERR
RETURN NUMBER
IS
BEGIN
$IF $$TESTING $THEN
RETURN 1;
$ELSE
$ERROR 'This is a Development database:'||$$ERR $END
RETURN 0;
$END
END;
/
FUNCTION created.

Now we execute the function in a SELECT statement.

Sample Code

SQL> SELECT F_CHECK_ERR FROM DUAL;
F_CHECK_ERR
-----------
1

Now, Let's modify the value of TESTING flag from TRUE to FALSE and recompile the above function to synchronize with the changes. Check, what happens upon compilation.

Ads

Sample Code