How to configure Oracle OCI Driver and connect to Oracle Database using OCI ( Type II ) Driver.

Oracle OCI driver is also called the 'thick' driver, because of its thick native code it uses to communicate with the database. OCI expands to Oracle Call Interface, its written in C language and all the Java calls use JNI to direct the calls to the C layer. The C layer does the talking with the database and returns the results to the Java layer. Even thoug C is the fastest way to communicate with database, the overhead incurred during in the JNI layer makes it a bit slower than the 100% Java thin driver. But in some cases the OCI driver beats the thin driver, i am yet to a performance analysis on that.

In a nutshell, the Oracle OCI (thick) driver uses Oracle Call Interface (written in C and is native code ) to communicate with the database. Hence, all Java calls have to be mapped to the C calls in OCI. Due to this mapping, the driver classes must be in sync with the OCI files - this results in connection problems which are listed and solved below.

Here are the steps that have to be done to Connect to Oracle database using Oracle OCI driver.

ORACLE_HOME : This refers to the folder where Oracle Client or Database has been installed.
Example D:\oracle9i , /home/oracle/orahome. This folder will have sub-folders such as bin, sqlj, network, rdbms, jdbc, jlib, lib, ...etc.

Windows

1. Set ORACLE_HOME environment variable
2. Add ORACLE_HOME\lib to System PATH
3 Set CLASSPATH environment variable to use Oracle JDBC driver from ORACLE_HOME\jdbc\lib

*nix ( Linux / Unix )

1. Set ORACLE_HOME environment variable
2. Add ORACLE_HOME/lib to LD_LIBRARY_PATH env variable
3 Set CLASSPATH environment variable to use Oracle JDBC driver from ORACLE_HOME/jdbc/lib.

While connecting to Oracle Database using OCI driver, here are some of the error messages that you might encounter. If the error message matchs, then the answer to the checklist question must be 'yes', else perform the nesseccary operation to answer 'yes'.

Example :

Is the jdbc driver from ORACLE_HOME/jdbc/lib ?

- If you are using a classes12.jar taken from an other location other than this, then your answer will be no. Hence you will have to use the jdbc driver from ORACLE_HOME/jdbc/lib.

Error Messages and Solutions For Windows Platform

Error Message : Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9.dll in java.library.path
Checklist :
1. Is ORACLE_HOME environment variable set
2. Is ORACLE_HOME\bin added to System path
3. Is ORACLE_HOME\lib added to System path

Error Message : Exception No Such Method : make_c_state error
Checklist :
1. Is ORACLE_HOME\lib added to System path
2. Is the JDBC driver used to connect to database is taken from ORACLE_HOME\jdbc\lib

Error Message : Exception in thread "main" java.lang.NoSuchFieldError: envCharSetId
Checklist :
1. Is ORACLE_HOME\lib added to System path
2. Is the JDBC driver used to connect to database is taken from ORACLE_HOME\jdbc\lib

Error Messages and Solutions For *nix (Linux / Unix) Platform

Error Message : Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path
Checklist :
1. Is ORACLE_HOME environment variable set
2. Is ORACLE_HOME/lib added to LD_LIBRARY_PATH

Error Message : Exception in thread "main" java.lang.NoSuchFieldError: envCharSetId
Checklist :
1. Is ORACLE_HOME environment variable set
2. Is the JDBC driver used to connect to database is taken from ORACLE_HOME/jdbc/lib

Error Message : make_c_state error
Checklist :
1. Is ORACLE_HOME/bin added to System path
2. Is the JDBC driver used to connect to database is taken from ORACLE_HOME/jdbc/lib

Error Message : Error connecting to database : java.sql.SQLException: internal error: oracle.jdbc.oci8.OCIEnv@291aff
Checklist :
1. Is ORACLE_HOME environment variable set

I am just putting down the stack trace down here so that search engines will index them too.

Exception in thread "main" java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver

-- Set classpath to Oracle JDBC drivers

---------------------
Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path

Exception in thread "main" java.lang.UnsatisfiedLinkError: no ocijdbc9 in java.library.path
at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1403)
at java.lang.Runtime.loadLibrary0(Runtime.java:788)
at java.lang.System.loadLibrary(System.java:832)
at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:300)
at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:370)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:505)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:342)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at JDBCOCITest.dbconnect(JDBCOCITest.java:40)
at JDBCOCITest.main(JDBCOCITest.java:30)

-- LD_LIBRARY_PATH has not been set, set LD_LIBRARY_PATH to ORACLE_HOME/lib
---------------------

Error connecting to database : java.sql.SQLException: internal error: oracle.jdbc.oci8.OCIEnv@291aff
Refer to http://myjdbc.tripod.com/basic/jdbcoci.html for the appropriate solution to this error
Exception in thread "main" java.sql.SQLException: internal error: oracle.jdbc.oci8.OCIEnv@291aff
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:188)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:230)
at oracle.jdbc.oci8.OCIEnv.getEnvHandle(OCIEnv.java:79)
at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:390)
at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:370)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:505)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:342)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at JDBCOCITest.dbconnect(JDBCOCITest.java:40)
at JDBCOCITest.main(JDBCOCITest.java:30)

-- ORACLE_HOME not set

---------------------

Connecting with URL=jdbc:oracle:oci8:@ias904 as scott/tiger
Exception in thread "main" java.lang.NoSuchFieldError: envCharSetId
at oracle.jdbc.oci8.OCIEnv.get_env_handle(Native Method)
at oracle.jdbc.oci8.OCIEnv.getEnvHandle(OCIEnv.java:70)
at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:390)
at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:361)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va:485)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:337)
at java.sql.DriverManager.getConnection(DriverManager.java:517)
at java.sql.DriverManager.getConnection(DriverManager.java:177)
at JDBCOCITest.dbconnect(JDBCOCITest.java:40)
at JDBCOCITest.main(JDBCOCITest.java:30)

-- wrong version of classes12.jar is being used, use classes12.jar from ORACLE_HOME/jdbc/lib

---------------------


Connecting with URL=jdbc:oracle:oci8:@ias904 as scott/tiger
Error connecting to database : java.sql.SQLException: ORA-12154: TNS:could not resolve service name

Refer to http://myjdbc.tripod.com/basic/jdbcoci.html for the appropriate solution to this error
Exception in thread "main" java.sql.SQLException: ORA-12154: TNS:could not resolve service name

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.oci8.OCIDBAccess.check_error(OCIDBAccess.java:2321)
at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:477)
at oracle.jdbc.driver.OracleConnection.(OracleConnection.java:346)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:468)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:314)
at java.sql.DriverManager.getConnection(DriverManager.java:517)
at java.sql.DriverManager.getConnection(DriverManager.java:177)
at JDBCOCITest.dbconnect(JDBCOCITest.java:40)
at JDBCOCITest.main(JDBCOCITest.java:30)

-- The tnsname specified in jdbc url is incorrect, check if the names match in ORACLE_HOME/network/admin/tnsnames.ora