Skip navigation.

eDocs Home > BEA WebLogic Server 8.1 Documentation > WebLogic Server Frequently Asked Questions > FAQs: WebLogic JDBC

WebLogic Server Frequently Asked Questions

  Previous Next vertical dots separating previous/next from contents/index/pdf Contents View as PDF   Get Adobe Reader

FAQs: WebLogic JDBC

Configuration

Programming — General

Programming — Oracle


Q. Can I use the PointBase DBMS included with WebLogic Server for development or production?

A. PointBase Server is an all-Java DBMS product included in the WebLogic Server distribution solely in support of WebLogic Server evaluation, either in the form of custom trial applications or through packaged sample applications provided with WebLogic Server. Non-evaluation development and/or production use of the PointBase Server requires a separate license be obtained by the end user directly from PointBase.


Q. How can I enable Oracle Advanced Security encryption on the JDBC Oracle Thin driver with a WebLogic JDBC Connection Pool?

A. Oracle Advanced Security encryption relies on features available through connection properties in the JDBC driver from Oracle. You can specify connection properties in a WebLogic JDBC connection pool in the Properties attribute. This attribute is available on the JDBC Connection Pool —> Configuration —> General tab in the Administration Console. When WebLogic Server creates database connections for the connection pool, it passes the properties to the JDBC driver so that connections are created with the specified properties.

For example, to enable Oracle Advanced Security encryption, you may want to specify the following options:

Properties:  user=SCOTT
             oracle.net.encryption_client=ACCEPTED
             oracle.net.encryption_types_client=RC4_256
             oracle.net.crypto_checksum_client=ACCEPTED
             protocol=thin

Note: See the Oracle documentation for details about required properties for Oracle Advanced Security encryption. Properties listed above are for illustration only.

The resulting entry in the config.xml file would look like:

<JDBCConnectionPool
    DriverName="oracle.jdbc.driver.OracleDriver"
    Name="oraclePool"
    Password="{3DES}1eNn7kYGZVw="
    Properties="user=SCOTT;
    oracle.net.encryption_client=ACCEPTED;
    oracle.net.encryption_types_client=RC4_256;
    oracle.net.crypto_checksum_client=ACCEPTED;
    protocol=thin"
    URL="jdbc:oracle:thin:@server:port:sid"
/>

Note: Line breaks added for readability.


Q. When should I use a TxDataSource instead of a DataSource?

A. See "When to Enable Global Transactions in a Data Source" in the Administration Console Online Help. When you select Honor Global Transactions in the Administration Console, you create a JDBCTxDataSource in the config.xml file (the default). If you clear the Honor Global Transactions check box, you create a JDBCDataSource in the config.xml file.


Q. Which is better: all-java (Type 4) drivers or native drivers (Type 2)?

A. The advantages of Type 4 drivers are the same advantages of Java code, namely portability and safety. Since Java code runs within the Java Virtual Machine, it cannot crash the process.

The traditional advantage of Type 2 native drivers has been performance. However, with the rapid advances in Java performance, this is no longer always true. In general, JDBC driver performance is dependent on many factors, especially the SQL code used issued and the JDBC driver implementation.


Q. How should I set the connection pool initial size?

A. In a production system, set the initial size equal to the maximum size. This will create all necessary JDBC connections at server start-up.

The reason is that if initial number of connections is less than the maximum number of connections, the server has to create additional connections when it becomes loaded. When a server is under heavy load, the goal is to only do useful work with minimal overhead. That is exactly the time that you do not want to burden the server with creating additional database connections. For both performance and stability reasons, it is best to have the connection pool make all its connections at startup.

During development, set the initial size to a small value like 1. This helps the server boot faster and allows you to use a database that doesn't support as many concurrent connections.


Q. How should I set the connection pool's maximum size?

A. In production environments, the best maximum pool size depends on the application and the system. A good starting point is to set the maximum pool size equal to the execute thread count. This allows each execute thread to get its own JDBC connection without blocking while waiting for a connection in use by another thread.

Some applications can run with a maximum pool size less than the number of execute threads. This case generally occurs when there is work entering the server that does not ever need a JDBC connection, and the server has lots of threads doing I/O tasks.

