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.
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
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
SQL> SELECT version FROM v$instance;
VERSION
-----------------
11.2.0.3.0
V$INSTANCE
is apparently not globally available by default.
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.
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
For Oracle use:
Select * from v$version;
For SQL server use:
Select @@VERSION as Version
and for MySQL use:
Show variables LIKE "%version%";
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;
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)
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;
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');
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.
Success story sharing
ORA-00942: table or view does not exist
? What do you get?