See Also:

Database Management Using JDBC

Oracle Database 11g Release 1 (11.1) introduces new JDBC methods,startup andshutdown, in theoracle.jdbc.OracleConnection interface that enable you to start up and shut down an Oracle Database instance. You also have support for the Database Change Notification feature of Oracle Database. These new features have been discussed in details in"Database Management".

Support for Oracle Data Types

One of the features of the Oracle JDBC extensions is the type support in theoracle.sql package. This package includes classes that are an exact representation of the data in Oracle format. Keep the following important points in mind, when you useoracle.sql types in your program:

• For numeric type of data, the conversion to standard Java types does not guarantee to retain full precision due to limitations of the data conversion process. Use theBigDecimal type to minimize any data loss issues.

• For certain data types, the conversion to standard Java types can be dependent on the system settings and your program may not run as expected. This is a known limitation while converting data fromoracle.sql types to standard Java types.

• If the functionalities of your program is limited to reading data from one table and writing the same to another table, then for numeric and date data,oracle.sql types are slightly faster as compared to standard Java types. But, if your program involves even a simple data manipulation opearation like compare or print, then standard Java types are faster.

• oracle.sql.CHAR is not an exact representation of the data in Oracle format.oracle.sql.CHAR is constructed fromjava.lang.String. There is no advantage of usingoracle.sql.CHAR becausejava.lang.String is always faster and represents the same character sets, excluding a couple of desupported character sets.

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 anEmployee object type, where eachEmployee object has afirstname attribute (character string), alastname attribute (character string), and anemployeenumber attribute (integer).

Oracle JDBC 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 weakjava.sql.Struct type or to strongly typed customized classes. These strong types are referred to ascustom Java classes, which must implement either the standardjava.sql.SQLData interface or the Oracle extensionoracle.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 eitherSQLData orORAData interface implementations.

ForSQLData interface implementations, atype 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 definesgetXXX methods of the custom Java classes, which retrieve data into your Java application.

Support forSchema 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 andsql_type_name is the SQL type name of the object.schema_name andsql_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 the 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 within quotation marks. However, if the SQL type name has a period in it, such asCORPORATE.EMPLOYEE, the type name must be quoted.

• The JDBC driver looks for the first period in the object name that is not within quotation marks 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 put the type name within quotation marks if the type name has a dot in it.

  For example, assume that user Scott creates a type calledperson.address and then wants to use it in his session. Scott may want to skip the schema name and pass inperson.address to the JDBC driver. In this case, ifperson.address is not within quotation marks, then the period will be detected and the JDBC driver will mistakenly interpretperson as the schema name andaddress 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 the object type name is within quotation marks.

  For example, ifScott.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 theRETURNING 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.

Accessing PL/SQL Index-by Tables

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

Package oracle.sql

Theoracle.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 containers for SQL data.

Each of theoracle.sql.* data type classes extendsoracle.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, asTable 4-1 indicates, implement standard JDBC 2.0 interfaces in thejava.sql package, as well as extending theoracle.sql.Datum class.

Classes of the oracle.sql Package

Table 4-1 lists theoracle.sql data type classes and their corresponding Oracle SQL types.

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

• oracle.sql.ArrayDescriptor

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

• oracle.sql.StructDescriptor

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

• oracle.sql.ORAData andoracle.sql.ORADataFactory

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

• oracle.sql.OpaqueDescriptor

  This class is used to obtain the metadata for an instance of theoracle.sql.OPAQUE class.

• oracle.sql.TypeDescriptor

  This class is used to represent transient and persistent SQL types in Java.

General oracle.sql.* Data Type Support

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

• Data storage as Java byte arrays for SQL data

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

• AtoJdbc() 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 asBFILE. The driver returns the object in the correspondingoracle.sql.* format.

• AppropriatexxxValue methods to convert SQL data to Java type. For example,stringValue,intValue,booleanValue,dateValue, andbigDecimalValue

• Additional conversion methods,getXXX andsetXXX, 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 theREF 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 aSQLData 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 theoracle.sql.STRUCT class.

