5 Oracle Extensions

Support for Oracle Data Types

A key feature of the Oracle JDBC extensions is the type support in the oracle.sql package. This package includes classes that map to all the Oracle SQL data types, acting as wrappers for raw SQL data. This functionality provides two significant advantages in manipulating SQL data:

• Accessing data directly in SQL format is sometimes more efficient than first converting it to Java format.

• Performing mathematical manipulations of the data directly in SQL format avoids the loss of precision that can occur in converting between SQL and Java formats.

Once manipulations are complete and it is time to output the information, each of the oracle.sql.* date type support classes has all the necessary methods to convert data to appropriate Java formats.

Support for Oracle Objects

Oracle JDBC supports the use of structured objects in the database, where an object data type is a user-defined type with nested attributes. For example, a user application could define an Employee object type, where each Employee object has a firstname attribute (character string), a lastname attribute (character string), and an employeenumber attribute (integer).

JDBC implementation of Oracle supports Oracle object data types. When you work with Oracle object data types in a Java application, you must consider the following:

• How to map between Oracle object data types and Java classes

• How to store Oracle object attributes in corresponding Java objects

• How to convert attribute data between SQL and Java formats

• How to access data

Oracle objects can be mapped either to the weak java.sql.Struct or oracle.sql.STRUCT types or to strongly typed customized classes. These strong types are referred to as custom Java classes, which must implement either the standard java.sql.SQLData interface or the Oracle extension oracle.sql.ORAData interface. Each interface specifies methods to convert data between SQL and Java.

Oracle recommends the use of the Oracle JPublisher utility to create custom Java classes to correspond to your Oracle objects. Oracle JPublisher performs this task seamlessly with command-line options and can generate either SQLData or ORAData implementations.

For SQLData implementations, a type map defines the correspondence between Oracle object data types and Java classes. Type maps are objects that specify which Java class corresponds to each Oracle object data type. Oracle JDBC uses these type maps to determine which Java class to instantiate and populate when it retrieves Oracle object data from a result set.

JPublisher automatically defines getXXX methods of the custom Java classes, which retrieve data into your Java application. For more information on the JPublisher utility.

Support for Schema Naming

Oracle object data type classes have the ability to accept and return fully qualified schema names. A fully qualified schema name has this syntax:

{[schema_name].}[sql_type_name] 
 

Where schema_name is the name of the schema and sql_type_name is the SQL type name of the object. schema_name and sql_type_name are separated by a period (.).

To specify an object type in JDBC, use its fully qualified name. It is not necessary to enter a schema name if the type name is in current naming space, that is, the current schema. Schema naming follows these rules:

• Both the schema name and the type name may or may not be quoted. However, if the SQL type name has a period in it, such as CORPORATE.EMPLOYEE, the type name must be quoted.

• The JDBC driver looks for the first unquoted period in the object name and uses the string before the period as the schema name and the string following the period as the type name. If no period is found, then the JDBC driver takes the current schema as default. That is, you can specify only the type name, without indicating a schema, instead of specifying the fully qualified name if the object type name belongs to the current schema. This also explains why you must quote the type name if the type name has a dot in it.

  For example, assume that user Scott creates a type called person.address and then wants to use it in his session. Scott may want to skip the schema name and pass in person.address to the JDBC driver. In this case, if person.address is not quoted, then the period will be detected and the JDBC driver will mistakenly interpret person as the schema name and address as the type name.

• JDBC passes the object type name string to the database unchanged. That is, the JDBC driver will not change the character case even if it is quoted.

  For example, if Scott.PersonType is passed to the JDBC driver as an object type name, then the JDBC driver will pass the string to the database unchanged. As another example, if there is white space between characters in the type name string, then the JDBC driver will not remove the white space.

DML Returning

Oracle Database supports the use of the RETURNING clause with data manipulation language (DML) statements. This enables you to combine two SQL statements into one. Both the Oracle JDBC Oracle Call Interface (OCI) driver and the Oracle JDBC Thin driver support DML returning. DML returning provides richer functionality compared to retrieval of auto-generated keys. It can be used to retrieve not only auto-generated keys, but also other columns or values that the application may use.

Accessing PL/SQL Index-by Tables

The Oracle JDBC drivers enable JDBC applications to make PL/SQL calls with index-by table parameters. The Oracle JDBC drivers support PL/SQL index-by tables of scalar data types

Package oracle.sql

The oracle.sql package supports direct access to data in SQL format. This package consists primarily of classes that provide Java mappings to SQL data types and their support classes.

Essentially, the classes act as Java wrappers for SQL data. The characters are converted to Java chars and, then, to bytes in the UCS2 character set.