Some applications require a maximum pool size greater than the number of execute threads. In this case, each thread may require more than one connection from the same connection pool. A common way this occurs is when an EJB method uses a connection and then calls a RequiresNew method on another EJB before committing its transaction. The RequiresNew EJB then gets its own connection from the pool.

Theoretically, you need as many connections as you need concurrent users to be served (though this may not be practical and connections may need to be serially reused).

Note that these recommendations do take into account any connection leaks (connections that you reserve but do not close).

During development, set the maximum size to a small value. Unless you are doing load tests against your development machine, you probably do not have very many concurrent users so you don't need many connections. Also, with fewer connections in the connection pool, it will be easier to run into situations where connections are leaking from the connection pool because application code is not closing them. It is better to encounter this during development and fix it before going into production.

If a connection is not explicitly released by an object, the connection is returned back to the connection pool when the object is destroyed or the connection is actually closed. When the references to a connection are gone, the connection is returned to the connection pool during garbage collection. Note that in case of remote references to a connection (when a connection is obtained using a data source), there is an extra delay that may look as though the connection did not get returned. This is because the distributed garbage collection is more time consuming. It may take 6 minutes or more before it is returned to the pool.

Do not rely on the garbage collection to return connections to the connection pool. It is best to track down offending code that doesn't properly return connections to the connection pool after use and fix the code.


Q. Can I enable requests to a JDBC connection pool for a database connection to wait until a connection is available?

A. Yes. You can set two JDBC connection pool properties to enable connection requests to wait for a connection:

See "Enabling Connection Requests to Wait for a Connection" in Programming WebLogic JDBC.


Q. How can I avoid ResourceExceptions when sending more requests for database connections from the connection pool than are currently available?

A. The fundamental problem is too few resources (database connections in the connection pool) for the work load. The correct response is to increase the maximum number of connections in the connection pool. Optimally designed applications only require the server to have one pool connection per execute thread.

You can also enable connection requests to wait for a connection. See "Enabling Connection Requests to Wait for a Connection" in Programming WebLogic JDBC.

Note that the proper application response to a resource exception is not to retry the request in a tight loop, which would tie up execute threads on the server.

You should design your application to gracefully fail if no connections are available. Try to ensure that you get the connection as late as possible in your application code and return it to the connection pool as early as possible so that you do not see as many NoResource exceptions. It is better to have the connection as a method level variable and close the connection in a finally block as in the following example:

try{ 
  ...
} catch(Exception handleEx) {
  ...
} finally { 
  try{ conn.close(); // always return the connection to pool
}catch (Exception ignore){} 
}

Q. When are JDBC connections returned to the connection pool?

A. It depends on how the application obtains the connection. If the connection is obtained by an EJB or other application code in a way that the connection is part of a WebLogic-managed transaction (using a tx data source, the JTS driver, and so forth), then the connection is returned to the connection pool as soon as the transaction ends, either by commit, rollback, or timeout.

If the connection is obtained by application code outside the context of a WebLogic-managed transaction, such as from a non-tx data source, the connection is returned to the pool as soon as the application code closes it. If the connection is not closed, eventually it will be garbage collected and then returned to the connection pool, but this is slow and indeterminate and can cause failures for applications that need a connection, so it is important to ensure that applications close pool connections when finished using them. Also, in JSPs and servlets, where one class may be running simultaneously in multiple threads, it is important that Connection objects be method objects, not class or instance variables, because one thread may overwrite a connection object with a new one. The overwritten connection cannot be closed, and will be unavailable (out of the connection pool) until it is garbage collected.


Q. What happens when my database is restarted or becomes unreachable? Does my connection pool stick around?

A. Yes. The pool is independent of its ability to communicate with to the DBMS. All connections in the connection pool may become defunct, but the connection pool still exists. You can configure the connection pool so that WebLogic Server tests the connections in the pool and replaces bad connections when it can. See "Testing Connection Pools and Database Connections" in Programming WebLogic JDBC for more information.

To manually restart the connection pool using the Administration Console after a database failure, you can undeploy the connection pool by removing all of its deployment targets, and then redeploy the connection pool by adding deployment targets. See "Shutting Down a JDBC Connection Pool" in the Administration Console Help.

To do this from the command line using "weblogic.Admin", set the "Targets" attribute of the pool to an empty string ("") and then set it to the desired set of targets. See "Using weblogic.Admin Commands to Target Resources" in the WebLogic Server Command Reference.


Q. What is the "Connections Total" in the console?

A. The connections total is a cumulative number that indicates how many connections were created during the existence of the pool. It is not the total number of connections currently in the connection pool. Connections get added when the connection pool grows or if a bad connection is replaced.


Q. When should I use MultiPools?

A. You can use MultiPools in one of two ways 1) for high availability in the event a database connection fails, or 2) for load balancing between JDBC connection pools. Because you can choose only one option, you need to determine the primary purpose of your MultiPool.

Note: If you implement Multipools for a JDBC application , do not configure driver-level load balancing or failover for the connection pools used by the MultiPool—the MultiPool provides the same functionality as configuring driver-level load balancing or failover.

For more information, see "Configuring and Using MultiPools" in Programming WebLogic JDBC.


Q. Are MultiPools supported in a cluster?

A. Support for MultiPools in a cluster is identical to support for basic connection pools. As far as an application is concerned, MultiPools are indistinguishable from basic connection pools. If you have an application that uses a connection pool, and will be running on all the members of a cluster, then you need to have a like-named connection pool on each member of the cluster. It is identical for MultiPools. The application will ask for a connection from the MultiPool, so there needs to be a like-named MultiPool on each member of the cluster.


Q. When should I use JTS?

A. JTS enables you to use a non-XA JDBC resource (driver) in a global transaction with other XA JDBC resources as participants in the transaction. To configure this XA emulation mode, you configure a JDBC connection pool to use a non-XA JDBC driver to create database connections, then configure a tx data source with the EnableTwoPhaseCommit attribute set to true (in the Administration Console, the attribute is "Emulate Two-Phase Commit for non-XA Driver"), and point tx data source to the JDBC connection pool. Using this mode, database updates are committed or rolled back using a local database transaction. This mode of operation is suited for the following situations:

The limitations of using JTS are as follows.

See "Emulating Two-Phase Commit" in the Administration Console Online Help for more details about this mode.


Q. How can I tell if a database is unavailable?

A. Fundamentally, there is no way to tell if a database has gone down except by trying to make a connection and failing.

Furthermore, a database can become unavailable at any time after you make and use a connection. We recommend that you write your code to handle unexpected failures, which can come in any form depending on what the client is doing when the database goes down.

WebLogic Server does provide the dbping utility to test the connection between WebLogic Server and your DBMS using a JDBC driver. See dbping in "Using the WebLogic Java Utilities" in the Command Reference.


Q. How do I configure the driver from Microsoft for SQL Server 2000?

A. See "Installing and Using the SQL Server 2000 Driver for JDBC from Microsoft" in Programming WebLogic JDBC.

Each instance of MS SQL Server must be listening on a different port. So, you can use the port number in the properties that you pass to the getConnection() method or, in case of connection pools, you can specify the port property in the following properties: 

server=machine 
Nameport=instancePort

To find the port number where each MS SQL Server instance is running, run the server network utility (in the Microsoft SQL Server program group), select the server instance, select TCP/IP, and click the properties button.

The full pathnames for the msbase.jar, msutil.jar, and mssqlserver.jar files must be in the in the CLASSPATH for the server startup script (e.g., STARTWEBLOGIC.CMD), not just in your CLASSPATH.

Also, it may be necessary to append connection properties to the URL for the connection pool in the administration console, separated by semicolons, rather than using the Properties field. For example: 

jdbc:microsoft:sqlserver://127.0.0.1:1433;user=User;password=Password;databasename=DBName

Note: BEA recommends using the WebLogic Type 4 JDBC Driver for Microsoft SQL Server to create connections to a Microsoft SQL Server database. For information about the WebLogic Type 4 JDBC Driver for Microsoft SQL Server, see WebLogic Type 4 JDBC Drivers.


Q. I have a long running transaction that makes multiple updates to an XA-compliant resource manager and a resource enlistment, prepare, or rollback operation fails with an XAException (errorCode=XAER_NOTA). What causes this behavior and how can it be prevented?

A. Some resource managers, such as Oracle, have an internal timeout mechanism that will internally roll back work associated with a transaction branch when the timeout period expires. Afterwards, when the WebLogic transaction manager invokes a XAResource.start/prepare/rollback operation on the resource, the Xid provided no longer exists within the resource manager so the resource manager responds with a XAException XAER_NOTA.

