Friday, May 15, 2009

About checking oracle database version: rediscovery of hot water?

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Little problem of the day:
how to check for the oracle database version currently running from inside a PL/SQL program or query?

I knew there were various built-in dictionary views for checking the version of installed components, but i had another requirement, i wanted to run this query without special privileges, so, running the code as SYS was out of question.
I made a first attempt by memory:
select * from V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

5 rows selected
Now, this kind query is good for a human being but i find it of little use inside a program.

I mean, doesn't Oracle really provide something better to check for?

The answer is yes.

There is a view called PRODUCT_COMPONENT_VERSION that any user can query and it returns a formatted VERSION column.
select * from PRODUCT_COMPONENT_VERSION;

PRODUCT VERSION STATUS
----------------------------------- ---------- ----------
Oracle Database 10g Express Edition 10.2.0.1.0 Product
NLSRTL 10.2.0.1.0 Production
PL/SQL 10.2.0.1.0 Production
TNS for Linux: 10.2.0.1.0 Production

Apparently all we need to do is to filter out the results:
select * from PRODUCT_COMPONENT_VERSION
where product like 'Oracle Database%';

PRODUCT VERSION STATUS
----------------------------------- ---------- ----------
Oracle Database 10g Express Edition 10.2.0.1.0 Product

A further look at web results for PRODUCT_COMPONENT_VERSION turned up some interesting OTN forum comments reporting that the query above would fail on an Oracle Personal Edition Database because column PRODUCT contains the word Personal before Oracle.
In view of these bizarre inconsistencies, i modified the query to minimize the possibilities of further surprises:
select version from PRODUCT_COMPONENT_VERSION
where lower(product) like '%oracle%database%';
Finally note that as of Oracle 10gR1 there is a package called DBMS_DB_VERSION containing two functions and several constants enabling simple version checking from PL/SQL.
Oddly enough the documentation of 10gR1 does not mention this package in the PL/SQL Packages and Types Reference, but the package is apparently there, although i must say i executed the code on a 10.1.0.5 (the results displayed below are taken from Oracle XE though).

begin
dbms_output.put_line(dbms_db_version.version);
dbms_output.put_line(dbms_db_version.release);
end;

10
2
In conclusion, the packaged function approach works fine as long as you are sure that you are running on version 10g or above and you don't need to check for the patch level, so i can't say if the package has been added later with a patch.

5 comments:

Laurent Schneider said...

in 9i and above, try this :
select version from v$instance

Byte64 said...

Hi Laurent,
you can't query that view as a normal user, which is one of the prerequisites i stated at the beginning of my posting.

Ciao
Flavio

Laurent Schneider said...

yes, that's true

Anonymous said...

from sqlplus,
SQL> prompt &_O_VERSION
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> prompt &_O_RELEASE
1002000100
SQL> rem define rem to show all built-in ...

Byte64 said...

thanks for this tip anonymous!

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles