DBMS_DB_VERSION is a package of constants that describe the database release: VERSION, RELEASE, and Boolean flags such as VER_LE_19 and VER_LE_21. Because they are constants, PL/SQL can test them at compile time with conditional compilation, so one source works on several releases.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_db_version.version -- major version number dbms_db_version.release -- release number dbms_db_version.ver_le_21 -- true if version 21 or earlier (also ver_le_19, ...)
Read the Constants
Example:
begin
dbms_output.put_line('version ' || dbms_db_version.version || ', release '
|| dbms_db_version.release);
dbms_output.put_line('version 23 or later? '
|| case when dbms_db_version.ver_le_21 then 'no' else 'yes' end);
end;
/Output:
version 23, release 0 version 23 or later? yes PL/SQL procedure successfully completed.
Oracle AI Database 26ai reports version 23, release 0: 26ai is the name of the 23 release line. VER_LE_21 is false, so the code knows it runs on 23 or later.
The Full Version Number
For the exact release update, query PRODUCT_COMPONENT_VERSION.
Example:
select version, version_full from product_component_version;
Output:
VERSION VERSION_FULL _____________ _______________ 23.0.0.0.0 23.26.3.0.0
VERSION_FULL shows the release update, 23.26.3 here, which the package constants do not include.
Use It in Conditional Compilation
Example:
$if dbms_db_version.version >= 23 $then -- code that uses features from 23 onward $else -- code for older databases $end
The branch that does not apply is never compiled, so it may use syntax the database does not understand.
Things to Know
- The constants are fixed for a database; they do not change with release updates within a version.
- Use VERSION_FULL or V$INSTANCE for reports, and DBMS_DB_VERSION for code decisions.
Related Guides
Conclusion
DBMS_DB_VERSION gives the major version and release as constants that conditional compilation can test, so one PL/SQL source can support several database releases. For the exact release update, query PRODUCT_COMPONENT_VERSION.
See my articles more often when you search Google.