The javax.transaction.xa.XAResource interface provides a method, setTransactionTimeout, which in some driver implementations sets the resource's internal timeout interval. The WebLogic transaction manager can be instructed to invoke this method with a value equal to the global transaction timeout prior to each resource enlistment. For JDBC connection pool configurations, set the attribute "XASetTransactionTimeout" to "true" to enable this feature. Note that this JDBCConnectionPool attribute is only applicable for XA-compliant drivers. Also note that setting this attribute has no affect on XA drivers that do not implement the XAResource.setTransactionTimeout method.

For more details about setting the XASetTransactionTimeout attribute, see "Support for XAResource Transaction Timeout" in the WebLogic Server Release Notes.

The Oracle thin driver supports XAResource.setTransactionTimeout. The WebLogic jDriver for Oracle driver does not implement this method. To set the Oracle session timeout when using the WebLogic jDriver for Oracle, set the SesTm in the XA open string. The following is an example of what the XA open string might look like in the JDBCConnectionPool entry in the config.xml file: 

<:JDBCConnectionPool
DriverName= "weblogic.jdbc.oci.xa.XADataSource" 
Name="XAPool" 
Properties="user=system;password=manager; 
openString=Oracle_XA+Acc=p/system/manager+SesTm=177+db=SUPPORT+Threads=true+Sqlnet=LINUX+logDir=.+dbgFl=0x15;dataSourceName=XAPool"
Targets="myserver" />

XAER_NOTA errors are also seen during recovery. These XAER_NOTA errors are thrown for transactions that have been committed before the server restart but still exist in the transaction log at the time the server was booted. During recovery processing, for each transaction record in the transaction log, the transaction manager will inform the participating resources of the commit decision. If the resource commit directive succeeded before the restart, the resource manager will respond to a subsequent commit with XAER_NOTA because it no longer has knowledge of the Xid. The transaction manager ignores this error assuming that the commit succeeded before the crash. The reason why there are transaction log records that exist for transactions that have already completed is because the transaction manager only removes entries during checkpoint operations. A checkpoint occurs every five minutes by default and deletes transaction log files for which all records have been released. The checkpoint interval can be configured via the JTAMBean.CheckpointIntervalSeconds attribute. You can set the Checkpoint Interval in the administration console on the Domain —> Configuration —> JTA tab. See "Configuring Transactions" in the Administration Console Online Help.


Q. How do testConnsOnReserve and refreshTestMinutes work together?

A. They are almost independent. BEA recommends testing connections at reserve time, and not using the refresh testing option. Refresh testing may cause temporary exceptions when an application wants a connection and all the free connections are being tested by the refresh test. One value for using refresh testing is when there is a firewall or DBMS that kills connections if they stay idle too long. The refresh tests keep the connections all looking busy enough.

For more information about connection testing, see "Connection Testing Options" in the Administration Console Online Help.


Q. How do I use the prepared statement cache?

A. See "Increasing Performance with the Statement Cache" in the Administration Console Online Help.

There is also an article on using prepared statements at http://www.theserverside.com/resources/article.jsp?l=Prepared-Statements.


Q. Are there recommended programming practices for using JDBC connections?

A. The general rule is to get the connection from the connection pool as late as possible and give it back to the connection pool as soon as possible. Ensure that the connection is a method variable, and get and release the connection within the same method as where you use it (each thread should have its own connection). The cost of getting the connection is small, the prepared statement cache will reduce the preparation time, the set statements are small, the execute needs to be done no matter what the usage, and the close is small. It is not recommended to create the connection at ejbCreate/activate and close it on ejbRemove/pasivate.


Q. Why should I not use DriverManager.getConnection?

A. DriverManager.getConnection can cause a deadlock. In the server, all DriverManager calls are class-synchronized including many frequent calls that all drivers make, and JDBC drivers do a lot of synchronization internally. One long-waiting call can stop all JDBC work in the whole JVM and cause deadlocks. Also, you should not reregister the driver repeatedly. Regardless of the DBMS state, the one driver that is initially loaded at startup will always work.


Q. Can I use a prepared statement across multiple transactions?

