Skip Headers

Oracle9i JDBC Developer's Guide and Reference
Release 2 (9.2)

Part Number A96654-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

12
Performance Extensions

This chapter describes the Oracle performance extensions to the JDBC standard.

In the course of discussing update batching, it also includes a discussion of the standard update-batching model provided with JDBC 2.0.

This chapter covers the following topics:

Update Batching

You can reduce the number of round trips to the database, thereby improving application performance, by grouping multiple UPDATE, DELETE, or INSERT statements into a single "batch" and having the whole batch sent to the database and processed in one trip. This is referred to in this manual as update batching and in the Sun Microsystems JDBC 2.0 specification as batch updates.

This is especially useful with prepared statements, when you are repeating the same statement with different bind variables.

With Oracle8i release 8.1.6 and higher, Oracle JDBC supports two distinct models for update batching:

Overview of Update Batching Models

This section compares and contrasts the general models and types of statements supported for standard update batching and Oracle update batching.

Oracle Model versus Standard Model

Oracle update batching uses a batch value that typically results in implicit processing of a batch. The batch value is the number of operations you want to batch (accumulate) for each trip to the database. As soon as that many operations have been added to the batch, the batch is executed. Note the following:

Standard update batching is a manual, explicit model. There is no batch value. You manually add operations to the batch and then explicitly choose when to execute the batch.

Oracle update batching is a more efficient model because the driver knows ahead of time how many operations will be batched. In this sense, the Oracle model is more static and predictable. With the standard model, the driver has no way of knowing in advance how many operations will be batched. In this sense, the standard model is more dynamic in nature.

If you want to use update batching, here is how to choose between the two models:

Types of Statements Supported

As implemented by Oracle, update batching is intended for use with prepared statements, when you are repeating the same statement with different bind variables. Be aware of the following:

Oracle Update Batching

The Oracle update batching feature associates a batch value (limit) with each prepared statement object. With Oracle update batching, instead of the JDBC driver executing a prepared statement each time its executeUpdate() method is called, the driver adds the statement to a batch of accumulated execution requests. The driver will pass all the operations to the database for execution once the batch value is reached. For example, if the batch value is 10, then each batch of 10 operations will be sent to the database and processed in one trip.

A method in the OracleConnection class allows you to set a default batch value for the Oracle connection as a whole, and this batch value is relevant to any Oracle prepared statement in the connection. For any particular Oracle prepared statement, a method in the OraclePreparedStatement class allows you to set a statement batch value that overrides the connection batch value. You can also override both batch values by choosing to manually execute the pending batch.


Notes:
  • Do not mix standard update batching syntax with Oracle update batching syntax in the same application. The JDBC driver will throw an exception when you mix these syntaxes.
  • Disable auto-commit mode if you use either update batching model. In case an error occurs while you are executing a batch, this allows you the option of committing or rolling back the operations that executed successfully prior to the error.

Oracle Update Batching Characteristics and Limitations

Note the following limitations and implementation details regarding Oracle update batching:

Setting the Connection Batch Value

You can specify a default batch value for any Oracle prepared statement in your Oracle connection. To do this, use the setDefaultExecuteBatch() method of the OracleConnection object. For example, the following code sets the default batch value to 20 for all prepared statement objects associated with the conn connection object:

((OracleConnection)conn).setDefaultExecuteBatch(20);

Even though this sets the default batch value for all the prepared statements of the connection, you can override it by calling setDefaultBatch() on individual Oracle prepared statements.

The connection batch value will apply to statement objects created after this batch value was set.

Note that instead of calling setDefaultExecuteBatch(), you can set the defaultBatchValue Java property if you use a Java Properties object in establishing the connection. See "Specifying a Database URL and Properties Object".

Setting the Statement Batch Value

