10 Oracle Extensions

10.3.1.1 Classes of the oracle.sql Package

Table 10-1 lists the oracle.sql datatype classes and their corresponding Oracle SQL types.

You can find more detailed information about each of these classes later in this chapter. Additional details about use of the Oracle extended types (STRUCT, REF, ARRAY, BLOB, CLOB, BFILE, and ROWID) are described in the following locations:

• "Oracle Character Datatypes Support"

• "Additional Oracle Type Extensions"

• Chapter 14, "Working with LOBs and BFILEs"

• Chapter 13, "Working with Oracle Object Types"

• Chapter 15, "Using Oracle Object References"

• Chapter 16, "Working with Oracle Collections"

  
  

  Notes: For information about retrieving data from a result set or callable statement object into oracle.sql.* types, as opposed to Java types, see Chapter 11, "Accessing and Manipulating Oracle Data". The LONG and LONG RAW SQL types and REF CURSOR type category have no oracle.sql.* classes. Use standard JDBC functionality for these types. For example, retrieve LONG or LONG RAW data as input streams using the standard JDBC result set and callable statement methods getBinaryStream() and getCharacterStream(). Use the getCursor() method for REF CURSOR types.

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

• oracle.sql.ArrayDescriptor class: Used in constructing oracle.sql.ARRAY objects; describes the SQL type of the array. (See "Creating ARRAY Objects and Descriptors".)

• oracle.sql.StructDescriptor class: Used in constructing oracle.sql.STRUCT objects, which you can use as a default mapping to Oracle objects in the database. (See "Creating STRUCT Objects and Descriptors".)

• oracle.sql.ORAData and oracle.sql.ORADataFactory interfaces: Used in Java classes implementing the Oracle ORAData scenario of Oracle object support. (The other possible scenario is the JDBC-standard SQLData implementation.) See "Understanding the ORAData Interface" for more information on ORAData.

• oracle.sql.OpaqueDescriptor class: Used to obtain the meta data for an instance of the oracle.sql.OPAQUE class.

10.3.1.2 General oracle.sql.* Datatype Support

Each of the Oracle datatype 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 (in the raw format in which JDBC received the data from the database)

• 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 datatypes 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 typed—for example: stringValue(), intValue(), booleanValue(), dateValue(), bigDecimalValue()

• additional conversion, getXXX() and setXXX() methods as appropriate for the functionality of the datatype (such as methods in the 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)

Refer to the Oracle JDBC Javadoc for additional information about these classes. See "Class oracle.sql.CHAR" to learn how the oracle.sql.CHAR class supports character data.

10.3.1.3 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. (If you use a SQLData custom Java class, 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.

In the database, Oracle stores the raw bytes of object data in a linearized form. A STRUCT object is a wrapper for the raw bytes of an Oracle object. It contains the SQL type name of the Oracle object and a "values" array of oracle.sql.Datum objects that hold the attribute values in SQL format.

You can materialize a STRUCT's attributes 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 all the 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 values 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 the oracle.sql.CHAR type. Nested objects in the values array of a STRUCT object are materialized by the JDBC driver as instances of STRUCT.

In some cases, you might 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.

For more information about working with Oracle objects using the oracle.sql.STRUCT and StructDescriptor classes, see "Using the Default STRUCT Class for Oracle Objects".

10.3.1.4 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.* datatype 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. Be aware, however, that 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.

For more information about working with Oracle object references using the oracle.sql.REF class, see Chapter 15, "Using Oracle Object References".

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

For more information about working with Oracle collections using the oracle.sql.ARRAY and ArrayDescriptor classes, see "Overview of Collection (Array) Functionality".

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

BLOBs and CLOBs (referred to collectively as "LOBs"), and BFILEs (for external files) 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 datatypes in several ways:

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

• CLOBs point to large fixed-width character data items (that is, characters that require a fixed number of bytes per character) 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, but bear in mind that you are receiving only the locator, not the data itself. Additional steps are necessary to retrieve the data.

For information about how to access and manipulate locators and data for LOBs and BFILEs, see Chapter 14, "Working with LOBs and BFILEs".

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

These classes map to primitive SQL datatypes, which are a part of standard JDBC, and supply conversions to and from the corresponding JDBC Java types. For more information, see the Javadoc.

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 

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

The JDBC drivers support the following date/time datatypes:

• TIMESTAMP (TIMESTAMP)

• TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)

• TIMESTAMP WITH LOCAL TIME ZONE (TIMESTAMPLTZ)

The JDBC drivers allow conversions among DATE and date/time datatypes. 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

Use the following methods from the oracle.jdbc.OraclePreparedStatement interface to set a date/time:

• setTIMESTAMP(int paramIdx,TIMESTAMP x)

• setTIMESTAMPTZ(int paramIdx,TIMESTAMPTZ x)

• setTIMESTAMPLTZ(int paramIdx,TIMESTAMPLTZ x)

Use the following methods from the oracle.jdbc.OracleCallableStatement interface to get a date/time:

• TIMESTAMP getTIMESTAMP (int paramIdx)

• TIMESTAMPTZ getTIMESTAMPTZ(int paramIdx)

• TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)

Use the following methods from the oracle.jdbc.OracleResultSet interface to get a date/time:

• TIMESTAMP getTIMESTAMP(int paramIdx)

• TIMESTAMP getTIMESTAMP(java.lang.String colName)

• TIMESTAMPTZ getTIMESTAMPTZ(int paramIdx)

• TIMESTAMPTZ getTIMESTAMPTZ(java.lang.String colName)

• TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)

• TIMESTAMPLTZ getTIMESTAMPLTZ(java.lang.String colName)

• TIMESTAMPLTZ getTIMESTAMPLTZ(int paramIdx)

Use the following methods from the oracle.jdbc.OracleResultSet interface to update a date/time:

• updateTIMESTAMP(int paramIdx)

• updateTIMESTAMPTZ(int paramIdx)

• updateTIMESTAMPLTZ(int paramIdx)

Before accessing TIMESTAMP WITH LOCAL TIME ZONE data, call the OracleConnection.setSessionTime() 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.

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

For information about accessing ROWID data, see "Oracle ROWID Type".

10.3.1.10 Class oracle.sql.OPAQUE

The oracle.sql.OPAQUE class gives you the name and characteristics of the OPAQUE type and any attributes. OPAQUE types provide 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.

• public boolean 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 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 (raw bytes).

• toJdbc(): Returns the JDBC representation of the Datum object.

10.3.2.1 Interface oracle.jdbc.OracleConnection

This interface extends standard JDBC connection functionality to create and return Oracle statement objects, set flags and options for Oracle performance extensions, support type maps for Oracle objects, and support client identifiers.

"Additional Oracle Performance Extensions" describes the performance extensions, including row prefetching and update batching.

10.3.2.2 Interface oracle.jdbc.OracleStatement

This interface extends standard JDBC statement functionality and is the superinterface of the OraclePreparedStatement and OracleCallableStatement classes. Extended functionality includes support for setting flags and options for Oracle performance extensions on a statement-by-statement basis, as opposed to the OracleConnection interface that sets these on a connection-wide basis.

"Additional Oracle Performance Extensions" describes the performance extensions, including row prefetching and column type definitions.

Key methods include:

• executeQuery(): Executes a database query and returns an OracleResultSet object.

• getResultSet(): Retrieves an OracleResultSet object.

• close(): Closes the current statement.

These oracle.jdbc.OracleStatement methods are Oracle-defined extensions:

• defineColumnType(): Defines the type you will use to retrieve data from a particular database table column.

  
  

  Note: This method is no longer needed or recommended for use with the Thin driver. See the disableDefineColumnType connection property in the oracle.jdbc.pool.OracleDataSource JavaDoc.

• getRowPrefetch(): Retrieves the row-prefetch value for this statement.

• setRowPrefetch(): Sets the row-prefetch value for this statement.

10.3.2.3 Interface oracle.jdbc.OraclePreparedStatement

This interface extends the OracleStatement interface and extends standard JDBC prepared statement functionality. Also, the oracle.jdbc.OraclePreparedStatement interface is extended by the OracleCallableStatement interface. Extended functionality consists of setXXX() methods for binding oracle.sql.* types and objects into prepared statements, and methods to support Oracle performance extensions on a statement-by-statement basis. "Additional Oracle Performance Extensions" describes the performance extensions, including database update batching.