A. Yes. Every transaction uses a dedicated JDBC connection, and all database interaction needs to use this connection object in order to participate in the transaction. So a prepared statement is tied to a particular connection and can't be shared with other connections. But a prepared statement can span transactions.


Q. Why do I get a java.lang.AbstractMethodError when calling a method on a driver?

A. This usually indicates that the driver has not implemented the method. For instance, you might be calling a JDBC 3.0 method on a driver that has only implemented the JDBC 2.0 methods.


Q. Why do I get "ResourceException: No Resource Available"?

A. One common reason is that you have too many consumers (connection users) for the number of configured JDBC connections in the connection pool or execute threads on the server.

Another reason may be that the refresh testing process has reserved one or more connections for testing so these connections are briefly unavailable.


Q. How do I ensure that a new database connection is created each time an EJB's container-managed transaction is started (so that I get a new authentication/authorization each time)?

A. The EJB should be tx-requires, which means it will start a transaction when called if one is not underway already, or will join the transaction in progress if there is one. Your code will use the standard JTS/JTA API to obtain and start a UserTransaction. Then you should obtain your JDBC connection from a tx data source, and it will be included in the transaction. To get a new connection each time, you could use the dynamic pool API to make a one-connection pool. We suggest configuring the server to have a one-connection pool and a tx data source for it at startup. Then when you want to do a transaction in an external client, you would destroy the initial pool and recreate it with the DBMS user you want. This will allow you to use the tx data source to get a connection, which if obtained in the context of a running UserTransaction, will get automatically included in the tx.


Q. When you look up a data source via JNDI and access a database connection from an external process, do you get a stub for the Connection instance in the WebLogic process or does it create a new connection pool with separate Connections in the local process?

A. If it is a WebLogic DataSource, then you get a stub for the Connection instance, not a connection pool in the local process.


Q. If a distributed transaction involves JMS and JDBC, how do I ensure that the JDBC update is available when the JMS message is processed?

A. The problem is that an application can receive the JMS message from the destination before the associated JDBC data is in the database.

Distributed transactions guarantee all involved changes will either succeed or fail as a unit, but cannot guarantee that they will happen exactly simultaneously (the transaction manager instructs all resource managers to commit but cannot control the timing of the completion of that operation).

For the WebLogic transaction manager, if the JDBC connection pool and the JMS server are both on the same server, and the transaction starts on the same server, the changes are committed in the order in which they were asked for by the transaction. This is not supported behavior, it just happens to be the current behavior. So if you can co-locate JMS and the JDBC connection pool, then you may have a chance.

You could send the JMS message with a delayed birth-time, and hope that this is good enough.

If the receiver fails to find the associated JDBC record, it could rollback/recover the message. You could use the WebLogic JMS redelivery delay feature to prevent the message from being redelivered instantly.


Q. If an application calls DataSource.getConnection multiple times in the same thread and transaction, will WebLogic Server handle giving me the same connection and transaction?

A. A common scenario might be to have multiple methods that are called within a transaction (begin/commit) that do something like the following:

Context ctx = new InitialContext();
DataSource ds = (javax.sql.DataSource) ctx.lookup("connpoll");
// work using Connection

In this case, all of the work will be done within the transaction and the same underlying JDBC connection will be used as long as the DataSource ds is a tx data source.


Q. Why do I get a SystemException failure when trying to enlist my XAResource in a client?

A. WebLogic Server does not allow you to register or enlist an XA resource on a client. The reason for this restriction is that a client is deemed to be less reliable than a server in terms of availability. This is also why a client is not allowed to act as a transaction coordinator and register Synchronization objects.

Your client could invoke a remote object on a server that accesses the resource within a transaction. If it's a JDBC resource, then you can configure a JDBCConnectionPool and JDBCTxDataSource using an Oracle XA driver (Oracle thin or WebLogic Type 4 driver for Oracle) and obtain a connection from the data source. Or the client could look up the data source using JNDI and retrieve and manipulate a connection in a transaction. Transaction enlistment is performed automatically.


Q. What causes an OCIW32.dll error?

A. You may see the following error message when using your JDBC driver for Oracle: "The ordinal 40 could not be loaded in the dynamic link library OCIW32.dll." This problem is caused by an out-of-date version of OCIW32.DLL in your system directory. Some programs install this file in the system directory in order to run. If you remove this file from the system directory you should no longer receive this error.


