Skip Headers

Oracle9i JDBC Developer's Guide and Reference
Release 2 (9.2)

Part Number A96654-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

17
JDBC OCI Extensions

This chapter describes the following OCI driver-specific features:

OCI Driver Connection Pooling

OCI driver connection pooling functionality, provided by the OracleOCIConnectionPool class, is part of the JDBC client. Enhanced connection pooling provides the following benefits:

A JDBC application can have multiple pools at the same time. Multiple pools can correspond to multiple application servers, or pools to different data sources. The connection pooling provided by OCI in Oracle9i allows applications to have many logical connections, all using a small set of physical connections. Each call on this logical connection will be routed on the physical connection that is available at that time. Call-duration based pooling of connections is a more scalable connection pooling solution.

For information about Oracle JDBC connection pooling and caching features that apply to all Oracle JDBC drivers, see Chapter 16, "Connection Pooling and Caching".

OCI Driver Connection Pooling: Background

With the Oracle9i JDBC OCI driver, there are 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 backend 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 backend 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 backend 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 offeron the backend. 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 backend 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.

Stateless Sessions Compared to Stateful Sessions

OCI connection pooling offers stateless physical connections and stateful sessions. If you need to work with a stateless session behavior, you can use the OracleConnectionCacheImpl interface.

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 

Importing the oracle.jdbc.pool and oracle.jdbc.oci Packages

Before you create an OCI connection pool, import the following to have Oracle OCI connection pooling functionality:

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

The oracle.jdbc.pool.* package contains the OracleDataSource, OracleConnectionPoolDataSource, and OracleOCIConnectionPool classes, in addition to classes for connection caching and event-handling. The oracle.jdbc.oci.* package contains the OracleOCIConnection class and the OracleOCIFailover interface.

Creating an OCI Connection Pool

The following code show how you create an instance of the OracleOCIConnectionPool class called cpool:

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

poolConfig is a set of properties which specify the connection pool. If poolConfig is null, then the default values are used. For example, consider the following:

As an alternative to the above constructor call, you can create an instance of the OracleOCIConnectionPool class using individual methods to specify the user, password, and connection string.

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.

Setting the OCI Connection Pool Parameters

The connection pool configuration is determined by the following OracleOCIConnectionPool class attributes:

You can configure all of these attributes dynamically. Therefore, an application has the flexibility of reading the current load (number of open connections and number of busy connections) and adjusting these attributes appropriately, using the setPoolConfig() method.


Note:

The default values for the CONNPOOL_MIN_LIMIT, CONNPOOL_MAX_LIMIT, and CONNPOOL_INCREMENT parameters are 1, 1, and 0, respectively.


The setPoolConfig() method is used to configure OCI connection pool properties. The following is a typical example of how the OracleOCIConnectionPool class attributes can be set:

...
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);
...

Observe the following rules when setting the above attributes:

Checking the OCI Connection Pool Status

To check the status of the connection pool, use the following methods from the OracleOCIConnectionPool class:

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 "Data Sources" for database connection descriptions that apply to all JDBC drivers.

Since the OracleOCIConnection class extends OracleConnection class, it has the funtionality 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():

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.


Note:

The OracleStatement object created from OracleOCIConnection has the same behavior as one that is created from OracleConnection.