Use the following steps to set the statement batch value for a particular Oracle prepared statement. This will override any connection batch value set using the setDefaultExecuteBatch() method of the OracleConnection instance for the connection in which the statement executes.

  1. Write your prepared statement and specify input values for the first row:
    PreparedStatement ps = conn.prepareStatement 
                                  ("INSERT INTO dept VALUES (?,?,?)");
    ps.setInt (1,12);
    ps.setString (2,"Oracle");
    ps.setString (3,"USA");
    
    
  2. Cast your prepared statement to an OraclePreparedStatement object, and apply the setExecuteBatch() method. In this example, the batch size of the statement is set to 2.
    ((OraclePreparedStatement)ps).setExecuteBatch(2);
    
    

    If you wish, insert the getExecuteBatch() method at any point in the program to check the default batch value for the statement:

    System.out.println (" Statement Execute Batch Value " +
                       ((OraclePreparedStatement)ps).getExecuteBatch());
    
    
  3. If you send an execute-update call to the database at this point, then no data will be sent to the database, and the call will return 0.
    // No data is sent to the database by this call to executeUpdate
    System.out.println ("Number of rows updated so far: "
                                      + ps.executeUpdate ());
    
    
  4. If you enter a set of input values for a second row and an execute-update, then the number of batch calls to executeUpdate() will be equal to the batch value of 2. The data will be sent to the database, and both rows will be inserted in a single round trip.
    ps.setInt (1, 11);
    ps.setString (2, "Applications");
    ps.setString (3, "Indonesia");
    
    int rows = ps.executeUpdate ();
    System.out.println ("Number of rows updated now: " + rows);
    
    ps.close ();
    

Checking the Batch Value

To check the overall connection batch value of an Oracle connection instance, use the OracleConnection class getDefaultExecuteBatch() method:

Integer batch_val = ((OracleConnection)conn).getDefaultExecuteBatch();

To check the particular statement batch value of an Oracle prepared statement, use the OraclePreparedStatement class getExecuteBatch() method:

Integer batch_val = ((OraclePreparedStatement)ps).getExecuteBatch();


Note:

If no statement batch value has been set, then getExecuteBatch() will return the connection batch value.


Overriding the Batch Value

If you want to execute accumulated operations before the batch value in effect is reached, then use the sendBatch() method of the OraclePreparedStatement object.

For this example, presume you set the connection batch value to 20. (This sets the default batch value for all prepared statement objects associated with the connection to 20.) You could accomplish this by casting your connection to an OracleConnection object and applying the setDefaultExecuteBatch() method for the connection, as follows:

((OracleConnection)conn).setDefaultExecuteBatch (20);

Override the batch value as follows:

  1. Write your prepared statement and specify input values for the first row as usual, then execute the statement:
    PreparedStatement ps =
       conn.prepareStatement ("insert into dept values (?, ?, ?)");
        
    ps.setInt (1, 32);
    ps.setString (2, "Oracle");
    ps.setString (3, "USA");
    
    System.out.println (ps.executeUpdate ()); 
    
    

    The batch is not executed at this point. The ps.executeUpdate() method returns "0".

  2. If you enter a set of input values for a second operation and call executeUpdate() again, the data will still not be sent to the database, because the batch value in effect for the statement is the connection batch value: 20.
    ps.setInt (1, 33);
    ps.setString (2, "Applications");
    ps.setString (3, "Indonesia");
    
    // this batch is still not executed at this point
    int rows = ps.executeUpdate ();  
        
    System.out.println ("Number of rows updated before calling sendBatch: "
                            + rows);
    
    

    Note that the value of rows in the println statement is "0".

  3. If you apply the sendBatch() method at this point, then the two previously batched operations will be sent to the database in a single round trip. The sendBatch() method also returns the total number of updated rows. This property of sendBatch() is used by println to print the number of updated rows.
    // Execution of both previously batched executes will happen
    // at this point. The number of rows updated will be
    // returned by sendBatch.
    rows = ((OraclePreparedStatement)ps).sendBatch ();
    
    System.out.println ("Number of rows updated by calling sendBatch: "
                            + rows);
    ps.close ();
    
    

Committing the Changes in Oracle Batching

After you execute the batch, you must still commit the changes, presuming auto-commit is disabled as recommended.

Calling commit() on the connection object in Oracle batching not only commits operations in batches that have been executed, but also issues an implicit sendBatch() call to execute all pending batches. So commit() effectively commits changes for all operations that have been added to a batch.

Update Counts in Oracle Batching

In a non-batching situation, the executeUpdate() method of an OraclePreparedStatement object will return the number of database rows affected by the operation.

In an Oracle batching situation, this method returns the number of rows affected at the time the method is invoked, as follows:

Similarly, the sendBatch() method of an OraclePreparedStatement object returns the total number of rows affected by all operations in the batch.

Example: Oracle Update Batching