Q. How do I use Unicode codesets with the WebLogic jDriver for Oracle driver?

A. To use Unicode codesets:

  1. Install the appropriate codeset when you install Oracle. If you did not do this in the original installation, you will need to re-run the Oracle installer and install the proper codeset.
  2. Define the NLS_LANG variable in the environment where the JDBC driver is running. Do this by assigning the proper codeset to NLS_LANG in the shell from where you start the WebLogic Server.

The Oracle Developers Guide has more information about internationalization support. For general information about Unicode, see the Unicode Web site. For a list of Unicode language abbreviations, see the JavaSoft Web site.


Q. What type of object is returned by ResultSet.getObject() when using the WebLogic jDriver for Oracle?

A. WebLogic jDriver for Oracle always returns a Java object that preserves the precision of the data retrieved. It returns the following from the getObject() method:

For example, 1.0000 will be an integer. A long is returned for a value such as 123456789123.00000. If a value has a non-zero fractional component, a Double is returned if the precision of the value can be represented by a Double; otherwise a BigDecimal is returned.


Q. How do I limit the number of Oracle database connections generated by WebLogic Server?

A. You can use connection pools to limit the number of Oracle database connections generated by WebLogic Server in response to client requests. Connection pools allow T3 applications to share a fixed number of database connections. For information on how to set up connection pools, see Programming WebLogic JDBC.


Q. How do I call Oracle stored procedures that take no parameters?

