Tuesday, 24 February 2009

How to Create a JDBC/OCI Connection Pool For Stand Alone OC4J 10.1.3.x

Recently I had to setup JDBC/OCI connection pool in stand alone OC4J 10.1.3.x using an oracle client 10.2.0.3. The problem here is OC4J 10.1.3.x comes with JDBC driver 10.1.0.5 of ojdbc14.jar. Which means you will have issues using the 10.2.0.3 oracle client libraries.

As a result of that you must upgrade the driver to use the client version JDBC driver as well as set various environment variables before starting stand alone OC4J. The steps are as follows.

Note: This is based on OC4J 10.1.3.4 and on a windows environment, using instant client 10.2.0.3

1. Download 10.2.0.3 instant client as follows, in my case installed in directory as follows

D:\jdev\instant-clients\10203

You can download the 10.2.0.3 version from here.

http://www.oracle.com/technology/software/tech/oci/instantclient/

2. Open up command window and set environment variables as follows

set PATH=D:\jdev\instant-clients\10203;%PATH%
set TNS_ADMIN=D:\jdev\instant-clients\10203

Note: A tnsnames.ora exists in the directory D:\jdev\instant-clients\10203 and has the following entry which I will use in stand alone OC4J shortly.

LINUX10G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxx.au.oracle.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = linux10g)
)
)

3. Start stand alone OC4J as follows, ensuring you use the same commend window from step 2 above.

set ORACLE_HOME=D:\jdev\stand-alone-oc4j\10134
set JAVA_HOME=D:\jdev\jdevprod\10134\jdk

D:\jdev\stand-alone-oc4j\10134\bin>oc4j -start
Starting OC4J from D:\jdev\stand-alone-oc4j\10134\j2ee\home ...
09/02/23 13:42:29 Oracle Containers for J2EE 10g (10.1.3.4.0) initialized

4. OC4J 10.1.3.x is using 10.1.0.5 JDBC driver which means it will have issues connecting using 10.2.0.3 instant client so we have to upgrade the JDBC driver to 10.2.0.3 as shown below for the whole container. Follow this metalink note to fix that so it matches the same version of instant client which would mean you use ojdbc14.jar from 10.2.0.3 jar file of ojdbc14.jar.

Note 420303.1 - How to Use The Latest Thin JDBC Driver Across All Applications For a 10.1.3.x OAS Container

You can download the 10.2.0.3 JDBC driver jar file from this link, but it should be available in your oracle client install.

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

5. Stop stand alone OC4J to pick up the shared library change.
6. Start stand alone OC4J.
7. Create a data source as follows.


<connection-pool name="ocitest-pool">
<connection-factory factory-class="oracle.jdbc.pool.OracleDataSource"
user="scott" password="tiger" url="jdbc:oracle:oci:@LINUX10G"/>
</connection-pool>


For more information on data sources refer to the following documentation.

Oracle® Containers for J2EE Services Guide
10g (10.1.3.1.0)
Part Number B28958-01
http://download-uk.oracle.com/docs/cd/B31017_01/web.1013/b28958/datasrc.htm#BABIDCBJ

No comments: