Finding database version with DBMS_UTILITY.DB_VERSION

This very short article comes from a post to comp.databases.oracle.server in answer to a question on how to get the database version using PL/SQL. This simple example program in PL/SQL using DBMS_UTILITY.DB_VERSION shows how the version and compatibility can be found. Here it is:

SQL> set serveroutput on size 1000000
SQL> l
  1  declare
  2     lv_version varchar2(100):='';
  3     lv_compat  varchar2(100):='';
  4  begin
  5     dbms_utility.db_version(lv_version,lv_compat);
  6     dbms_output.put_line('version = '||lv_version);
  7     dbms_output.put_line('compatability = '||lv_compat);
  8  exception
  9     when others then
 10             dbms_output.put_line(sqlerrm);
 11* end;
SQL> /
version = 9.2.0.1.0
compatability = 9.2.0.0.0

PL/SQL procedure successfully completed.

SQL>

Running my privilege check script for dbms_utility shows that it is available for public use:

SQL>
who_can_access: Release 1.0.0.0.0 - Production on Thu Mar 11 15:09:27
2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

get user input

NAME OF OBJECT TO CHECK [USER_OBJECTS]: dbms_utility
OWNER OF THE OBJECT TO CHECK    [USER]: sys
OUTPUT METHOD Screen/File          [S]:
FILE NAME FOR OUTPUT        [priv.lst]:
OUTPUT DIRECTORY                [/tmp]:

Checking object => SYS.DBMS_UTILITY
====================================================================


Object type is => PACKAGE (TAB)
        Privilege => EXECUTE is granted to =>
                Role => PUBLIC

PL/SQL procedure successfully completed.


For updates please visit /tools.htm

SQL>

That's , a simple example of the use of the built in package DBMS_UTILITY.DB_VERSION available for public use. DBMS_UTILITY.DB_VERSION is an alternative to using simple SQL commands to get the compatibility from v$parameter and the versions from v$version techniques.

Back