A. Try this: 

  CallableStatement cstmt = conn.prepareCall("Begin procName;
      END;");
  cstmt.execute();

where procName is the name of an Oracle stored procedure. This is standard Oracle SQL syntax that works with any Oracle DBMS. You can also use the following syntax: 

 CallableStatement cstmt = conn.prepareCall("{call procName};");
  cstmt.execute();

This code, which conforms to the Java Extended SQL specification, will work with any DBMS, not just Oracle.


Q. How do I bind string values in a PreparedStatement?

A. Suppose you are trying to get the PreparedStatement class to bind Strings in a statement. The setString() method doesn't seem to work. Here is how you have set up the PreparedStatement: 

  String pstmt = "select n_name from n_table where n_name LIKE '?%'";
  PreparedStatement ps = conn.prepareStatement(pstmt);
  ps.setString(1, "SMIT");
  ResultSet rs = ps.executeQuery();

The preceding code does not work because the complete value needs to be specified in a String (without using embedded quotes) and then bound to an unquoted question mark (?). Here is the corrected code: 

  String matchvalue = "smit%";
  String pstmt = "select n_name from n_table where n_name LIKE ?";
  PreparedStatement ps = conn.prepareStatement(pstmt);
  ps.setString(1, matchvalue);
  ResultSet rs = ps.executeQuery();

Q. Why do I get unexpected characters from 8-bit character sets in WebLogic jDriver for Oracle?

A. If you are using an Oracle database with an 8-bit character set on Solaris, make sure you set NLS_LANG to the proper value on the client. If NLS_LANG is not set, it defaults to a 7-bit ASCII character set, and tries to map characters greater than ASCII 128 to a reasonable approximation (for example, &aacute;, &agrave;, &acirc; would all map to a). Other characters are mapped to a question mark (?).


Q. How do I learn what codesets are available in Oracle?

A. To find out what codesets you currently have available in Oracle, execute the following SQL query from SQLPlus at the command line: 

 SQL> SELECT value FROM v$nls_valid_values
          WHERE parameter='CHARACTERSET';

The response lists all codesets currently installed on your system. This listing will look something like the following shortened list: 

  VALUE
  -----------------------------------------------------------
  US7ASCII
  WE8DEC
  WE8HP
  US8PC437
  WE8EBCDIC37
  WE8EBCDIC500
  WE8EBCDIC285
  ...

If you want to constrain the value in the query to a specific codeset you are searching for, you can use a SQL query like the following: 

 SQL> SELECT value FROM v$nls_valid_values 
          WHERE parameter='CHARACTERSET' and VALUE='AL24UTFFSS';

This would produce the following response if the codeset is installed: 

  VALUE
  -------------------------------------------------------------
  AL24UTFFSS

You can use Oracle's installation tools to install additional codesets. Contact Oracle for more information.


Q. How do I look up an "ORA" SQLException?

A. If your WebLogic jDriver for Oracle application produces an SQLException, you can look up the Oracle error by using the oerr command. For example, if you see the following SQLException: 

  java.sql.SQLException: ORA-12536: TNS: operation would block

You can find the description of error ORA-12536 can be found with the command: 

  > oerr ora 12536

Q. What is error "ORA-6502?"

A. The default length of a string bound to an OUTPUT parameter of a CallableStatement is 128 characters. If the value you assign to the bound parameter exceeds that length, you will get this error.

You can adjust the length of the value of the bound parameter by passing an explicit length with the scale argument to the CallableStatement.registerOutputParameter() method.


Q. Why do I get an error while trying to retrieve the text for ORA-12705?

A. This error occurs when you have not set the ORACLE_HOME environment variable properly. In order to use WebLogic jDriver for Oracle, the Oracle client software needs to be installed and ORACLE_HOME must be set.

You may also see this error message if you try to use WebLogic jDriver for Oracle's internationalization capabilities with a language/codeset combination that is not installed on your system. If you get the ORA-12705 error with the correct error text, then either you have set NLS_LANG improperly, or you do not have the right codesets installed on your system.


Q. Why do I run out of resources during updates with Oracle's database link?

A. When you use Oracle's database link to update your database, you may get error "maximum number of temporary table locks exceeded" even if you close your result sets and statements when you finish.

The database link is an object in the local database that allows you to access tables, views, and so forth in a remote database. The database link is controlled by the Oracle server, so the driver has no control over its use of resources. The link appears to perform the commit (since other processes could see the records that were being created), but it doesn't free any resources until the connection is closed. The solution is to remove the database link and use the JDBC driver to do your selects, inserts, and updates.


Q. Why does executing the PreparedStatement class cause a "TRUNC fails: ORA-00932: inconsistent datatypes" error?

A. According to Oracle Metalink Bug Database Doc ID: 144784.1, in the absence of explicit data typecasting, OCI assumes that a bind variable is a CHAR data type. If the SQL statement intends to use the bind variable as a DATE data type, but OCI thought it was a CHAR, the SQL parser will have a conflict in data types. The fix is to explicitly use data conversion functions to convert the bind variables in the problem queries. For example, a select string of 

String st = "select count(*) from simple_table where TRUNC(mydate) = TRUNC(?)";

should be changed to: 

String st = "select count(*) from simple_table where TRUNC(mydate) = TRUNC(TO_DATE(?))";

Q. How do I create and update Oracle Blob fields?

A. The following code sample shows how to create and update Oracle Blob fields.

public void insert() throws SQLException {
  try {
    // Connect to the database using WebLogic JDBC connection pool
    Hashtable ht = new Hashtable();
    ht.put(Context.INITIAL_CONTEXT_FACTORY,
      "weblogic.jndi.WLInitialContextFactory");
    ht.put(Context.PROVIDER_URL, "t3://localhost:7001");
    InitialContext ctx = new InitialContext(ht);
    javax.sql.DataSource ds = (javax.sql.DataSource)
      ctx.lookup("java:comp/env/jdbc/DSName");
    Connection conn = ds.getConnection();
    // This is necessary in any case where you are doing
    // a select for update. Not doing this will result in a ORA-1002
    conn.setAutoCommit (false);
    BLOB blob = null;
    // Create a Statement
    Statement stmt = conn.createStatement ();
    // Drop the table if it exists
    try {
      stmt.execute ("drop table ImageTable");
      System.out.println("Table droped ...");
    }
    catch (SQLException e) {
      System.out.println("Table does not exist");
    }
    // Create the table
    stmt.execute ("create table ImageTable (column1 varchar2(20),
      image BLOB)");
    System.out.println("Table created ...");
    // create a blob entry in the table
    stmt.execute("insert into ImageTable values ('one', empty_blob())");
    stmt.execute("commit");
    System.out.println("inserted empty blob");
    String cmd = "select * from ImageTable for update";
    ResultSet rset = stmt.executeQuery(cmd);
    if (rset.next())  {
          blob = ((OracleResultSet)rset).getBLOB(2);
          System.out.println("got blob reference");
    }
    else System.out.println("no row to get!!!!");
    rset.close();
    blob = readFromFile();
    cmd = "update ImageTable set image = ? where column1 = 'one'";
    PreparedStatement pstmt = conn.prepareStatement(cmd);
    pstmt.setBlob(1, blob);
    pstmt.execute();
    stmt.execute("commit");
    System.out.println("blob updated");
    blob = null;
    cmd = "select * from ImageTable for update";
    rset = stmt.executeQuery(cmd);
    if (rset.next())  {
      System.out.println("get blob");
      blob = ((OracleResultSet)rset).getBLOB(2);
      // do something with blob
    }
    else
      System.out.println("no row to get (2)!!!!");
  }
  catch (SQLException sqle) {
    System.out.println("SQL Exception occured: " + sqle.getMessage());
    sqle.printStackTrace();
  }
  catch(FileNotFoundException e) {
    System.out.println("File Not Found");
  }
  catch (IOException ioe) {
    System.out.println("IO Exception" + ioe.getMessage());
  }
  catch (Exception ioe) {
    System.out.println("Exception" + ioe.getMessage());
  }
}

Q. How do I enlist an Oracle XAResource?

A. This code will only work on the server side. It cannot be run in a client. Also note that enlistment is generally done transparently for JDBC resources that implement XAResource. 

// Here is the XAResource for oracle
String URL = "jdbc:oracle:thin:@DbmsHost:DbmsPort:DbmsName";
DriverManager.registerDriver(new OracleDriver());
// Create XA Connection
OracleXADataSource oxds1 = new OracleXADataSource();
oxds1.setURL(URL);
oxds1.setUser("scott");
oxds1.setPassword("tiger");
javax.sql.XAConnection pc1  = oxds1.getXAConnection();
m_oracleResource = pc1.getXAResource ();
m_oracleConnection = pc1.getConnection();
// Here is the source code for getting the TM.
Context ctx = null;
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,
"weblogic.jndi.WLInitialContextFactory");
// Parameters for the WebLogic Server.
// Substitute the correct hostname, port number
// user name, and password for your environment:
env.put(Context.PROVIDER_URL, "t3://localhost:7001");
env.put(Context.SECURITY_PRINCIPAL, "system");
env.put(Context.SECURITY_CREDENTIALS, "managers");
ctx = new InitialContext(env);
m_tManager =
(TransactionManager)ctx.lookup("javax.transaction.TransactionManager");
//Here is the source code for registering the TM.
m_tManager.begin();
m_transaction = m_tManager.getTransaction();
weblogic.transaction.TransactionManager weblogicTManager =
((weblogic.transaction.TransactionManager) m_tManager);
weblogicTManager.registerStaticResource("oracle",m_oracleResource);
// enlist the resources with the transaction
m_transaction.enlistResource(m_oracleResource);

