Common Problems

This section describes some common problems that you might encounter while using the Oracle JDBC drivers. These problems include:

• Space Padding for CHAR Columns Defined as OUT or IN/OUT Variables

• Memory Leaks and Running Out of Cursors

• Boolean Parameters in PL/SQL Stored Procedures

• Opening More Than 16 OCI Connections for a Process

Space Padding for CHAR Columns Defined as OUT or IN/OUT Variables

In PL/SQL, CHAR columns defined as OUT or IN/OUT variables are returned to a length of 32767 bytes, padded with spaces as needed. Note that VARCHAR2 columns do not exhibit this behavior.

To avoid this problem, use the setMaxFieldSize() method on the Statement object to set a maximum limit on the length of the data that can be returned for any column. The length of the data will be the value you specify for setMaxFieldSize() padded with spaces as needed. You must select the value for setMaxFieldSize() carefully because this method is statement-specific and affects the length of all CHAR, RAW, LONG, LONG RAW, and VARCHAR2 columns.

To be effective, you must invoke the setMaxFieldSize() method before you register your OUT variables.

Memory Leaks and Running Out of Cursors

If you receive messages that you are running out of cursors or that you are running out of memory, make sure that all of 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, serious memory leaks could occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.

Similarly, you must explicitly close Connection objects to avoid leaks 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 objects on the servers side.

Boolean Parameters in PL/SQL Stored Procedures

Due to a restriction in the OCI layer, the JDBC drivers do not support the passing of Boolean parameters to PL/SQL stored procedures. If a PL/SQL procedure contains Booleans, 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 integer 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
Connection conn = DriverManager.getConnection 
("jdbc:oracle:oci8:@<hoststring>", "scott", "tiger");
CallableStatement cs = 
conn.prepareCall ("begin boolWrap(?); end;");
cs.setInt(1, 1);
cs.execute ();

Opening More Than 16 OCI Connections for a Process

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 the number of processes on the server exceeded the limit specified in the initialization file or 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 it.

---