How to find Oracle version information in a simple way

A version issue occurs when an application is to be distributed to customers, or even multiple locations in the same company. If the program is designed to use specific features of a specific version of Oracle, it will fail to run in earlier versions.

One way to check VERSION information is to select the dynamic performance View table V $ VERSION. It outputs a set of title-like text that specifies the version of Oracle and the release level of various components. However, parsing this output is very cumbersome.

After Oracle 9i Release 2 is introduced, it includes a new PL/SQL data packet called DBMS_DB_VERSION, making version check easier. There are no programs or functions in this data packet, and only a series of constants can be queried to determine the useful constants of the current running version.

In this packet, the simplest two constants are VERSION and RELEASE. Take 10gR2 as an example. Execute the following command in SQL * Plus and return the number 10:

SET SERVEROUT ON
EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION );

Other constants are logical values, which indicate whether the current running version is earlier or the same than a specific end version. You just need to test the appropriate logical values, instead of checking by extracting the version, publishing information, and writing the logic:

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 is introduced to support conditional editing in PL/SQL. Based on these constants, you can specify different data types for your PL/SQL code or avoid using unimplemented features.

Author: Bob Watkins (OCP, mcba, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator.

(