Statement caching in OracleOCIConnectionPool is a little different from the standard functionality in OracleConnectionCacheImpl. The setStmtCacheSize() method sets the statement cache sizes of all the OracleOCIConnection objects retrieved from this pool. But unlike logical (OracleConnection) connection objects obtained from OracleConnectionCacheImpl, the individual cache sizes of the logical (OracleOCIConnection) connection objects can also be changed if desired. (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 

Types of Statement Caching used with the OCI Connection Pool

There are two forms of statement caching: implicit and explicit. (See Chapter 14, "Statement Caching" for a complete description of implicit and explicit statement caching.) Both forms of statement caching use the setStmtCacheSize() method. Explicit statement caching requires the JDBC application to provide a key while opening and closing Statement objects. Implicit statement caching does not require the JDBC application to provide the key; the caching is transparent to the application. Also in explicit statement caching, the fetch state of the result set is not cleared. So after doing a Statement.close(key="abc"), Connection.preparedStatement(key="abc") will return the Statement object and fetches will continue with the fetch state when the previous Statement.close(key="abc") is done.

For implicit statement caching, the fetch state is cleared and the cursor is re-executed, but the cursor meta data is cached to improve performance. In some cases, the client may also need to clear the meta data (through the clearMetaData parameter).

The following header information documents method signatures:

synchronized public void setStmtCacheSize (int size) 

  /** 
   * 
   * @param size Size of the Cache 
   * @param clearMetaData Whether the state has to be cleared or not 
   * @exception     SQLException 
   */ 
  public synchronized void setStmtCacheSize (int size, boolean clearMetaData) 

 /** 
   * Return the size of Statement Cache. 
   * @return int Size of Statement Cache. 

                If not set ie if statement caching is not enabled , 
   *             the default 0 is returned. 
   */ 
  public synchronized int getStmtCacheSize()

   /* 
   * Check whether Statement 
   * Caching is enabled for this pool or Not. 
   */ 
 public synchronized boolean isStmtCacheEnabled ()
  

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.

Middle-Tier Authentication Through Proxy Connections

Middle-tier authentication allows one JDBC connection (session) to act as proxy for other JDBC connections. A proxy session could be required for one of the following:

There are three ways to create proxy sessions in the OCI driver. Roles can be associated with any of the following options:

The following code shows signatures of the getProxyConnection() method with information about the proxy type process:

/* 
   * For creating a proxy connection. All macros are defined 
   * in OracleOCIConnectionPool.java 
   * 
   * @param proxyType  Can be one of following types 
               PROXYTYPE_USER_NAME 
                        - This will be the normal mode of specifying the user
                          name in proxyUser as in Oracle8i 

               PROXYTYPE_DISTINGUISHED_NAME 
                        - This will specify the distinguished name of the user
                          in proxyUser 

              PROXYTYPE_CERTIFICATE 
                        - This will specify the proxy certificate 

     The Properties (ie prop) should be set as follows. 

     If PROXYTYPE_USER_NAME 
        PROXY_USER_NAME and/or PROXY_USER_PASSWORD depending 
           on how the connection-pool owner was authenticated 
           to act as proxy for this proxy user 
           PROXY_USER_NAME (String) = user to be proxied for 
           PROXY_PASSWORD (String) = password of the user to be proxied for 

    else if PROXYTYPE_DISTINGUISHED_NAME 
         PROXY_DISTINGUISHED_NAME (String) = (global) distinguished name of the 
user to be proxied for 
    else if PROXYTYPE_CERTIFICATE (byte[]) 
         PROXY_CERTIFICATE = certficate containing the encoded 
                                  distinguished name 



    PROXY_ROLES (String[])  Set of roles which this proxy connection can use. 
Roles can be null, and can be associated 
with any of the above proxy methods. 

   * 
   * @return   connection object 
   * 
   * Notes: The user and password used to create OracleOCIConnectionPool() 
   *        must be allowed to act as proxy for user 'us'. 
   */ 
   public synchronized OracleConnection getProxyConnection(String proxyType,
     Properties prop) 
     throws SQLException 

OCI Driver Transparent Application Failover

Transparent Application Failover (TAF) or simply Application Failover is a feature of the OCI driver. It enables you to automatically reconnect to a database if the database instance to which the connection is made goes down. In this case, the active transactions roll back. (A transaction rollback restores the last committed transaction.) The new database connection, though created by a different node, is identical to the original. This is true regardless of how the connection was lost.

TAF is always active and does not have to be set.

For additional details regarding OCI and TAF, see the Programmer's Guide to the Oracle Call Interface.

Failover Type Events

The following are possible failover events in the OracleOCIFailover interface:

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.


Note:

The callback setting is optional.


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

Handling the FO_ERROR Event

In case of an error while failing-over to a new connection, the JDBC application is able to retry failover. Typically, the application sleeps for a while and then it retries, either indefinitely or for a limited amount of time, by having the callback return FO_RETRY.

Handling the FO_ABORT Event

Callback registered should return the FO_ABORT event if the FO_ERROR event is passed to it.

OCI HeteroRM XA

Unlike the regular JDBC XA feature which works only with Oracle8i 8.1.6 and later databases, JDBC HeteroRM XA also allows you to do XA operations in Oracle8i releases prior to 8.1.6. In general, the HeteroRM XA is recommended for use whenever possible.

HeteroRM XA is enabled through the use of the tnsEntry and nativeXA properties of the OracleXADataSource class. Table 16-2, "Oracle Extended Data Source Properties" explains these properties in detail.

For a complete discussion of XA, see Chapter 15, "Distributed Transactions".

Configuration and Installation

The Solaris shared libraries, libheteroxa9.so and libheteroxa9_g.so, enable the HeteroRM XA feature to support access to Oracle8i releases prior to release 8.1.6. The NT version of these libraries are heteroxa9.dll and heteroxa9_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 NT DLL path, depending on your system.


Note:

Libraries with the _g suffix are debug libraries.


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.


Note:

The mapping from SQL error codes to standard XA error codes does not apply to the HeteroRM XA feature.


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");

Accessing PL/SQL Index-by Tables

The Oracle JDBC OCI driver enables JDBC applications to make PL/SQL calls with index-by table parameters.


Important:

Index-by tables of PL/SQL records are not supported.


Overview

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

Table 17-1 PL/SQL Types and Corresponding JDBC Types 
PL/SQL Types JDBC Types

BINARY_INTEGER

NUMERIC

NATURAL

NUMERIC

NATURALN

NUMERIC

PLS_INTEGER

NUMERIC

POSITIVE

NUMERIC

POSITIVEN

NUMERIC

SIGNTYPE

NUMERIC

STRING

VARCHAR


Note:

Oracle JDBC does not support RAW, DATE, and PL/SQL RECORD as element types.


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:

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 17-2 describes the arguments of the setPlsqlIndexTable() method.

Table 17-2 Arguments of the setPlsqlIndexTable () Method  
Argument Description

int paramIndex

This argument indicates the parameter position within the statement.

Object arrayData

This argument is an array of values to be bound to the PL/SQL index-by table parameter. The value is of type java.lang.Object, and the value can be a Java primitive type array such as int[] or a Java object array such as BigDecimal[].

int maxLen

This argument specifies the maximum table length of the index-by table bind value which defines the maximum possible curLen for batch updates. For standalone binds, maxLen should use the same value as curLen. This argument is required.

int curLen

This argument specifies the actual size of the index-by table bind value in arrayData. If the curLen value is smaller than the size of arrayData, only the curLen number of table elements is passed to the database. If the curLen value is larger than the size of arrayData, the entire arrayData is sent to the database.

int elemSqlType

This argument specifies the index-by table element type based on the values defined in the OracleTypes class.

int elemMaxLen

This argument specifies the index-table element maximum length in case the element type is CHAR, VARCHAR, or RAW. This value is ignored for other types.

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.


Note:

The methods this section describes apply to function return values and the IN OUT parameter mode as well.


Registering the OUT Parameters

To register a PL/SQL index-by table as an OUT parameter, use the registerIndexTableOutParameter() method defined in the OracleCallableStatement class.

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

Table 17-3 describes the arguments of the registerIndexTableOutParameter() method.

Table 17-3 Arguments of the registerIndexTableOutParameter () Method
Argument Description

int paramIndex

This argument indicates the parameter position within the statement.

int maxLen

This argument specifies the maximum table length of the index-by table bind value to be returned.

int elemSqlType

This argument specifies the index-by table element type based on the values defined in the OracleTypes class.

int elemMaxLen

This argument specifies the index-by table element maximum length in case the element type is CHAR, VARCHAR, or RAW. This value is ignored for other types.

The following code example uses the registerIndexTableOutParameter() method to register an index-by table as an OUT parameter:

// 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);

Accessing the OUT Parameter Values

To access the OUT bind value, the OracleCallableStatement class defines multiple methods that return the index-by table values in different mapping styles. There are three mapping choices available in JDBC drivers:

Mappings Methods to Use

JDBC default mappings

getPlsqlIndexTable(int)

Oracle mappings

getOraclePlsqlIndexTable(int)

Java primitive type mappings

getPlsqlIndexTable(int, Class)

JDBC Default Mappings

The getPlsqlIndexTable() method with the (int) signature returns index-by table elements using JDBC default mappings.

public Object getPlsqlIndexTable (int paramIndex)
   throws SQLException

Table 17-4 describes the argument of the getPlsqlIndexTable() method.

Table 17-4 Argument of the getPlsqlIndexTable () Method
Argument Description

int paramIndex

This argument indicates the parameter position within the statement.

The return value is a Java array. The elements of this array are of the default Java type corresponding to the SQL type of the elements. For example, for an index-by table with elements of NUMERIC typecode, the element values are mapped to BigDecimal by the Oracle JDBC driver, and the getPlsqlIndexTable() method returns a BigDecimal[] array. For a JDBC application, you must cast the return value to a BigDecimal[] array to access the table element values. (See "Datatype Mappings" for a list of default mappings.)

The following code example uses the getPlsqlIndexTable() method to return index-by table elements with JDBC default mapping:

// 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()); 

Oracle Mappings

The getOraclePlsqlIndexTable() method returns index-by table elements using Oracle mapping.

public Datum[] getOraclePlsqlIndexTable (int paramIndex)
      throws SQLException 

Table 17-5 describes the argument of the getOraclePlsqlIndexTable() method.

Table 17-5 Argument of the getOraclePlsqlIndexTable () Method
Argument Description

int paramIndex

This argument indicates the parameter position within the statement.

The return value is an oracle.sql.Datum array and the elements in the Datum array will be the default Datum type corresponding to the SQL type of the element. For example, the element values of an index-by table of numeric elements are mapped to the oracle.sql.NUMBER type in Oracle mapping, and the getOraclePlsqlIndexTable() method returns an oracle.sql.Datum array that contains oracle.sql.NUMBER elements.

The following code example uses the getOraclePlsqlIndexTable() method to access the elements of a PL/SQL index-by table OUT parameter, using Oracle mapping. (The code for registration is omitted.)

// 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());
Java Primitive Type Mappings

The getPlsqlIndexTable() method with the (int, Class) signature returns index-by table elements in Java primitive types. The return value is a Java array.

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

Table 17-6 describes the arguments of the getPlsqlIndexTable() method.

Table 17-6 Arguments of the getPlsqlIndexTable () Method
Argument Description

int paramIndex

This argument indicates the parameter position within the statement.

Class primitiveType

This argument specifies a Java primitive type to which the index-by table elements are to be converted. For example, if you specify java.lang.Integer.TYPE, the return value is an int array.

The following are the possible values of this parameter:

java.lang.Integer.TYPE

java.lang.Long.TYPE

java.lang.Float.TYPE

java.lang.Double.TYPE

java.lang.Short.TYPE

The following code example uses the getPlsqlIndexTable() method to access the elements of a PL/SQL index-by table of numbers. In the example, the second parameter specifies java.lang.Integer.TYPE, so the return value of the getPlsqlIndexTable() method is an int array.

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]); 


Go to previous page Go to next page
Oracle
Copyright © 1999, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback