This appendix describes how to troubleshoot a Java Database Connectivity (JDBC) application or applet, and contains the following topics:
This section describes some common problems that you might encounter while using the Oracle JDBC drivers. These problems include:
In PL/SQL, when a CHAR
or a VARCHAR
column is defined as a OUT
or IN/OUT
variable, the driver allocates a CHAR
array of 32512 chars. This can cause a memory consumption problem. Note that VARCHAR2
columns do not exhibit this behavior.
At previous releases, the solution to the problem was to invoke the Statement.setMaxFieldSize
method. A better solution is to use OracleCallableStatement.registerOutParameter
.
We encourage you always to call registerOutParameter(int paramIndex, int sqlType, int scale, int maxLength)
on each CHAR
or VARCHAR
column. This method is defined in oracle.jdbc.driver.OracleCallableStatement
. Use the fourth argument, maxLength
, to limit the memory consumption. maxLength
tells the driver how many characters are necessary to store this column. The column will be truncated if the character array cannot hold the column data. The third argument, scale
, is ignored by the driver.
If you receive messages that you are running out of cursors or that you are running out of memory, make sure that all your Statement
and ResultSet
objects are explicitly closed. The Oracle JDBC drivers do not have finalizer methods. They perform cleanup routines by using the close
method of the ResultSet
and Statement
classes. If you do not explicitly close your result set and statement objects, significant memory leaks can occur. You could also run out of cursors in the database. Closing a statement releases the corresponding cursor in the database.
Similarly, you must explicitly close Connection
objects to avoid leaking and running out of cursors on the server side. When you close the connection, the JDBC driver closes any open statement objects associated with it, thus releasing the cursor on the server side.
The JDBC drivers do not support the passing of BOOLEAN
parameters to PL/SQL stored procedures. If a PL/SQL procedure contains BOOLEAN
values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an INT
and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from INT
to BOOLEAN
.
The following is an example of a stored procedure, BOOLPROC
, that attempts to pass a BOOLEAN
parameter, and a second procedure, BOOLWRAP
, that performs the substitution of an INT
value for the BOOLEAN
.
CREATE OR REPLACE PROCEDURE boolproc(x boolean)
AS
BEGIN
[...]
END;
CREATE OR REPLACE PROCEDURE boolwrap(x int)
AS
BEGIN
IF (x=1) THEN
boolproc(TRUE);
ELSE
boolproc(FALSE);
END IF;
END;
// Create the database connection from a DataSource
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:oci:@<...hoststring...>");
ods.setUser("scott");
ods.setPassword("tiger");
Connection conn = ods.getConnection();
CallableStatement cs = conn.prepareCall ("begin boolwrap(?); end;");
cs.setInt(1, 1);
cs.execute ();
You might find that you are not able to open more than approximately 16 JDBC-OCI connections for a process at any given time. The most likely reasons for this would be either that the number of processes on the server exceeded the limit specified in the initialization file, or that the per-process file descriptors limit was exceeded. It is important to note that one JDBC-OCI connection can use more than one file descriptor (it might use anywhere between 3 and 4 file descriptors).
If the server allows more than 16 processes, then the problem could be with the per-process file descriptor limit. The possible solution would be to increase this limit.
The JDBC standard method Statement.cancel
attempts to cleanly stop the execution of a SQL statement by sending a message to the database. In response, the database stops execution and replies with an error message. The Java thread that invoked Statement.execute
waits on the server, and continues execution only when it receives the error reply message invoked by the other thread's call to Statement.cancel
.
As a result, Statement.cancel
relies on the correct functioning of the network and the database. If either the network connection is broken or the database server is hung, the client does not receive the error reply to the cancel message. Frequently, when the server process dies, JDBC receives an IOException
that frees the thread that invoked Statement.execute
. In some circumstances, the server is hung, but JDBC does not receive an IOException
. Statement.cancel
does not free the thread that initiated the Statement.execute
.
When JDBC does not receive an IOException
, Oracle Net may eventually time out and close the connection. This causes an IOException
and frees the thread. This process can take many minutes. For information on how to control this time-out, see the description of the readTimeout
property for OracleDatasource.setConnectionProperties
. You can also tune this time-out with certain Oracle Net settings. See the Oracle Database Net Services Administrator's Guide for more information.
The JDBC standard method Statement.setQueryTimeout
relies on Statement.cancel
. If execution continues longer than the specified time-out interval, then the monitor thread calls Statement.cancel
. This is subject to all the same limitations described previously. As a result, there are cases when the time-out does not free the thread that invoked Statement.execute
.
The length of time between execution and cancellation is not precise. This interval is no less than the specified time-out interval but can be several seconds longer. If the application has active threads running at high priority, then the interval can be arbitrarily longer. The monitor thread runs at high priority, but other high priority threads may keep it from running indefinitely. Note that the monitor thread is started only if there are statements executed with non zero time-out. There is only one monitor thread that monitors all Oracle JDBC statement execution.
Statement.cancel
and Statement.setQueryTimeout
are not supported in the server-side internal driver. The server-side internal driver runs in the single-threaded server process; the Oracle JVM implements Java threads within this single-threaded process. If the server-side internal driver is executing a SQL statement, then no Java thread can call Statement.cancel
. This also applies to the Oracle JDBC monitor thread.
Firewall timeout for idle-connections may sever a connection. This can cause JDBC applications to hang while waiting for a connection. You can perform one or more of the following actions to avoid connections from being severed due to firewall timeout:
If you are using connection caching or connection pooling, then always set the inactivity timeout value on the connection cache to be shorter than the firewall idle timeout value.
Pass oracle.net.READ_TIMEOUT
as connection property to enable read timeout on socket. The timeout value is in milliseconds.
For both JDBC OCI and JDBC Thin drivers, use net descriptor to connect to the database and specify the ENABLE=BROKEN
parameter in the DESCRIPTION
clause in the connect descriptor. Also, set a lower value for tcp_keepalive_interval
.
Enable Oracle Net DCD by setting SQLNET.EXPIRE_TIME=1
in the sqlnet.ora
file on the server-side.
This section describes strategies for debugging a JDBC program:
For information about processing SQL exceptions, including printing stack traces to aid in debugging, see "Processing SQL Exceptions".
You can enable client and server Oracle-Net trace to trap the packets sent over Oracle Net. You can use client-side tracing only for the JDBC OCI driver; it is not supported for the JDBC Thin driver. You can find more information on tracing and reading trace files in the Oracle Net Services Administrator's Guide.
The trace facility produces a detailed sequence of statements that describe network events as they execute. "Tracing" an operation lets you obtain more information on the internal operations of the event. This information is output to a readable file that identifies the events that led to the error. Several Oracle Net parameters in the SQLNET.ORA
file control the gathering of trace information. After setting the parameters in SQLNET.ORA
, you must make a new connection for tracing to be performed.
The higher the trace level, the more detail is captured in the trace file. Because the trace file can be hard to understand, start with a trace level of 4 when enabling tracing. The first part of the trace file contains connection handshake information, so look beyond this for the SQL statements and error messages related to your JDBC program.
Note:
The trace facility uses a large amount of disk space and might have significant impact upon system performance. Therefore, enable tracing only when necessary. Set the following parameters in the SQLNET.ORA
file on the client system.
Purpose:
Turns tracing on/off to a certain specified level.
Default Value:
0 or OFF
Available Values:
0 or OFF - No trace output
4 or USER - User trace information
10 or ADMIN - Administration trace information
16 or SUPPORT - WorldWide Customer Support trace information
Example:
TRACE_LEVEL_CLIENT=10
Purpose:
Specifies the destination directory of the trace file.
Default Value:
ORACLE_HOME
/network/trace
Example:
UNIX: TRACE_DIRECTORY_CLIENT=/oracle/traces
Windows: TRACE_DIRECTORY_CLIENT=C:\ORACLE\TRACES
Set the following parameters in the SQLNET.ORA
file on the server system. Each connection will generate a separate file with a unique file name.
Purpose:
Turns tracing on/off to a certain specified level.
Default Value:
0 or OFF
Available Values:
0 or OFF
- No trace output
4 or USER
- User trace information
10 or ADMIN
- Administration trace information
16 or SUPPORT
- WorldWide Customer Support trace information
Example:
TRACE_LEVEL_SERVER=10