+

Search Tips   |   Advanced Search

(dist)

Create a data source that uses the Oracle JDBC Driver for OCI

We can use the Oracle JDBC for OCI driver with Version 7.0 and later of the Application Server.

To create a data source in the Application Server that takes advantage of the Oracle Call Interface (OCI), or Oracle features that require OCI to be used, use the Oracle JDBC OCI native libraries. Any type of Oracle JDBC provider can be configured with the Application Server to use OCI.

  1. From the console, create the JDBC provider.

    1. Click Resources, expand JDBC, and click JDBC Providers.

    2. Select the scope for the JDBC Provider. The scope can be set to a cell, node, or server level.

    3. Click New to create the JDBC Provider.

  2. Configure the settings for the JDBC provider. This provider can be used to create data sources that use either OCI or thin URLs. The type used depends on what is specified in the URL. This provider cannot be used with class loader isolated when used with OCI.

    1. Select Oracle for the database type on the Step 1: Create new JDBC provider panel.

    2. Select an Oracle JDBC driver for the provider type on Step 1: Create new JDBC provider panel.

    3. Select Connection pool data source or XA data source for the implementation type on Step 1: Create new JDBC provider panel.

    4. Specify a name and, optionally, a description, for the JDBC provider.

    5. Click Next.

    6. Specify a directory location for the ojdbc6.jar or ojdbc7.jar (if you are running Java 7 or later) JAR file on Step 2: Enter database specific properties for the data source panel.

      Best practice: An Application Server variable can be used in the class path.

      For example, we can use the variable ${ORACLE_JDBC_DRIVER_PATH} in the field for the class path but define the variable ORACLE_JDBC_DRIVER_PATH to the Application Server. We can set the value of this variable at this step to the directory where the ojdbc JAR is installed. Alternately, after creating the provider, we can, at the appropriate scope level, define or change this variable in the console by clicking Environment > Manage WebSphere Variables. Set its value to the directory where the ojdbc JAR file is installed.

      For example, the class path field could contain ${ORACLE_JDBC_DRIVER_PATH}/ojdbc6.jar and the directory location field could contain /root/InstantClient/12.1.0.1.0. bprac

    7. Click Next.

    8. Review the configuration on the Summary panel and click Finish. If we need to make changes to the configuration, select the new configuration on the next panel, make the changes, and click Apply.

  3. Create the data source in Application Server.

    1. Click Resources, expand JDBC, and click JDBC Providers > provider_name.

    2. Under Additional Properties, click Data sources > New.

    3. Specify the name for the data source on Step 1: Enter basic data source information panel.

    4. Specify the JNDI name for the data source on Step 1: Enter basic data source information panel.

    5. Click Next.

    6. Specify the URL of the database from which the data source obtains connections. Specify this information on Step 2: Enter database specific properties for the data source panel.

      For an OCI driver, one of these examples could be used:

        jdbc:oracle:oci:@//mySystem:1521/sample

        jdbc:oracle:oci:@sample

    7. Select the name of the data store helper class that corresponds to the type of JDBC provider configuredd. Select this information on Step 2: Enter database specific properties for the data source panel.

    8. Click Next.

    9. Optional: Specify the values for the associated security aliases.

    10. Click Next.

    11. Review the configuration on the Summary panel and click Finish. If we need to make changes to the configuration, select the new configuration on the next panel, make the changes, and click Apply.

    12. Save the configuration.

    13. Perform a test connection. If the test works, then the configuration is done. If the test fails with linkage errors, proceed to the next step..

  4. Optional: Set the native library path on the JDBC Provider, set the OS environment variables, or set the environment variables on the server.

    1. First, try setting the native library path on the JDBC provider and then perform a test connection. To set the native library path:

      1. Edit the JDBC Provider

      2. Fill in the native library path with the path to the Oracle JDBC driver native libraries. This might be the same path that you used for the class path.

      Best practice: An Application Server variable can be used in the native library path.

      For example, if the class path and the native library path are the same, we can use the variable that was used for the class path or we can use a variable like ${ORACLE_HOME} in the field for the native library path. We must then define the variable ORACLE_HOME to the Application Server. At the appropriate scope level, define or change this variable in the console by clicking Environment > Manage WebSphere Variables and set its value to the directory to where the Oracle native client libraries are installed.

      For example, the field could contain ${ORACLE_HOME}/lib if lib is where the native libraries are installed. bprac

    2. If there are still linkage failures, then the path to the Oracle native client libraries must either be set in the OS environment that the WebSphere Application server is running in or they can be set in the environment entries for the server. The environment variable to be set is specific to the platform type. These items may already be set as part of the Oracle installation process.

      • AIX: LIBPATH

      • HP-UX: SHLIBPATH

      • Linux/Solaris: LD_LIBRARY_PATH

      • WINDOWS: PATH

    3. The TNS_ADMIN variable might need to be set to the path containing files such as tnsnames.ora or sglnet.ora. For example, if tnsnames.ora contains TNS services names used in a URL such as jdbc:oracle:thin:@mydb.

    4. To set variables in the OS environment, follow the instructions for the platform. For example, for AIX, the following might be used: 
      export LIBPATH=/InstantClient:$LIBPATH
export TNS_ADMIN=/InstantClient

    5. To set variables on the server, use environment entires. For example, using the console:

      1. Application Servers > myServer.

      2. Select Configuration tab.

      3. Under Server Infrastructure, expand Java and Process Management.

      4. Select Process Definition and then under Additional Properties, select Environment Entries.

      5. Add a name and value pair for each environment variable to be set.

    6. After setting the appropriate environment variables, try to test connection again.

  5. You will need to restart the server before using the data source in an application.


What to do next

Note the following items:

  1. The bit level of the Instant Client installed must match the bit level of the operating system.

  2. The Instant Client selected must be the correct version for the platform it is installed on. Instant Client for AIX5L can be used with AIX 5L or later.

  3. The ojdbc JAR file must be the correct version for the java version being used by WebSphere Application Server. The ojdbc6.jar can be used with Java 6 or Java 7. The ojdbc7.jar can only be used with Java 7.

  4. The ojdbc JAR file being used should be the one that was provided with the client that is being used.

  • Data source minimum required settings for Oracle