Obtaining the database version within SQL or PL/SQL

How do you get the version of the database you are connected to within a PL/SQL session?

Short answer

With the DBMS_UTILITY.DB_VERSION procedure, which returns database version information.

Long answer

This question caused me a bit of grief.

I had been writing a procedure to drop a users objects, so that developers could just clean out everything owned by a user without doing DROP USER and CREATE USER. After all, who wants to give developers the DROP USER privilege? Anyway, after spending a while sidetracked with SYS_CONTEXT function and the USERENV context, which would seem to be the logical place to find the database version detail, I eventually stumbled on to DBMS_UTILITY.DB_VERSION.

The following simple example program in PL/SQL demonstrates how to get the database version using PL/SQL.

using DBMS_UTILITY.DB_VERSION shows how the version and compatibility can be found.

SET SERVEROUTPUT ON

DECLARE
  l_version  VARCHAR2(100);
  l_compatibility  VARCHAR2(100);
BEGIN
  DBMS_UTILITY.db_version (version       => l_version,
                           compatibility => l_compatibility);
  DBMS_OUTPUT.put_line('Version: ' || l_version || '  Compatibility: ' || l_compatibility);
END;
/

Output:

anonymous block completed

Version: 8.1.7.4.0  Compatibility: 8.1.7