Release 2 (9.2)
Part Number A96654-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i JDBC Developer's Guide and Reference Release 2 (9.2) Part Number A96654-01 |
|
This chapter describes the following OCI driver-specific features:
OCI driver connection pooling functionality, provided by the OracleOCIConnectionPool class, is part of the JDBC client. Enhanced connection pooling provides the following benefits:
OraclePooledConnection class, since fewer physical connections are needed to support a large number of non-current, logical connections. This is valuable since physical connections are expensive. The physical connection of the OraclePooledConnection object is available for reuse after the application is done using it. Also, since the user session is not closed on the server-side once the OraclePooledConnection object is returned to the pool of available connection objects, every new call to the getConnection() method of the OracleConnectionCacheImpl class requires that the user remain the same. For a dedicated server instance, this results in the number of backend Oracle processes being reduced as the number of in-coming connections are also reduced. To boost performance, a physical connection is locked only for the duration of a call.OracleOCIConnection object can have a different user ID and therefore point to different schemas.|
Note: The existing connection support of mapping one JDBC user session to one physical connection, and the reuse of physical connection objects using the |
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".
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.
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.
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.
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
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.
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.
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 |
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 zeroCONNPOOL_TIMEOUT must be a value greater than zero.CONNPOOL_NOWAIT must be "true" or "false" (case insensitive).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 estimate and for statistical analysis.int getActiveSize() : Retrieves the number of physical connections that are open and busy. This should be used only as 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().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():
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 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 |
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
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 ()
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 allows one JDBC connection (session) to act as proxy for other JDBC connections. A proxy session could be required for one of the following:
alter user jeff grant connect through scott with roles role1, role2;
Then the method allows you to connect as "jeff" using the already authenticated credentials of "scott". It is sometimes a security concern for the middle tier to know the passwords of all the database users. Though the created session will behave much like "jeff" was connected normally (using "jeff"/"jeff-password"), "jeff" will not have to divulge its password to the middle tier. The schema which this proxy session has access to is schema of "jeff" plus what is indicated in the list of roles. Therefore, if "scott" wants "jeff" to access its table EMP, the following code can be used:
create role role1; grant select on EMP to role1;
The role clause can also be thought as limiting "jeff's" access to only those database objects of "scott" mentioned in the list of the roles. The list of roles can be empty.
jeff"), under different users such as "scott", "scott2" assuming "scott"and "scott2" are authenticated. Transactions made under these different proxy sessions by "jeff" can be logged separately.There are three ways to create proxy sessions in the OCI driver. Roles can be associated with any of the following options:
USER NAME : This is done by supplying the user name and/or the password. The reason why the "password" option exists is so that database operations made by the user ("jeff"), can be accounted. The SQL clause is:
alter user jeff grant connect through scott authenticated using password;
Having no authenticated clause implies the default--authenticated using the user-name without the password requirement.
DISTINGUISHED NAME : This is a global name in lieu of the password of the user being proxied for. So you could say "create user jeff identified globally as:
'CN=jeff,OU=americas,O=oracle,L=redwoodshores,ST=ca,C=us';
The string after the "globally as" clause is the distinguished name. It is then necessary to authenticate as:
alter user jeff grant connect through scott authenticated using distinguished name;
CERTIFICATE : This is a more encrypted way of passing the credentials of the user (to be proxied) to the database. The certificate contains the distinguished encoded name. One way of generating it is by creating a wallet (using "runutl mkwallet"), then decoding the wallet to get the certificate. It is then necessary to authenticate as:
alter user jeff grant connect through scott authenticated using certificate;
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
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.
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 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.
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
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.
Callback registered should return the FO_ABORT event if the FO_ERROR event is passed to it.
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".
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.
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. |
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");
The Oracle JDBC OCI driver enables JDBC applications to make PL/SQL calls with index-by table parameters.
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.
| PL/SQL Types | JDBC 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.
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.
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 ();
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 |
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.
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);
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 |
|
|
Oracle mappings |
|
|
Java primitive type 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.
| Argument | Description |
|---|---|
|
|
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());
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.
| Argument | Description |
|---|---|
|
|
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());
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.
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]);
|
 Copyright © 1999, 2002 Oracle Corporation. All Rights Reserved. |
|