6
Coding Tips and Troubleshooting

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

JDBC and Multi-Threading

The Oracle JDBC drivers provide full support for programs that use multiple threads. The following example program uses the default Oracle employee database emp. The program creates a number of threads. Each thread opens a connection and sends a query to the database for the contents of the emp table. The program then displays the thread and the employee name and employee ID associated with it.

Execute the program by entering:

java JdbcMTSample [number_of_threads]

on the command line where number_of_threads is the number of threads that you want to create. If you do not specify the number of threads, then the program creates 10 by default.

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

public class JdbcMTSample extends Thread
{
    // Set default number of threads to 10
    private static int NUM_OF_THREADS = 10;

    int m_myId;

    static     int c_nextId = 1;
    static  Connection s_conn = null;

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

    public static void main (String args [])
    {
        try  
        {  
            // Load the JDBC driver //
            DriverManager.registerDriver 
                     (new oracle.jdbc.driver.OracleDriver());
    
            // If NoOfThreads is specified, then read it
            if (args.length > 1) {
                System.out.println("Error: Invalid Syntax. ");
                System.out.println("java JdbcMTSample [NoOfThreads]");
                System.exit(0);
            }
            else if (args.length == 1)
                NUM_OF_THREADS = Integer.parseInt (args[0]);
    
            // 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();
            }

            // wait for all threads to end
            for (int i = 0; i < NUM_OF_THREADS; i++)
            {
                    threadList[i].join();
            }
            
        }
        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
            conn = DriverManager.getConnection("jdbc:oracle:oci8:@", 
                                        "scott","tiger");

            // Create a Statement
            stmt = conn.createStatement ();
            
            // 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));
            
            // Close all the resources
            rs.close();
              stmt.close();
            if (conn != null)
                conn.close();
            System.out.println("Thread " + m_myId +  " is finished. ");
      }
      catch (Exception e)
      {
          System.out.println("Thread " + m_myId + " got Exception: " + e);
          e.printStackTrace();
          return;
      }
  }

}

Performance Optimization

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

Disabling Auto-Commit Mode

Auto-commit mode indicates to the database whether to issue an execute and commit after every SQL statement. 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 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 (setAutoCommit(false)), then the JDBC driver groups the connection's SQL statements into transactions that it terminates by either a commit() or rollback() statement.

Example: Disabling AutoCommit

The following example illustrates loading the driver and connecting to the database. Since 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.

// Load the Oracle JDBC driver 
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
 
    // Connect to the database 
    // You can put a database hostname after the @ sign in the connection URL. 
    Connection conn = 
      DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); 
 
    // It's faster when auto commit is off 
    conn.setAutoCommit (false); 
 
    // Create a Statement 
    Statement stmt = conn.createStatement (); 
    ...

Prefetching Rows

Oracle JDBC drivers allow you to set the number of rows to prefetch into the client while the result set is being populated during a query. The default number of rows to prefetch is 10. Prefetching row data into the client reduces the number of round trips to the server. In contrast, standard JDBC fetches the result set one row at a time, where each row requires a round trip to the database.

You can set the row prefetching value for an individual statement or for all statements in your connection. For a description of row prefetching and how to enable it, see "Row Prefetching".

Batching Updates

The Oracle JDBC drivers allow you to accumulate inserts and updates of prepared statements at the client and send them to the server in batches once it reaches a specified batch value. This feature reduces round trips to the server. The default batch value is one.

You can set the batch value for any individual Oracle prepared statement or for all Oracle prepared statements in your Oracle connection. For a description of update batching and how to enable it, see "Database Update Batching".

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

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.

Basic Debugging Procedures

This section describes four strategies for debugging a JDBC program.

Trapping Exceptions

Most errors that occur in JDBC programs are handled as exceptions. Java provides the try...catch statement to catch the exception and the printStackTrace() method to print the stack trace.

The following code fragment illustrates how you can catch SQL exceptions and print the stack trace.

 try { <some code> } 
 catch(SQLException e){ e.printStackTrace (); }

To illustrate how the JDBC drivers handle errors, the following incorrect code was intentionally added to the Employee.java sample:

// Iterate through the result and print the employee names 
// of the code 
 
try { 
  while (rset.next ()) 
System.out.println (rset.getString (5)); }  // incorrect column index
  catch(SQLException e){ e.printStackTrace (); }

Notice an error was intentionally introduced by changing the column index to 5. When you execute the program you get the following error text:

java.sql.SQLException: Invalid column index 
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:235) 
at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatemen 
t.java:1560) 
at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.jav 
a:1653) 
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:175 
) 
at Employee.main(Employee.java:41)

For more information on how the JDBC drivers handle errors, and the SQLException() and the printStackTrace() methods, see "Error Messages and JDBC".

Logging JDBC Calls

You can use the java.io.PrintStream.DriverManager.setLogStream() method to log JDBC calls. This method sets the logging/tracing PrintStream used by the DriverManager and all drivers. Insert the following line at the location in your code where you want to start logging JDBC calls:

DriverManager.setLogStream(System.out);

Net8 Tracing to Trap Network Events

You can enable client and server Net8 trace to trap the packets sent over Net8. 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 Net8 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 Net8 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. You can find more information on these parameters in the Net8 Administrator's Guide.

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.

Client-Side Tracing

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

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.

TRACE_UNIQUE_CLIENT

Default Value:

OFF

Example:

TRACE_UNIQUE_CLIENT = ON

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.

TRACE_LEVEL_SERVER

TRACE_DIRECTORY_SERVER

Purpose:

Specifies the destination directory of the trace file

Default Value:

$ORACLE_HOME/network/trace

Example:

TRACE_DIRECTORY_SERVER=/oracle/traces

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.

Using Third Party 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.

Transaction Isolation Levels and the Oracle Server

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.driver.OracleConnection class to get and set the level:

getTransactionIsolation(): gets this connection's current transaction isolation level.
setTransactionIsolation(): changes the transaction isolation level using one of the TRANSACTION_* values.

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 name of the client trace file
Default Value:	SQLNET.TRC
Example:	TRACE_FILE_CLIENT=cli_Connection1.trc

Purpose:	Specifies the destination directory of the trace file
Default Value:	$ORACLE_HOME/network/trace
Example:	TRACE_DIRECTORY_SERVER=/oracle/traces

Purpose:	Specifies the name of the server trace file
Default Value:	SERVER.TRC
Example:	TRACE_FILE_SERVER= svr_Connection1.trc

6 Coding Tips and Troubleshooting