The following example illustrates how you use the Oracle update batching feature. It assumes you have imported the oracle.driver.* interfaces.

...
Connection conn = 
  DriverManager.getConnection("jdbc:oracle:oci:","scott","tiger"); 
 
conn.setAutoCommit(false);

PreparedStatement ps = 
  conn.prepareStatement("insert into dept values (?, ?, ?)"); 
     
//Change batch size for this statement to 3 
((OraclePreparedStatement)ps).setExecuteBatch (3);
 
ps.setInt(1, 23); 
ps.setString(2, "Sales"); 
ps.setString(3, "USA"); 
ps.executeUpdate(); //JDBC queues this for later execution 
 
ps.setInt(1, 24); 
ps.setString(2, "Blue Sky"); 
ps.setString(3, "Montana"); 
ps.executeUpdate(); //JDBC queues this for later execution 
 
ps.setInt(1, 25); 
ps.setString(2, "Applications"); 
ps.setString(3, "India"); 
ps.executeUpdate(); //The queue size equals the batch value of 3 
                    //JDBC sends the requests to the database

ps.setInt(1, 26); 
ps.setString(2, "HR"); 
ps.setString(3, "Mongolia"); 
ps.executeUpdate(); //JDBC queues this for later execution
 
((OraclePreparedStatement)ps).sendBatch(); // JDBC sends the queued request
conn.commit();

ps.close();
...


Note:

Updates deferred through batching can affect the results of other queries. In the following example, if the first query is deferred due to batching, then the second will return unexpected results:

UPDATE emp SET name = "Sue" WHERE name = "Bob";
SELECT name FROM emp WHERE name = "Sue";

Standard Update Batching

Oracle implements the standard update batching model according to the Sun Microsystems JDBC 2.0 Specification. Because it is a JDBC 2.0 feature, it is intended for use in a JDK 1.2.x environment. To use standard update batching in a JDK 1.1.x environment, you must cast to Oracle statement objects.

This model, unlike the Oracle update batching model, depends on explicitly adding statements to the batch using an addBatch() method and explicitly executing the batch using an executeBatch() method. (In the Oracle model, you invoke executeUpdate() as in a non-batching situation, but whether an operation is added to the batch or the whole batch is executed is typically determined implicitly, depending on whether a pre-determined batch value is reached.)


Notes:
  • Do not mix standard update batching syntax with Oracle update batching syntax in the same application. The Oracle JDBC driver will throw exceptions when these syntaxes are mixed.
  • Disable auto-commit mode if you use either update batching model. In case an error occurs while you are executing a batch, this allows you the option of committing or rolling back the operations that executed successfully prior to the error.

Limitations in the Oracle Implementation of Standard Batching

Note the following limitations and implementation details regarding Oracle's implementation of standard update batching:

Adding Operations to the Batch

When any statement object is first created, its statement batch is empty. Use the standard addBatch() method to add an operation to the statement batch. This method is specified in the standard java.sql.Statement, PreparedStatement, and CallableStatement interfaces, which are implemented by interfaces oracle.jdbc.OracleStatement, OraclePreparedStatement, and OracleCallableStatement, respectively.

For a Statement object (or OracleStatement), the addBatch() method takes a Java string with a SQL operation as input. For example (assume a Connection instance conn):

...
Statement stmt = conn.createStatement();

stmt.addBatch("INSERT INTO emp VALUES(1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO dept VALUES(260, 'Sales')");
stmt.addBatch("INSERT INTO emp_dept VALUES(1000, 260)");
...

At this point, three operations are in the batch.

(Remember, however, that in the Oracle implementation of standard update batching, you will probably see no performance improvement in batching generic statements.)

For prepared statements, update batching is used to batch multiple executions of the same statement with different sets of bind parameters. For a PreparedStatement or OraclePreparedStatement object, the addBatch() method takes no input--it simply adds the operation to the batch using the bind parameters last set by the appropriate setXXX() methods. (This is also true for CallableStatement or OracleCallableStatement objects, but remember that in the Oracle implementation of standard update batching, you will probably see no performance improvement in batching callable statements.)

For example (again assuming a Connection instance conn):

...
PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();
...

At this point, two operations are in the batch.

Because a batch is associated with a single prepared statement object, you can batch only repeated executions of a single prepared statement, as in this example.

Executing the Batch