Q. Why do I get "ORA-00600" ?

A. This error generally means that version of Oracle server is newer than version of the driver you are using. In case you are using the Oracle thin driver, you will need to download the latest ojdbc14.jar from Oracle and put it at the beginning of your CLASSPATH (and possibly update any scripts that start the server, such as startweblogic.cmd, since they override the CLASSPATH).


Q. Why do I get "ORA-24327" ?

A. This error generally means that the environment ORACLE_HOME is not set or is set incorrectly or the D_LIBRARY_PATH or PATH does not include the right dynamic link libraries. It can also indicate a mismatch when trying to use weblogic.jdbc.oci.Driver with an earlier or later version of the Oracle client software than is supported. In that case, try to use the Oracle Thin driver instead.


Q. Why do I get "java.sql.SQLException: getOBJECT is not supported by the WebLogic JDBC Driver"?

A. When using the WebLogic JDBC connection pool and weblogic.jdbc.vendor.oracle.OracleResultSet, the error is returned (where OBJECT is the name of some Oracle object). It implies that this feature is not supported by WebLogic Server JDBC because the object type is not serializable. There are two alternatives.


Q. Why do I get an "ORA-01453" when I use SET TRANSACTION?

A. When using Oracle, the message "java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction" may be logged. This is due to a limitation in the Oracle interfaces, starting in Oracle 8.1.7. WebLogic tries to minimize the problem by not calling SET TRANSACTION if the mode already matches the current state.

 

Skip navigation bar  Back to Top Previous Next