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

Question

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

User · Answer

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

User · Answer

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 · Answer

SQL gt  SELECT version FROM v instance  VERSION ----------------- 11 2 0 3 0

User · Answer

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 · Answer

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 gt  SELECT   FROM v version

User · Answer

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    gt  0     THEN         v database     EXPRESS       ELSIF INSTR  UPPER vrec banner banner    STANDARD    gt  0     THEN         v database     STANDARD       ELSIF INSTR  UPPER vrec banner banner    PERSONAL    gt  0     THEN         v database     PERSONAL       ELSIF INSTR  UPPER vrec banner banner    ENTERPRISE    gt  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

User · Answer

For Oracle use   Select   from v version    For SQL server use   Select   VERSION as Version   and for MySQL use   Show variables LIKE   version

User · Answer

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 · Answer

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 · Answer

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

User · Answer

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 gt  select     2  from v version

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

Examples related to sql

Examples related to oracle