Oracle8i JDBC Developer's Guide and Reference Release 8.1.5 A64685-01 |
|
This section describes some common problems that you might encounter while using the Oracle JDBC drivers. These problems include:
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.
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.
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 ();
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.