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."