Oracle9i JDBC Developer's Guide and Reference Release 1 (9.0.1) Part Number A90211-01 |
|
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:
For a general overview of Oracle extensions and detailed discussion of Oracle packages and type extensions, see Chapter 5, "Overview of Oracle Extensions".
Note:
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.
Oracle JDBC supports two distinct models for update batching:
This section compares and contrasts the general models and types of statements supported for standard update batching and Oracle update batching.
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:
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:
Note that because Oracle update batching is vendor-specific, you must actually use (or cast to) OraclePreparedStatement
objects, not general PreparedStatement
objects.
UPDATE
, INSERT
, or DELETE
operations. Executing a batch that includes an operation that attempts to return a result set will cause an exception.
Note that with standard update batching, you can use either standard PreparedStatement
, CallableStatement
, and Statement
objects, or Oracle-specific OraclePreparedStatement
, OracleCallableStatement
, and OracleStatement
objects.
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.
Note the following limitations and implementation details regarding Oracle update batching:
sendBatch()
method of an Oracle prepared statement in any of the following circumstances: 1) the connection receives a COMMIT
request, either as a result of invoking the commit()
method or as a result of auto-commit mode; 2) the statement receives a close()
request; or 3) the connection receives a close()
request.
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".
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.
PreparedStatement ps = conn.prepareStatement ("INSERT INTO dept VALUES (?,?,?)"); ps.setInt (1,12); ps.setString (2,"Oracle"); ps.setString (3,"USA");
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());
// No data is sent to the database by this call to executeUpdate System.out.println ("Number of rows updated so far: " + ps.executeUpdate ());
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 ();
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();
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:
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".
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".
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 ();
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.
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:
executeUpdate()
call results in the operation being added to the batch, then the method returns a value of 0, because nothing was written to the database yet.
executeUpdate()
call results in the batch value being reached and the batch being executed, then the method will return the total number of rows affected by all operations in the batch.
Similarly, the sendBatch()
method of an OraclePreparedStatement
object returns the total number of rows affected by all operations in the batch.
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:oci8:","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(); ...
For complete sample applications for Oracle update batching, including how to execute the batch both implicitly and explicitly, see "Oracle Update Batching with Implicit Execution--SetExecuteBatch.java" and "Oracle Update Batching with Explicit Execution--SendBatch.java".
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.)
Note the following limitations and implementation details regarding Oracle's implementation of standard update batching:
The Oracle implementation of standard update batching does not implement true batching for generic statements and callable statements. Even though Oracle JDBC supports the use of standard batching syntax for Statement
and CallableStatement
objects, you are unlikely to see performance improvement.
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.
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".
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.
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(); ... }
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". |
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").
For a complete sample application, see "Standard Update Batching--BatchUpdates.java".
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:
You should always perform a ROLLBACK
operation in this situation.
For example, if there were 20 operations in the batch, the first 13 succeeded, and the 14th generated an exception, then the update counts array will have 13 elements, containing actual update counts of the successful operations.
You can either commit or roll back the successful operations in this situation, as you prefer.
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.
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 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:oci8:@", info);
((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 ();
In addition to update batching, discussed previously, Oracle JDBC drivers support the following extensions that improve performance by reducing round trips to the database:
This reduces round trips to the database by fetching multiple rows of data each time data is fetched--the extra data is stored in client-side buffers for later access by the client. The number of rows to prefetch can be set as desired.
This avoids an inefficiency in the normal JDBC protocol for performing and returning the results of queries.
TABLE_REMARKS
columns
This avoids an expensive outer join operation.
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 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". |
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:
OracleStatement
, OraclePreparedStatement
, or OracleCallableStatement
object, as applicable, if it is not already one of these.
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:
Connection
object to an OracleConnection
object.
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:
|
The following example illustrates the row-prefetching feature. It assumes you have imported the oracle.jdbc.*
interfaces.
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:","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();
For complete sample applications, including how to set the connection default row-prefetch value and the statement row-prefetch value, see "Oracle Row Prefetching Specified in Connection--RowPrefetch_connection.java" and "Oracle Row Prefetching Specified in Statement--RowPrefetch_statement.java".
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".
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.
For a complete sample application, see "Oracle Column Type Definitions--DefineColumnType.java".
Following these general steps to define column types for a query:
OracleStatement
, OraclePreparedStatement
, or OracleCallableStatement
object, as applicable, if it is not already one of these.
clearDefines()
method of your Statement
object to clear any previous column definitions for this Statement
object.
defineColumnType()
method of your Statement
object, passing it these parameters:
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.
For structured objects, object references, and arrays, you must also specify the type name (for example, Employee
, EmployeeRef
, or EmployeeArray
).
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:
or:
or:
Once you complete these steps, use the statement's executeQuery()
method to perform the query.
The following example illustrates the use of this feature. It assumes you have imported the oracle.jdbc.*
interfaces.
Connection conn = DriverManager.getConnection("jdbc:oracle:oci8:","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.
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()
.
Assuming conn
is the name of your standard Connection
object, the following statement enables TABLE_REMARKS
reporting.
( (oracle.jdbc.OracleConnection)conn ).setRemarksReporting(true);
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:
catalog
: Oracle does not have multiple catalogs, but it does have packages. Consequently, the catalog
parameter is treated as the package name. This applies both on input (the catalog
parameter) and output (the catalog
column in the returned ResultSet
). On input, the construct "
"
(the empty string) retrieves procedures and arguments without a package, that is, standalone objects. A null
value means to drop from the selection criteria, that is, return information about both stand-alone and packaged objects (same as passing in "%
"). Otherwise the catalog
parameter should be a package name pattern (with SQL wild cards, if desired).
schemaPattern
: All objects within Oracle must have a schema, so it does not make sense to return information for those objects without one. Thus, the construct "
"
(the empty string) is interpreted on input to mean the objects in the current schema (that is, the one to which you are currently connected). To be consistent with the behavior of the catalog
parameter, null
is interpreted to drop the schema from the selection criteria (same as passing in "%
"). It can also be used as a pattern with SQL wild cards.
procedureNamePattern
and columnNamePattern
: The empty string (" ") does not make sense for either parameter, because all procedures and arguments must have names. Thus, the construct "
"
will raise an exception. To be consistent with the behavior of other parameters, null
has the same effect as passing in "%
".
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|