Each of the oracle.sql.* data type classes extends oracle.sql.Datum, a superclass that encapsulates functionality common to all the data types. Some of the classes are for JDBC 2.0-compliant data types. These classes, as Table 5-1 indicates, implement standard JDBC 2.0 interfaces in the java.sql package, as well as extending the oracle.sql.Datum class.

Classes of the oracle.sql Package

Table 5-1 lists the oracle.sql data type classes and their corresponding Oracle SQL types.

In addition to the data type classes, the oracle.sql package includes the following support classes and interfaces, primarily for use with objects and collections:

• oracle.sql.ArrayDescriptor

  This class is used in constructing oracle.sql.ARRAY objects. It describes the SQL type of the array.

• oracle.sql.StructDescriptor

  This class is used in constructing oracle.sql.STRUCT objects, which you can use as a default mapping to Oracle objects in the database.

• oracle.sql.ORAData and oracle.sql.ORADataFactory

  These interfaces are used in Java classes implementing the Oracle ORAData scenario of Oracle object support.

• oracle.sql.OpaqueDescriptor

  This class is used to obtain the meta data for an instance of the oracle.sql.OPAQUE class.

General oracle.sql.* Data Type Support

Each of the Oracle data type classes provides, among other things, the following:

• One or more constructors, typically with a constructor that uses raw bytes as input and a constructor that takes a Java type as input

• Data storage as Java byte arrays for SQL data

• A getBytes() method, which returns the SQL data as a byte array

• A toJdbc() method that converts the data into an object of a corresponding Java class as defined in the JDBC specification

  The JDBC driver does not convert Oracle-specific data types that are not part of the JDBC specification, such as ROWID. The driver returns the object in the corresponding oracle.sql.* format. For example, it returns an Oracle ROWID as an oracle.sql.ROWID.

• Appropriate xxxValue methods to convert SQL data to Java type. For example, stringValue, intValue, booleanValue, dateValue, and bigDecimalValue

• Additional conversion methods, getXXX and setXXX, as appropriate, for the functionality of the data type, such as methods in the large object (LOB) classes that get the data as a stream and methods in the REF class that get and set object data through the object reference

Overview of Class oracle.sql.STRUCT

For any given Oracle object type, it is usually desirable to define a custom mapping between SQL and Java. For example, if you use a SQLData custom Java class, then the mapping must be defined in a type map.

If you choose not to define a mapping, however, then data from the object type will be materialized in Java in an instance of the oracle.sql.STRUCT class.

The STRUCT class implements the standard JDBC 2.0 java.sql.Struct interface and extends the oracle.sql.Datum class.

A STRUCT object is a Java representation of the raw bytes of an Oracle object. It contains the SQL type name of the Oracle object and an array of oracle.sql.Datum objects that hold the attribute values in SQL format.

The signature of the constructors for STRUCT are as follows:

STRUCT(Connection connection, java.sql.StructDescriptor structDescriptor, Object[] attributes)

STRUCT(Connection connection, java.sql.StructDescriptor structDescriptor, java.util.Map map)

You can materialize attributes of a STRUCT object as oracle.sql.Datum[] objects, if you use the getOracleAttributes method, or as java.lang.Object[] objects, if you use the getAttributes method. Materializing the attributes as oracle.sql.* objects gives you the following advantages of the oracle.sql.* format:

• Materializing oracle.sql.STRUCT data in oracle.sql.* format completely preserves data by maintaining it in SQL format. No translation is performed. This is useful if you want to access data but not necessarily display it.

• It allows complete flexibility in how your Java application unpacks data.

  Notes:

  • Elements of the array, although of the generic Datum type, actually contain data associated with the relevant oracle.sql.* type appropriate for the given attribute. You can cast the element to the appropriate oracle.sql.* type as desired. For example, a CHAR data attribute within the STRUCT is materialized as oracle.sql.Datum. To use it as CHAR data, you must cast it to oracle.sql.CHAR.

  • Nested objects in the values array of a STRUCT object are materialized by the JDBC driver as instances of STRUCT.

In some cases, you may want to manually create a STRUCT object and pass it to a prepared statement or callable statement. To do this, you must also create a StructDescriptor object.

Overview of Class oracle.sql.REF

The oracle.sql.REF class is the generic class that supports Oracle object references. This class, as with all oracle.sql.* data type classes, is a subclass of the oracle.sql.Datum class. It implements the standard JDBC 2.0 java.sql.Ref interface.

The REF class has methods to retrieve and pass object references. However, selecting an object reference retrieves only a pointer to an object. This does not materialize the object itself. But the REF class also includes methods to retrieve and pass the object data.

You cannot create REF objects in your JDBC application. You can only retrieve existing REF objects from the database.

Overview of Class oracle.sql.ARRAY

The oracle.sql.ARRAY class supports Oracle collections, either VARRAYs or nested tables. If you select either a VARRAY or a nested table from the database, then the JDBC driver materializes it as an object of the ARRAY class. The structure of the data is equivalent in either case. The oracle.sql.ARRAY class extends oracle.sql.Datum and implements the standard JDBC 2.0 java.sql.Array interface.

You can use the setARRAY method of the OraclePreparedStatement or OracleCallableStatement class to pass an ARRAY as an input parameter to a prepared statement. Similarly, you might want to manually create an ARRAY object to pass it to a prepared statement or callable statement, perhaps to insert into the database. This involves the use of ArrayDescriptor objects.

Overview of Classes oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.BFILE

Binary large objects (BLOBs), character large objects (CLOBs), and binary files (BFILEs) are for data items that are too large to store directly in a database table. Instead, the database table stores a locator that points to the location of the actual data.

The oracle.sql package supports these data types in several ways:

• BLOBs point to large unstructured binary data items and are supported by the oracle.sql.BLOB class.

• CLOBs point to large character data items and are supported by the oracle.sql.CLOB class.

• BFILEs point to the content of external files (operating system files) and are supported by the oracle.sql.BFILE class.

You can select a BLOB, CLOB, or BFILE locator from the database using a standard SELECT statement. However, you receive only the locator, and not the data. Additional steps are necessary to retrieve the data.

Classes oracle.sql.DATE, oracle.sql.NUMBER, and oracle.sql.RAW

These classes map to primitive SQL data types, which are a part of standard JDBC, and supply conversions to and from the corresponding JDBC Java types.

Because Java Double and Float NaN values do not have an equivalent Oracle NUMBER representation, a NullPointerException is thrown whenever a Double.NaN value or a Float.NaN value is converted into an Oracle NUMBER using oracle.sql.NUMBER, For instance, the following code throws a NullPointerException:

oracle.sql.NUMBER n = new oracle.sql.NUMBER(Double.NaN); 
System.out.println(n.doubleValue());  // throws NullPointerException 

Classes oracle.sql.TIMESTAMP, oracle.sql.TIMESTAMPTZ, and oracle.sql.TIMESTAMPLTZ

The JDBC drivers support the following date/time data types:

• TIMESTAMP (TIMESTAMP)

• TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)

• TIMESTAMP WITH LOCAL TIME ZONE (TIMESTAMPLTZ)

The JDBC drivers allow conversions between DATE and date/time data types. For example, you can access a TIMESTAMP WITH TIME ZONE column as a DATE value.

The JDBC drivers support the most popular time zone names used in the industry as well as most of the time zone names defined in the JDK from Sun Microsystems. Time zones are specified by using the java.util.Calendar class.

The following code shows how the TimeZone and Calendar objects are created for US_PACIFIC, which is a time zone name not defined in the JDK:

TimeZone tz = TimeZone.getDefault();
tz.setID("US_PACIFIC");
GregorianCalendar gcal = new GregorianCalendar(tz);

The following Java classes represent the SQL date/time types:

• oracle.sql.TIMESTAMP

• oracle.sql.TIMESTAMPTZ

• oracle.sql.TIMESTAMPLTZ

Before accessing TIMESTAMP WITH LOCAL TIME ZONE data, call the OracleConnection.setSessionTimeZone(String regionName) method to set the session time zone. When this method is called, the JDBC driver sets the session time zone of the connection and saves the session time zone so that any TIMESTAMP WITH LOCAL TIME ZONE data accessed through JDBC can be adjusted using the session time zone.

Overview of Class oracle.sql.ROWID

This class supports Oracle ROWIDs, which are unique identifiers for rows in database tables. You can select a ROWID as you would select any column of data from the table. Note, however, that you cannot manually update ROWIDs. The Oracle Database updates them automatically as appropriate.

The oracle.sql.ROWID class does not implement any noteworthy functionality beyond what is in the oracle.sql.Datum superclass. However, ROWID does provide a stringValue method that overrides the stringValue method in the oracle.sql.Datum class and returns the hexadecimal representation of the ROWID bytes.

Class oracle.sql.OPAQUE

The oracle.sql.OPAQUE class gives you the name and characteristics of the OPAQUE type and any attributes. OPAQUE type provides access only to the uninterrupted bytes of the instance.

The following are the methods of the oracle.sql.OPAQUE class:

• getBytesValue

  Returns a byte array that represents the value of the OPAQUE object, in the format used in the database.

• isConvertibleTo(Class jClass)

  Determines if a Datum object can be converted to a particular class, where Class is any class and jClass is the class to convert. true is returned if conversion to jClass is permitted and, false is returned if conversion to jClass is not permitted.

• getDescriptor

  Returns the OpaqueDescriptor object that contains the type information.

• getJavaSqlConnection

  Returns the connection associated with the receiver. Because methods that use the oracle.jdbc.driver package are deprecated, the getConnection method has been deprecated in favor of the getJavaSqlConnection method.

• getSQLTypeName

  Implements the java.sql.Struct interface function and retrieves the SQL type name of the SQL structured type that this Struct object represents. This method returns the fully-qualified type name of the SQL structured type which this STRUCT object represents.

• getValue

  Returns a Java object that represents the value.

• toJdbc

  Returns the JDBC representation of the Datum object.

Package oracle.jdbc

The interfaces of the oracle.jdbc package define the Oracle extensions to the interfaces in java.sql. These extensions provide access to Oracle SQL-format data and other Oracle-specific functionality, including Oracle performance enhancements.

SQL CHAR Data Types

The SQL CHAR data types include CHAR, VARCHAR2, and CLOB. These data types let you store character data in the database character set encoding scheme. The character set of the database is established when you create the database.

SQL NCHAR Data Types

The SQL NCHAR data types were created for Globalization Support. The SQL NCHAR data types include NCHAR, NVARCHAR2, and NCLOB. These data types allow you to store Unicode data in the database NCHAR character set encoding. The NCHAR character set, which never changes, is established when you create the database.

The usage of SQL NCHAR data types is similar to that of the SQL CHAR data types. JDBC uses the same classes and methods to access SQL NCHAR data types that are used for the corresponding SQL CHAR data types. Therefore, there are no separate, corresponding classes defined in the oracle.sql package for SQL NCHAR data types. Similarly, there is no separate, corresponding constant defined in the oracle.jdbc.OracleTypes class for SQL NCHAR data types. The only difference in usage between the two data types occur in a data bind situation: a JDBC program must call the setFormOfUse method to specify if the data is bound for a SQL NCHAR data type.

The following code shows how to access SQL NCHAR data:

// 
// Table TEST has the following columns: 
// - NUMBER 
// - NVARCHAR2 
// - NCHAR 
// 
oracle.jdbc.OraclePreparedStatement pstmt = 
  (oracle.jdbc.OraclePreparedStatement) 
conn.prepareStatement("insert into TEST values(?, ?, ?)");

// 
// oracle.jdbc.OraclePreparedStatement.FORM_NCHAR should be used for all NCHAR, 
// NVARCHAR2 and NCLOB data types.
//
pstmt.setFormOfUse(2, OraclePreparedStatement.FORM_NCHAR);
pstmt.setFormOfUse(3, OraclePreparedStatement.FORM_NCHAR);

pstmt.setInt(1, 1);                    // NUMBER column
pstmt.setString(2, myUnicodeString1);  // NVARCHAR2 column
pstmt.setString(3, myUnicodeString2);  // NCHAR column
pstmt.execute();
OraclePreparedStatement.FORM_NCHAR

Class oracle.sql.CHAR

The oracle.sql.CHAR class is used by Oracle JDBC in handling and converting character data. This class provides the Globalization Support functionality to convert character data. This class has two key attributes: Globalization Support character set and the character data. The Globalization Support character set defines the encoding of the character data. It is a parameter that is always passed when a CHAR object is constructed. Without the Globalization Support character set information, the data bytes in the CHAR object are meaningless. The oracle.sql.CHAR class is used for both SQL CHAR and SQL NCHAR data types.

The only remaining use of oracle.sql.CHAR is to handle character data in the form of raw bytes encoded in an Oracle Globalization Support character set. All character data retrieved from Oracle Database should be accessed using java.lang.String. When processing byte data from another source, you can use an oracle.sql.CHAR to convert the bytes to java.lang.String.

To convert an oracle.sql.CHAR, you must provide the data bytes and an oracle.sql.CharacterSet instance that represents the Globalization Support character set used to encode the data bytes.

The CHAR objects that are Oracle object attributes are returned in the database character set.

JDBC application code rarely needs to construct CHAR objects directly, because the JDBC driver automatically creates CHAR objects as needed.

To construct a CHAR object, you must provide character set information to the CHAR object by way of an instance of the CharacterSet class. Each instance of this class represents one of the Globalization Support character sets that Oracle supports. A CharacterSet instance encapsulates methods and attributes of the character set, mainly involving functionality to convert to or from other character sets.

Constructing an oracle.sql.CHAR Object

Follow these general steps to construct a CHAR object:

1. Create a CharacterSet object by calling the static CharacterSet.make method.

   This method is a factory for the character set instance. The make method takes an integer as input, which corresponds to a character set ID that Oracle supports. For example:

   int oracleId = CharacterSet.JA16SJIS_CHARSET; // this is character set ID,
                                                 // 832
   ...
   CharacterSet mycharset = CharacterSet.make(oracleId);
   
   

   Each character set that Oracle supports has a unique, predefined Oracle ID.

2. Construct a CHAR object.

   Pass a string, or the bytes that represent the string, to the constructor along with the CharacterSet object that indicates how to interpret the bytes based on the character set. For example:

   String mystring = "teststring";
   ...
   CHAR mychar = new CHAR(teststring, mycharset);
   
   

   The CHAR class has multiple constructors, which can take a String, a byte array, or an object as input along with the CharacterSet object. In the case of a String, the string is converted to the character set indicated by the CharacterSet object before being placed into the CHAR object.

   Notes:

   • The CharacterSet object cannot be null.

   • The CharacterSet class is an abstract class, therefore it has no constructor. The only way to create instances is to use the make method.

   • The server recognizes the special value CharacterSet.DEFAULT_CHARSET as the database character set. For the client, this value is not meaningful.

   • Oracle does not intend or recommend that users extend the CharacterSet class.

oracle.sql.CHAR Conversion Methods

The CHAR class provides the following methods for translating character data to strings:

• getString

  Converts the sequence of characters represented by the CHAR object to a string, returning a Java String object. If you enter an invalid OracleID, then the character set will not be recognized and the getString method throws a SQLException.

• toString

  Identical to the getString method. But if you enter an invalid OracleID, then the character set will not be recognized and the toString method returns a hexadecimal representation of the CHAR data and does not throw a SQLException.

• getStringWithReplacement

  Identical to getString, except a default replacement character replaces characters that have no unicode representation in the CHAR object character set. This default character varies from character set to character set, but is often a question mark (?).

The database server and the client, or application running on the client, can use different character sets. When you use the methods of the CHAR class to transfer data between the server and the client, the JDBC drivers must convert the data from the server character set to the client character set or vice versa. To convert the data, the drivers use Globalization Support.

Oracle ROWID Type

A ROWID is an identification tag unique for each row of an Oracle Database table. The 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 type.

ROWIDs provide functionality similar to the getCursorName method specified in the java.sql.ResultSet interface and the setCursorName method specified in the java.sql.Statement interface.

If you include the ROWID pseudo-column in a query, then you can retrieve the ROWIDs with the result set getString method. You can also bind a ROWID to a PreparedStatement parameter with the setString method. This enables in-place updation, as in the example that follows.

Example

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 of a query work area, rather than the contents of the area. Declaring a cursor variable creates a pointer. In SQL, a pointer has the data type REF x, where REF is short for REFERENCE and x represents the entity being referenced. A REF CURSOR, then, identifies a reference to a cursor variable. Because many cursor variables might exist to point to many work areas, REF CURSOR can be thought of as a category or data type specifier that identifies many different types of cursor variables.

To create a cursor variable, begin by identifying a 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 type DeptCursorTyp:

dept_cv DeptCursorTyp  - - declare cursor variable
...
 

REF CURSOR, then, is a category of data types, rather than a particular data type.

Stored procedures can return 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, a REF CURSOR is materialized as a ResultSet object and can be accessed as follows:

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

2. The stored procedure returns a REF CURSOR.

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

4. The result set is processed as requested.

   Important:

   The cursor associated with a REF CURSOR is closed whenever the statement object that produced the REF CURSOR is closed.

   Unlike in past releases, the cursor associated with a REF CURSOR is not closed when the result set object in which the REF CURSOR was materialized is closed.

Example

This example shows how to access REF CURSOR data.

import oracle.jdbc.*;
...
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 that returns a REF CURSOR.

• As always, the output parameter of the callable statement must be registered to define its type. Use the type code OracleTypes.CURSOR for a REF CURSOR.

• The callable statement is run, returning the REF CURSOR.

• The CallableStatement object is cast to OracleCallableStatement to use the getCursor method, which is an Oracle extension to the standard JDBC API, and returns the REF CURSOR into a ResultSet object.

Oracle BINARY_FLOAT and BINARY_DOUBLE Types

The Oracle BINARY_FLOAT and BINARY_DOUBLE types are used to store IEEE 574 float and double data. These correspond to the Java float and double scalar types with the exception of negative zero and NaN.

If you include a BINARY_DOUBLE column in a query, then the data is retrieved from the database in the binary format. Also, the getDouble method will return the data in the binary format. In contrast for a NUMBER type column, the number bits are returned and converted to the Java double type.

A call to the JDBC standard setDouble(int, double) method of PreparedStatement converts the Java double argument to Oracle NUMBER style bits and send them to the database. In contrast, the setBinaryDouble(int, double) method of oracle.jdbc.OraclePreparedStatement converts the data to the internal binary bits and sends them to the database.

You must ensure that the data format used matches the type of the target parameter of PreparedStatement. This will result in correct data and least use of CPU. If you use setBinaryDouble for a NUMBER parameter, then the binary bits are sent to the server and converted to NUMBER format. The data will be correct, but server CPU load will be increased. If you use setDouble for a BINARY_DOUBLE parameter, then the data will first be converted to NUMBER bits on the client and sent to the server, where it will be converted back to binary format. This will use excess CPU on both client and server and can result in data corruption as well.

The SetFloatAndDoubleUseBinary connection property when set to true causes the JDBC standard APIs, setFloat(int, float), setDouble(int, double), and all the variations, to send internal binary bits instead of NUBMER bits.

The oracle.jdbc Package

The interfaces of the oracle.jdbc package define the Oracle extensions to the interfaces in java.sql. These extensions provide access to Oracle SQL-format data as described in this chapter. They also provide access to other Oracle-specific functionality, including Oracle performance enhancements.

For the oracle.jdbc package, Table 5-2 lists key interfaces and classes used for connections, statements, and result sets.

This section covers the following topics:

• Interface oracle.jdbc.OracleConnection

• Interface oracle.jdbc.OracleStatement

• Interface oracle.jdbc.OraclePreparedStatement

• Interface oracle.jdbc.OracleCallableStatement

• Interface oracle.jdbc.OracleResultSet

• Interface oracle.jdbc.OracleResultSetMetaData

• Class oracle.jdbc.OracleTypes

• Method getJavaSqlConnection

cs.registerOutParameter(int index, int sqlType);

cs.registerOutParameter(int index, int sqlType, String sql_name);

cs.registerOutParameter(int index, int sqlType, int scale);

CallableStatement cs = conn.prepareCall ("BEGIN charout (?); END;");
cs.registerOutParameter (1, OracleTypes.CHAR);
cs.execute ();
System.out.println ("Out argument is: " + cs.getString (1));

CallableStatement cs = conn.prepareCall ("BEGIN structout (?); END;");
cs.registerOutParameter (1, OracleTypes.STRUCT, "EMPLOYEE");
cs.execute ();

// get the value into a STRUCT because it 
// is assumed that no type map has been defined
STRUCT emp = ((OracleCallableStatement)cs).getSTRUCT (1);

ps.setNull(int index, int sqlType);

ps.setNull(int index, int sqlType, String sql_name);

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

pstmt.setNull (1, OracleTypes.NUMERIC);
pstmt.execute ();

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

pstmt.setNull (1, OracleTypes.STRUCT, "EMPLOYEE");
pstmt.execute ();

public class ARRAY
{
  // New API
  //
  java.sql.Connection getJavaSqlConnection()
    throws SQLException;

  // Deprecated API. 
  //
  oracle.jdbc.driver.OracleConnection
    getConnection() throws SQLException;

  ...
}

Oracle-Specific APIs

The OraclePreparedStatement interface is enhanced with Oracle-specific application programming interfaces (APIs) to support DML returning. The following APIs are added to the oracle.jdbc.OraclePreparedStatement interface, to register parameters that are returned and data retrieved by DML returning:

public void registerReturnParameter(int paramIndex, int externalType) throws SQLException;
public void registerReturnParameter(int paramIndex, int externalType, int maxSize) throws SQLException;
public void registerReturnParameter(int paramIndex, intsqlType, String typeName) throws SQLException;
public ResultSet getReturnResultSet() throws SQLException;

The registerReturnParameter method is used to register the return parameter for DML returning. The method throws an SQLException instance if an error occurs. The paramIndex parameter is used to specify the index of the return parameter. Its value should be greater than zero. The externalType parameter specifies the type of the return parameter. The maxSize parameter specifies the maximum bytes or characters of the return parameter. This method can be used only with char or RAW types. The typeName parameter specifies the fully-qualified name of a SQL structured type.

The getReturnResultSet method fetches the data returned from DML returning and returns it as a ResultSet object. The method throws a SQLException if an error occurs.

Running DML Returning Statements

Before running a DML returning statement, the JDBC application needs to call one or more of the registerReturnParameter methods. The method provides the JDBC drivers with information, such as type and size, of the return parameters. The DML returning statement is then processed using one of the standard JDBC APIs, executeUpdate or execute. You can then fetch the returned parameters as a ResultSet object using the getReturnResultSet method of the oracle.jdbc.OraclePreparedStatement interface.

In order to read the values in the ResultSet object, the underlying Statement object must be open. When the underlying Statement object is closed, the returned ResultSet object is also closed. This is consistent with ResultSet objects that are retrieved by processing SQL query statements.

When a DML returning statement is run, the concurrency of the ResultSet object returned by the getReturnResultSet method must be CONCUR_READ_ONLY and the type of the ResultSet object must be TYPE_FORWARD_ONLY or TYPE_SCROLL_INSENSITIVE.

Example of DML Returning

This section provides two code examples of DML returning.

The following code example illustrates the use of DML returning. In this example, assume that the maximum size of the name column is 100 characters. Because the maximum size of the name column is known, the registerReturnParameter(int paramIndex, int externalType, int maxSize) method is used.

...
OraclePreparedStatement pstmt = (OraclePreparedStatement)conn.prepareStatement(
       "delete from tab1 where age < ? returning name into ?");
pstmt.setInt(1,18);

/** register returned parameter
  * in this case the maximum size of name is 100 chars
  */
pstmt.registerReturnParameter(2, OracleTypes.VARCHAR, 100);

// process the DML returning statement
count = pstmt.executeUpdate();
if (count>0)
{
  ResultSet rset = pstmt.getReturnResultSet(); //rest is not null and not empty
  while(rset.next())
  {
    String name = rset.getString(1);
    ...
  }
}
...

The following code example also illustrates the use of DML returning. However, in this case, the maximum size of the return parameters is not known. Therefore, the registerReturnParameter(int paramIndex, int externalType) method is used.

...
OraclePreparedStatement pstmt = (OraclePreparedStatement)conn.prepareStatement(
  "insert into lobtab values (100, empty_clob()) returning col1, col2 into ?, ?");

// register return parameters
pstmt.registerReturnParameter(1, OracleTypes.INTEGER);
pstmt.registerReturnParameter(2, OracleTypes.CLOB);

// process the DML returning SQL statement
pstmt.executeUpdate();
ResultSet rset = pstmt.getReturnResultSet();
int r;
CLOB clob;
if (rset.next())
{
  r = rset.getInt(1);
  System.out.println(r);
  clob = (CLOB)rset.getClob(2);
  ...
}
...

Limitations of DML Returning

When using DML returning, you need to be aware of the following:

• It is unspecified what the getReturnResultSet method returns when it is invoked more than once. You should not rely on any specific action in this regard.

• The ResultSet objects returned from the execution of DML returning statements do not support the ResultSetMetaData type. Therefore, the applications need to know the information of return parameters before running DML returning statements.

• Streams are not supported with DML returning.

• DML returning cannot be combined with batch update.

• You cannot use both the auto-generated key feature and the DML returning feature in a single SQL DML statement. For example, the following is not allowed:

  ...
  PreparedStatement pstmt = conn.prepareStatement('insert into orders (?, ?, ?) returning order_id into ?");
  pstmt.setInt(1, seq01.NEXTVAL);
  pstmt.setInt(2, 100);
  pstmt.setInt(3, 966431502);
  pstmt.registerReturnParam(4, OracleTypes.INTEGER);
  pstmt.executeUpdate;
  ResultSet rset = pstmt.getGeneratedKeys;
  ...
  
  

Overview

The Oracle JDBC drivers support PL/SQL index-by tables of scalar data types. Table 5-3 displays the supported scalar types and the corresponding JDBC type codes.

Typical Oracle JDBC input binding, output registration, and data-access methods do not support PL/SQL index-by tables. This chapter introduces additional methods to support these types.

The OraclePreparedStatement and OracleCallableStatement classes define the additional methods. These methods include the following:

• setPlsqlIndexTable

• registerIndexTableOutParameter

• getOraclePlsqlIndexTable

• getPlsqlIndexTable

These methods handle PL/SQL index-by tables as IN, OUT, or IN OUT parameters, including function return values.

Binding IN Parameters

To bind a PL/SQL index-by table parameter in the IN parameter mode, use the setPlsqlIndexTable method defined in the OraclePreparedStatement and OracleCallableStatement classes.

synchronized public void setPlsqlIndexTable    (int paramIndex, Object arrayData, int maxLen, int curLen, int elemSqlType,    int elemMaxLen) throws SQLException

Table 5-4 describes the arguments of the setPlsqlIndexTable method.

The following code example uses the setPlsqlIndexTable method to bind an index-by table as an IN parameter:

// Prepare the statement
OracleCallableStatement procin = (OracleCallableStatement) 
   conn.prepareCall ("begin procin (?); end;"); 

// index-by table bind value 
int[] values = { 1, 2, 3 }; 

// maximum length of the index-by table bind value. This 
// value defines the maximum possible "currentLen" for batch 
// updates. For standalone binds, "maxLen" should be the 
// same as "currentLen". 
int maxLen = values.length; 

// actual size of the index-by table bind value 
int currentLen = values.length; 

// index-by table element type 
int elemSqlType = OracleTypes.NUMBER; 

// index-by table element length in case the element type 
// is CHAR, VARCHAR or RAW. This value is ignored for other 
// types. 
int elemMaxLen = 0; 

// set the value 
procin.setPlsqlIndexTable (1, values, 
                           maxLen, currentLen, 
                           elemSqlType, elemMaxLen); 

// execute the call 
procin.execute (); 

Receiving OUT Parameters

This section describes how to register a PL/SQL index-by table as an OUT parameter. In addition, it describes how to access the OUT bind values in various mapping styles.

Registering the OUT Parameters

To register a PL/SQL index-by table as an OUT parameter, use the registerIndexTableOutParameter method defined in the OracleCallableStatement class.

synchronized public void registerIndexTableOutParameter     (int paramIndex, int maxLen, int elemSqlType, int elemMaxLen)
   throws SQLException

Table 5-5 describes the arguments of the registerIndexTableOutParameter method.

The following code example uses the registerIndexTableOutParameter method to register an index-by table as an OUT parameter:

// maximum length of the index-by table value. This 
// value defines the maximum table size to be returned.
int maxLen = 10;

// index-by table element type
int elemSqlType = OracleTypes.NUMBER;

// index-by table element length in case the element type
// is CHAR, VARCHAR or FIXED_CHAR. This value is ignored for other
// types
int elemMaxLen = 0;

// register the return value
funcnone.registerIndexTableOutParameter
   (1, maxLen, elemSqlType, elemMaxLen);

Accessing the OUT Parameter Values

To access the OUT bind value, the OracleCallableStatement class defines multiple methods that return the index-by table values in different mapping styles. There are three mapping choices available in JDBC drivers:

Type Mappings

This section covers the following topics:

• JDBC Default Mappings

• Oracle Mappings

• Java Primitive Type Mappings

JDBC Default Mappings

The getPlsqlIndexTable(int) method with returns index-by table elements using the JDBC default mappings. The syntax for this method is:

public Object getPlsqlIndexTable (int paramIndex)
   throws SQLException

Table 5-6 describes the argument of the getPlsqlIndexTable method.

The return value is a Java array. The elements of this array are of the default Java type corresponding to the SQL type of the elements. For example, for an index-by table with elements of NUMERIC type code, the element values are mapped to BigDecimal by the Oracle JDBC driver, and the getPlsqlIndexTable method returns a BigDecimal[] array. For a JDBC application, you must cast the return value to BigDecimal[] to access the table element values.

The following code example uses the getPlsqlIndexTable method to return index-by table elements with JDBC default mapping:

// access the value using JDBC default mapping 
BigDecimal[] values = 
   (BigDecimal[]) procout.getPlsqlIndexTable (1); 

// print the elements 
for (int i=0; i<values.length; i++) 
   System.out.println (values[i].intValue()); 

Oracle Mappings

The getOraclePlsqlIndexTable method returns index-by table elements using Oracle mapping.

public Datum[] getOraclePlsqlIndexTable (int paramIndex)
      throws SQLException 

Table 5-7 describes the argument of the getOraclePlsqlIndexTable method.

The return value is an oracle.sql.Datum array, and the elements in the array are of the default Datum type corresponding to the SQL type of the element. For example, the element values of an index-by table of numeric elements are mapped to the oracle.sql.NUMBER type in Oracle mapping, and the getOraclePlsqlIndexTable method returns an oracle.sql.Datum array that contains oracle.sql.NUMBER elements.

The following code example uses the getOraclePlsqlIndexTable method to access the elements of a PL/SQL index-by table OUT parameter, using Oracle mapping.

// Prepare the statement 
OracleCallableStatement procout = (OracleCallableStatement)
                                  conn.prepareCall ("begin procout (?); end;");

...

// run the call
procout.execute ();
 
// access the value using Oracle JDBC mapping
Datum[] outvalues = procout.getOraclePlsqlIndexTable (1);

// print the elements
for (int i=0; i<outvalues.length; i++)
   System.out.println (outvalues[i].intValue());

Java Primitive Type Mappings

The getPlsqlIndexTable(int, Class) method returns index-by table elements in Java primitive types. The return value is a Java array. The syntax for this method is:

synchronized public Object getPlsqlIndexTable    (int paramIndex, Class primitiveType) throws SQLException

Table 5-8 describes the arguments of the getPlsqlIndexTable method.

The following code example uses the getPlsqlIndexTable method to access the elements of a PL/SQL index-by table of numbers. In the example, the second parameter specifies java.lang.Integer.TYPE and the return value of the getPlsqlIndexTable method is an int array.

OracleCallableStatement funcnone = (OracleCallableStatement) 
   conn.prepareCall ("begin ? := funcnone; end;"); 

// maximum length of the index-by table value. This 
// value defines the maximum table size to be returned. 
int maxLen = 10; 

// index-by table element type 
int elemSqlType = OracleTypes.NUMBER; 

// index-by table element length in case the element type 
// is CHAR, VARCHAR or RAW. This value is ignored for other 
// types 
int elemMaxLen = 0; 

// register the return value 
funcnone.registerIndexTableOutParameter (1, maxLen, 
                                        elemSqlType, elemMaxLen); 
// execute the call 
funcnone.execute (); 

// access the value as a Java primitive array. 
int[] values = (int[]) 
   funcnone.getPlsqlIndexTable (1, java.lang.Integer.TYPE); 

// print the elements 
for (int i=0; i<values.length; i++) 
   System.out.println (values[i]);