Friday, January 18, 2008

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

No comments:

Post a Comment