A Connection Example of JDBC THIN, THICK or SQLPLUS using Oracle Instant Client

Posted by Steve Racanovic | Posted in | Posted on 12:10 PM

0

First, download the instant client you which to use from - http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html

The following steps demonstrate how to use it with simple connection.

1. I downloaded and extracted the following zips file;

- instantclient-basic-win-x86-64-11.1.0.7.0.zip
- instantclient-sqlplus-win-x86-64-11.1.0.7.0.zip

to: D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0


[sracanov@SRACANOV-AU D]$ ll
 Volume in drive D is Data
 Volume Serial Number is 12FC-9B4B

 Directory of D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0

07/02/2012  01:47 PM              .
07/02/2012  01:47 PM              ..
29/10/2008  07:09 AM            14,336 adrci.exe
29/10/2008  07:09 AM             4,648 adrci.sym
29/10/2008  07:09 AM               335 BASIC_README
29/10/2008  07:09 AM            47,104 genezi.exe
29/10/2008  07:09 AM            26,272 genezi.sym
12/01/2006  11:36 PM               342 glogin.sql
15/10/2008  07:11 AM         1,060,864 mfc71.dll
15/10/2008  07:11 AM           348,160 msvcr71.dll
29/10/2008  07:06 AM           659,968 oci.dll
29/10/2008  07:06 AM           426,200 oci.sym
07/10/2008  11:58 PM           132,608 ocijdbc11.dll
07/10/2008  11:58 PM            22,712 ocijdbc11.sym
26/10/2008  01:10 PM           468,480 ociw32.dll
26/10/2008  01:10 PM            73,024 ociw32.sym
07/10/2008  11:00 PM         1,890,262 ojdbc5.jar
07/10/2008  11:00 PM         1,988,193 ojdbc6.jar
14/10/2008  11:47 PM         1,527,296 orannzsbb11.dll
14/10/2008  11:47 PM           382,728 orannzsbb11.sym
27/10/2008  01:41 PM         1,330,176 oraocci11.dll
29/10/2008  07:09 AM           421,472 oraocci11.sym
29/10/2008  07:08 AM       126,414,336 oraociei11.dll
29/10/2008  07:08 AM         7,200,336 oraociei11.sym
29/10/2008  07:09 AM           426,496 orasql11.dll
29/10/2008  07:09 AM            35,744 orasql11.sym
02/10/2008  11:24 PM         1,685,504 Orasqlplusic11.dll
02/10/2008  11:24 PM           757,760 sqlplus.exe
29/10/2008  07:10 AM           135,976 sqlplus.sym
29/10/2008  07:10 AM               339 SQLPLUS_README
29/10/2008  07:09 AM              vc71
07/02/2012  01:47 PM              vc8
              28 File(s)    147,481,671 bytes
               4 Dir(s)  41,876,062,208 bytes free


2. Use sqlplus.exe and connect to the database.


[sracanov@SRACANOV-AU D]$ sqlplus scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sracanov-au3.au.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=linux11gr2.au.oracle.com)))

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 14 11:10:17 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[sracanov@SRACANOV-AU D]$

This is how my database listener looks like:


[oracle@sracanov-au3 dbhome_1]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 14-MAR-2012 11:17:49

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                13-MAR-2012 10:22:16
Uptime                    1 days 0 hr. 49 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/log/diag/tnslsnr/sracanov-au3/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sracanov-au3.au.oracle.com)(PORT=1521)))
Services Summary...
Service "linux11gr2.au.oracle.com" has 1 instance(s).
  Instance "linux11gr2", status READY, has 1 handler(s) for this service...
Service "linux11gr2XDB.au.oracle.com" has 1 instance(s).
  Instance "linux11gr2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@sracanov-au3 dbhome_1]$


3. Download the simple standalone java client from Doc 467804.1 - How To Determine The Exact JDBC Driver Version (9.x - 11.x) For Standalone Programs (Doc ID 467804.1)


[sracanov@SRACANOV-AU D]$ cd /d C:\Users\sracanov\Downloads

[sracanov@SRACANOV-AU C]$ ll
 Volume in drive C is System
 Volume Serial Number is 3E06-1C2F

 Directory of C:\Users\sracanov\Downloads