TheSTRUCT class implements the standard JDBC 2.0java.sql.Struct interface and extends theoracle.sql.Datum class.

ASTRUCT 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 oforacle.sql.Datum objects that hold the attribute values in SQL format.

If you want to create aSTRUCT object, then use thecreateStruct method of theoracle.jdbc.OracleConnection interface. The signature of this factory method for creatingSTRUCT objects is as follows:

Struct createStruct (String typeName, Object[] attributes) throws SQLException

The parameters in this signature are as follows:

• ThetypeName parameter is the SQL type name of the SQL structured type to which the STRUCT object maps. ThetypeName is the name of a user-defined type that has been defined for this database. It is the value returned by theStruct.getSQLTypeName method.

• Theattributes parameter specifies the attributes that populate the returned object.

You can materialize attributes of aSTRUCT object asoracle.sql.Datum[] objects, if you use thegetOracleAttributes method, or asjava.lang.Object[] objects, if you use thegetAttributes method. Materializing the attributes asoracle.sql.* objects gives you the following advantages of theoracle.sql.* format:

• Materializingoracle.sql.STRUCT data inoracle.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.

  Note:

  • Elements of the array, although of the genericDatum type, actually contain data associated with the relevantoracle.sql.* type appropriate for the given attribute. You can cast the element to the appropriateoracle.sql.* type as desired. For example, aCHAR data attribute within theSTRUCT is materialized asoracle.sql.Datum. To use it asCHAR data, you must cast it tooracle.sql.CHAR.

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

Overview of Class oracle.sql.REF

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

TheREF 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 theREF class also includes methods to retrieve and pass the object data.

You cannot createREF objects in your JDBC application. You can only retrieve existingREF objects from the database.

Overview of Class oracle.sql.ARRAY

Theoracle.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 theARRAY class. The structure of the data is equivalent in either case. Theoracle.sql.ARRAY class extends theoracle.sql.Datum class and implements the standard JDBC 2.0java.sql.Array interface.

You can use thesetARRAY method of theOraclePreparedStatement orOracleCallableStatement interface topass anARRAY as an input parameter to a prepared statement. Similarly, you can use thecreateARRAY method of theOracleConnection interface to create anARRAY object to pass it to a prepared statement or callable statement, perhaps to insert into the database.

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.

Theoracle.sql package supports these data types in several ways:

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

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

• BFILEs point to the content of external files (operating system files) and are supported by theoracle.sql.BFILE class. BFiles are read-only.

You can select a BLOB,CLOB, orBFILE locator from the database using a standardSELECT 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 JavaDouble andFloatNaN values do not have an equivalent OracleNUMBER representation, aNullPointerException is thrown whenever aDouble.NaN value or aFloat.NaN value is converted into an OracleNUMBER using theoracle.sql.NUMBER class. For instance, the following code throws aNullPointerException:

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 betweenDATE and date/time data types. For example, you can access aTIMESTAMP WITH TIME ZONE column as aDATE 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. Time zones are specified by using thejava.util.Calendar class.

The following code shows how theTimeZone andCalendar objects are created forUS_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 accessingTIMESTAMP WITH LOCAL TIME ZONE data, call theOracleConnection.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 anyTIMESTAMP WITH LOCAL TIME ZONE data accessed through JDBC can be adjusted using the session time zone.

Class oracle.sql.OPAQUE

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

Package oracle.jdbc

The interfaces of theoracle.jdbc package define the Oracle extensions to the interfaces injava.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 includeCHAR,VARCHAR2, andCLOB. 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 SQLNCHAR data types were created for Globalization Support. The SQLNCHAR data types includeNCHAR,NVARCHAR2, andNCLOB. These data types allow you to storeUnicode data in the databaseNCHAR character set encoding. TheNCHAR character set, which never changes, is established when you create the database.

The usage of SQLNCHAR data types is similar to that of the SQLCHAR data types. JDBC uses the same classes and methods to access SQLNCHAR data types that are used for the corresponding SQLCHAR data types. Therefore, there are no separate, corresponding classes defined in theoracle.sql package for SQLNCHAR data types. Similarly, there is no separate, corresponding constant defined in theoracle.jdbc.OracleTypes class for SQLNCHAR data types.

