How to Confirm a Database Is Oracle & What Version It Is Using SQL

How can I confirm a database is Oracle & what version it is using 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

Query to get Oracle DB version

Why not just query v$instance?

SQL> select version from v$instance;

VERSION
-----------------
12.2.0.1.0

No need to muck around with pl/sql or called procedures.

How do we check version of Oracle

select banner from v$version;

should work.

you can also use.

select version from PRODUCT_COMPONENT_VERSION where rownum = 1;

How to get Oracle database version?

Execute this statement from SQL*Plus, SQLcl, Oracle SQL Developer, SQL Navigator or other IDE:

select * from product_component_version

And you'll get:

PRODUCT                                VERSION    VERSION_FULL STATUS
-------------------------------------- ---------- ------------ ----------
Oracle Database 18c Enterprise Edition 18.0.0.0.0 18.3.0.0.0   Production

How to find which version of Oracle is installed on a Linux server (In terminal)

As the user running the Oracle Database one can also try $ORACLE_HOME/OPatch/opatch lsinventory which shows the exact version and patches installed.

For example this is a quick oneliner which should only return the version number:

$ORACLE_HOME/OPatch/opatch lsinventory | awk '/^Oracle Database/ {print $NF}'

How to check oracle database version after connection in java code

You can use the below code to get the version details,

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;

public class TestDatabaseMetaDataToolDatabaseInformation {
  public static void main(String[] args) {
    Connection conn = getOracleConnection();
    DatabaseMetaData meta = conn.getMetaData();
    // Oracle (and some other vendors) do not support
    // some the following methods; therefore, we need
    // to use try-catch block.
    try {
      int majorVersion = meta.getDatabaseMajorVersion();
      System.out.println("major Version: " + majorVersion);
      int minorVersion = meta.getDatabaseMinorVersion();
      System.out.println("minorVersion" + minorVersion);
      String productName = meta.getDatabaseProductName();
      String productVersion = meta.getDatabaseProductVersion();
      System.out.println("productName" + productName);
      System.out.println("productVersion" + productVersion);
    } catch (SQLException e) {
      System.out.println("minorVersion unsupported feature");
      System.out.println("major Version: unsupported feature");
    } finally {
        conn.close();
    }
  }

  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "password";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }

}

Which Oracle version is best for Windows 10 and how to check Database connection in Oracle Sql Developer

19c doesn't have express edition, so - if you really installed 19c, its SID most probably isn't XE.

If you installed 18cXE, then try to put "XE" into Service Name (instead of into the "SID" field on SQL Developer's login screen).

Execute query depending of Oracle version

I think this is actually a very bad plan. If the versions are different just write a different procedure specific to each version, if they're the same then name the tables the same. Proceeding with this plan is likely to be a maintenance nightmare and will clearly be so in problem analysis and resolution later. But you can do this with Conditional Compilation. Example:

CREATE OR REPLACE PROCEDURE foo_or_bar  AS

    $IF DBMS_DB_VERSION.VER_LE_11 $THEN
    type myfoobar_t is table of bar%rowtype;
    $ELSE
    type myfoobar_t in table of foo%rowtype;
    $END

    myfoobar myfoobar_t;
BEGIN
    $IF DBMS_DB_VERSION.VER_LE_11 $THEN
    select * 
      bulk collect 
      into myfoobar
      from bar; 
    $ELSE
    select * 
      bulk collect 
      into myfoobar
      from foo;     
    $END
   ... 
end foo_or_bar;