Q. Why won’t the examples work?
A. Usually problems with examples are related to your environment. Here are some
troubleshooting hints:
1. If you are using a database, make sure you have run the utility utils.dbping to verify that
your JDBC driver is correctly installed and configured.
2. Run the setEnv script to make sure your CLASSPATH is correctly set in the shell or DOS
window in which you are running the examples.
3. Check the instructions for the examples to make sure you have changed any user-specific
variables in the code before compiling.
4. Verify that you are compiling with the -d option to direct the class files into the proper
directory, as defined in the example instructions.
If the example is an applet, check the CODE and CODEBASE, and make sure WebLogic Server
is running.
Q. Where can I get help with Java error messages?
A. Many questions we receive at BEA are related to generic Java error messages and are not
specific to WebLogic Server. Here are some links that contain helpful information about Java
error messages.
Q. Why did a client-server message generate a StackOverflowException?
A. If you are sending a particularly large data structure using java.io.Serialization, you may
exceed the per-thread size limit for either the Java or native stack. You can increase the stack size
by using the following command line options:
-ss Stacksize to increase the native stack size or
-oss Stacksize to increase the Java stack size,
where Stacksize is expressed as an integer followed by “k” or “m” for kbytes or mbytes. For
example,
$java -ss156k (native)
$java -oss600k (Java)
Table 9-1
Source Description
Sun’s Java Developer Connection at
http://developer.java.sun.com/develo
per/
This forum includes Questions and Answers on
a wide variety of Java topics, including error
messages. Use the Search box for fast results.
For example, type "classpath error" in the
Search box.
Sun’s Java APIs at
http://java.sun.com/products/
Check the Java API to see if there is an
exception description for the class you are
using.
The default native stack size is 128k, with a minimum value of 1000 bytes. The default java stack
size is 400k, with a minimum value of 1000 bytes.
Q. Will a JIT make my Java application run faster?
A. A Just-In-Time compiler will make some Java applications run faster. A JIT works by storing
generated machine code in memory and reusing it when possible. For example, if you execute the
same operation 1000 times in a loop, a JIT will improve performance of this operation since the
code will only be generated once. Applications with a lot of native methods will not see as much
performance improvement as pure-Java applications.
If you use a JIT, you may want to turn off the JIT during debugging to facilitate stacktracing. If
you are doing performance testing with a JIT, make sure that you execute the same test several
times in the same invocation and then throw away the first result to get an idea of how long the
transaction will take when your application is running in a steady state. The first time the code is
executed, your test will take longer (the “code generation hit”).
Q. Can I redistribute the JDK that is bundled with WebLogic Server?
A. BEA Systems has the non-exclusive right to grant a third party, such as an independent
software vendor (ISV), the right to redistribute the JDK that is bundled with WebLogic Server
without any modifications of any kind. The following are caveats to this general statement:
The ISV cannot remove or alter any proprietary legends or notices contained in the JDK.
The ISV shall not decompile, disassemble, decrypt, extract, or otherwise reverse engineer
or modify the JDK. The JDK may not be leased or assigned in whole or in part.
The ISV must enter into a signed agreement with its distributors on terms substantially
similar to those contained here in this redistribution policy explanation.
The ISV requires an end user license agreement with the product within which it embeds
WebLogic Server.
The embedding of the JDK in ISV products does not include maintenance and support of
the JDK by the JDK Provider. BEA Systems shall be solely responsible for providing
maintenance and support for its ISVs and distributors. The ISV shall be solely responsible
for providing maintenance and support for the end users of its products.
If an ISV wants to ship a JDK that is different from the JDK that BEA ships with
WebLogic Server, that ISV needs to get those bundling rights directly from Sun or HP. For
example:
Assume that BEA ships WebLogic Server 6.0 with JDK 1.3 only, that BEA ships
WebLogic Server 5.1 with JDK 1.1 only, and that an ISV wants to ship JDK 1.1 with
WebLogic Server 6.0 in an integrated product offered by that ISV. Unless BEA, for BEA
business reasons, elects to make WebLogic Server 6.0 with JDK 1.1 generally available,
the ISV couldn't ship JDK 1.1 with WebLogic Server 6.0 in an integrated product offered
by that ISV under BEA's agreement with that ISV or under BEA's agreement with Sun.
The ISV could, however, obtain its own binary distribution agreement for the JDK from
Sun and under that agreement bundle JDK 1.1 with its value added software solution
consisting of the ISV applications and WebLogic Server 6.0 integrated.
Q. Why do I get the following exception when viewing the JNDI tree?
isSerializable(class.javax.naming.Binding)
java.io.NotSerializableException:
java.io.PrintWriter at
java.io.ObjectOutputStream.OutputObject
A. The Weblogic Server JNDI implementation requires objects to be serializable, not
referencable. A PrintWriter cannot be serialized and therefore should be declared transient.
Q. Is it possible to use the Oracle database rather than PointBase with the current implementation
of the WebLogic J2EE Connector Architecture?
A. The sample provided contains a resource adapter that represents any database system. By
default, configurations are set to use PointBase. In particular, the configuration properties in the
weblogic-ra.xml file set a PointBase datasource. This can be replaced with Oracle settings.
Also, the resource adapter (in particular the ManagedConnectionFactory) must be
implemented to support Oracle. The resource adapter provided in this sample makes use of the
JDBC and therefore can support any database system which the resource adapter is configured to
represent.
Q. When deploying a resource adapter (.rar) to WebLogic Server, are its classes placed in the
WebLogic classpath?
For instance, I am deploying an EJB and a resource adapter (.rar), the EJB has no dependencies
on the .rar because the EJB is writing to the common client interface (CCI). The EJB client
application has sends/marshals as parameter classes that are defined in the .rar. For some reason
the EJB's class loader hierarchy cannot find the definition of this .rar-specific class, even though
the .rar is deploying successfully. I receive the following error on the EJB client:
java.rmi.UnmarshalException: error unmarshalling arguments; nested
exception
is:
java.lang.ClassNotFoundException:
com.mycompany.InteractionSpecImpl
A. When you pass an instance of
com.myclientcompany.server.eai.InteractionSpecImpl as an argument to your EJB,
the appServer needs to de-serialize (unmarshal) the object under the EJB
context, and it needs the required class for unmarshalling, inside the ejb-jar(raTester.jar).
So if you include the interactionspecimpl class in your ejb-jar file, then you do not need
to include those classes in your server's classpath.
Q. Why does WebLogic Server invoke the
ManagedConnection.addConnectionEventListener() function whenever the sample EJB
calls ConnectionFactory.getConnection() to connect to the EIS?
A. This is a requirement and is part of the contract between the Resource Adapter and the
application server.
Q. Why do I get an exception when I compile my EJB to use a Resource Adapter that supports
CCI?
I specify javax.resource.cci.ConnectionFactory in the resource-ref but I get the
following exception when I attempt to compile my EJB:
weblogic.xml.process.SAXValidationException:
ejb-jar.enterprise-beans.session.resource-ref.res-type.
must be one of the values:
javax.sql.DataSource,javax.jms.QueueConnectionFactory,
javax.jms.TopicConnectionFactory,
java.net.URL,
javax.mail.Session
at
weblogic.ejb20.dd.xml.EjbJarLoader_EJB11.__post_84
A. Make sure your ejb-jar.xml is referencing the EJB2.0 DTD instead of the EJB1.1 DTD.
The ConnectionFactory resource ref is only supported in the EJB 2.0 DTD.
Q. The BEA com.bea.adapter.dbms.cci.ConnectionImpl does not directly implement
javax.resource.cci.Connection. Is there a work-around for this?
A. Yes. The BEA com.bea.adapter.dbms.cci.ConnectionImpl extends
com.bea.adapter.cci.AbstractConnection; this in turn implements the Connection
interface. The Proxy is constructed using the interfaces from the most-derived class
(ConnectionImpl). The dumpFamilyTree printout shows that the getInterfaces call on the
ConnectionImpl class does NOT include the Connection interface. However, the
getInterfaces call on AbstractConnection does include the Connection interface.
The work-around is that the ConnectionImpl class must directly implement the interface class
specified in the ra.xml file. (This might be a redundant statement in the code, particularly if it
extends a class that has already implemented the class.) Then rebuild the adapter and try your test
again.
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:
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:
ConnectionReserveTimeoutSeconds
HighestNumWaiters
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
118
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:
When there is a single resource involved in the global transaction. In this case, a one-phase
commit optimization is used so heuristic outcomes and recovery failures are not an issue.
This will perform better than using an XA JDBC driver.
When the application can tolerate data inconsistencies.
The limitations of using JTS are as follows.
Data inconsistencies may result if the transaction is committed but the local transaction
fails to commit. This results in a heuristically completed global transaction.
No recovery is available for a non-XA participant in a transaction. If a transaction is
committed but the non-XA participant has not been informed of the decision before the
failure, manual intervention is required to commit the transaction.
All communications for the commit processing are done via connection that is first
established with the resource. This may result in additional RMI traffic if multiple servers
are involved using the same resource.
Only one non-XA participant may be involved with any transaction.
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;data
basename=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=tru
e+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.
1112
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
1113
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 columns of types NUMBER(n) and NUMBER(m,n): a Double is returned if the
defined precision of the column can be represented by a Double; otherwise BigDecimal is
returned.
1116
For columns of type NUMBER: Because there is no explicit precision, the Java type to
return is determined based on the actual value in each row, and this may vary from row to
row. An Integer is returned if the value has a zero-valued fractional component and the
value can be represented by an integer.
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();
1117
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, á, à, â 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:
1118
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.
1119
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)
1120
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();
1121
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());
}
}
1122
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);
1123
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.
You can switch to using the Oracle thin driver directly. That means that you will get a
connection directly to the database using the Thin driver instead of getting the connection
from a pool of JDBC connections. That means that you lose all advantages of using the
WebLogic Server JDBC subsystem, such as transactions, connection pooling, and caching
of prepared statements.
BEA recommends moving your processing to a stored procedure.
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.
Q. Can the Weblogic JDriver for MSSSQL Server connect to the database server using a trusted
connection on NT/WIN2K?
A. Our driver doesn't support trusted connections.
Q. How do I connect to an SQL Server instance that is running on a machine with multiple
instances of SQL Server 2000?
A. 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=machineName
port=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.
Q. Why does FOR UPDATE in Oracle 8 cause an ORA-01002 error?
A. The Oracle 8 server generates an ORA-01002:fetch out of sequence error message when
you use a FOR UPDATE statement with AUTOCOMMIT turned on (which is the default state
when using JDBC). This is known to happen on Oracle 8.0 and 8.1 on Solaris and on Oracle 8.1
on Windows NT. If you turn AUTOCOMMIT off, you will not receive this error. Because this
problem is due to a change in the Oracle 8 server, you should contact Oracle support for more
information.
Q. What causes an OCIW32.dll error?
A. You may receive 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. What transaction isolation levels does the WebLogic jDriver for Oracle support?
A. Your servlet application may use Oracle Thin Drivers to access a database that includes
BLOB fields. If you install and try to use WebLogic jDriver for Oracle and the same code fails
and produces an exception similar to the following:
com.roguewave.jdbtools.v2_0.LoginFailureException:
TRANSACTION_READ_UNCOMMITTED isolation level not allowed
The Stack Trace:
com.roguewave.jdbtools.v2_0.LoginFailureException:
TRANSACTION_READ_UNCOMMITTED isolation level not allowed
at
com.roguewave.jdbtools.v2_0.jdbc.JDBCServer.createConnection
(JDBCServer.java :46)
at com.roguewave.jdbtools.v2_0.ConnectionPool.getConnection_
133
(ConnectionPool.jav a:412)
at com.roguewave.jdbtools.v2_0.ConnectionPool.getConnection
(ConnectionPool.java :109)
Setting the Isolation_level to 1 in the code that calls the RogueWave JDBCServer class
works with the Oracle thin driver but fails with WebLogic jDriver for Oracle.
WebLogic jDriver for Oracle supports the following transaction isolation levels:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
According to the Oracle documentation, the Oracle DBMS only supports these two isolation
levels. Unlike other JDBC drivers, WebLogic’s drivers throw an exception if you try to use an
isolation level that is unsupported. Some drivers silently ignore attempts to set an unsupported
isolation level. WebLogic suggests testing whether the Oracle thin driver is not just ignoring
settings for unsupported isolation events.
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 Developers Guide has more information about internationalization support. For general
information about Unicode see the Unicode Web site at http://www.unicode.org. For a list of
Unicode language abbreviations, see the JavaSoft Web site at
http://java.sun.com/products/jdk/1.2/docs/guide/internat/encoding.doc.html.
Q. How do I use OS Authentication with WebLogic jDriver for Oracle and Connection Pools?
A. Using OS authentication in connection pools essentially means that you are using the UserId
of the user who started WebLogic Server. OS authentication is available on Windows and UNIX.
This means that database security will rely strictly on the security of WebLogic; that is, if you are
allowed to make a client connection to the WebLogic Server and access the pool, then you can
get to the database.
134
You can do this with WebLogic jDriver for Oracle because Oracle uses the process owner to
determine who is attempting the connection. In the case of WebLogic JDBC, this is always the
user that started the WebLogic Server.
To set up your Oracle instance to use this feature, your DBA needs to follow these basic steps.
The full procedure is described in more detail in your Oracle documentation.
1. Add the following line to the INIT[sid].ORA file:
OS_AUTHENT_PREFIX = OPS$
Note that the string “OPS$” is arbitrary and up to the DBA.
2. Log in to the Oracle server as SYSTEM.
3. Create a user named OPS$userid, where userid is some operating system login ID. This
user should be granted the standard privileges (for example, CONNECT and RESOURCE).
4. Once the userid is set up, you can connect with WebLogic jDriver for Oracle by specifying
“/” as the username property and “/” as the password property. Here is an example for
testing this connection with the dbping utility:
$ java utils.dbping ORACLE "/" "" myserver
Here is a code example for WebLogic jDriver for Oracle:
Properties props = new Properties();
props.put("user", "/");
props.put("password", "/");
props.put("server", "myserver");
Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn = myDriver.connect("jdbc:weblogic:oracle",
props);
5. Use the Administration Console to set the attribute for your connection pool. The following
code is an example of a JDBC connection pool configuration using the WebLogic jDriver
for Oracle:
Name="myPool"
Targets="myserver,server1"
DriverName="weblogic.jdbc.oci.Driver"
InitialCapacity="1"
135
MaxCapacity="10"
CapacityIncrement="2"
Properties="databaseName=myOracleDB"
Q. What type of object is returned by ResultSet.getObject()?
A. WebLogic jDriver for Oracle always returns a Java object that preserves the precision of the
data retrieved. WebLogic jDriver for Oracle returns the following from the getObject()
method:
For columns of types NUMBER(n) and NUMBER(m,n): a Double is returned if the
defined precision of the column can be represented by a Double; otherwise BigDecimal is
returned.
For columns of type NUMBER: Because there is no explicit precision, the Java type to
return is determined based on the actual value in each row, and this may vary from row to
row. An Integer is returned if the value has a zero-valued fractional component and the
value can be represented by an integer.
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 at http://e-docs.bea.com/wls/docs81/jdbc.html.
Q. How do I call Oracle stored procedures that take no parameters?
A. Here is what we use that works:
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 might also use the following syntax:
CallableStatement cstmt = conn.prepareCall("{call procName};");
cstmt.execute();
136
This code, which conforms to the Java Extended SQL spec, 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 unset, it defaults to a 7-bit ASCII
character set, and tries to map characters greater than ASCII 128 to a reasonable approximation
(for example, á, à, â 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 of all codesets currently installed on your system. This listing will look
something like the following shortened list:
137
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
might 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 at
http://www.oracle.com/ for more information.
Q. How do I look up an "ORA" SQLException?
A. If your WebLogic jDriver for Oracle application produces an SQLException such as:
java.sql.SQLException: ORA-12536: TNS: operation would block
You can look up an Oracle error by using the oerr command. For example, 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.
138
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 —
a common mistake. 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 get error "ORA-03120" when trying to access multi-byte characters from the
CLOB/NCLOB column?
A. Accessing exceeded characters would result in ORA-03120 error when getting the length of
the clob from the CLOB/NCLOB column returns a bigger value than the actual length from the
OCI layer. Using Oracle 8.1.6.3 solves this problem.
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. Why do I get an “ORA-01400: Cannot insert NULL into column name” when inserting a
blank string?
A. This is a known Oracle issue. When inserting or updating a value for a varchar2, if you try
to insert an empty string (“”), Oracle interprets the value as NULL. If there is a NOT NULL
restriction on the column in which you are inserting the value, the database throws the
ORA-01400 error.
0 comments:
Post a Comment