07/02/2012  11:47 AM              .
07/02/2012  11:47 AM              ..
07/02/2012  11:45 AM             3,401 JDBCInfo.java
               1 File(s)          3,401 bytes
               2 Dir(s)  146,445,778,944 bytes free

[sracanov@SRACANOV-AU C]$ java -version
java version "1.6.0_30"
Java(TM) SE Runtime Environment (build 1.6.0_30-b12)
Java HotSpot(TM) 64-Bit Server VM (build 20.5-b03, mixed mode)

[sracanov@SRACANOV-AU C]$

There are 2 crucial points here:
A) - I'am using JDK 1.6, so I must use ojdbc6.jar driver.
B) - I am using 64bit JDK. My platform is 64bit. As per the files downloaded *win-x86-64-11.1.0.7.0*. (If platform is 64bit, use 64bit JDK and Client. This needs to be correct for OCI to work. Due to dll's. Thin will run with 32bit).

My platform:


[sracanov@SRACANOV-AU C]$ systeminfo | findstr /B /C:"OS Name" /C:"System Type"
OS Name:                   Microsoft Windows 7 Professional
System Type:               x64-based PC

If you dont have the correct JDK as per your platform, you can download it from here - http://www.oracle.com/technetwork/java/javase/downloads/index.html

4. Now compile and run using JDBC THIN to connect.


[sracanov@SRACANOV-AU C]$ javac -cp .;D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ojdbc6.jar JDBCInfo.java

[sracanov@SRACANOV-AU C]$ java -cp .;D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ojdbc6.jar JDBCInfo jdbc:oracle:thin:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)
(HOST=sracanov-au3.au.oracle.com))(CONNECT_DATA=(SERVICE_NAME=linux11gr2.au.oracle.com)))

Database
==============
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

JDBC
==============
Oracle JDBC driver: 11.1.0.7.0-Production

Connection URL
==============
jdbc:oracle:thin:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=sracanov-au3.au.oracle.com))(CONNECT_DATA=(SERVICE_NAME=linux11gr2.au.oracle.com)))

JVM
===
Sun Microsystems Inc.
Java HotSpot(TM) 64-Bit Server VM
20.5-b03
1.6.0_30

LOCALE
===========
en_AU

CLASSPATH
=========
.
D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ojdbc6.jar

LIBRARYPATH
===========
C:\Program Files\Java\jdk1.6.0_30\bin
C:\windows\Sun\Java\bin
C:\windows\system32
C:\windows
.
C:\windows\SYSTEM_BIN
C:\windows\SYSTEM_EXE
C:\Program Files\Windows Resource Kits\Tools
C:\windows\SYSTEM_EXE\unixutils\bin
C:\windows\SYSTEM_EXE\unixutils\usr\local\wbin
C:\Program Files\WinSCP3
C:\Program Files\cvsnt
C:\Program Files\GNU\WinCvs 2.0\
C:\Program Files\GnuWin32\bin
C:\Program Files\Java\jdk1.6.0_30\bin
C:\Program Files (x86)\apache-ant-1.7.0\bin
\bin
\bin
C:\windows
C:\windows\system32
.

[sracanov@SRACANOV-AU C]$

5. Now to use JDBC OCI to connect.


[sracanov@SRACANOV-AU C]$ set path=D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0;%path%
[sracanov@SRACANOV-AU C]$ java -cp .;D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ojdbc6.jar JDBCInfo jdbc:oracle:oci:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(
HOST=sracanov-au3.au.oracle.com))(CONNECT_DATA=(SERVICE_NAME=linux11gr2.au.oracle.com)))

Database
==============
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

JDBC
==============
Oracle JDBC driver: 11.1.0.7.0-Production

Connection URL
==============
jdbc:oracle:oci:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=sracanov-au3.au.oracle.com))(CONNECT_DATA=(SERVICE_NAME=linux11gr2.au.oracle.com)))

JVM
===
Sun Microsystems Inc.
Java HotSpot(TM) 64-Bit Server VM
20.5-b03
1.6.0_30

LOCALE
===========
en_AU

CLASSPATH
=========
.
D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ojdbc6.jar

LIBRARYPATH
===========
C:\Program Files\Java\jdk1.6.0_30\bin
C:\windows\Sun\Java\bin
C:\windows\system32
C:\windows
D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0
.
C:\windows\SYSTEM_BIN
C:\windows\SYSTEM_EXE
C:\Program Files\Windows Resource Kits\Tools
C:\windows\SYSTEM_EXE\unixutils\bin
C:\windows\SYSTEM_EXE\unixutils\usr\local\wbin
C:\Program Files\WinSCP3
C:\Program Files\cvsnt
C:\Program Files\GNU\WinCvs 2.0\
C:\Program Files\GnuWin32\bin
C:\Program Files\Java\jdk1.6.0_30\bin
C:\Program Files (x86)\apache-ant-1.7.0\bin
\bin
\bin
C:\windows
C:\windows\system32
.

[sracanov@SRACANOV-AU C]$

NOTE:

- If I was using 32bit JDK:

[sracanov@SRACANOV-AU C]$ set path=C:\Program Files (x86)\Java\jdk1.6.0_26\bin

[sracanov@SRACANOV-AU C]$ java -version
java version "1.6.0_26"
Java(TM) SE Runtime Environment (build 1.6.0_26-b03)
Java HotSpot(TM) Client VM (build 20.1-b02, mixed mode, sharing)

[sracanov@SRACANOV-AU C]$ java -cp .;D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ojdbc6.jar JDBCInfo jdbc:oracle:thin:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)
(HOST=sracanov-au3.au.oracle.com))(CONNECT_DATA=(SERVICE_NAME=linux11gr2.au.oracle.com)))

Database
==============
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

JDBC
==============
Oracle JDBC driver: 11.1.0.7.0-Production

Connection URL
==============
jdbc:oracle:thin:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(HOST=sracanov-au3.au.oracle.com))(CONNECT_DATA=(SERVICE_NAME=linux11gr2.au.oracle.com)))

JVM
===
Sun Microsystems Inc.
Java HotSpot(TM) Client VM
20.1-b02
1.6.0_26

LOCALE
===========
en_AU

CLASSPATH
=========
.
D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ojdbc6.jar

LIBRARYPATH
===========
C:\Program Files (x86)\Java\jdk1.6.0_26\bin
C:\windows\Sun\Java\bin
C:\windows\system32
C:\windows
C:\Program Files (x86)\Java\jdk1.6.0_26\bin
.

[sracanov@SRACANOV-AU C]$
[sracanov@SRACANOV-AU C]$
[sracanov@SRACANOV-AU C]$ set path=D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0;C:\Program Files (x86)\Java\jdk1.6.0_26\bin

[sracanov@SRACANOV-AU C]$ java -cp .;D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ojdbc6.jar JDBCInfo jdbc:oracle:oci:scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(PORT=1521)(
HOST=sracanov-au3.au.oracle.com))(CONNECT_DATA=(SERVICE_NAME=linux11gr2.au.oracle.com)))
Exception in thread "main" java.lang.UnsatisfiedLinkError: D:\Oracle\JDBC\instantclient\x86-64\11.1.0.7.0\ocijdbc11.dll: Can't find dependent libraries
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1807)
        at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1732)
        at java.lang.Runtime.loadLibrary0(Runtime.java:823)
        at java.lang.System.loadLibrary(System.java:1028)
        at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:3178)
        at java.security.AccessController.doPrivileged(Native Method)
        at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:3174)
        at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:233)
        at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:508)
        at oracle.jdbc.driver.T2CConnection.(T2CConnection.java:133)
        at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:53)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:510)
        at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:275)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:206)
        at oracle.jdbc.pool.OracleDataSource.getConnection(OracleDataSource.java:156)
        at JDBCInfo.main(JDBCInfo.java:58)

[sracanov@SRACANOV-AU C]$

- Thin works fine.

- OCI will run into dependent libraries issue on the platform. So you need to use the correct JDK.

- Set the envionment variable for your library on the system used. http://www.oracle.com/technetwork/database/features/instant-client/index-100365.html

"Set the library loading path in your environment to the directory in Step 2 ("instantclient"). On many UNIX platforms, LD_LIBRARY_PATH is the appropriate environment variable. On Windows, PATH should be used."