The following code shows how to access SQLNCHAR 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.setInt(1, 1);                    // NUMBER columnpstmt.setNString(2, myUnicodeString1);  // NVARCHAR2 columnpstmt.setNString(3, myUnicodeString2);  // NCHAR columnpstmt.execute();

Class oracle.sql.CHAR

Theoracle.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 aCHAR object is constructed. Without the Globalization Support character set information, the data bytes in theCHAR object are meaningless. Theoracle.sql.CHAR class is used for both SQLCHAR and SQLNCHAR data types.

The only remaining use of theoracle.sql.CHAR class 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 thejava.lang.String class. When processing byte data from another source, you can use anoracle.sql.CHAR to convert the bytes tojava.lang.String.

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

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

JDBC application code rarely needs to constructCHAR objects directly, because the JDBC driver automatically createsCHAR objects as needed.

To construct aCHAR object, you must provide character set information to theCHAR object by way of an instance of theCharacterSet class. Each instance of this class represents one of the Globalization Support character sets that Oracle supports. ACharacterSet 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 aCHAR object:

1. Create aCharacterSet object by calling thestaticCharacterSet.make method.

   This method is a factory for the character set instance. Themake 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 aCHAR object.

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

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

   There are multiple factory methods forCHAR, which can take aString, abyte array, or an object as input along with theCharacterSet object. In the case of aString, the string is converted to the character set indicated by theCharacterSet object before being placed into theCHAR object.

   Note:

   • TheCharacterSet object cannot be a null value.

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

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

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

oracle.sql.CHAR Conversion Methods

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

• getString

  This method converts the sequence of characters represented by theCHAR object to a string, returning a JavaString object. If you enter an invalidOracleID, then the character set will not be recognized and thegetString method will throw aSQLException exception.

• toString

  This method is identical to thegetString method. But if you enter an invalidOracleID, then the character set will not be recognized and thetoString method will return a hexadecimal representation of theCHAR data and willnot throw aSQLException exception.

• getStringWithReplacement

  This method is identical to thegetString method, except a default replacement character replaces characters that have no unicode representation in theCHAR 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 differentcharacter sets. When you use the methods of theCHAR 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

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

Theoracle.sql.ROWID class is supplied as a container forROWID SQL data type.

ROWIDs provide functionality similar to thegetCursorName method specified in thejava.sql.ResultSet interface and thesetCursorName method specified in thejava.sql.Statement interface.

If you include the ROWID pseudo-column in a query, then you can retrieve the ROWIDs with the result setgetString method. You can also bind a ROWID to aPreparedStatement parameter with thesetString method. This enables in-place updating, 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 typeREFx, whereREF is short forREFERENCE andx represents the entity being referenced. AREF CURSOR, then, identifies a reference to a cursor variable. Because many cursor variables might exist to point to many work areas,REFCURSOR 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 theREFCURSOR category. For example:

DECLARE TYPE DeptCursorTyp IS REF CURSOR

Then, create the cursor variable by declaring it to be of the typeDeptCursorTyp:

dept_cv DeptCursorTyp  - - declare cursor variable...

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

Stored procedures can return cursor variables of theREFCURSOR category. This output is equivalent to a database cursor or a JDBC result set. AREF CURSOR essentially encapsulates the results of a query.

In JDBC,aREF CURSOR is materialized as aResultSet 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 aREF CURSOR.

3. The Java application casts the callable statement to an Oracle callable statement and uses thegetCursor method of theOracleCallableStatement class to materialize theREF CURSOR as a JDBCResultSet object.

4. The result set is processed as requested.

   Important:

   The cursor associated with aREF CURSOR is closed whenever the statement object that produced theREF CURSOR is closed.

   Unlike in past releases, the cursor associated with aREF CURSOR isnot closed when the result set object in which theREF CURSOR was materialized is closed.

Example

This example shows how to accessREF CURSOR data.

import oracle.jdbc.*;...CallableStatement cstmt;ResultSet cursor;// Use a PL/SQL block to open the cursorcstmt = 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 standard ResultSetwhile (cursor.next ())    {System.out.println (cursor.getString(1));}

