Skip Headers

Oracle® Database JDBC Developer's Guide and Reference
10g Release 1 (10.1)

Part Number B10979-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

28 Coding Tips and Troubleshooting

This chapter describes how to optimize and troubleshoot a JDBC application or applet, including the following topics:

28.1 JDBC and Multithreading

The Oracle JDBC drivers provide full support for programs that use Java multithreading. The following example creates a specified number of threads and lets you determine whether or not the threads will share a connection. If you choose to share the connection, then the same JDBC connection object will be used by all threads (each thread will have its own statement object, however).

Because all Oracle JDBC API methods are synchronized, if two threads try to use the connection object simultaneously, then one will be forced to wait until the other one finishes its use.

The program displays each thread ID and the employee name and employee ID associated with that thread.

Execute the program by entering:

java JdbcMTSample [number_of_threads] [share]

Where number_of_threads is the number of threads that you want to create, and share specifies that you want the threads to share the connection. If you do not specify the number of threads, then the program creates 10 by default.

/*
 * This sample is a  multi-threaded JDBC program.
 */

import java.sql.*;
import oracle.jdbc.OracleStatement;

public class JdbcMTSample extends Thread
{
  // Default no of threads to 10
  private static int NUM_OF_THREADS = 10;

  int m_myId;

  static  int c_nextId = 1;
  static  Connection s_conn = null;
  static  boolean   share_connection = false;

  synchronized static int getNextId()
  {
      return c_nextId++;
  }

  public static void main (String args [])
  {
    try  
    {  
  
      // If NoOfThreads is specified, then read it
      if ((args.length > 2)  || 
           ((args.length > 1) && !(args[1].equals("share"))))
      {
         System.out.println("Error: Invalid Syntax. ");
         System.out.println("java JdbcMTSample [NoOfThreads] [share]");
         System.exit(0);
      }

      if (args.length > 1) 
      {
         share_connection = true;
         System.out.println
                ("All threads will be sharing the same connection");
      }
  
      // get the no of threads if given
      if (args.length > 0)
         NUM_OF_THREADS = Integer.parseInt (args[0]);
  
      // get a shared connection
      if (share_connection) 
          {
          OracleDataSource ods = new OracleDataSource();          ods.setURL("jdbc:oracle:" +args[1]);          ods.setUser("scott");          ods.setPassword("tiger");          Connection s_conn = ods.getConnection();
          }
      // Create the threads
      Thread[] threadList = new Thread[NUM_OF_THREADS];

      // spawn threads
      for (int i = 0; i < NUM_OF_THREADS; i++)
      {
          threadList[i] = new JdbcMTSample();
          threadList[i].start();
      }
    
      // Start everyone at the same time
      setGreenLight ();

      // wait for all threads to end
      for (int i = 0; i < NUM_OF_THREADS; i++)
      {
          threadList[i].join();
      }

      if (share_connection)
      {
          s_conn.close();
          s_conn = null;
      }
          
    }
    catch (Exception e)
    {
       e.printStackTrace();
    }
  
  }  

  public JdbcMTSample()
  {
     super();
     // Assign an Id to the thread
     m_myId = getNextId();
  }

  public void run()
  {
    Connection conn = null;
    ResultSet     rs   = null;
    Statement  stmt = null;

    try
    {    
      // Get the connection

      if (share_connection)
        stmt = s_conn.createStatement (); // Create a Statement
      else 
      {
        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:oci:@");
        ods.setUser("scott");
        ods.setPassword("tiger");
        Connection conn = ods.getConnection();
        stmt = conn.createStatement (); // Create a Statement
      }

      while (!getGreenLight())
        yield();
          
      // Execute the Query
      rs = stmt.executeQuery ("select * from EMP");
          
      // Loop through the results
      while (rs.next())
      {
        System.out.println("Thread " + m_myId + 
                           " Employee Id : " + rs.getInt(1) + 
                           " Name : " + rs.getString(2));
        yield();  // Yield To other threads
      }
          
      // Close all the resources
      rs.close();
      rs = null;
  
      // Close the statement
      stmt.close();
      stmt = null;
  
      // Close the local connection
      if ((!share_connection) && (conn != null))
      {
         conn.close();
         conn = null;
      }
      System.out.println("Thread " + m_myId +  " is finished. ");
    }
    catch (Exception e)
    {
      System.out.println("Thread " + m_myId + " got Exception: " + e);
      e.printStackTrace();
      return;
    }
  }

  static boolean greenLight = false;
  static synchronized void setGreenLight () { greenLight = true; }
  synchronized boolean getGreenLight () { return greenLight; }

}

28.2 Performance Optimization

You can significantly enhance the performance of your JDBC programs by using any of these features:

28.2.1 Disabling Auto-Commit Mode

Auto-commit mode indicates to the database whether to issue an automatic COMMIT operation after every SQL operation. Being in auto-commit mode can be expensive in terms of time and processing effort if, for example, you are repeating the same statement with different bind variables.

By default, new connection objects are in auto-commit mode. However, you can disable auto-commit mode with the setAutoCommit() method of the connection object (either java.sql.Conection or oracle.jdbc.OracleConnection).

In auto-commit mode, the COMMIT operation occurs either when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet, the statement completes when the last row of the ResultSet has been retrieved or when the ResultSet has been closed. In more complex cases, a single statement can return multiple results as well as output parameter values. Here, the COMMIT occurs when all results and output parameter values have been retrieved.

If you disable auto-commit mode with a setAutoCommit(false) call, then you must manually commit or roll back groups of operations using the commit() or rollback() method of the connection object.

28.2.1.1 Example: Disabling AutoCommit

The following example illustrates loading the driver and connecting to the database. Because new connections are in auto-commit mode by default, this example shows how to disable auto-commit. In the example, conn represents the Connection object, and stmt represents the Statement object.

// Connect to the database 
// You can put a database hostname after the @ sign in the connection URL.
   OracleDataSource ods = new OracleDataSource();   ods.setURL("jdbc:oracle:oci:@");   ods.setUser("scott");   ods.setPassword("tiger");   Connection conn = ods.getConnection();    
// It's faster when auto commit is off 
conn.setAutoCommit (false); 

// Create a Statement 
Statement stmt = conn.createStatement (); 
...

28.2.2 Standard Fetch Size and Oracle Row Prefetching

Oracle JDBC connection and statement objects allow you to specify the number of rows to prefetch into the client with each trip to the database while a result set is being populated during a query. You can set a value in a connection object that affects each statement produced through that connection, and you can override that value in any particular statement object. The default value in a connection object is 10. Prefetching data into the client reduces the number of round trips to the server.

Similarly, and with more flexibility, JDBC 2.0 allows you to specify the number of rows to fetch with each trip, both for statement objects (affecting subsequent queries) and for result set objects (affecting row refetches). By default, a result set uses the value for the statement object that produced it. If you do not set the JDBC 2.0 fetch size, then the Oracle connection row-prefetch value is used by default.

For more information, see "Oracle Row Prefetching" and "Fetch Size".

28.2.3 Standard and Oracle Update Batching

The Oracle JDBC drivers allow you to accumulate INSERT, DELETE, and UPDATE operations of prepared statements at the client and send them to the server in batches. This feature reduces round trips to the server. You can either use Oracle update batching, which typically executes a batch implicitly once a pre-set batch value is reached, or standard update batching, where the batch is executed explicitly.

For a description of the update batching models and how to use them, see "Update Batching".

28.2.4 Mapping Between Built-in SQL and Java Types

The SQL "built-in" types are those types with system-defined names such as NUMBER, CHAR, etc. (as opposed to the Oracle objects, varray, and nested table types, which have user-defined names). In JDBC programs that access data of built-in SQL types, all type conversions are unambiguous, because the program context determines the Java type to which an SQL datum will be converted.

Table 28-1 is a subset of the information presented in Table 4-3, "Default Mappings Between SQL Types and Java Types". The table lists the one-to-one type-mapping of the SQL database type to its Java oracle.sql.* representation.

Table 28-1 Mapping of SQL Datatypes to Java Classes that Represent SQL Datatypes

SQL Datatype ORACLE Mapping - Java Classes Representing SQL Datatypes
CHAR oracle.sql.CHAR
VARCHAR2 oracle.sql.CHAR
DATE oracle.sql.DATE
DECIMAL oracle.sql.NUMBER
DOUBLE PRECISION oracle.sql.NUMBER
FLOAT oracle.sql.NUMBER
INTEGER oracle.sql.NUMBER
REAL oracle.sql.NUMBER
RAW oracle.sql.RAW
LONG RAW oracle.sql.RAW
REF CURSOR java.sql.ResultSet
CLOB LOCATOR oracle.sql.CLOB
BLOB LOCATOR oracle.sql.BLOB
BFILE oracle.sql.BFILE
nested table oracle.sql.ARRAY
varray oracle.sql.ARRAY
SQL object value If there is no entry for the object value in the type map:
  • oracle.sql.STRUCT

If there is an entry for the object value in the type map:

  • customized Java class

REF to SQL object type class that implements oracle.sql.SQLRef, typically by extending oracle.sql.REF

This mapping provides the most efficient conversion between SQL and Java data representations. It stores the usual representations of SQL data as byte arrays. It avoids re-formatting the data or performing character-set conversions (aside from the usual network conversions). It is information-preserving. This "Oracle Mapping" is the most efficient type-mapping for applications that "shovel" data from SQL to Java, or vice versa.

28.3 Common Problems

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

28.3.1 Memory Consumption for CHAR Columns Defined as OUT or IN/OUT Variables

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.

28.3.2 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.

28.3.3 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 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 ();

28.3.4 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.

28.3.5 Using statement.cancel()

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(). Due to limitations in the Java thread API, there is no acceptable workaround.

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 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.

28.4 Basic Debugging Procedures

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".

28.4.1 Oracle Net Tracing to Trap Network Events

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.

28.4.1.1 Client-Side Tracing

Set the following parameters in the SQLNET.ORA file on the client system.

28.4.1.1.1 TRACE_LEVEL_CLIENT

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

28.4.1.1.2 TRACE_DIRECTORY_CLIENT

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

28.4.1.1.3 TRACE_FILE_CLIENT

Purpose:

Specifies the name of the client trace file.

Default Value:

SQLNET.TRC

Example:

TRACE_FILE_CLIENT=cli_Connection1.trc


Note:

Ensure that the name you choose for the TRACE_FILE_CLIENT file is different from the name you choose for the TRACE_FILE_SERVER file.
28.4.1.1.4 TRACE_UNIQUE_CLIENT

Purpose:

Gives each client-side trace a unique name to prevent each trace file from being overwritten with the next occurrence of a client trace. The PID is attached to the end of the file name.

Default Value:

OFF

Example:

TRACE_UNIQUE_CLIENT = ON

28.4.1.2 Server-Side Tracing

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.

28.4.1.2.1 TRACE_LEVEL_SERVER

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

28.4.1.2.2 TRACE_DIRECTORY_SERVER

Purpose:

Specifies the destination directory of the trace file.

Default Value:

ORACLE_HOME/network/trace

Example:

TRACE_DIRECTORY_SERVER=/oracle/traces

28.4.1.2.3 TRACE_FILE_SERVER

Purpose:

Specifies the name of the server trace file.

Default Value:

SERVER.TRC

Example:

TRACE_FILE_SERVER= svr_Connection1.trc


Note:

Ensure that the name you choose for the TRACE_FILE_SERVER file is different from the name you choose for the TRACE_FILE_CLIENT file.

28.4.2 Third Party Debugging Tools

You can use tools such as JDBCSpy and JDBCTest from Intersolv to troubleshoot at the JDBC API level. These tools are similar to ODBCSpy and ODBCTest.

28.5 Transaction Isolation Levels and Access Modes

Read-only connections are supported by the Oracle server, but not by the Oracle JDBC drivers.

For transactions, the Oracle server supports only the TRANSACTION_READ_COMMITTED and TRANSACTION_SERIALIZABLE transaction isolation levels. The default is TRANSACTION_READ_COMMITTED. Use the following methods of the oracle.jdbc.OracleConnection interface to get and set the level: