Connectivity Jdbc & oracle database Troubleshooting java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

This blog will allow DBA’s to troubleshoot connectivity between Jdbc & oracle database

It will also help to tackle common Java connectivity errors like below in windows environment where tracing/debugging is very difficult  

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at bpos.Db.Conectar(Db.java:20)
at bpos.Process.main(Process.java:19)

Background:

Generally we rely on ping, telnet, tnsping & sqlplus for connectivity checks between oracle client & database

e.g.

ping <HostName/IP>

Telnet <Hostname/IP>  <Listener_Port>

tnsping REMCORP_NEW

sqlplus aradmin@REMCORP_NEW/<password>

And we are happy to say “No issues at DB end, able to connect to database from sqlplus please check from you Java Application!”

Now going one step ahead lets help the Java developers to resolve this issue by getting our test script in place on client machine

Java Script: Save it as JdbcCon.java

import java.sql.*;
import java.lang.*;
public class JdbcCon
 {
    public static void main(String args[]) {
        //Connection con = null;
        String url = "jdbc:oracle:thin:@10.19.90.130:1521:mssintr";
        //String db = "css";
        String driver = "oracle.jdbc.driver.OracleDriver";
        String user = "css";
        String pass = "css1234";
        try {
            Class.forName(driver);
            Connection con = DriverManager.getConnection(url, user, pass);
            System.out.println("Connection is created...");
            con.close();
            System.out.println("Connection is closed...");
        } catch (Exception e) {
            System.out.println(e);
        }
    }
} 

What script does? It will make a connection to DB using Oracle Jdbc driver oracle.jdbc.driver.OracleDriver & disconnect. Appropriate database DB IP, Port, DB/Service name user & password (Highlighted) should be provided in script

Testing:

On oracle client machine save above java code file as JdbcCon.java in a location e.g. D:\

Java file Location = D:\JdbcCon.java

Variables:

ORACLE_HOME=D:\oracle\product\10.2.0\client_1

For 10g & Above

CLASSPATH= D:\oracle\product\10.2.0\client_1\jdbc\lib\ojdbc14.jar

For 9i & below

CLASSPATH= D:\oracle\product\10.2.0\client_1\jdbc\lib\classes12.jar

Set & Verify variable at session level for testing as below

set CLASSPATH=D:\oracle\product\10.2.0\client_1\jdbc\lib\ojdbc14.jar

echo %CLASSPATH%

Complie code:

javac JdbcCon.java –verbose

Execute Code:

java JdbcCon

D:> javac JdbcCon.java –verbose

D:> java Jdbc

Connection is created...

Connection is closed...

Note: During multiple round of testing if you do not change code, then compilation step is not necessary in the same session, before re-compiling delete the .class file generated in script location location 

You can verify your listener.log at DB server to check if connection establishes w.r.t. IP/Hostname of the application Machine

In Windows, You can set environment variables across the sessions as below 

Start>Computer (Right Click)>Properties>Advance System Setting>Environment Variables > New/Edit

D:\oracle\product\10.2.0\client_1\jdbc\lib\ojdbc14.jar;.;

Any In-appropriate setting will lead to error

java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

Optionally, You can set Jdk lib path as well in environment variable separated by “;” make sure at the end its “;.;”

Also, Make Sure JAVA_HOME of application is set appropriately in environment variables