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> SELECT version FROM v$instance;
VERSION
-----------------
11.2.0.3.0
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');
For Oracle use:
Select * from v$version;
For SQL server use:
Select @@VERSION as Version
and for MySQL use:
Show variables LIKE "%version%";
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
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.
The following SQL statement:
select edition,version from v$instance
returns:
(select privilege on the v$instance view is of course necessary)
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.
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
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;
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;
Source: Stackoverflow.com