How can I confirm a database is Oracle & what version it is using SQL?

modius_10750 0
Viewed 256574

I'm building an installer for an application. The user gets to select a datasource they have configured and nominate what type of database it is. I want to confirm that the database type is indeed Oracle, and if possible, what version of Oracle they are running by sending a SQL statement to the datasource.

oracle sql
11 Answers

Run this SQL:

select * from v$version;

And you'll get a result like: 

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
user_187470

Two methods:

select * from v$version;

will give you:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

OR Identifying Your Oracle Database Software Release:

select * from product_component_version;

will give you:

PRODUCT VERSION STATUS
NLSRTL  11.1.0.6.0  Production
Oracle Database 11g Enterprise Edition  11.1.0.6.0  64bit Production
PL/SQL  11.1.0.6.0  Production
TNS for Solaris:    11.1.0.6.0  Production
Lawrence_176210

You can either use

SELECT * FROM v$version;

or

SET SERVEROUTPUT ON
EXEC dbms_output.put_line( dbms_db_version.version );

if you don't want to parse the output of v$version.

user_173430

There are different ways to check Oracle Database Version. Easiest way is to run the below SQL query to check Oracle Version.

SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
SQL> SELECT * FROM v$version;
user_99123060

The following SQL statement:

select edition,version from v$instance

returns:

  • database edition eg. "XE"
  • database version eg. "12.1.0.2.0"

(select privilege on the v$instance view is of course necessary)

user_30516270

We can use the below Methods to get the version Number of Oracle.

Method No : 1

set serveroutput on;
BEGIN 
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE); 
END;

Method No : 2

SQL> select *
  2  from v$version;
user_52563370

This will work starting from Oracle 10

select version
      , regexp_substr(banner, '[^[:space:]]+', 1, 4) as edition 
from    v$instance
     ,  v$version where regexp_like(banner, 'edition', 'i');
user_88480250
Related