To execute the current batch of operations, use the executeBatch() method of the statement object. This method is specified in the standard Statement interface, which is extended by the standard PreparedStatement and CallableStatement interfaces.

Following is an example that repeats the prepared statement addBatch() calls shown previously and then executes the batch:

...
PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();

int[] updateCounts = pstmt.executeBatch();
...

The executeBatch() method returns an int array, typically one element per batched operation, indicating success or failure in executing the batch and sometimes containing information about the number of rows affected. This is discussed in "Update Counts in the Oracle Implementation of Standard Batching".


Notes:
  • After calling addBatch(), you must call either executeBatch() or clearBatch() before a call to executeUpdate(), otherwise there will be a SQL exception.
  • When a batch is executed, operations are performed in the order in which they were batched.
  • The statement batch is reset to empty once executeBatch() has returned.
  • An executeBatch() call closes the statement object's current result set, if one exists.

Committing the Changes in the Oracle Implementation of Standard Batching

After you execute the batch, you must still commit the changes, presuming auto-commit is disabled as recommended.

Calling commit() commits non-batched operations and commits batched operations for statement batches that have been executed, but for the Oracle implementation of standard batching, has no effect on pending statement batches that have not been executed.

Clearing the Batch

To clear the current batch of operations instead of executing it, use the clearBatch() method of the statement object. This method is specified in the standard Statement interface, which is extended by the standard PreparedStatement and CallableStatement interfaces.

Following is an example that repeats the prepared statement addBatch() calls shown previously but then clears the batch under certain circumstances:

...
PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();

if (...condition...)
{
   int[] updateCounts = pstmt.executeBatch();
   ...
}
else
{
   pstmt.clearBatch();
   ...
}


Notes:
  • After calling addBatch(), you must call either executeBatch() or clearBatch() before a call to executeUpdate(), otherwise there will be a SQL exception.
  • A clearBatch() call resets the statement batch to empty.
  • Nothing is returned by the clearBatch() method.

Update Counts in the Oracle Implementation of Standard Batching

If a statement batch is executed successfully (no batch exception is thrown), then the integer array--or update counts array--returned by the statement executeBatch() call will always have one element for each operation in the batch. In the Oracle implementation of standard update batching, the values of the array elements are as follows:

In your code, upon successful execution of a batch, you should be prepared to handle either -2's or true update counts in the array elements. For a successful batch execution, the array contains either all -2's or all positive integers.


Note:

For information about possible values in the update counts array for an unsuccessful batch execution, see "Error Handling in the Oracle Implementation of Standard Batching".


Example: Standard Update Batching

This example combines the sample fragments in the previous sections, accomplishing the following steps:

Assume a Connection instance conn:

conn.setAutoCommit(false);

PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();

int[] updateCounts = pstmt.executeBatch();

conn.commit();

pstmt.close();
...

You can process the update counts array to determine if the batch executed successfully. This is discussed in the next section ("Error Handling in the Oracle Implementation of Standard Batching").

Error Handling in the Oracle Implementation of Standard Batching

If any one of the batched operations fails to complete successfully (or attempts to return a result set) during an executeBatch() call, then execution stops and a java.sql.BatchUpdateException is generated (a subclass of java.sql.SQLException).

After a batch exception, the update counts array can be retrieved using the getUpdateCounts() method of the BatchUpdateException object. This returns an int array of update counts, just as the executeBatch() method does. In the Oracle implementation of standard update batching, contents of the update counts array are as follows after a batch exception:

In your code, upon failed execution of a batch, you should be prepared to handle either -3's or true update counts in the array elements when an exception occurs. For a failed batch execution, you will have either a full array of -3's or a partial array of positive integers.

Intermixing Batched Statements and Non-Batched Statements

You cannot call executeUpdate() for regular, non-batched execution of an operation if the statement object has a pending batch of operations (essentially, if the batch associated with that statement object is non-empty).

You can, however, intermix batched operations and non-batched operations in a single statement object if you execute non-batched operations either prior to adding any operations to the statement batch or after executing the batch. Essentially, you can call executeUpdate() for a statement object only when its update batch is empty. If the batch is non-empty, then an exception will be generated.

For example, it is legal to have a sequence such as the following:

...
PreparedStatement pstmt = 
          conn.prepareStatement("INSERT INTO employees VALUES(?, ?)");

pstmt.setInt(1, 2000);
pstmt.setString(2, "Milo Mumford");

