Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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 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 result set or 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 objects on the server 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 `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
    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 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.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index