Check out this easy way to find Oracle version information

by Bob Watkins | Oct 10, 2006 7:00:00 AM

Tags: Programming languages, Databases, Bob Watkins, Oracle Corp., PL/SQL, Oracle Tips Newsletter

Takeaway: The PL/SQL package DBMS_DB_VERSION supplies several useful constants that you can use in your code to determine the currently running version of Oracle. Bob Watkins explains how this package makes version checking easier.

When an application will be distributed to customers—or even to multiple sites within the same company—version issues may arise. If the program is designed to use features specific to a particular version of Oracle, it will fail when run on earlier versions.

One way to check for version information is to select from the dynamic performance view V$VERSION. This outputs a set of banner texts identifying the version and release level of various components of Oracle. However, it can be a chore to parse this output.

When Oracle 9i Release 2 was introduced, it contained a new PL/SQL package called DBMS_DB_VERSION that makes version checking easier. This package has no procedures or functions, only a series of useful constants that you can interrogate to determine the currently running version.

The two simplest constants in the package are VERSION and RELEASE. On a 10gR2 instance, executing the following at a SQL*Plus prompt will return the number 10:

SET SERVEROUT ON

EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);

The other constants are booleans that indicate whether the currently running version is less than or equal to a specific cutoff. Instead of extracting the version and release and writing logic to check them, simply test the appropriate boolean:

BEGIN
   IF (DBMS_DB_VERSION.VER_LE_9_2) THEN
      DBMS_OUTPUT.PUT_LINE ('Oracle 10g is required.');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('Version check successful.');
   END IF;

END;

/

This feature was introduced primarily to support conditional compilation in PL/SQL. Based on these constants, you could have your PL/SQL code specify different data types or avoid using features not yet implemented.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Print/View all Posts Comments on this article

Thanks for the tip - simple but very useful! yparesh@... | 10/11/06

Another way to check the particular version of Oracle manfred.kleander | 06/04/07