JDBC Oracle

JDBC Thin Driver

The JDBC Thin driver does not require Oracle client software to be installed, but does require the server to be configured with a TCP/IP listener. Because it is written entirely in Java, this driver is platform-independent. The JDBC Thin driver can be downloaded into any browser as part of a Java application.

See: here to determine which file you need (summarised below)...

Download if the file is not already under $ORACLE_HOME/jdbc/lib

Note: you can copy the jar file from your $ORACLE_HOME and use it on any target.. the jar files are not platform specific and do not need a paid license.

You will need the appropriate jar file in your CLASSPATH. For example...

export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar:.

JDBC-JDK-Oracle-Matrix

JDBC Install

OJDBC8

If you have installed JDK8 and you plan to access Oracle 12.2...

Download ojdbc8-full.tar.gz

tar xvf ojdbc8-full.tar.gz

This will create a directory called OJDBC8-Full, which you should add to your CLASSPATH

The 12.2 download includes:
ojdbc8

OJDBC7

If you have installed JDK7 or JDK8 and you plan to access Oracle 12.1...

The 12.1 download includes:
ojdbc7 and ojdbc6

OJDBC6

If you have installed JDK6 and you plan to access Oracle 12.1... Or if you plan to access Oracle 11.2

The 12.1 download includes:
ojdbc7 and ojdbc6

Example

At it's simplest, you can then create a .java file containing your Java program (e.g. JDBCtest.java)...

import java.sql.*;

class JDBCtest {

  public static void main (String args []) throws SQLException

  {

    DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

 

    Connection conn = DriverManager.getConnection

      ("jdbc:oracle:thin:@machineName:1521/SERVICE", "username", "password"); // @machineName:port:SID, userid, password


    Statement stmt = conn.createStatement();

    ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");

    while (rset.next())

      System.out.println (rset.getString(1));   // Print col 1

    stmt.close();

  }

}

Compile it...

javac JDBCtest.java

Run it..

java JDBCtest

Long form connection string

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myservice))) 

Mainframe

On a mainframe you may need to force compatible encoding otherwise you will see strange characters in the listener.log...

To force it to use UTF8 ASCII use...

java -Dfile.encoding=UTF8 JDBCtest

JDBC and Multi-Tenant Architecture

When connecting to a PDB via JDBC you must connect via the SERVICE not the SID, hence the first example below is invalid, the second is valid...

jdbc:oracle:thin:@myserver:1521:mypdb

jdbc:oracle:thin:@myserver:1521/mypdb

JDBC and Oracle Wallet

Note: Using Wallet does mean you will need an Oracle Client installation on the computer you are running your Java program from.

In addition to adding ojdbcx.jar to the CLASSPATH (as described above) you should also add these additional items to the CLASSPATH...

$ORACLE_HOME/jlib/oraclepki.jar

$ORACLE_HOME/jlib/osdt_cert.jar

$ORACLE_HOME/jlib/osdt_core.jar

Note that you can either add these to the CLASSPATH environment variable or specify then on the java command line using the -cp argument. Using the -cp argument is recommended if you need to cron schedule your program

Create a tnsnames.ora file containing stanzas for the target databases.

Note that if you plan to connect to the same database with different userids from your Java program then you will need to create separate stanzas in the tnsnames.ora file with different names (e.g. DB1U1 and DB1U2).

Create a Wallet (if you don't have one already). 

Note: It is recommended to create the wallet using orapki with the -auto_login_local option.

Add passwords to the wallet.

Remember to only assign one password for each alias in your tnsnames.ora file

You can then connect using a command similar to this in your Java code...

Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:/@TNSALIAS");

To run the code you should define two system properties on the command line to specify the tnsnames.ora and wallet location...

-Doracle.net.tns_admin=/u01/app/oracle/product/12.1.0.2/network/admin 

-Doracle.net.wallet_location=/home/oracle/wallet

For example...

/u01/app/oracle/product/12.1.0.2/jdk/bin/java -cp /u01/app/oracle/product/12.1.0.2/jdbc/lib/ojdbc6.jar:/u01/dba/java:/u01/app/oracle/product/12.1.0.2/jlib/oraclepki.jar:/u01/app/oracle/product/12.1.0.2/jlib/osdt_cert.jar:/u01/app/oracle/product/12.1.0.2/jlib/osdt_core.jar -Doracle.net.tns_admin=/u01/app/oracle/product/12.1.0.2/network/admin -Doracle.net.wallet_location=/home/oracle/wallet myclass

Bibliography