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

Sql Problem Overview

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.

Sql Solutions

Solution 1 - Sql

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

Solution 2 - Sql

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

Solution 3 - Sql

SQL> SELECT version FROM v$instance;
VERSION
-----------------
11.2.0.3.0

Solution 4 - Sql

You can either use

SELECT * FROM v$version;

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

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

Solution 5 - Sql

If your instance is down, you are look for version information in alert.log

Or another crude way is to look into Oracle binary, If DB in hosted on Linux, try strings on Oracle binary.

strings -a $ORACLE_HOME/bin/oracle |grep RDBMS | grep RELEASE

Solution 6 - Sql

For Oracle use:

Select * from v$version;

For SQL server use:

Select @@VERSION as Version

and for MySQL use:

Show variables LIKE "%version%";

Solution 7 - Sql

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;

Solution 8 - Sql

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)

Solution 9 - Sql

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;

Solution 10 - Sql

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');

Solution 11 - Sql

Here's a simple function:

CREATE FUNCTION fn_which_edition
        RETURN VARCHAR2
    IS

    /*

        Purpose: determine which database edition

        MODIFICATION HISTORY
        Person      Date        Comments
        ---------   ------      -------------------------------------------
        dcox        6/6/2013    Initial Build

    */

    -- Banner
    CURSOR c_get_banner
    IS
        SELECT banner
          FROM v$version
         WHERE UPPER(banner) LIKE UPPER('Oracle Database%');

    vrec_banner c_get_banner%ROWTYPE; -- row record
    v_database VARCHAR2(32767); --
  
BEGIN
    -- Get banner to get edition
    OPEN c_get_banner;
    FETCH c_get_banner INTO vrec_banner;
    CLOSE c_get_banner;

    -- Check for Database type
    IF INSTR( UPPER(vrec_banner.banner), 'EXPRESS') > 0
    THEN
        v_database := 'EXPRESS';
    ELSIF INSTR( UPPER(vrec_banner.banner), 'STANDARD') > 0
    THEN
        v_database := 'STANDARD';
    ELSIF INSTR( UPPER(vrec_banner.banner), 'PERSONAL') > 0
    THEN
        v_database := 'PERSONAL';
    ELSIF INSTR( UPPER(vrec_banner.banner), 'ENTERPRISE') > 0
    THEN
        v_database := 'ENTERPRISE';
    ELSE
        v_database := 'UNKNOWN';
    END IF;

    RETURN v_database;
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN 'ERROR:' || SQLERRM(SQLCODE);
END fn_which_edition; -- function fn_which_edition
/

Done.

Content Type	Original Author	Original Content on Stackoverflow
Question	modius	View Question on Stackoverflow
Solution 1 - Sql	Tony Andrews	View Answer on Stackoverflow
Solution 2 - Sql	Lawrence	View Answer on Stackoverflow
Solution 3 - Sql	Ugur	View Answer on Stackoverflow
Solution 4 - Sql	Peter Lang	View Answer on Stackoverflow
Solution 5 - Sql	user3362908	View Answer on Stackoverflow
Solution 6 - Sql	Jack	View Answer on Stackoverflow
Solution 7 - Sql	santosh tiwary	View Answer on Stackoverflow
Solution 8 - Sql	Pancho	View Answer on Stackoverflow
Solution 9 - Sql	Lova Chittumuri	View Answer on Stackoverflow
Solution 10 - Sql	Prokhozhii	View Answer on Stackoverflow
Solution 11 - Sql	user2460369	View Answer on Stackoverflow