Key methods include:

• getExecuteBatch(): Retrieves the update-batching value for this statement.

• setExecuteBatch(): Sets the update-batching value for this statement.

• setOracleObject(): This is a generic setXXX() method for binding oracle.sql.* data into a prepared statement as an oracle.sql.Datum object.

• setXXX(): These methods, such as setBLOB(), are for binding specific oracle.sql.* types into prepared statements.

• setXXXAtName(): Unlike the JDBC standard method setXXX(int,XXX), which sets the value of the nth SQL parameter specified by the integer argument, setXXXAtName(String,XXX) sets the SQL parameter with the specified character name in the SQL string. The SQL parameter is a SQL identifier preceded by a colon (:). For example, :id in

  ps = conn.prepareStatement("select * from tab where id = :id");
    ((OraclePreparedStatement)ps).setIntByName("id", 42);
  
  

• setORAData(): Binds an ORAData object (for use in mapping Oracle object types to Java) into a prepared statement.

• setNull(): Sets the value of the object specified by its SQL type name to NULL. For setNull(param_index, type_code, sql_type_name), if type_code is REF, ARRAY, or STRUCT, then sql_type_name is the fully qualified name (schema.sql_type_name) of the SQL type.

• setFormOfUse(): Sets which form of use this method is going to use. There are two constants that specify the form of use: FORM_CHAR and FORM_NCHAR, where FORM_CHAR is the default, meaning that the regular database character set is used. If the form of use is set to FORM_NCHAR, the JDBC driver will represent the provided data in the national character set of the server. The following code show how the FORM_NCHAR is used:

pstmt.setFormOfUse 
  (parameter index,
  oracle.jdbc.OraclePreparedStatement.FORM_NCHAR)

• close(): Closes the current statement.

10.3.2.4 Interface oracle.jdbc.OracleCallableStatement

This interface extends the OraclePreparedStatement interface (which extends the OracleStatement interface) and incorporates standard JDBC callable statement functionality.

Key methods include:

• getOracleObject(): This is a generic getXXX() method for retrieving data into an oracle.sql.Datum object, which can be cast to the specific oracle.sql.* type as necessary.

• getXXX(): These methods, such as getCLOB(), are for retrieving data into specific oracle.sql.* objects.

• setOracleObject(): This is a generic setXXX() method for binding oracle.sql.* data into a callable statement as an oracle.sql.Datum object.

• setXXX(): These methods, such as setBLOB(), are inherited from OraclePreparedStatement for binding specific oracle.sql.* objects into callable statements.

• setXXX(String, XXX): The definition of a PL/SQL stored procedure may include one or more named parameters. When you create a CallableStatement to invoke this stored procedure, you must supply values for all IN parameters. You can either do this with the JDBC standard setXXX(int,XXX) methods, or using the Oracle extension setXXX(String,XXX) The first argument to this method specifies the name of the PL/SQL formal parameter; the second argument specifies the value. For example, if you have a stored procedure foo defined as:

  CREATE OR REPLACE foo (myparameter VARACHAR2)
    BEGIN
    ...
    END;
  

  and you create an OracleCallableStatement to invoke foo:

  OracleCallableStatement cs = (OracleCallableStatement)
     conn.prepareCall("call foo(?)");
  
  

  you can pass the string "bar" to this procedure in one of two ways:

  cs.setString(1,"bar"); // JDBC standard
  // or...
  cs.setString("myparameter","bar"); // Oracle extension
  
  

  
  

  Note: The argument is the name of the formal parameter declared in the PL/SQL stored procedure. This name does not necessarily appear anywhere in the SQL string. This differs from the setXXXatName method, whose first argument is a substring of the SQL string.

• setNull(): Sets the value of the object specified by its SQL type name to NULL. For setNull(param_index, type_code, sql_type_name), if type_code is REF, ARRAY, or STRUCT, then sql_type_name is the fully qualified (schema.type) name of the SQL type.

