19 JDBC OCI Extensions

OCI Driver Connection Pooling: Background

The Oracle JDBC OCI driver provides several transaction monitor capabilities, such as the fine-grained management of Oracle sessions and connections. It is possible for a high-end application server or transaction monitor to multiplex several sessions over fewer physical connections on a call-level basis, thereby achieving a high degree of scalability by pooling of connections and back-end Oracle server processes.

The connection pooling provided by the OracleOCIConnectionPool interface simplifies the Session/Connection separation interface hiding the management of the physical connection pool. The Oracle sessions are the OracleOCIConnection connection objects obtained from the OracleOCIConnectionPool. The connection pool itself is normally configured with a much smaller shared pool of physical connections, translating to a back-end server pool containing an identical number of dedicated server processes. Note that many more Oracle sessions can be multiplexed over this pool of fewer shared connections and back-end Oracle processes.

OCI Driver Connection Pooling and Shared Servers Compared

In some ways, what OCI driver connection pooling offers on the middle tier is similar to what shared server processes offer on the back-end. OCI driver connection pooling makes a dedicated server instance behave as an shared instance by managing the session multiplexing logic on the middle tier. Therefore, the pooling of dedicated server processes and incoming connections into the dedicated server processes is controlled by the OCI connection pool on the middle tier.

The main difference between OCI connection pooling and shared servers is that in case of shared servers, the connection from the client is normally to a dispatcher in the database instance. The dispatcher is responsible for directing the client request to an appropriate shared server. On the other hand, the physical connection from the OCI connection pool is established directly from the middle tier to the Oracle dedicated server process in the back-end server pool.

Note that OCI connection pool is mainly beneficial only if the middle tier is multi-threaded. Each thread could maintain a session to the database. The actual connections to the database are maintained by the OracleOCIConnectionPool and these connections (including the pool of dedicated database server processes) are shared among all the threads in the middle tier.

Defining an OCI Connection Pool

An OCI connection pool is created at the beginning of the application. Creating connections from a pool is quite similar to creating connections using the OracleDataSource class.

The oracle.jdbc.pool.OracleOCIConnectionPool class, which extends the OracleDataSource class, is used to create OCI connection pools. From an OracleOCIConnectionPool class instance, you can obtain logical connection objects. These connection objects are of the OracleOCIConnection class type. This class implements the OracleConnection interface. The Statement objects you create from the OracleOCIConnection class have the same fields and methods as OracleStatement objects you create from OracleConnection instances.

The following code shows header information for the OracleOCIConnectionPool class:

/* 
   * @param us  ConnectionPool user-id. 
   * @param p   ConnectionPool password 
   * @param name  logical name of the pool. This needs to be one in the 
   *                   tnsnames.ora configuration file. 
     @param config (optional)  Properties of the pool, if the default does not 
                suffice. Default connection configuration is min =1, max=1,
                incr=0 
                  Please refer setPoolConfig for property names. 

                 Since this is optional, pass null if the default configuration 
                 suffices. 

   * @return 
   * 
   * Notes: Choose a userid and password that can act as proxy for the users 
   *        in the getProxyConnection() method. 

            If config is null, then the following default values will take
            effect 
            CONNPOOL_MIN_LIMIT = 1 
            CONNPOOL_MAX_LIMIT = 1 
            CONNPOOL_INCREMENT = 0 

*/ 

public synchronized OracleOCIConnectionPool 
  (String       user,    String   password,  String name, Properties config) 
  throws SQLException 

/* 
 * This will use the user-id, password and connection pool name values set 
   LATER using the methods setUser, setPassword, setConnectionPoolName. 

 * @return 
 * 
 * Notes: 

     No OracleOCIConnection objects can be created on 
     this class unless the methods setUser, setPassword, setPoolConfig 
     are invoked. 
     When invoking the setUser, setPassword later, choose a userid and 
     password that can act as proxy for the users 
 *   in the getProxyConnection() method. 
 */ 
  public synchronized OracleOCIConnectionPool () 
    throws SQLException 

import oracle.jdbc.pool.*;
import oracle.jdbc.oci.*;

OracleOCIConnectionPool cpool = new OracleOCIConnectionPool
    ("SCOTT", "TIGER", "jdbc:oracle:oci:@(description=(address=(host=
    myhost)(protocol=tcp)(port=1521))(connect_data=(INSTANCE_NAME=orcl)))",
    poolConfig);

