Systems Engineering and RDBMS

DBMS_DB_VERSION

Posted by decipherinfosys on January 13, 2009

This is one of the supplied PL/SQL packages, which enables us to perform conditional compilation based on the oracle version. This allows us to take advantage of new features available in the newer versions and releases without maintaining the separate code base for each version. We already covered conditional compilation in one of our previous blog post.

Let us create a stored procedure and then execute it to see how it works.

CREATE OR REPLACE PROCEDURE test_proc
AS
BEGIN
$IF DBMS_DB_VERSION.ver_le_9 $THEN
dbms_output.put_line( ‘code for Ver. 9 and earlier’ );
$ELSIF  DBMS_DB_VERSION.ver_le_10_2 $THEN
dbms_output.put_line( ‘code for Ver. 10 Rel. 2 and earlier’ );
$END
END;
/

SQL> exec test_proc
code for Ver. 10 Rel. 2 and earlier

PL/SQL procedure successfully completed.

We are using 10g Release 2 and hence code executed the ver_le_10_2 condition. The package contains different constants for each version going back to ver_le_9 (version less than 9). For each version and each release there is separate constant.  One thing to remember though is if you are trying to use constant of newer version ver_le_11 in 10g, then it should be used after use of ver_le_10_2 otherwise procedure will not compile and will give an error. Let us try that.

CREATE OR REPLACE PROCEDURE test_proc
AS
BEGIN
$IF DBMS_DB_VERSION.ver_le_9 $THEN
dbms_output.put_line( ‘code for Rel. 9 and earlier’ );
$ELSIF  DBMS_DB_VERSION.ver_le_11 $THEN
dbms_output.put_line( ‘code for Ver. 11 and earlier’ );
$ELSIF  DBMS_DB_VERSION.ver_le_10_2 $THEN
dbms_output.put_line( ‘code for Ver. 10 Rel. 2 and earlier’ );
$END
END;
/

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
——– —————————————————————–
5/37     PL/SQL: Statement ignored
6/11     PLS-00174: a static boolean expression must be used
6/27     PLS-00302: component ‘VER_LE_11’ must be declared
SQL>

Now if we put ver_le_11 condition in last, procedure will compile fine without giving any error.

CREATE OR REPLACE PROCEDURE test_proc
AS
BEGIN
$IF DBMS_DB_VERSION.ver_le_9 $THEN
dbms_output.put_line( ‘code for Rel. 9 and earlier’ );
$ELSIF  DBMS_DB_VERSION.ver_le_10_2 $THEN
dbms_output.put_line( ‘code for Ver. 10 rel. 2 and earlier’ );
$ELSIF  DBMS_DB_VERSION.ver_le_11 $THEN
dbms_output.put_line( ‘code for Ver. 11 and earlier’ );
$END
END;
/

Procedure created.

This is because, conditional compilation will not evaluate $ELSIF condition once DBMS_DB_VERSION.ver_le_10_2 evaluates to true. Package constants should be used in the chronological order of releases.

As mentioned earlier, this is useful to make code work across versions without keeping separate code set for different versions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: