How do I set up JDBC to communicate with Oracle database?

Question ID : 191
Created on 2012-02-27 at 10:57 AM
Author : Veryant Support [support@veryant.com]

Online URL : http://support.veryant.com/support/phpkb/question.php?ID=191

An Oracle JDBC FAQ is at http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html

Where can I get the class files?
You can get the classes files from the Oracle Technology Network SQLJ & JDBC Download Page.
http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
JDBC Driver Downloads
Oracle Database 11g Release 2 (11.2.0.3), (11.2.0.2.0), (11.2.0.1.0) drivers

What are all of these files for?
There are a large number of classes files that are part of the Oracle JDBC installation.
...
ojdbc6.jar
All the classes to support basic functionality for the Thin and OCI drivers when using JDK 1.6 (JSE 6). Additional jar files are required when you use some features.
...

The Oracle Database Documentation Library is at http://www.oracle.com/pls/db112/homepage
To read about the JDBC drivers provided by Oracle, search for "JDBC Driver" and click on the first result http://docs.oracle.com/cd/E11882_01/server.112/e25789/srvrside.htm#CNCPT825
The Oracle Database JDBC Developer's Guide is at http://docs.oracle.com/cd/E11882_01/java.112/e16548/toc.htm
"Choosing the Appropriate Driver" is at http://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#BABCHIGI

We recommend starting with the JDBC Thin driver.

You can find information in the isCOBOL Data Access Guide.

The key information is:

In order to let isCOBOL interact with a RDBMS you need the proper JDBC driver. JDBC drivers are Java libraries (jar) that are usually supplied by the RDBMS vendor. Each RDMBS has its own drivers. The Java library must appear in the CLASSPATH and the driver class name must be specified by the iscobol.jdbc.driver configuration property.

In addition you must specify the connection URL by setting the iscobol.jdbc.url property.

JDBC : Common Driver and URL settings : Oracle
________________________________________
Oracle

Library:
ojdbc6.jar

Value for iscobol.jdbc.driver:
oracle.jdbc.OracleDriver

Value for iscobol.jdbc.url:
jdbc:oracle:thin:/@::

Value for iscobol.jdbc.url when using a TNS:
jdbc:oracle:thin:/@(description=(address=(host=)(protocol=tcp)(port=))(connect_data=(sid=)))

Note that if you are using Java 6 then the Oracle library name is ojdbc6.jar

Here is a real example from one of our Linux boxes:

setup.sh:

export ISCOBOL_JDK_ROOT=/usr/jdk1.6.0_11
export PATH=$ISCOBOL_JDK_ROOT/bin:$PATH
export ISCOBOL_JRE_ROOT=$ISCOBOL_JDK_ROOT
export ISCOBOL=/home/dl136134/porting/Linux32/isCOBOL2012R1
export PATH=$ISCOBOL/bin:$PATH
export LD_LIBRARY_PATH=$ISCOBOL/native/lib:$LD_LIBRARY_PATH
export CLASSPATH=.:$ISCOBOL/lib/iscobol.jar:$ISCOBOL/lib/isxms.jar:$ISCOBOL/lib/utility.jar
export ORACLE_HOME=/database/oracle/app/oracle/product/11.1.0/client_1
export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar:$CLASSPATH
export PATH=$ORACLE_HOME/bin:$PATH
export TWO_TASK=mach1

Note that we are using TWO_TASK as documented at http://docs.oracle.com/cd/E11882_01/install.112/e24322/post_inst_task.htm#CHDJCCBI

and

A tnsnames.ora entry and the following:

tnsnames.ora file located in $ORACLE_HOME/network/admin

The ORACLE_HOME environment variable set to this Oracle home

Our tnsnames.ora file contains the following:

MACH1.PHX03.WEST.SUN.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mach-1.phx03.west.sun.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = mach1)
    )
  )

which defines the mach1 service name.

To set up the environment run the following command:

. ./setup.sh

To run a program named PROGRAM run the following command:

iscrun -c program.properties PROGRAM

where program.properties contains the following:

iscobol.jdbc.driver=oracle.jdbc.OracleDriver
iscobol.jdbc.url=jdbc:oracle:thin:user/password@//mach-1:1521/mach1

replacing username/password with the actual username and password

In this example, mach-1 is the hostname and mach1 is the service name.

The syntax is:

Thin-style Service Name Syntax
Thin-style service names are supported only by the JDBC Thin driver. The syntax is:
@//host_name:port_number/service_name For example:
jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

For more information about the syntax of the connection URL see "Data Sources and URLs" at http://docs.oracle.com/cd/E11882_01/java.112/e16548/urls.htm#JJDBC28267

Back to Original Question