In the preceding example:

• ACallableStatement object is created by using theprepareCall method of the connection class.

• The callable statement implements a PL/SQL procedure that returns aREF CURSOR.

• As always, the output parameter of the callable statement must be registered to define its type. Use the type codeOracleTypes.CURSOR for aREF CURSOR.

• The callable statement is run, returning theREF CURSOR.

• TheCallableStatement object is cast toOracleCallableStatement to use thegetCursor method, which is an Oracle extension to the standard JDBC API, and returns theREF CURSOR into aResultSet object.

Oracle BINARY_FLOAT and BINARY_DOUBLE Types

The OracleBINARY_FLOAT andBINARY_DOUBLE types are used to store IEEE 574 float and double data. These correspond to the Javafloat anddouble scalar types with the exception of negative zero andNaN.

If you include aBINARY_DOUBLE column in a query, then the data is retrieved from the database in the binary format. Also, thegetDouble method will return the data in the binary format. In contrast, for aNUMBER data type column, the number bits are returned and converted to the Javadouble data type.

A call to the JDBC standardsetDouble(int, double) method of thePreparedStatement interface converts the Javadouble argument to OracleNUMBER style bits and send them to the database. In contrast, thesetBinaryDouble(int, double) method of theoracle.jdbc.OraclePreparedStatement interface 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 thePreparedStatement interface. This will result in correct data and least use of CPU. If you usesetBinaryDouble for aNUMBER parameter, then the binary bits are sent to the server and converted toNUMBER format. The data will be correct, but server CPU load will be increased. If you usesetDouble for aBINARY_DOUBLE parameter, then the data will first be converted toNUMBER bits on the client and sent to the server, where it will be converted back to binary format. This will increase the CPU load on both client and server and can result in data corruption as well.

TheSetFloatAndDoubleUseBinary connection property when set totrue causes the JDBC standard APIs,setFloat(int, float),setDouble(int, double), and all the variations, to send internal binary bits instead ofNUBMER bits.

Oracle SYS.ANYTYPE and SYS.ANYDATA Types

Oracle Database 11g Release 1 (11.1) provides a Java interface to access theSYS.ANYTYPE andSYS.ANYDATA Oracle types.

An instance of theSYS.ANYTYPE type contains a type description of any SQL type, persistent or transient, named or unnamed, including object types and collection types. You can use theoracle.sql.TypeDescriptor class to access theSYS.ANYTYPE type. AnANYTYPE instance can be retrieved from a PL/SQL procedure or a SQLSELECT statement whereSYS.ANYTYPE is used as a column type. To retrieve anANYTYPE instance from the database, use thegetObject method. This method returns an instance of theTypeDescriptor.

The retrievedANYTYPE instance could be any of the following:

• Transient object type

• Transient predefined type

• Persistent object type

• Persistent predefined type