int scount = pstmt.executeUpdate();   // OK; no operations in pstmt batch

pstmt.setInt(1, 3000);
pstmt.setString(2, "Sulu Simpson");
pstmt.addBatch();                    // Now start a batch

pstmt.setInt(1, 4000);
pstmt.setString(2, "Stan Leland");
pstmt.addBatch();

int[] bcounts = pstmt.executeBatch();

pstmt.setInt(1, 5000);
pstmt.setString(2, "Amy Feiner");

int scount = pstmt.executeUpdate();   // OK; pstmt batch was executed
...

Intermixing non-batched operations on one statement object and batched operations on another statement object within your code is permissible. Different statement objects are independent of each other with regards to update batching operations. A COMMIT request will affect all non-batched operations and all successful operations in executed batches, but will not affect any pending batches.

Premature Batch Flush

Premature batch flush happens due to a change in cached meta data. Cached meta data can be changed due to various reasons, such as the following:

The premature batch flush count is summed to the return value of the next executeUpdate() or sendBatch() method.

The old functionality lost all these batch flush values which can be obtained now. To switch back to the old functionality, you can set the AccumulateBatchResult property to false, as shown below:

HashTable info = new HashTable (); 
info.put ("user", "SCOTT"); 
info.put ("passwd", "TIGER"); 
// other properties 
... 

// property: batch flush type 
info.put ("AccumulateBatchResult", "false");

Connection con = DriverManager.getConnection ("jdbc:oracle:oci:@", info); 


Note:

The AccumulateBatchResult property is set to true by default, in Oracle9i.


Example:
((OraclePreparedStatement)pstmt).setExecuteBatch (2); 

pstmt.setNull (1, OracleTypes.NUMBER); 
pstmt.setString (2, "test11"); 
int count = pstmt.executeUpdate (); // returns 0 

/* 
* Premature batch flush happens here. 
*/ 
pstmt.setInt (1, 22); 
pstmt.setString (2, "test22"); 
int count = pstmt.executeUpdate (); // returns 0 

pstmt.setInt (1, 33); 
pstmt.setString (2, "test33"); 
/* 
*  returns 3 with the new batching scheme where as, 
*  returns 2 with the old batching scheme. 
*/ 
int count = pstmt.executeUpdate ();

Additional Oracle Performance Extensions

In addition to update batching, discussed previously, Oracle JDBC drivers support the following extensions that improve performance by reducing round trips to the database:

Oracle provides several extensions to connection properties objects to support these performance extensions. These extensions enable you to set the remarksReporting flag and default values for row prefetching and update batching. For more information, see "Specifying a Database URL and Properties Object".

Oracle Row Prefetching

Oracle JDBC drivers include extensions that allow you to set the number of rows to prefetch into the client while a result set is being populated during a query. This feature reduces the number of round trips to the server.


Note:

With JDBC 2.0, the ability to preset the fetch size has become standard functionality. For information about the standard implementation of this feature, see "Fetch Size".


Setting the Oracle Prefetch Value

Standard JDBC receives the result set one row at a time, and each row requires a round trip to the database. The row-prefetching feature associates an integer row-prefetch setting with a given statement object. JDBC fetches that number of rows at a time from the database during the query. That is, JDBC will fetch N rows that match the query criteria and bring them all back to the client at once, where N is the prefetch setting. Then, once your next() calls have run through those N rows, JDBC will go back to fetch the next N rows that match the criteria.

You can set the number of rows to prefetch for a particular Oracle statement (any type of statement). You can also reset the default number of rows that will be prefetched for all statements in your connection. The default number of rows to prefetch to the client is 10.

Set the number of rows to prefetch for a particular statement as follows:

  1. Cast your statement object to an OracleStatement, OraclePreparedStatement, or OracleCallableStatement object, as applicable, if it is not already one of these.
  2. Use the setRowPrefetch() method of the statement object to specify the number of rows to prefetch, passing in the number as an integer. If you want to check the current prefetch number, use the getRowPrefetch() method of the Statement object, which returns an integer.

Set the default number of rows to prefetch for all statements in a connection, as follows:

  1. Cast your Connection object to an OracleConnection object.
  2. Use the setDefaultRowPrefetch() method of your OracleConnection object to set the default number of rows to prefetch, passing in an integer that specifies the desired default. If you want to check the current setting of the default, then use the getDefaultRowPrefetch() method of the OracleConnection object. This method returns an integer.

    Equivalently, instead of calling setDefaultRowPrefetch(), you can set the defaultRowPrefetch Java property if you use a Java Properties object in establishing the connection. See "Specifying a Database URL and Properties Object".


    Notes:
    • Do not mix the JDBC 2.0 fetch size API and the Oracle row-prefetching API in your application. You can use one or the other, but not both.
    • Be aware that setting the Oracle row-prefetch value can affect not only queries, but also: 1) explicitly refetching rows in a result set through the result set refreshRow() method available with JDBC 2.0 (relevant for scroll-sensitive/read-only, scroll-sensitive/updatable, and scroll-insensitive/updatable result sets); and 2) the "window" size of a scroll-sensitive result set, affecting how often automatic refetches are performed. The Oracle row-prefetch value will be overridden, however, by any setting of the fetch size. See "Fetch Size" for more information.

Example: Row Prefetching

The following example illustrates the row-prefetching feature. It assumes you have imported the oracle.jdbc.* interfaces.

Connection conn = 
       DriverManager.getConnection("jdbc:oracle:oci:","scott","tiger"); 

//Set the default row-prefetch setting for this connection 
((OracleConnection)conn).setDefaultRowPrefetch(7); 

/* The following statement gets the default row-prefetch value for
   the connection, that is, 7.
 */
Statement stmt = conn.createStatement(); 

/* Subsequent statements look the same, regardless of the row
   prefetch value. Only execution time changes. 
 */
ResultSet rset = stmt.executeQuery("SELECT ename FROM emp");  
System.out.println( rset.next () ); 

while( rset.next () ) 
    System.out.println( rset.getString (1) ); 

//Override the default row-prefetch setting for this statement
( (OracleStatement)stmt ).setRowPrefetch (2); 

ResultSet rset = stmt.executeQuery("SELECT ename FROM emp");  
System.out.println( rset.next () ); 

while( rset.next() ) 
   System.out.println( rset.getString (1) ); 

stmt.close(); 

Oracle Row-Prefetching Limitations

There is no maximum prefetch setting, but empirical evidence suggests that 10 is effective. Oracle does not recommend exceeding this value in most situations. If you do not set the default row-prefetch value for a connection, 10 is the default.

A statement object receives the default row-prefetch setting from the associated connection at the time the statement object is created. Subsequent changes to the connection's default row-prefetch setting have no effect on the statement's row-prefetch setting.

If a column of a result set is of datatype LONG or LONG RAW (that is, the streaming types), JDBC changes the statement's row-prefetch setting to 1, even if you never actually read a value of either of those types.

If you use the form of the DriverManager class getConnection() method that takes a Properties object as an argument, then you can set the connection's default row-prefetch value that way. See "Specifying a Database URL and Properties Object".

Defining Column Types

Oracle JDBC drivers enable you to inform the driver of the types of the columns in an upcoming query, saving a round trip to the database that would otherwise be necessary to describe the table.

When standard JDBC performs a query, it first uses a round trip to the database to determine the types that it should use for the columns of the result set. Then, when JDBC receives data from the query, it converts the data, as necessary, as it populates the result set.

When you specify column types for a query, you avoid the first round trip to the database. The server, which is optimized to do so, performs any necessary type conversions.

Following these general steps to define column types for a query:

  1. Cast your statement object to an OracleStatement, OraclePreparedStatement, or OracleCallableStatement object, as applicable, if it is not already one of these.
  2. If necessary, use the clearDefines() method of your Statement object to clear any previous column definitions for this Statement object.
  3. For each column of the expected result set, invoke the defineColumnType() method of your Statement object, passing it these parameters:
    • column index (integer)
    • typecode (integer)

      Use the static constants of the java.sql.Types class or oracle.jdbc.OracleTypes class (such as Types.INTEGER, Types.FLOAT, Types.VARCHAR, OracleTypes.VARCHAR, and OracleTypes.ROWID). Typecodes for standard types are identical in these two classes.

    • type name (string) (structured objects, object references, and arrays only)

      For structured objects, object references, and arrays, you must also specify the type name (for example, Employee, EmployeeRef, or EmployeeArray).

    • (optionally) maximum field size (integer)

      Optionally specify a maximum data length for this column.

      You cannot specify a maximum field size parameter if you are defining the column type for a structured object, object reference, or array. If you try to include this parameter, it will be ignored.

    For example, assuming stmt is an Oracle statement, use this syntax:

    stmt.defineColumnType(column_index, typeCode);
    
    

    or (recommended if the column is VARCHAR or equivalent and you know the length limit):

    stmt.defineColumnType(column_index, typeCode, max_size);
    
    

    or (for structured object, object reference, and array columns):

    stmt.defineColumnType(column_index, typeCode, typeName);
    
    

    Set a maximum field size if you do not want to receive the full default length of the data. Calling the setMaxFieldSize() method of the standard JDBC Statement class sets a restriction on the amount of data returned. Specifically, the size of the data returned will be the minimum of:

    • the maximum field size set in defineColumnType()

    or:

    • the maximum field size set in setMaxFieldSize()

    or:

    • the natural maximum size of the datatype

Once you complete these steps, use the statement's executeQuery() method to perform the query.


Note:

You must define the datatype for every column of the expected result set. If the number of columns for which you specify types does not match the number of columns in the result set, the process fails with a SQL exception.


Example: Defining Column Types

The following example illustrates the use of this feature. It assumes you have imported the oracle.jdbc.* interfaces.

Connection conn =        
     DriverManager.getConnection("jdbc:oracle:oci:","scott","tiger"); 
      
Statement stmt = conn.createStatement();

/*Ask for the column as a string: 
 *Avoid a round trip to get the column type.
 *Convert from number to string on the server. 
 */
((OracleStatement)stmt).defineColumnType(1, Types.VARCHAR); 
      
ResultSet rset = stmt.executeQuery("select empno from emp");

while (rset.next() )
    System.out.println(rset.getString(1));

stmt.close(); 

As this example shows, you must cast the statement (stmt) to type OracleStatement in the invocation of the defineColumnType() method. The connection's createStatement() method returns an object of type java.sql.Statement, which does not have the defineColumnType() and clearDefines() methods. These methods are provided only in the OracleStatement implementation.

The define-extensions use JDBC types to specify the desired types. The allowed define types for columns depend on the internal Oracle type of the column.

All columns can be defined to their "natural" JDBC types; in most cases, they can be defined to the Types.CHAR or Types.VARCHAR typecode.

Table 12-1 lists the valid column definition arguments you can use in the defineColumnType() method.

Table 12-1 Valid Column Type Specifications  
If the column has Oracle
SQL type:
You can use defineColumnType()
to define it as:

NUMBER, VARNUM

BIGINT, TINYINT, SMALLINT, INTEGER, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, CHAR, VARCHAR

CHAR, VARCHAR2

CHAR, VARCHAR

LONG

CHAR, VARCHAR, LONGVARCHAR

LONGRAW

LONGVARBINARY, VARBINARY, BINARY

RAW

VARBINARY, BINARY

DATE

DATE, TIME, TIMESTAMP, CHAR, VARCHAR

ROWID

ROWID

DatabaseMetaData TABLE_REMARKS Reporting

The getColumns(), getProcedureColumns(), getProcedures(), and getTables() methods of the database metadata classes are slow if they must report TABLE_REMARKS columns, because this necessitates an expensive outer join. For this reason, the JDBC driver does not report TABLE_REMARKS columns by default.

You can enable TABLE_REMARKS reporting by passing a true argument to the setRemarksReporting() method of an OracleConnection object.

Equivalently, instead of calling setRemarksReporting(), you can set the remarksReporting Java property if you use a Java Properties object in establishing the connection. See "Specifying a Database URL and Properties Object".

If you are using a standard java.sql.Connection object, you must cast it to OracleConnection to use setRemarksReporting().

Example: TABLE_REMARKS Reporting

Assuming conn is the name of your standard Connection object, the following statement enables TABLE_REMARKS reporting.

( (oracle.jdbc.OracleConnection)conn ).setRemarksReporting(true);

Considerations for getProcedures() and getProcedureColumns() Methods

According to JDBC versions 1.1 and 1.2, the methods getProcedures() and getProcedureColumns() treat the catalog, schemaPattern, columnNamePattern, and procedureNamePattern parameters in the same way. In the Oracle definition of these methods, the parameters are treated differently:


Go to previous page Go to next page
Oracle
Copyright © 1999, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback