Oracle Extensions

Additional Oracle Extensions

This section has the following subsections:

This section describes Oracle extensions not related to datatypes in the JDBC 2.0 specification. This consists of additional datatype extensions as well as performance extensions.

Performance Extensions

Oracle JDBC drivers support these extensions that improve performance by reducing round trips to the database:

Prefetching rows 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.
Batching updates also reduces round trips to the database, saving on the client side a number of updates that are to be made, and then going to the database once to execute all the updates.
Specifying column types gets around an inefficiency in the usual JDBC protocol for performing and returning the results of queries.
Suppressing database metadata TABLE_REMARKS columns avoids an expensive outer join operation.

Oracle supports several extensions to connection properties objects to support these performance extensions. The properties object extensions enable you to set the remarksReporting flag and default values for prefetching and update-batching. For more information, see "Oracle Extensions for Connection Properties".

Note:
The prefetching and batch update extensions were designed prior to the announcement of the JDBC 2.0 standard. They do not match JDBC 2.0.

Row Prefetching

Oracle JDBC drivers 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.

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:

Cast your statement object to an OracleStatement, OraclePreparedStatement, or OracleCallableStatement object, as applicable, if it is not already one of these.
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:

Cast your Connection object to an OracleConnection object.
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.

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 number 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" and "Oracle Extensions for Connection Properties" for more information about the Properties object and connection properties.

Example: Row Prefetching

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

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

Database Update Batching

Oracle JDBC drivers allow you to accumulate inserts and updates of prepared statements at the client and send them to the server in batches, reducing round trips to the server. You might want to do this when you are repeating the same statement with different bind variables.

Normally JDBC makes a round trip to the database to execute a prepared statement whenever the statement's executeUpdate() method is called. The Oracle update-batching feature, however, associates a batch value with each prepared statement object. Oracle JDBC accumulates execution requests for the prepared statement, then automatically passes them all to the database for execution once the batch value is reached.

Update Batching Limitations

You can use update batching with CallableStatements except when the CallableStatement has OUT parameters. In this case, the driver automatically overrides any previous batch value and resets it to 1.

Do not use the addBatch() and executeBatch() methods of the JDBC 2.0 PreparedStatement interface. These methods are not consistent with the functionality offered by the methods associated with the OraclePreparedStatement.

Regardless of the batch value of an Oracle prepared statement, if any of the bind variables of the statement is (or becomes) a streaming type, then JDBC sets the batch value to 1 and sends any queued requests to the database for execution.

JDBC automatically executes the statement's sendBatch() method whenever the connection receives a commit request, the statement receives a close request, or the connection receives a close request.

If you use the form of the DriverManager.getConnection() method that takes a Properties object as an argument, then you can set the connection's default batch value in the object. See "Oracle Extensions for Connection Properties" for more information about Properties objects.

The default batch update value is 1.

Setting Update Batch Value for Individual Statements

You can set the batch value for any individual Oracle prepared statement by applying it to the OraclePreparedStatement object. The batch value that you set for an individual statement overrides the value set for the connection. You can also set a default batch value that will apply to any Oracle prepared statement in your Oracle connection by applying it to the OracleConnection object.

Follow these steps to apply the Oracle batch value feature for a particular prepared statement:

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");

Cast your prepared statement to an OraclePreparedStatement object and apply the setDefaultExecuteBatch() method. In this example, the default batch size of the statement is set to 2.
```
((OraclePreparedStatement)ps).setDefaultExecuteBatch(2);
```
If you wish, insert the getExecuteBatch() method at any point in the program to check the default batch value for the statement:
```
System.outPrintln (" Statement Execute Batch Value " +
     
((OraclePreperedStatement)ps).getExecuteBatch());

     
     
```

If you send an execute update statement to the database at this point, then no data will be sent to the database. Instead, a call to executeUpdate() 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 ());

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

Overriding the Default Batch Update Value

If you want to execute accumulated statements before the batch value is reached, then use the sendBatch() method of the OraclePreparedStatement object. For example:

Cast your connection to an OracleConnection object and apply the setDefaultExecuteBatch() method for the connection. This example sets the default batch for all statements in the connection to 50.
```
((OracleConnection)conn).setDefaultExecuteBatch (50);
```

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 execute update does not happen at this point. The ps.executeUpdate() method returns "0".

If you enter a set of input values for a second row and an executeUpdate(), the data will still not be sent to the database since the batch default value for the statement is the same as for the connection: 50.

ps.setInt (1, 33);
ps.setString (2, "Applications");
ps.setString (3, "Indonesia");

// this execute does not actually happen 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".

If you apply the sendBatch() method at this point, then the two previously batched executes will be sent to the database in a single round trip. The sendBatch() method also returns the 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 ();

Setting Update Batch Value for the Connection

You can specify a default batch value for any Oracle prepared statement in your Oracle connection. To do this, set the setDefaultExecute() method on the OracleConnection object. For example, the following statement sets the default batch value for all prepared statements belonging to the conn connection object to 20:

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

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

Checking Batch Value

The getExecuteBatch() method enables you to check the current setting of the default batch value for a specific Oracle prepared statement object or for all of the prepared statements that belong to the Oracle connection. For example:

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

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

Example: Update Batching

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

Connection conn = 
  DriverManager.getConnection("jdbc:oracle:oci8:","scott","tiger"); 
 
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
ps.close();

Notes:

Each statement has its own batch count. Only executes on a particular statement add to the batch count.

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

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

Redefining Column Types Limitations

To use this feature, you must specify a datatype for each 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 SQLException.

You cannot define column types for objects or object references.

Redefining Column Types for a Query

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

Cast your statement object to an OracleStatement, OraclePreparedStatement, or OracleCallableStatement object, as applicable, if it is not already one of these.
If necessary, use the clearDefines() method of your Statement object to clear any previous column definitions for this Statement object.
Determine the following for each column of the expected result set:
- column index (position)
- code for the type of the expected return data (which can differ from the column type)
  
  This is according to oracle.jdbc.driver.OracleTypes for Oracle-specific types, and according to either java.sql.Types or OracleTypes for standard types (constants for standard types have the same value in Types and OracleTypes).
For each column of the expected result set, invoke the defineColumnType(), method of your Statement object, passing it these parameters:
- column index (integer)
- type code (integer)
  
  Use the static constants of the java.sql.Types class or, for Oracle-specific types, the static constants of the oracle.jdbc.driver.OracleTypes class (such as Types.INTEGER, Types.FLOAT, Types.VARCHAR, OracleTypes.VARCHAR, and OracleTypes.ROWID.).
- (optionally) maximum field size (integer)
For example, assuming stmt is an Oracle statement, use this syntax:
```
stmt.defineColumnType(column_index, type);
```
OR
```
stmt.defineColumnType(column_index, type, max_size);
```
Set maximum field size if you do not want to receive the full default length of the data. Less data than this maximum size will be returned if the maximum field size is set to a smaller value using the setMaxFieldSize() method of the standard JDBC Statement class, or if the natural maximum size of the datatype is smaller. 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.

Example: Defining Column Types

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

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 Types.CHAR or Types.VARCHAR.

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

Table 4-6 Valid Column Type Specifications

If the column has Oracle SQL type:	You can use defineColumnType() to redefine 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.

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

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, stand-alone 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 "%".

Oracle Extensions for Connection Properties

One of the forms of the DriverManager.getConnection() method enables you to specify a URL and a properties object:

getConnection(String URL, Properties info);

where the URL is of the form:

jdbc:oracle:<drivertype>:@<database>

In addition to the URL, you use an object of the standard Java Properties class as input. For example:

java.util.Properties info = new java.util.Properties();
info.put ("user", "scott");
info.put ("password","tiger");
getConnection ("jdbc:oracle:oci8:",info);

Table 4-7 lists the connection properties that Oracle JDBC drivers support, including the Oracle extensions for defaultRowPrefetch, remarksReporting, and defaultBatchValue.

Table 4-7 Connection Properties Recognized by Oracle JDBC Drivers

Name	Short Name	Type	Description
user	N/A	String	the user name for logging into the database
password	N/A	String	the password for logging into the database
database	server	String	the connect string for the database; equivalent to using `setDefaultRowPrefetch()`
defaultRowPrefetch	prefetch	Integer	the default number of rows to prefetch from the server. The default value is 10.
remarksReporting	remarks	Boolean	true if `getTables()` and `getColumns()` should report `TABLE_REMARKS`; equivalent to using `setRemarksReporting()`. The default value is false.
defaultBatchValue	batchvalue	Integer	the default batch value that triggers an execution request. The default value is 10.

The following example shows how to use the java.util.Properties.put() method to set performance extension options before connection to the database.

//import packages and register the driver
import java.sql.*;
import java.math.*;
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

//specify the properties object
java.util.Properties info = new java.util.Properties();
info.put("user", "scott");
info.put ("password", "tiger");
info.put ("defaultRowProfetch","20");
info.put ("defaultBatchValue", 5);

//specify the connection object 
Connection conn = DriverManager.getConnection 
("jdbc:oracle:thin:@database",info);

Additional Type Extensions

Oracle JDBC drivers support the Oracle-specific datatypes ROWID and REF CURSOR, which were introduced in Oracle7 and are not part of the standard JDBC specification.

ROWID is supported as a Java string and REF CURSOR as a JDBC result set.

Oracle ROWID Type

A ROWID is an identification tag that is unique for each row of an Oracle database table. ROWID can be thought of as a virtual column, containing the ID for each row.

The oracle.sql.ROWID class is supplied as a wrapper for ROWID SQL data.

ROWIDs provide functionality similar to the java.sql.ResultSet.getCursorName() and java.sql.Statement.setCursorName() JDBC methods, which are not supported by the Oracle implementation.

If you include the ROWID pseudo-column in a query, then you can retrieve the ROWIDs with the ResultSet.getString() method (passing in either the column index or the column name). You can also bind a ROWID to a PreparedStatement parameter with the setString() method. This allows in-place updates, as in the example that immediately follows.

Notes:

The oracle.sql.ROWID class replaces oracle.jdbc.driver.ROWID, which was used in previous releases of Oracle JDBC.

Refer to the Javadoc for information about features of the ROWID class.

Example: ROWID

The following example shows how to access and manipulate ROWID data.

Statement stmt = conn.createStatement(); 

// Query the employee names with "FOR UPDATE" to lock the rows. 
// Select the ROWID to identify the rows to be updated. 

ResultSet rset =  
   stmt.executeQuery ("SELECT ename, rowid FROM emp FOR UPDATE"); 

// Prepare a statement to update the ENAME column at a given ROWID 

PreparedStatement pstmt = 
   conn.prepareStatement ("UPDATE emp SET ename = ? WHERE rowid = ?"); 

// Loop through the results of the query 
while (rset.next ()) 
{ 
    String ename = rset.getString (1); 
    oracle.sql.ROWID rowid = rset.getROWID (2);  // Get the ROWID as a String 
    pstmt.setString (1, ename.toLowerCase ()); 
    pstmt.setROWID (2, rowid); // Pass ROWID to the update statement 
    pstmt.executeUpdate ();     // Do the update 
}

Oracle REF CURSOR Type Category

A cursor variable holds the memory location (address) of a query work area rather than the contents of the area. So, declaring a cursor variable creates a pointer. In SQL, a pointer has the datatype REF x where REF is short for REFERENCE and x represents the entity that is being referenced. "REF CURSOR", then, identifies a reference to a cursor variable. Since many cursor variables might exist to point to many work areas, REF CURSOR can be thought of as a category or "datatype specifier" that identifies many different cursor variables.

To create a cursor variable, begin by identifying a user-defined type that belongs to the REF CURSOR category. For example:

DECLARE TYPE DeptCursorTyp IS REF CURSOR

Then create the cursor variable by declaring it to be of the user-defined type DeptCursorTyp:

dept_cv DeptCursorTyp  - - declare cursor variable
...

A REF CURSOR, then, is a category of datatype rather than a datatype.

Stored procedures can return user-defined types, or cursor variables, of the REF CURSOR category. This output is equivalent to a database cursor or a JDBC result set. A REF CURSOR essentially encapsulates the results of a query.

In JDBC, REF CURSORs are materialized as ResultSet objects and can be accessed like this:

Use a JDBC callable statement to call a stored procedure (it must be a callable statement as opposed to a prepared statement because there is an output parameter).
The stored procedure returns a REF CURSOR.
The Java application casts the callable statement to an Oracle callable statement and uses the getCursor() method of the OracleCallableStatement class to materialize the REF CURSOR as a JDBC ResultSet object.
The result set is processed as requested.

Example: Accessing REF CURSOR Data

This example shows how to access REF CURSOR data.

import oracle.jdbc.driver.*;
...
CallableStatement cstmt;
ResultSet cursor;

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("begin open ? for select ename from emp; end;");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

// Use the cursor like a normal ResultSet
while (cursor.next ())
    {System.out.println (cursor.getString(1));}

In the preceding example:

A CallableStatement object is created by using the prepareCall() method of the connection class.
The callable statement implements a PL/SQL procedure which returns a REF CURSOR.
As always, the output parameter of the callable statement must be registered to define its type. The Oracle type code to use for a REF CURSOR is OracleTypes.CURSOR.
The callable statement is executed, returning the REF CURSOR.
The CallableStatement object is cast to an OracleCallableStatement object to use the getCursor() method, which is an Oracle extension to the standard JDBC API, and returns the REF CURSOR into a ResultSet object.

For a full sample application using a REF CURSOR, see "REF CURSOR Sample".