...ResultSet rs = stmt.executeQuery("select anytype_column from my_table");TypeDescriptor td = (TypeDescriptor)rs.getObject(1);short typeCode = td.getInternalTypeCode();if(typeCode == TypeDescriptor.TYPECODE_OBJECT){  // check if it's a transient type  if(td.isTransientType())  {    AttributeDescriptor[] attributes = ((StructDescriptor)td).getAttributesDescriptor();    for(int i=0; i<attributes.length; i++)      System.out.println(attributes[i].getAttributeName());  }  else  {    System.out.println(td.getTypeName());  }}...

...OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall  ("BEGIN ? := transient_obj_type (); END;");cstmt.registerOutParameter(1,OracleTypes.OPAQUE,"SYS.ANYTYPE");cstmt.execute();TypeDescriptor obj = (TypeDescriptor)cstmt.getObject(1);if(!obj.isTransient())  System.out.println("This must be a JDBC bug");cstmt.close();return obj;...

...CallableStatement cstmt = conn.prepareCall("BEGIN ? := dumpanytype(?); END;");cstmt.registerOutParameter(1,OracleTypes.VARCHAR);// obj is the instance of TypeDescriptor that you have retrievedcstmt.setObject(2,obj);cstmt.execute();String str = (String)cstmt.getObject(1);...

Theoracle.sql.ANYDATA class enables you to accessSYS.ANYDATA instances from the database. An instance of this class can be obtained from any valid instance oforacle.sql.Datum class. TheconvertDatum factory method takes an instance ofDatum and returns an instance ofANYDATA. The syntax for this factory method is as follows:

public static ANYDATA convertDatum(Datum datum) throws SQLException

The following is sample code for creating an instance oforacle.sql.ANYDATA:

// struct is a valid instance of oracle.sql.STRUCT that either comes from the // database or has been constructed in Java.ANYDATA myAnyData = ANYDATA.convertDatum(struct);

...// anydata_table has been created as:// CREATE TABLE anydata_tab (data SYS.ANYDATA)Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("select data from my_anydata_tab");while(rs.next()){  ANYDATA anydata = (ANYDATA)rs.getObject(1);  if(!anydata.isNull())  {    TypeDescriptor td = anydata.getTypeDescriptor();    if(td.getTypeCode() == OracleType.TYPECODE_OBJECT)      STRUCT struct = (STRUCT)anydata.accessDatum();  }}...

CREATE TABLE anydata_tab ( id NUMBER, data SYS.ANYDATA)CREATE OR REPLACE TYPE employee AS OBJECT ( empno NUMBER, ename VARCHAR2(10) )

...PreparedStatement pstmt = conn.prepareStatement("insert into anydata_table values (?,?)");StructDescriptor sd = StructDescriptor.createDescriptor("EMPLOYEE",(OracleConnection)conn);Object[] objattr = new Object[2];objattr[0] = new BigDecimal(1120);objattr[1] = new String("Papageno");STRUCT myEmployeeStr = new STRUCT(sd,conn,objattr);ANYDATA anyda = ANYDATA.convertDatum(myEmployeeStr);pstmt.setInt(1,123);pstmt.setObject(2,anyda);pstmt.executeUpdate();...

...Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery("select data from anydata_table");while(rs.next()){  ANYDATA obj = (ANYDATA)rs.getObject(1);  TypeDescriptor td = obj.getTypeDescriptor();}rs.close();stmt.close();...

The oracle.jdbc Package

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

For theoracle.jdbc package,Table 4-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 definedSTRUCT 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{  java.sql.Connection getJavaSqlConnection()    throws SQLException;  ...}

Note:

• The server-side internal driver does not support DML returning and retrieval of auto-generated keys.

• You cannot use both DML returning and retrieval of auto-generated keys in the same statement.

See Also:

Oracle-Specific APIs

TheOraclePreparedStatement interface is enhanced with Oracle-specific application programming interfaces (APIs) to support DML returning. TheregisterReturnParameter andgetReturnResultSet methods have been added to theoracle.jdbc.OraclePreparedStatement interface, to register parameters that are returned and data retrieved by DML returning.

TheregisterReturnParameter method is used to register the return parameter for DML returning. The method throws aSQLException instance if an error occurs. You must pass a positive integer specifying the index of the return parameter. You also must specify the type of the return parameter. You can also specify the maximum bytes or characters of the return parameter. This method can be used only withchar orRAW types. You can also specify the fully qualified name of a SQL structure type.

ThegetReturnResultSet method fetches the data returned from DML returning and returns it as aResultSet object. The method throws aSQLException exception if an error occurs.

RunningDML Returning Statements

Before running a DML returning statement, the JDBC application must call one or more of theregisterReturnParameter 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 orexecute. You can then fetch the returned parameters as aResultSet object using thegetReturnResultSet method of theoracle.jdbc.OraclePreparedStatement interface.

In order to read the values in theResultSet object, the underlyingStatement object must be open. When the underlyingStatement object is closed, the returnedResultSet object is also closed. This is consistent withResultSet objects that are retrieved by processing SQL query statements.

When a DML returning statement is run, the concurrency of theResultSet object returned by thegetReturnResultSet method must beCONCUR_READ_ONLY and the type of theResultSet object must beTYPE_FORWARD_ONLY orTYPE_SCROLL_INSENSITIVE.

Example ofDML 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 thename column is 100 characters. Because the maximum size of thename column is known, theregisterReturnParameter(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 statementcount = 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, theregisterReturnParameter(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 parameterspstmt.registerReturnParameter(1, OracleTypes.INTEGER);pstmt.registerReturnParameter(2, OracleTypes.CLOB);// process the DML returning SQL statementpstmt.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 ofDML Returning

When using DML returning, be aware of the following:

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

• TheResultSet objects returned from the execution of DML returning statements do not support theResultSetMetaData type. Therefore, the applications must 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;...

Note:

Overview

Oracle JDBC drivers supportPL/SQL index-by tables of scalar data types.Table 4-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.

TheOraclePreparedStatement andOracleCallableStatement classes define the additional methods. These methods include the following:

• setPlsqlIndexTable

• registerIndexTableOutParameter

• getOraclePlsqlIndexTable

• getPlsqlIndexTable

These methods handle PL/SQL index-by tables asIN,OUT, orIN OUT parameters, including function return values.

Binding IN Parameters

To bind a PL/SQL index-by table parameter in theIN parameter mode, use thesetPlsqlIndexTable method defined in theOraclePreparedStatement andOracleCallableStatement classes.

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

Table 4-4 describes the arguments of thesetPlsqlIndexTable method.

The following code example uses thesetPlsqlIndexTable method to bind an index-by table as anIN parameter:

// Prepare the statementOracleCallableStatement 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 anOUT parameter. In addition, it describes how to access theOUT bind values in various mapping styles.

Registering the OUT Parameters

To register a PL/SQL index-by table as anOUT parameter, use theregisterIndexTableOutParameter method defined in theOracleCallableStatement class.

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

Table 4-5 describes the arguments of theregisterIndexTableOutParameter method.

The following code example uses theregisterIndexTableOutParameter method to register an index-by table as anOUT 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 typeint 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// typesint elemMaxLen = 0;// register the return valuefuncnone.registerIndexTableOutParameter   (1, maxLen, elemSqlType, elemMaxLen);

Accessing the OUT Parameter Values

To access theOUT bind value, theOracleCallableStatement 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

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

public Object getPlsqlIndexTable (int paramIndex)   throws SQLException

Table 4-6 describes the argument of thegetPlsqlIndexTable 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 ofNUMERIC type code, the element values are mapped toBigDecimal by Oracle JDBC driver, and thegetPlsqlIndexTable method returns aBigDecimal[] array. For a JDBC application, you must cast the return value toBigDecimal[] to access the table element values.

The following code example uses thegetPlsqlIndexTable 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

ThegetOraclePlsqlIndexTable method returns index-by table elements using Oracle mapping.

public Datum[] getOraclePlsqlIndexTable (int paramIndex)      throws SQLException

Table 4-7 describes the argument of thegetOraclePlsqlIndexTable method.

The return value is anoracle.sql.Datum array, and the elements in the array are of the defaultDatum 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 theoracle.sql.NUMBER type in Oracle mapping, and thegetOraclePlsqlIndexTable method returns anoracle.sql.Datum array that containsoracle.sql.NUMBER elements.

The following code example uses thegetOraclePlsqlIndexTable method to access the elements of a PL/SQL index-by tableOUT parameter, using Oracle mapping:

// Prepare the statement OracleCallableStatement procout = (OracleCallableStatement)                                  conn.prepareCall ("begin procout (?); end;");...// run the callprocout.execute (); // access the value using Oracle JDBC mappingDatum[] outvalues = procout.getOraclePlsqlIndexTable (1);// print the elementsfor (int i=0; i<outvalues.length; i++)   System.out.println (outvalues[i].intValue());

Java Primitive Type Mappings

ThegetPlsqlIndexTable(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 the following:

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

Table 4-8 describes the arguments of thegetPlsqlIndexTable method.

The following code example uses thegetPlsqlIndexTable method to access the elements of a PL/SQL index-by table of numbers. In the example, the second parameter specifiesjava.lang.Integer.TYPE and the return value of thegetPlsqlIndexTable method is anint 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]);