# Oracle Business Intelligence Beans Sample

## Using Connection Pool in OC4J

### Overview

This sample demonstrates how to use the connection pool within OC4J for improved performance.

### Setup Requirements

1. Add the files `CatalogPoolConnDriver.java` and `OlapiPoolConnDriver.java` to the JDeveloper project containing your thin client application (JSP, UIX, or servlet).
2. Deploy the thin client application to the Oracle Application Server 10g or to standalone OC4J.
3. Create or edit the application configuration files to use connection pooling:

   Under the META-INF directory of the application directory in the OC4J instance, create or edit orion-application.xml as follows:

   `<?xml version = '1.0' encoding = 'windows-1252'?>
   <!DOCTYPE orion-application PUBLIC "-//Evermind//DTD J2EE Application runtime 1.2//EN" "http://xmlns.oracle.com/ias/dtds/orion-application.dtd">
   <orion-application>
   <data-sources path="./data-sources.xml"/>
   </orion-application>`

   Under the META-INF directory of the application directory in the OC4J instance, use the oracle connection cache class and modify the connection settings by creating or editing the data-sources.xml file as follows:

   `<?xml version = '1.0' encoding = 'windows-1252'?>
   <!DOCTYPE data-sources PUBLIC "Orion data-sources" "http://xmlns.oracle.com/ias/dtds/data-sources.dtd">
   <data-sources>
   <data-source class="oracle.jdbc.pool.OracleConnectionCacheImpl" connection-driver="oracle.jdbc.driver.OracleDriver" ejb-location="jdbc/CatalogConnection1DS" inactivity-timeout="30" location="jdbc/CatalogConnection1CoreDS" name="jdev-connection:CatalogConnection1" password="BIBCAT" pooled-location="jdbc/CatalogConnection1PooledDS" url="jdbc:oracle:thin:@bibeans-pm.us.oracle.com:1521:BIBPM92" username="BIBCAT" xa-location="jdbc/xa/CatalogConnection1XADS"/>
   <data-source class="oracle.jdbc.pool.OracleConnectionCacheImpl" connection-driver="oracle.jdbc.driver.OracleDriver" ejb-location="jdbc/OLAPConnection1DS" inactivity-timeout="30" location="jdbc/OLAPConnection1CoreDS" name="jdev-connection:OLAPConnection1" password="bibdemo" pooled-location="jdbc/OLAPConnection1PooledDS" url="jdbc:oracle:thin:@bibeans-pm.us.oracle.com:1521:BIBPM92" username="bibdemo" xa-location="jdbc/xa/OLAPConnection1XADS"/>
   </data-sources>`

   Change what is in `bold` to what is seen above (i.e. `oracle.jdbc.pool.OracleConnectionCacheImpl` )
   Change what is in `italics` to your connection configuration information (for example, change `bibeans-pm.us.oracle.com` to the host name of the machine where Oracle OLAP is installed, change `thin` to `oci8` in the url attribute to use thick JDBC drivers)

   Under the WEB-INF/classes directory of the application directory in the OC4J instance, register the pluggable drivers by editing the Project1BIConfig1.xml file as follows:

   Open the file in an editor and add the following 4 lines of code below the `<Debug Mode="ERROR"/>` tag:

   `<PluggableDriver DriverType="c" InvocationLevel="bicatalog" Required="true" DriverClass="CatalogPoolConnDriver"/>
   <PluggableDriver DriverType="a" InvocationLevel="bicatalog" Required="false" DriverClass="CatalogPoolConnDriver"/>
   <PluggableDriver DriverType="c" InvocationLevel="olap" Required="true" DriverClass="OlapiPoolConnDriver"/>
   <PluggableDriver DriverType="a" InvocationLevel="olap" Required="false" DriverClass="OlapiPoolConnDriver"/>`

### Code Highlights

The pluggable authentication and connection drivers (i.e. OlapiPoolConnDriver and CatalogPoolConnDriver ) performs the following functions to enable connection pooling:

1. Lookup the connection using the datasource API
2. Use the JNDI look up mechanism to lookup the data source and use the location attribute when looking up

### How To Run

Run the application on OC4J or OracleAS. You should see a print out in the log indicating the attempt to use the connection pool and the time it took to get the connection.

Copyright © 2004 Oracle Corporation. All Rights Reserved.