Skip Headers

Oracle® Database JDBC Developer's Guide and Reference
10g Release 1 (10.1)

Part Number B10979-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

19 JDBC OCI Extensions

This chapter describes the following OCI driver-specific features:

19.1 OCI Driver Connection Pooling

OCI driver connection pooling functionality, provided by the OracleOCIConnectionPool class, is part of the JDBC client.

A JDBC application can have multiple pools at the same time. Multiple pools can correspond to multiple application servers, or pools to different datasources. The connection pooling provided by OCI 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.


Note:

Use OCI connection pooling if you need session multiplexing. Otherwise, we recommend using the Implicit Connection Cache; see Chapter 7, "Implicit Connection Caching" for details.

19.1.1 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.

19.1.2 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.

19.1.3 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 

19.1.3.1 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.*;

19.1.3.2 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:

  • poolConfig.put (OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "4");

  • poolConfig.put (OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "10");

  • poolConfig.put (OracleOCIConnectionPool.CONNPOOL_INCREMENT, "2");

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.

19.1.3.3 Setting the OCI Connection Pool Parameters

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

  • CONNPOOL_MIN_LIMIT : Specifies the minimum number of physical connections that can be maintained by the pool.

  • CONNPOOL_MAX_LIMIT : Specifies the maximum number of physical connections that can be maintained by the pool.

  • CONNPOOL_INCREMENT : Specifies the incremental number of physical connections to be opened when all the existing ones are busy and a call needs one more connection; the increment is done only when the total number of open physical connections is less than the maximum number that can be opened in that pool.

  • CONNPOOL_TIMEOUT : Specifies how much time must pass before an idle physical connection is disconnected; this does not affect a logical connection.

  • CONNPOOL_NOWAIT : When enabled, this attributes specifies that an error is returned if a call needs a physical connection while the maximum number of connections in the pool are busy; if disabled, a call waits until a connection is available. Once this attribute is set to "true", it cannot be reset to "false".

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:

  • CONNPOOL_MIN_LIMIT, CONNPOOL_MAX_LIMIT, and CONNPOOL_INCREMENT are mandatory.

  • CONNPOOL_MIN_LIMIT must be a value greater than zero.

  • CONNPOOL_MAX_LIMIT must be a value greater than or equal to CONNPOOL_MIN_LIMIT plus CONNPOOL_INCREMENT.

  • CONNPOOL_INCREMENT must be a value greater than or equal to zero

  • CONNPOOL_TIMEOUT must be a value greater than zero.

  • CONNPOOL_NOWAIT must be "true" or "false" (case insensitive).

19.1.3.4 Checking the OCI Connection Pool Status

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

  • int getMinLimit() : Retrieves the minimum number of physical connections that can be maintained by the pool.

  • int getMaxLimit() : Retrieves the maximum number of physical connections that can be maintained by the pool.

  • int getConnectionIncrement() : Retrieves the incremental number of physical connections to be opened when all the existing ones are busy and a call needs a connection.

  • int getTimeout() : Retrieves the specified time (in seconds) that a physical connection in a pool can remain idle before it is disconnected; the age of a connection is based on the Least Recently Used (LRU) scheme.

  • String getNoWait() : Retrieves whether the NOWAIT property is enabled. It returns a string of "true" or "false".

  • int getPoolSize() : Retrieves the number of physical connections that are open. This should be used only as an estimate and for statistical analysis.

  • int getActiveSize() : Retrieves the number of physical connections that are open and busy. This should be used only as an estimate and for statistical analysis.

  • boolean isPoolCreated() : Retrieves whether the pool has been created. The pool is actually created when OracleOCIConnection (user, password, url, poolConfig) is called or when setUser, setPassword, and setURL has been done after calling OracleOCIConnection().

19.1.4 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 */

19.1.5 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.

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 

19.1.6 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.

19.2 OCI Driver Transparent Application Failover

Transparent Application Failover (TAF) 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.


Note:

TAF does not work with the OCI Connection Pool.

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

19.2.1 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.

19.2.2 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.

19.2.3 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

19.2.3.1 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.

19.2.3.2 Handling the FO_ABORT Event

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

19.3 OCI HeteroRM XA

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

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

19.3.1 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.


Note:

Libraries with the _g suffix are debug libraries.

19.3.2 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.

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

19.4 Accessing PL/SQL Index-by Tables

The Oracle JDBC OCI driver, like the Thin 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.

19.4.1 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.

Table 19-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:

  • 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.

19.4.2 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.

Table 19-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 (); 

19.4.3 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.

19.4.3.1 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 public void registerIndexTableOutParameter     (int paramIndex, int maxLen, int elemSqlType, int elemMaxLen)
   throws SQLException

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

Table 19-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 FIXED_CHAR. 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 FIXED_CHAR. This value is ignored for other
// types
int elemMaxLen = 0;

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

19.4.3.2 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)
19.4.3.2.1 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 19-4 describes the argument of the getPlsqlIndexTable() method.

Table 19-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()); 

19.4.3.2.2 Oracle Mappings

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

public Datum[] getOraclePlsqlIndexTable (int paramIndex)
      throws SQLException 

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

Table 19-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());
19.4.3.2.3 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 19-4 describes the arguments of the getPlsqlIndexTable() method.

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