OracleOCIConnectionPool cpool = new OracleOCIConnectionPool ( );
cpool.setUser("SCOTT");
cpool.setPassword("TIGER");
cpool.setURL("jdbc:oracle:oci:@(description=(address=(host=
    myhost)(protocol=tcp)(port=1521))(connect_data=(INSTANCE_NAME=orcl)))");
cpool.setPoolConfig(poolConfig);  // In case you want to specify a different 
                                  // configuration other than the default 
                                  // values.

...
java.util.Properties p  = new java.util.Properties( );
p.put (OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "1");
p.put (OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "5");
p.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT, "2");
p.put (OracleOCIConnectionPool.CONNPOOL_TIMEOUT, "10");
p.put (OracleOCIConnectionPool.CONNPOOL_NOWAIT, "true");
cpool.setPoolConfig(p);
...

Connecting to an OCI Connection Pool

The OracleOCIConnectionPool class, through a getConnection() method call, creates an instance of the OracleOCIConnection class. This instance represents a connection. See "Datasources" for database connection descriptions that apply to all JDBC drivers.

Since the OracleOCIConnection class extends OracleConnection class, it has the functionality of this class too. Close the OracleOCIConnection objects once the user session is over, otherwise, they are closed when the pool instance is closed.

There are two ways of calling getConnection():

• OracleConnection getConnection(String user, String password) : Get a logical connection identified with the specified user and password, which can be different from that used for pool creation.

• OracleConnection getConnection() : If you do not supply the user name and password, then the default user name and password used for the creation of the connection pool are used while creating the connection objects.

As an enhancement to OracleConnection, the following new method is added into OracleOCIConnection as a way to change password for the user:

void passwordChange (String user, String oldPassword, String newPassword) 

The following code shows how an application uses connection pool with re-configuration:

import oracle.jdbc.oci.*;
import oracle.jdbc.pool.*;

public class cpoolTest
{
  public static void main (String args [])
    throws SQLException
  {
    /* pass the URL and "inst1" as the database link name from tnsnames.ora */
    OracleOCIConnectionPool cpool = new OracleOCIConnectionPool
      ("scott", "tiger", "jdbc:oracle:oci@inst1", null);

    /* create virtual connection objects from the connection pool "cpool." The 
       poolConfig can be null when using default values of min = 1, max = 1, and 
       increment = 0, otherwise needs to set the properties mentioned earlier */ 
    OracleOCIConnection conn1 = (OracleOCIConnection) cpool.getConnection 
      ("user1", password1");

    /* create few Statement objects and work on this connection, conn1 */
    Statement stmt = conn1.createStatement();
    ...
    OracleOCIConnection conn90 = (OracleOCIConnection) cpool.getConnection 
      ("user90", "password90")   /* work on statement object from virtual 
                                    connection "conn90" */
    ...
    /* if the throughput is less, increase the pool size */
    String newmin = String.valueOf (cpool.getMinLimit);
    String newmax = String.valueOf (2*cpool.getMaxLimit());
    String newincr = String.valueOf (1 + cpool.getConnectionIncrement());
    Properties newproperties = newProperties();
    newproperties.put (OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, newmin);
    newproperties.put (OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, newmax);
    newproperties.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT, newincr);
    cpool.setPoolConfig (newproperties);
    }  /* end of main */
}  /* end of cpoolTest */

Statement Handling and Caching

Statement caching is supported with OracleOCIConnectionPool. The caching improves performance by not having to open, parse and close cursors. When OracleOCIConnection.prepareStatement ("SQL query") is done, the statement cache is searched for a statement that matches the SQL query. If a match is found, we can reuse the Statement object instead of incurring the cost of creating another Statement object. The cache size can be dynamically increased or decreased. The default cache size is zero.

The following code shows the signatures of the getConnection() method:

public synchronized OracleConnection getConnection( ) 
                    throws SQLException 

/* 
 * For getting a connection to the database. 
 * 
 * @param us  Connection user-id 
 * @param p   Connection password 
 * @return     connection object 
 */ 
public synchronized OracleConnection getConnection(String us, String p) 
throws SQLException 

JNDI and the OCI Connection Pool

The Java Naming and Directory Interface (JNDI) feature makes persistent the properties of Java object so these properties can be used to construct a new instance of the object (such as cloning the object). The benefit is that the old object can be freed, and at a later time a new object with exactly the same properties can be created. The InitialContext.bind() method makes persistent the properties, either on file or in a database, while the InitialContext.lookup() method retrieves the properties from the persistent store and creates a new object with these properties.

OracleOCIConnectionPool objects can be bound and looked up using the JNDI feature. No new interface calls in OracleOCIConnectionPool are necessary.

Failover Type Events

The following are possible failover events in the OracleOCIFailover interface:

• FO_SESSION : Is equivalent to FAILOVER_MODE=SESSION in the tnsnames.ora file CONNECT_DATA flags. This means that only the user session is re-authenticated on the server-side while open cursors in the OCI application need to be re-executed.

• FO_SELECT : Is equivalent to FAILOVER_MODE=SELECT in tnsnames.ora file CONNECT_DATA flags. This means that not only the user session is re-authenticated on the server-side, but open cursors in the OCI can continue fetching. This implies that the client-side logic maintains fetch-state of each open cursor.

• FO_NONE : Is equivalent to FAILOVER_MODE=NONE in the tnsnames.ora file CONNECT_DATA flags. This is the default, in which no failover functionality is used. This can also be explicitly specified to prevent failover from happening. Additionally, FO_TYPE_UNKNOWN implies that a bad failover type was returned from the OCI driver.

• FO_BEGIN : Indicates that failover has detected a lost connection and failover is starting.

• FO_END : Indicates successful completion of failover.

• FO_ABORT : Indicates that failover was unsuccessful and there is no option of retrying.

• FO_REAUTH : indicates that a user handle has been re-authenticated.

• FO_ERROR : indicates that failover was temporarily un-successful, but it gives the application the opportunity to handle the error and retry failover. The usual method of error handling is to issue the sleep() method and retry by returning the value FO_RETRY.

• FO_RETRY : See above.

• FO_EVENT_UNKNOWN : A bad failover event.

TAF Callbacks

TAF callbacks are used in the event of the failure of one database connection, and failover to another database connection. TAF callbacks are callbacks that are registered in case of failover. The callback is called during the failover to notify the JDBC application of events generated. The application also has some control of failover.

Java TAF Callback Interface

The OracleOCIFailover interface includes the callbackFn() method, supporting the following types and events:

public interface OracleOCIFailover{

// Possible Failover Types
public static final int FO_SESSION = 1;
public static final int FO_SELECT  = 2;
public static final int FO_NONE  = 3;
public static final int;

// Possible Failover events registered with callback
public static final int FO_BEGIN   = 1;
public static final int FO_END     = 2;
public static final int FO_ABORT   = 3;
public static final int FO_REAUTH  = 4;
public static final int FO_ERROR  = 5;
public static final int FO_RETRY  = 6;
public static final int FO_EVENT_UNKNOWN = 7;

public int callbackFn (Connection conn,
                       Object ctxt, // ANy thing the user wants to save
                       int type, // One of the above possible Failover Types
                       int event ); // One of the above possible Failover Events

Configuration and Installation

The Solaris shared libraries, libheteroxa10.so and libheteroxa10_g.so, enable the HeteroRM XA feature. The Windows versions of these libraries are heteroxa10.dll and heteroxa10_g.dll. In order for the HeteroRM XA feature to work properly, these libraries need to be installed and available in either the Solaris search path or the Windows DLL path, depending on your system.

Exception Handling

When using the HeteroRM XA feature in distributed transactions, it is recommended that the application simply check for XAException or SQLException, rather than OracleXAException or OracleSQLException.

See "HeteroRM XA Messages" for a listing of HeteroRM XA messages.

HeteroRM XA Code Example

The following portion of code shows how to enable the HeteroRM XA feature.

// Create a XADataSource instance
OracleXADataSource oxds = new OracleXADataSource();
oxds.setURL(url);

// Set the nativeXA property to use HeteroRM XA feature
oxds.setNativeXA(true);

// Set the tnsEntry property to an older DB as required
oxds.setTNSEntryName("ora805");

Overview

The Oracle JDBC OCI driver supports PL/SQL index-by tables of scalar datatypes. Table 19-1 displays the supported scalar types and the corresponding JDBC typecodes.

Typical Oracle JDBC input binding, output registration, and data-access methods do not support PL/SQL index-by tables. This chapter introduces additional methods to support these types.

The OraclePreparedStatement and OracleCallableStatement classes define the additional methods. These methods include the following:

• setPlsqlIndexTable()

• registerIndexTableOutParameter()

• getOraclePlsqlIndexTable()

• getPlsqlIndexTable()

These methods handle PL/SQL index-by tables as IN, OUT (including function return values), or IN OUT parameters. For general information about PL/SQL syntax, see the PL/SQL User's Guide and Reference.

The following sections describe the methods used to bind and register PL/SQL index-by tables.

Binding IN Parameters

To bind a PL/SQL index-by table parameter in the IN parameter mode, use the setPlsqlIndexTable() method defined in the OraclePreparedStatement and OracleCallableStatement classes.

synchronized public void setPlsqlIndexTable    (int paramIndex, Object arrayData, int maxLen, int curLen, int elemSqlType,    int elemMaxLen) throws SQLException

Table 19-2 describes the arguments of the setPlsqlIndexTable() method.

The following code example uses the setPlsqlIndexTable() method to bind an index-by table as an IN parameter:

// Prepare the statement
OracleCallableStatement procin = (OracleCallableStatement) 
   conn.prepareCall ("begin procin (?); end;"); 

// index-by table bind value 
int[] values = { 1, 2, 3 }; 

// maximum length of the index-by table bind value. This 
// value defines the maximum possible "currentLen" for batch 
// updates. For standalone binds, "maxLen" should be the 
// same as "currentLen". 
int maxLen = values.length; 

// actual size of the index-by table bind value 
int currentLen = values.length; 

// index-by table element type 
int elemSqlType = OracleTypes.NUMBER; 

// index-by table element length in case the element type 
// is CHAR, VARCHAR or RAW. This value is ignored for other 
// types. 
int elemMaxLen = 0; 

// set the value 
procin.setPlsqlIndexTable (1, values, 
                           maxLen, currentLen, 
                           elemSqlType, elemMaxLen); 

// execute the call 
procin.execute (); 

Receiving OUT Parameters

This section describes how to register a PL/SQL index-by table as an OUT parameter. In addition, it describes how to access the OUT bind values in various mapping styles.

synchronized public void registerIndexTableOutParameter     (int paramIndex, int maxLen, int elemSqlType, int elemMaxLen)
   throws SQLException

// maximum length of the index-by table value. This 
// value defines the maximum table size to be returned.
int maxLen = 10;

// index-by table element type
int elemSqlType = OracleTypes.NUMBER;

// index-by table element length in case the element type
// is CHAR, VARCHAR or FIXED_CHAR. This value is ignored for other
// types
int elemMaxLen = 0;

// register the return value
funcnone.registerIndexTableOutParameter
   (1, maxLen, elemSqlType, elemMaxLen);

public Object getPlsqlIndexTable (int paramIndex)
   throws SQLException

// access the value using JDBC default mapping 
BigDecimal[] values = 
   (BigDecimal[]) procout.getPlsqlIndexTable (1); 

// print the elements 
for (int i=0; i<values.length; i++) 
   System.out.println (values[i].intValue()); 

public Datum[] getOraclePlsqlIndexTable (int paramIndex)
      throws SQLException 

// Prepare the statement 
OracleCallableStatement procout = (OracleCallableStatement)
                                  conn.prepareCall ("begin procout (?); end;");

...

// execute the call
procout.execute ();
 
// access the value using Oracle JDBC mapping
Datum[] outvalues = procout.getOraclePlsqlIndexTable (1);

// print the elements
for (int i=0; i<outvalues.length; i++)
   System.out.println (outvalues[i].intValue());

synchronized public Object getPlsqlIndexTable    (int paramIndex, Class primitiveType) throws SQLException

OracleCallableStatement funcnone = (OracleCallableStatement) 
   conn.prepareCall ("begin ? := funcnone; end;"); 

// maximum length of the index-by table value. This 
// value defines the maximum table size to be returned. 
int maxLen = 10; 

// index-by table element type 
int elemSqlType = OracleTypes.NUMBER; 

// index-by table element length in case the element type 
// is CHAR, VARCHAR or RAW. This value is ignored for other 
// types 
int elemMaxLen = 0; 

// register the return value 
funcnone.registerIndexTableOutParameter (1, maxLen, 
                                        elemSqlType, elemMaxLen); 
// execute the call 
funcnone.execute (); 

// access the value as a Java primitive array. 
int[] values = (int[]) 
   funcnone.getPlsqlIndexTable (1, java.lang.Integer.TYPE); 

// print the elements 
for (int i=0; i<values.length; i++) 
   System.out.println (values[i]);