• setFormOfUse(): Sets which form of use this method is going to use. There are two constants that specify the form of use: FORM_CHAR and FORM_NCHAR, where FORM_CHAR is the default. If the form of use is set to FORM_NCHAR, the JDBC driver will represent the provided data in the national character set of the server. The following code show how FORM_NCHAR is used:

pstmt.setFormOfUse 
  (parameter index,
  oracle.jdbc.OraclePreparedStatement.FORM_NCHAR)

• registerOutParameter(): Registers the SQL typecode of the statement's output parameter. JDBC requires this for any callable statement with an OUT parameter. It takes an integer parameter index (the position of the output variable in the statement, relative to the other parameters) and an integer SQL type (the type constant defined in oracle.jdbc.OracleTypes).

  This is an overloaded method. One version of this method is for named types only—when the SQL typecode is OracleTypes.REF, STRUCT, or ARRAY. In this case, in addition to a parameter index and SQL type, the method also takes a String SQL type name (the name of the Oracle user-defined type in the database, such as EMPLOYEE).

• close(): Closes the current result set, if any, and the current statement.

10.3.2.5 Interface oracle.jdbc.OracleResultSet

This interface extends standard JDBC result set functionality, implementing getXXX() methods for retrieving data into oracle.sql.* objects.

Key methods include:

• getOracleObject(): This is a generic getXXX() method for retrieving data into an oracle.sql.Datum object. It can be cast to the specific oracle.sql.* type as necessary.

• getXXX(): These methods, such as getCLOB(), are for retrieving data into oracle.sql.* objects.

10.3.2.6 Interface oracle.jdbc.OracleResultSetMetaData

This interface extends standard JDBC result set metadata functionality to retrieve information about Oracle result set objects. See "Using Result Set Meta Data Extensions" for information on the functionality of the OracleResultSetMetadata interface.

10.3.2.7 Class oracle.jdbc.OracleTypes

The OracleTypes class defines constants that JDBC uses to identify SQL types. Each variable in this class has a constant integer value. The oracle.jdbc.OracleTypes class duplicates the typecode definitions of the standard Java java.sql.Types class and contains these additional typecodes for Oracle extensions:

• OracleTypes.BFILE

• OracleTypes.ROWID

• OracleTypes.CURSOR (for REF CURSOR types)

As in java.sql.Types, all the variable names are in all-caps.

JDBC uses the SQL types identified by the elements of the OracleTypes class in two main areas: registering output parameters, and in the setNull() method of the PreparedStatement class.

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

10.3.2.8 Method getJavaSqlConnection()

The getJavaSqlConnection() method of the oracle.sql.* classes returns java.sql.Connection while the getConnection() method returns oracle.jdbc.driver.OracleConnection. Because the methods that use the oracle.jdbc.driver package are deprecated, the getConnection() method is also deprecated in favor of the getJavaSqlConnection() method.

For the following Oracle datatype classes, the getJavaSqlConnection() method is available:

• oracle.sql.ARRAY

• oracle.sql.BFILE

• oracle.sql.BLOB

• oracle.sql.CLOB

• oracle.sql.OPAQUE

• oracle.sql.REF

• oracle.sql.STRUCT

The following shows the getJavaSqlConnection() and the getConnection() methods in the Array class:

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

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

  ...
}

10.4.3.1 oracle.sql.CHAR Objects and Character Sets

The CHAR class provides Globalization Support functionality to convert character data. This class has two key attributes: (1) Globalization Support character set and (2) 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 being know, the bytes of data in the CHAR object are meaningless.

The oracle.sql.CharacterSet class is instantiated to represent character sets. 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. You can find a complete list of the character sets that Oracle supports in the Oracle Database Globalization Support Guide.

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

   For more information on character sets and character set IDs, see the Oracle Database Globalization Support Guide.

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—they 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.

   See the oracle.sql.CHAR class Javadoc for more information.

   
   

   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.

10.4.3.3 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 server (a database) 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. For more information on how the JDBC drivers convert between character sets, see Chapter 12, "Globalization Support".

10.5.1.1 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 
} 

10.5.2.1 Example: Accessing REF CURSOR Data

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 typecode OracleTypes.CURSOR for a REF 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.