6 Using API Extensions in JDBC Drivers

This chapter describes how to set up and use third-party JDBC drivers in WebLogic Server 10.3.6, including using API extensions, Oracle Virtual Private Databases, and batch processing with Oracle Thin Drivers.

Note:

The weblogic.jdbc.vendor.oracle.* interfaces documented in the following sections are deprecated and may be removed in a future WebLogic Server release.

This chapter includes the following sections:

Using API Extensions to JDBC Interfaces

Some database vendors, including Oracle, provide additional proprietary methods for working with data from a database that uses their DBMS. These methods extend the standard JDBC interfaces. WebLogic Server supports all extension methods exposed as a public interface in the vendor's JDBC driver.

To use the extension methods exposed in the JDBC driver, you must include these steps in your application code:

  • Import the driver interfaces from the JDBC driver used to create connections in the data source.

  • Get a connection from the data source.

  • Cast the connection object as the vendor's connection interface.

  • Use the API extensions as described in the vendor's documentation.

  • The JNDI lookup is wrapped in a try/catch block in order to catch a failed look up and also that the context is closed in a finally block.

The following sections provide details in code examples. For information about specific extension methods for a particular JDBC driver, refer to the documentation from the JDBC driver vendor.

Sample Code for Accessing API Extensions to JDBC Interfaces

The following code examples use extension methods available in the Oracle Thin driver to illustrate how to use API extensions to JDBC. You can adapt these examples to fit methods exposed in your JDBC driver.

Import Packages to Access API Extensions

Import the interfaces from the JDBC driver used to create the connection in the data source. This example uses interfaces from the Oracle Thin Driver.

import java.sql.*;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import oracle.jdbc.*;
// Import driver interfaces. The driver must be the same driver
// used to create the database connection in the data source.

Get a Connection

Establish the database connection using JNDI, DataSource and data source objects.

// Get a valid DataSource object for a data source.
// Here we assume that getDataSource() takes 
// care of those details.
javax.sql.DataSource ds = getDataSource(args);
// get a java.sql.Connection object from the DataSource
java.sql.Connection conn = ds.getConnection();

Cast the Connection as a Vendor Connection

Now that you have the connection, you can cast it as a vendor connection. This example uses the OracleConnection interface from the Oracle Thin Driver.

orConn = (oracle.jdbc.OracleConnection)conn;
// This replaces the deprecated process of casting the connection
// to a weblogic.jdbc.vendor.oracle.OracleConnection. For example:
// orConn = (weblogic.jdbc.vendor.oracle.OracleConnection)conn;

Use API Extensions

The following code fragment shows how to use the Oracle Row Prefetch method available from the Oracle Thin driver.

Example 6-1 Using an API Extension

// Cast to OracleConnection and retrieve the 
// default row prefetch value for this connection.
int default_prefetch = 
   ((oracle.jdbc.OracleConnection)conn).getDefaultRowPrefetch();
// This replaces the deprecated process of casting the connection
// to a weblogic.jdbc.vendor.oracle.OracleConnection. For example:
// ((weblogic.jdbc.vendor.oracle.OracleConnection)conn).
//       getDefaultRowPrefetch();
System.out.println("Default row prefetch 
    is " + default_prefetch);
java.sql.Statement stmt = conn.createStatement();
// Cast to OracleStatement and set the row prefetch
// value for this statement. Note that this
// prefetch value applies to the connection between
// WebLogic Server and the database.
      ((oracle.jdbc.OracleStatement)stmt).setRowPrefetch(20);
   // This replaces the deprecated process of casting the
   // statement to a weblogic.jdbc.vendor.oracle.OracleStatement.
   // For example:
   // ((weblogic.jdbc.vendor.oracle.OracleStatement)stmt).
   //       setRowPrefetch(20);
      // Perform a normal sql query and process the results...
      String query = "select empno,ename from emp";
      java.sql.ResultSet rs = stmt.executeQuery(query);
      while(rs.next()) {
         java.math.BigDecimal empno = rs.getBigDecimal(1);
         String ename = rs.getString(2);
         System.out.println(empno + "\t" + ename);
      }
      rs.close();
      stmt.close();
      conn.close();
      conn = null;
    }

Using API Extensions for Oracle JDBC Types

When Oracle implemented JDBC, concrete classes were used instead of using interfaces for Oracle JDBC Types. There are many of drawbacks in using concrete classes and in the 11.2.0.3 driver there are new interfaces corresponding to the Oracle types. The concrete classes now implement a public interface from the package oracle.jdbc. Programmers should use methods exposed in java.sql whenever possible and for Oracle extension methods use oracle.jdbc.

In the mean time, WebLogic Server implemented corresponding interfaces that could be used to work around the limitations of the concrete classes. These are now deprecated and should be replaced with the corresponding oracle.jdbc interfaces.

In Database version 11.2.0.3 the following types have interfaces.

Old Oracle types Deprecated WLS Interface New interfaces
oracle.sql.ARRAY weblogic.jdbc.vendor.oracle.OracleArray oracle.jdbc.OracleArray
oracle.sql.STRUCT weblogic.jdbc.vendor.oracle.OracleStruct oracle.jdbc.OracleStruct
oracle.sql.CLOB weblogic.jdbc.vendor.oracle.OracleThinClob oracle.jdbc.OracleClob
oracle.sql.BLOB weblogic.jdbc.vendor.oracle.OracleThinBlob oracle.jdbc.OracleBlob
oracle.sql.REF weblogic.jdbc.vendor.oracle.OracleRef oracle.jdbc.OracleRef

Changing the code to use new interfaces is not difficult, but should be handled with care. The below examples use oracle.sql.ARRAY and similar changes apply to other types as well. A list of suggested changes is mentioned below:

  • Import: Modify import statements to use the new interfaces (oracle.jdbc) instead of old interfaces (oracle.sql or weblogic.jdbc.vendor.oracle).

  • Declaration: Use standard Java interfaces for declaration whenever possible. If there is a need to use Oracle extension use the new Oracle interfaces under oracle.jdbc.

  • Methods: Use standard Java interfaces whenever possible:

    • (Oracle Types): Use methods in standard Java interfaces whenever possible. If required use methods from Oracle interfaces under oracle.jdbc.

    • (Defines): Refrain from using Oracle specific methods such as getARRAY; instead use standard Java methods such as getArray or getObject for those that does not have standard Java interfaces.

    • (Binds): Refrain from using Oracle specific methods such as setARRAY; instead use standard Java methods such as setArray or setObject for the ones that does not have standard Java interfaces.

Replacing import statements can be done by a script that uses find and sed. For example:

find . -name "*.java" -exec egrep ... > files.list
 
for f in `cat files.list`; do
 
  cat $f |sed 's@^import oracle\.sql\.ARRAY@oracle\.jdbc.OracleArray@g' > /tmp/temp.txt
 
  mv /tmp/temp.txt $f
 
done

Programmers should use factory methods on oracle.jdbc.OracleConnection to create an instance of the types. For example:

int[] intArray = { 5, 7, 9};
 
oracle.sql.ArrayDescriptor aDescriptor = new oracle.sql.ArrayDescriptor("SCOTT.TYPE1", connection);
 
oracle.sql.ARRAY array = new oracle.sql.ARRAY(aDescriptor, connection, intArray);

should be changed to:

int[] intArray = { 5, 7, 9};
java.sql.Array array = connection.createOracleArray("SCOTT.TYPE1", intArray);

Note:

Oracle does not support anonymous array types and so does not support the standard Connection.createArrayOf method.

There are some methods that are no longer available because:

  • There is a way to accomplish the same end using standard or already public methods.

  • The method refers to a deprecated type.

  • The method does not add significant value.

In these cases, the code needs to be modified to use standard API's.

Using Deprecated API Extensions with the Oracle Thin Driver

For most extensions in the Oracle Thin driver, you can use the standard technique as described in Using API Extensions to JDBC Interfaces. However, prior to version 11.2.0.3, the Oracle Thin driver does not provide public interfaces for its extension methods in the following classes:

  • oracle.sql.ARRAY

  • oracle.sql.STRUCT

  • oracle.sql.REF

  • oracle.sql.BLOB

  • oracle.sql.CLOB

WebLogic Server provides its own interfaces to access the extension methods for those classes:

  • weblogic.jdbc.vendor.oracle.OracleArray

  • weblogic.jdbc.vendor.oracle.OracleStruct

  • weblogic.jdbc.vendor.oracle.OracleRef

  • weblogic.jdbc.vendor.oracle.OracleThinBlob

  • weblogic.jdbc.vendor.oracle.OracleThinClob

The following sections provide code samples for using the WebLogic Server interfaces for these extensions. For a list of supported methods, see Tables of Oracle Thin Driver Extension Interfaces and Supported Methods. For more information, please refer to the Oracle documentation.

Note:

You can use this process to use any of the WebLogic Server interfaces for the extensions listed in the Tables of Oracle Thin Driver Extension Interfaces and Supported Methods. However, all the interfaces listed above are deprecated and will be removed in a future release of WebLogic Server.

Special Considerations When Using Oracle Thin Driver Extensions

Please note the following limitations when using Oracle Thin driver extensions to JDBC interfaces:

  • You can use Oracle extensions for ARRAYs, REFs, and STRUCTs in server-side applications that use the same JVM as the server only. You cannot use Oracle extensions for ARRAYs, REFs, and STRUCTs in remote client applications.

  • You can create ARRAYs and STRUCTs in your applications by using a non-standard Oracle descriptor object.

  • You can execute queries against a table that contains REF to Oracle Object types and the REF is materialized as a Java oracle.sql.REF object by JDBC. JDBC does not support creating new REF from scratch. Go to the database and insert the new REF in SQL, select the REF, and return it to the client.

  • There are interoperability limitations when using different versions of WebLogic Server clients and servers. See Support for API Extensions Between Versions of WebLogic Server Clients and Servers.

    Note:

    In the JDBC 4.0 API, the following java.sql.Connection can be used to create STRUCT and ARRAY objects in applications without a descriptor.
    • java.sql.Connection.createStruct(): When creating a SQL structure using Connection.createStruct(), it is necessary to unwrap all data types (java.sql.Clob, java.sql.Blob, java.sql.Struct, java.sql.Ref, java.sql.Array, java.sql.NClob, and java.sql.SQLXML). Once the structure is created, there is no way to re-wrap them before returning the structure to the application. The structure returned to the application has unwrapped values for the data types.

    • java.sql.Connection.createArrayOf(): The SQL specification requires anonymous array types. Create an array type in the database with a name before using the array. You cannot create an array from the client side.

Sample Code for Accessing Oracle Thin Driver Extensions to JDBC Interfaces

The following code examples show how to access the WebLogic Server interfaces for Oracle extensions that are not available as public interfaces prior to 11.2.0.3., including interfaces for:

If you selected the option to install server examples with WebLogic Server, see the JDBC examples for more code examples, typically at WL_HOME\samples\server\src\examples\jdbc, where WL_HOME is the folder where you installed WebLogic Server.

Programming with ARRAYs

In your WebLogic Server server-side applications, you can materialize an Oracle Collection (a SQL ARRAY) in a result set or from a callable statement as a Java array.

To use ARRAYs in WebLogic Server applications:

  1. Import the required classes.

  2. Get a connection and then create a statement for the connection.

  3. Get the ARRAY using a result set or a callable statement.

  4. Use the ARRAY as either a java.sql.Array or a weblogic.jdbc.vendor.oracle.OracleArray.

  5. Use the standard Java methods (when used as a java.sql.Array) or Oracle extension methods (when cast as a weblogic.jdbc.vendor.oracle.OracleArray) to work with the data.

The following sections provide more details for these actions.

Note:

You can use ARRAYs in server-side applications only. You cannot use ARRAYs in remote client applications.

Import Packages to Access WebLogic Oracle Extensions

Import the WebLogic interfaces used in this example. The OracleArray interface is counterpart to oracle.sql.ARRAY and can be used in the same way as the WebLogic interface when using the methods supported by WebLogic Server.

import java.sql.*;
import java.util.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import weblogic.jdbc.vendor.oracle.*;

Establish the Connection

Establish the database connection using JNDI and DataSource objects.

// Get a valid DataSource object.
// Here we assume that getDataSource() takes 
// care of those details.
javax.sql.DataSource ds = getDataSource(args);
// get a java.sql.Connection object from the DataSource
java.sql.Connection conn = ds.getConnection();

Getting an ARRAY

You can use the getArray() methods for a callable statement or a result set to get a Java array. You can then use the array as a java.sql.array to use standard java.sql.array methods, or you can cast the array as a weblogic.jdbc.vendor.oracle.OracleArray to use the Oracle extension methods for an array.

The following example shows how to get a java.sql.array from a result set that contains an ARRAY. In the example, the query returns a result set that contains an object column—an ARRAY of test scores for a student.

try {
  conn = getConnection(url);
  stmt = conn.createStatement();
  String sql = "select * from students";
//Get the result set
  rs = stmt.executeQuery(sql);
  while(rs.next()) {
    BigDecimal id = rs.getBigDecimal("student_id");
    String name   = rs.getString("name");
    log("ArraysDAO.getStudents() -- Id = "+id.toString()+", Student = "+name);
//Get the array from the result set
    Array scoreArray  = rs.getArray("test_scores");
    String[] scores = (String[])scoreArray.getArray();
    for (int i = 0; i < scores.length; i++) {
      log("   Test"+(i+1)+" = "+scores[i]);
    }
  }

Updating ARRAYs in the Database

To update an ARRAY in a database, you can Follow these steps:

  1. Create an array in the database using PL/SQL, if the array you want to update does not already exist in the database.

  2. Get the ARRAY using a result set or a callable statement.

  3. Work with the array in your Java application as either a java.sql.Array or a weblogic.jdbc.vendor.oracle.OracleArray.

  4. Update the array in the database using the setArray() method for a prepared statement or a callable statement. For example:

    String sqlUpdate = "UPDATE SCOTT." + tableName + " SET col1 = ?";
    conn  = ds.getConnection();
    pstmt = conn.prepareStatement(sqlUpdate);
    pstmt.setArray(1, array);
    pstmt.executeUpdate();
    

Using Oracle Array Extension Methods

To use the Oracle Thin driver extension methods for an ARRAY, you must first cast the array as a weblogic.jdbc.vendor.oracle.OracleArray. You can then make calls to the Oracle Thin driver extension methods for ARRAYs. For example:

   oracle.sql.Datum[] oracleArray = null;
   oracleArray =       ((weblogic.jdbc.vendor.oracle.OracleArray)scoreArray).getOracleArray();
   String sqltype = null
   sqltype = oracleArray.getSQLTypeName()

Programming with STRUCTs

In your WebLogic Server applications, you can access and manipulate objects from an Oracle database. When you retrieve objects from an Oracle database, you can cast them as either custom Java objects or as STRUCTs (java.sql.struct or weblogic.jdbc.vendor.oracle.OracleStruct). A STRUCT is a loosely typed data type for structured data which takes the place of custom classes in your applications. The STRUCT interface in the JDBC API includes several methods for manipulating the attribute values in a STRUCT. Oracle extends the STRUCT interface with several additional methods. WebLogic Server implements all of the standard methods and most of the Oracle extensions.

Note:

Please note the following limitations when using STRUCTs:
  • STRUCTs are supported for use with Oracle only. To use STRUCTs in your applications, you must use the Oracle Thin Driver to communicate with the database, typically through a data source.

  • You can use STRUCTs in server-side applications only. You cannot use STRUCTs in client applications.

To use STRUCTs in WebLogic Server applications:

  1. Import the required classes. (See Import Packages to Access WebLogic Oracle Extensions.)

  2. Get a connection. (See Establish the Connection.)

  3. Use getObject to get the STRUCT.

  4. Cast the STRUCT as a STRUCT, either java.sql.Struct (to use standard methods) or weblogic.jdbc.vendor.oracle.OracleStruct (to use standard and Oracle extension methods).

  5. Use the standard or Oracle Thin driver extension methods to work with the data.

The following sections provide more details for steps 3 through 5.

Getting a STRUCT

To get a database object as a STRUCT, you can use a query to create a result set and then use the getObject method to get the STRUCT from the result set. You then cast the STRUCT as a java.sql.Struct so you can use the standard Java methods. For example:

conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from people");
struct = (java.sql.Struct)(rs.getObject(2)); 
Object[] attrs = ((java.sql.Struct)struct).getAttributes();

WebLogic Server supports all of the JDBC API methods for STRUCTs:

  • getAttributes()

  • getAttributes(java.util.Dictionary map)

  • getSQLTypeName()

When you cast a STRUCT as a weblogic.jdbc.vendor.oracle.OracleStruct, you can use both the standard and extension methods.

Using WebLogic OracleStruct Extension Methods

To use the Oracle Thin driver extension methods for a STRUCT, you must cast the java.sql.Struct (or the original getObject result) as a weblogic.jdbc.vendor.oracle.OracleStruct. For example:

java.sql.Struct struct =
   (weblogic.jdbc.vendor.oracle.OracleStruct)(rs.getObject(2));

WebLogic Server supports the following Oracle extensions:

  • getDescriptor()

  • getOracleAttributes()

  • getAutoBuffering()

  • setAutoBuffering(boolean)

Getting STRUCT Attributes

To get the value for an individual attribute in a STRUCT, you can use the standard JDBC API methods getAttributes() and getAttributes(java.util.Dictionary map), or you can use the Oracle extension method getOracleAttributes().

To use the standard method, you can create a result set, get a STRUCT from the result set, and then use the getAttributes() method. The method returns an array of ordered attributes. You can assign the attributes from the STRUCT (object in the database) to an object in the application, including Java language types. You can then manipulate the attributes individually. For example:

conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from people");
//The third column uses an object data type.
//Use getObject() to assign the object to an array of values.
struct = (java.sql.Struct)(rs.getObject(2));
Object[] attrs = ((java.sql.Struct)struct).getAttributes();
String address = attrs[1]; 

In the preceding example, the third column in the people table uses an object data type. The example shows how to assign the results from the getObject method to a Java object that contains an array of values, and then use individual values in the array as necessary.

You can also use the getAttributes(java.util.Dictionary map) method to get the attributes from a STRUCT. When you use this method, you must provide a hash table to map the data types in the Oracle object to Java language data types. For example:

java.util.Hashtable map = new java.util.Hashtable();
map.put("NUMBER", Class.forName("java.lang.Integer"));
map.put("VARCHAR", Class.forName("java.lang.String"));
Object[] attrs = ((java.sql.Struct)struct).getAttributes(map);
String address = attrs[1];

You can also use the Oracle extension method getOracleAttributes() to get the attributes for a STRUCT. You must first cast the STRUCT as a weblogic.jdbc.vendor.oracle.OracleStruct. This method returns a datum array of oracle.sql.Datum objects. For example:

oracle.sql.Datum[] attrs =
   ((weblogic.jdbc.vendor.oracle.OracleStruct)struct).getOracleAttributes();
        oracle.sql.STRUCT address = (oracle.sql.STRUCT) attrs[1];
        Object address_attrs[] = address.getAttributes();

The preceding example includes a nested STRUCT. That is, the second attribute in the datum array returned is another STRUCT.

Using STRUCTs to Update Objects in the Database

To update an object in the database using a STRUCT, you can use the setObject method in a prepared statement. For example:

conn  = ds.getConnection();
stmt = conn.createStatement();
ps = conn.prepareStatement ("UPDATE SCHEMA.people SET EMPLNAME = ?,
EMPID = ? where EMPID = 101");
ps.setString (1, "Smith");
ps.setObject (2, struct);
ps.executeUpdate();

WebLogic Server supports all three versions of the setObject method.

Creating Objects in the Database

STRUCTs are typically used to materialize database objects in your Java application in place of custom Java classes that map to the database objects. In WebLogic Server applications, you cannot create STRUCTs that transfer to the database. However, you can use statements to create objects in the database that you can then retrieve and manipulate in your application. For example:

conn = ds.getConnection();
stmt = conn.createStatement();
cmd = "create type ob as object (ob1 int, ob2 int)"
stmt.execute(cmd);
cmd = "create table t1 of type ob";
stmt.execute(cmd);
cmd = "insert into t1 values (5, 5)"
stmt.execute(cmd);

Note:

You cannot create STRUCTs in your applications. You can only retrieve existing objects from a database and cast them as STRUCTs. To create STRUCT objects in your applications, you must use a non-standard Oracle STRUCT descriptor object, which is not supported in WebLogic Server.

Automatic Buffering for STRUCT Attributes

To enhance the performance of your WebLogic Server applications that use STRUCTs, you can toggle automatic buffering with the setAutoBuffering(boolean) method. When automatic buffering is set to true, the weblogic.jdbc.vendor.oracle.OracleStruct object keeps a local copy of all the attributes in the STRUCT in their converted form (materialized from SQL to Java language objects). When your application accesses the STRUCT again, the system does not have to convert the data again.

Note:

Buffering the converted attributes my cause your application to use an excessive amount of memory. Consider potential memory usage when deciding to enable or disable automatic buffering.

The following example shows how to activate automatic buffering:

 ((weblogic.jdbc.vendor.oracle.OracleStruct)struct).setAutoBuffering(true);

You can also use the getAutoBuffering() method to determine the automatic buffering mode.

Programming with REFs

A REF is a logical pointer to a row object. When you retrieve a REF, you are actually getting a pointer to a value in another table. The REF target must be a row in an object table. You can use a REF to examine or update the object it refers to. You can also change a REF so that it points to a different object of the same object type or assign it a null value.

Note:

Please note the following limitations when using REFs:
  • REFs are supported for use with Oracle databases only. To use REFs in your applications, you must use the Oracle Thin Driver to communicate with the database, typically through a data source.

  • You can use REFs in server-side applications only.

To use REFs in WebLogic Server applications, follow these steps:

  1. Import the required classes. (See Import Packages to Access WebLogic Oracle Extensions.)

  2. Get a database connection. (See Establish the Connection.)

  3. Get the REF using a result set or a callable statement.

  4. Cast the result as a STRUCT or as a Java object. You can then manipulate data using STRUCT methods or methods for the Java object.

You can also create and update a REF in the database.

The following sections describe these steps 3 and 4 in greater detail.

Getting a REF

To get a REF in an application, you can use a query to create a result set and then use the getRef method to get the REF from the result set. You then cast the REF as a java.sql.Ref so you can use the built-in Java method. For example:

conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");
rs.next();
//Cast as a java.sql.Ref and get REF
ref = (java.sql.Ref) rs.getRef(1);

Note that the WHERE clause in the preceding example uses dot notation to specify the attribute in the referenced object.

After you cast the REF as a java.sql.Ref, you can use the Java API method getBaseTypeName, the only JDBC 2.0 standard method for REFs.

When you get a REF, you actually get a pointer to a value in an object table. To get or manipulate REF values, you must use the Oracle extensions, which are only available when you cast the sql.java.Ref as a weblogic.jdbc.vendor.oracle.OracleRef.

Using WebLogic OracleRef Extension Methods

In order to use the Oracle Thin driver extension methods for REFs, you must cast the REF as an Oracle REF. For example:

   oracle.sql.StructDescriptor desc =       ((weblogic.jdbc.vendor.oracle.OracleRef)ref).getDescriptor();

WebLogic Server supports the following Oracle Thin driver extensions:

  • getDescriptor()

  • getSTRUCT()

  • getValue()

  • getValue(dictionary)

  • setValue(object)

Getting a Value

In the Oracle Thin driver, there are two versions of the getValue() method—one that takes no parameters and one that requires a hash table for mapping return types. When you use either version of the getValue() method to get the value of an attribute in a REF, the method returns a either a STRUCT or a Java object.

The example below shows how to use the getValue() method without parameters. In this example, the REF is cast as an oracle.sql.STRUCT. You can then use the STRUCT methods to manipulate the value, as illustrated with the getAttributes() method.

oracle.sql.STRUCT student1 = 
   (oracle.sql.STRUCT)((weblogic.jdbc.vendor.oracle.OracleRef)ref).getValue ();
Object attributes[] = student1.getAttributes();

You can also use the getValue(dictionary) method to get the value for a REF. You must provide a hash table to map data types in each attribute of the REF to Java language data types. For example:

java.util.Hashtable map = new java.util.Hashtable();
map.put("VARCHAR", Class.forName("java.lang.String"));
map.put("NUMBER", Class.forName("java.lang.Integer"));
oracle.sql.STRUCT result = (oracle.sql.STRUCT)
   ((weblogic.jdbc.vendor.oracle.OracleRef)ref).getValue (map);

Updating REF Values

When you update a REF, you can do any of the following:

  • Change the value in the underlying table with the setValue(object) method.

  • Change the location to which the REF points with a prepared statement or a callable statement.

  • Set the value of the REF to null.

To use the setValue(object) method to update a REF value, you create an object with the new values for the REF, and then pass the object as a parameter of the setValue method. For example:

STUDENT s1 = new STUDENT();
s1.setName("Terry Green");
s1.setAge(20);
((weblogic.jdbc.vendor.oracle.OracleRef)ref).setValue(s1);

When you update the value for a REF with the setValue(object) method, you actually update the value in the table to which the REF points.

To update the location to which a REF points using a prepared statement, you can follow these basic steps:

  1. Get a REF that points to the new location. You use this REF to replace the value of another REF.

  2. Create a string for the SQL command to replace the location of an existing REF with the value of the new REF.

  3. Create and execute a prepared statement.

For example:

try {
conn = ds.getConnection();
stmt = conn.createStatement();
//Get the REF.
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");
rs.next();
ref = (java.sql.Ref) rs.getRef(1); //cast the REF as a java.sql.Ref
}
//Create and execute the prepared statement.
String sqlUpdate = "update t3 s2 set col = ? where s2.col.ob1=20";
pstmt = conn.prepareStatement(sqlUpdate);
pstmt.setRef(1, ref);
pstmt.executeUpdate();

To use a callable statement to update the location to which a REF points, you prepare the stored procedure, set any IN parameters and register any OUT parameters, and then execute the statement. The stored procedure updates the REF value, which is actually a location. For example:

conn = ds.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("SELECT ref (s) FROM t1 s where s.ob1=5");
rs.next();
ref1 = (java.sql.Ref) rs.getRef(1);
// Prepare the stored procedure
sql = "{call SP1 (?, ?)}";
cstmt = conn.prepareCall(sql);
// Set IN and register OUT params
cstmt.setRef(1, ref1);
cstmt.registerOutParameter(2, getRefType(), "USER.OB");
// Execute
cstmt.execute();

Creating a REF in the Database

You cannot create REF objects in your JDBC application—you can only retrieve existing REF objects from the database. However, you can create a REF in the database using statements or prepared statements. For example:

conn = ds.getConnection();
stmt = conn.createStatement();
cmd = "create type ob as object (ob1 int, ob2 int)"
stmt.execute(cmd);
cmd = "create table t1 of type ob";
stmt.execute(cmd);
cmd = "insert into t1 values (5, 5)"
stmt.execute(cmd);
cmd = "create table t2 (col ref ob)";
stmt.execute(cmd);
cmd = "insert into t2 select ref(p) from t1 where p.ob1=5";
stmt.execute(cmd);

The preceding example creates an object type (ob), a table (t1) of that object type, a table (t2) with a REF column that can point to instances of ob objects, and inserts a REF into the REF column. The REF points to a row in t1 where the value in the first column is 5.

Programming with Large Objects

This section contains sample code that demonstrates how to work with Large Objects (LOBs), such as the OracleBlob interface. You can use the syntax of this example for the OracleBlob interface, when using methods supported by WebLogic Server. See Tables of Oracle Thin Driver Extension Interfaces and Supported Methods. For additional information, refer to "Working with LOBs" in Database SecureFiles and Large Objects Developer's Guide.

Query to Select BLOB Locator from the DBMS

The BLOB Locator, or handle, is a reference to an Oracle Thin Driver BLOB:

String selectBlob = "select blobCol from myTable where blobKey = 666"

Declare the WebLogic Server java.sql Objects

The following code presumes the Connection is already established:

ResultSet rs = null; 
Statement myStatement = null; 
java.sql.Blob myRegularBlob = null; 
java.io.OutputStream os = null;

Begin SQL Exception Block

In this try catch block, you get the BLOB locator and access the Oracle BLOB extension.

try { 
     // get our BLOB locator.. 
     myStatement = myConnect.createStatement(); 
     rs = myStatement.executeQuery(selectBlob);
     while (rs.next()) { 
       myRegularBlob = rs.getBlob("blobCol");
}
     // Access the underlying Oracle extension functionality for
     // writing. Cast to the OracleThinBlob interface to access
     // the Oracle method.
     os = ((OracleThinBlob)myRegularBlob).getBinaryOutputStream();
     ...
     } catch (SQLException sqe) {
       System.out.println("ERROR(general SQE): " +
         sqe.getMessage()); 
     }

Once you cast to the Oracle.ThinBlob interface, you can access the Oracle supported methods.

Updating a CLOB Value Using a Prepared Statement

If you use a prepared statement to update a CLOB and the new value is shorter than the previous value, the CLOB will retain the characters that were not specifically replaced during the update. For example, if the current value of a CLOB is abcdefghij and you update the CLOB using a prepared statement with zxyw, the value in the CLOB is updated to zxywefghij. To correct values updated with a prepared statement, you should use the dbms_lob.trim procedure to remove the excess characters left after the update. See the Oracle documentation for more information about the dbms_lob.trim procedure.

Transaction Boundaries Using LOBs

When using LOBs, you must take transaction boundaries into account; for example, direct all read/writes to a particular LOB within a transaction.

Recovering LOB Space

To free up space used by a LOB, it's necessary to call lob.close(). This is not automatically done when a ResultSet, Statement, or Connection is closed. For Oracle data bases only, it is also necessary to execute alter session set events '60025 trace name context forever'; on the session so that other sessions can use the freed memory.

Support for API Extensions Between Versions of WebLogic Server Clients and Servers

In general, WebLogic Server supports using API extensions in server-side code. To use API extensions in client-side code, the object type or data type must be serializable. Exceptions to this are the following object types:

  • CLOB

  • BLOB

  • InputStream

  • OutputStream

WebLogic Server handles de-serialization for these object types so they can be used in client-side code.

Because the way WebLogic Server supports vendor JDBC extensions was changed in WebLogic Server 8.1, interoperability between versions of client and servers is affected.When a WebLogic Server 8.1 or later client interacts with a WebLogic Server 7.0 or earlier server, Oracle extensions are not supported. When the client application tries to cast the JDBC objects to the Oracle extension interfaces, it will get a ClassCastException. However, when a WebLogic Server 7.0 or earlier client interacts with a WebLogic Server 8.1 or later server, Oracle extensions are supported.

Note:

weblogic.jdbc.vendor.oracle.* interfaces are deprecated and may be removed in a future WebLogic Server release. Standard JDBC interfaces are supported regardless of the client or server version.

Tables of Oracle Thin Driver Extension Interfaces and Supported Methods

In previous releases of WebLogic Server, only the JDBC extensions listed in the following tables were supported. The current release of WebLogic Server supports most extension methods exposed as a public interface in the vendor's JDBC driver. See Using API Extensions to JDBC Interfaces for instructions for using API extensions. Because the new internal mechanism for supporting API extensions does not rely on the previous implementation, several interfaces are no longer needed and are deprecated. These interfaces will be removed in a future release of WebLogic Server. See Table 6-1. Oracle encourages you to use the alternative interface listed in the table.

Table 6-1 Deprecated Interfaces for Oracle JDBC Extensions

Deprecated Interface Instead, use this interface from Oracle
weblogic.jdbc.vendor.oracle.
OracleConnection
oracle.jdbc.OracleConnection
weblogic.jdbc.vendor.oracle.
OracleStatement  
oracle.jdbc.OracleStatement
weblogic.jdbc.vendor.oracle.
OracleCallableStatement 
oracle.jdbc.OracleCallableStatement
weblogic.jdbc.vendor.oracle.
OraclePreparedStatement 
oracle.jdbc.OraclePreparedStatement
weblogic.jdbc.vendor.oracle.
OracleResultSet 
oracle.jdbc.OracleResultSet

weblogic.jdbc.vendor.oracle.OracleArray

oracle.jdbc.OracleArray

weblogic.jdbc.vendor.oracle.OracleStruct

oracle.jdbc.OracleStruct

weblogic.jdbc.vendor.oracle.OracleRef

oracle.jdbc.OracleRef

weblogic.jdbc.vendor.oracle.OracleThinClob

oracle.jdbc.OracleBlob

weblogic.jdbc.vendor.oracle.OracleThinBlob

oracle.jdbc.OracleClob


The following tables describe the Oracle interfaces and supported methods you use with the Oracle Thin Driver (or another driver that supports these methods) to extend the standard JDBC (java.sql.*) interfaces.

Table 6-2 OracleConnection Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleConnection

extends

java.sql.Connection

(This interface is deprecated.)

void clearClientIdentifier(String s) 
   throws java.sql.SQLException;

boolean getAutoClose() 
   throws java.sql.SQLException;

String getDatabaseProductVersion()
   throws java.sql.SQLException;

String getProtocolType() throws
   java.sql.SQLException;

String getURL() throws java.sql.SQLException;

String getUserName()
   throws java.sql.SQLException;

boolean getBigEndian() 
   throws java.sql.SQLException;

boolean getDefaultAutoRefetch() throws
   java.sql.SQLException;

boolean getIncludeSynonyms()
   throws java.sql.SQLException;

boolean getRemarksReporting()
   throws java.sql.SQLException;

boolean getReportRemarks() 
   throws java.sql.SQLException;

weblogic.jdbc.vendor.oracle.OracleConnection

extends

java.sql.Connection

(continued)

(This interface is deprecated.)

boolean getRestrictGetTables()
   throws java.sql.SQLException;

boolean getUsingXAFlag()
   throws java.sql.SQLException;

boolean getXAErrorFlag() 
   throws java.sql.SQLException;

boolean isCompatibleTo816()
   throws java.sql.SQLException;
   (Deprecated)

byte[] getFDO(boolean b) 
   throws java.sql.SQLException;

int getDefaultExecuteBatch() throws 
   java.sql.SQLException;

int getDefaultRowPrefetch() 
   throws java.sql.SQLException;

int getStmtCacheSize() 
   throws java.sql.SQLException;

java.util.Properties getDBAccessProperties()
   throws java.sql.SQLException;

short getDbCsId() throws java.sql.SQLException;
short getJdbcCsId() throws java.sql.SQLException;
short getStructAttrCsId() 
   throws java.sql.SQLException;
short getVersionNumber() 
   throws java.sql.SQLException;

void archive(int i, int j, String s) 
   throws java.sql.SQLException;

weblogic.jdbc.vendor.oracle.OracleConnection

extends

java.sql.Connection

(continued)

(This interface is deprecated.)

void close_statements() 
   throws java.sql.SQLException;

void initUserName() throws java.sql.SQLException;

void logicalClose() throws java.sql.SQLException;

void needLine() throws java.sql.SQLException;

void printState() throws java.sql.SQLException;

void registerSQLType(String s, String t) 
   throws java.sql.SQLException;

void releaseLine() throws java.sql.SQLException;  

void removeAllDescriptor() 
   throws java.sql.SQLException;

void removeDescriptor(String s) 
   throws java.sql.SQLException;

void setAutoClose(boolean on) throws
   java.sql.SQLException;

void setClientIdentifier(String s) 
   throws java.sql.SQLException;

void clearClientIdentifier(String s) throws java.sql.SQLException;

void setDefaultAutoRefetch(boolean b)
   throws java.sql.SQLException;

void setDefaultExecuteBatch(int i) 
   throws java.sql.SQLException;

void setDefaultRowPrefetch(int i) 
   throws java.sql.SQLException;

void setFDO(byte[] b) 
   throws java.sql.SQLException;

void setIncludeSynonyms(boolean b) 
   throws java.sql.SQLException;

weblogic.jdbc.vendor.oracle.OracleConnection

extends

java.sql.Connection

(continued)

(This interface is deprecated.)

void setPhysicalStatus(boolean b) 
   throws java.sql.SQLException;

void setRemarksReporting(boolean b) 
   throws java.sql.SQLException;

void setRestrictGetTables(boolean b) 
   throws java.sql.SQLException;

void setStmtCacheSize(int i) 
   throws java.sql.SQLException;

void setStmtCacheSize(int i, boolean b) 
   throws java.sql.SQLException;

void setUsingXAFlag(boolean b) 
   throws java.sql.SQLException;

void setXAErrorFlag(boolean b) 
   throws java.sql.SQLException;

void shutdown(int i) 
   throws java.sql.SQLException;

void startup(String s, int i)
       throws java.sql.SQLException;

Table 6-3 OracleStatement Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleStatement

extends

java.sql.Statement

(This interface is deprecated.)

String getOriginalSql() 
   throws java.sql.SQLException;
   (Removed in Oracle 11g.)

String getRevisedSql() 
   throws java.sql.SQLException;
   (Deprecated in Oracle 8.1.7, removed in Oracle 9i.)

boolean getAutoRefetch() 
   throws java.sql.SQLException;

boolean is_value_null(boolean b, int i) 
   throws java.sql.SQLException;

byte getSqlKind() 
   throws java.sql.SQLException;

int creationState() 
   throws java.sql.SQLException;

int getAutoRollback() 
   throws java.sql.SQLException;
   (Deprecated)

int getRowPrefetch() 
   throws java.sql.SQLException;

int getWaitOption() 
   throws java.sql.SQLException;
   (Deprecated)

int sendBatch() 
   throws java.sql.SQLException;

weblogic.jdbc.vendor.oracle.OracleStatement

extends

java.sql.Statement

(This interface is deprecated.)

void clearDefines() 
   throws java.sql.SQLException;

void defineColumnType(int i, int j) 
   throws java.sql.SQLException;

void defineColumnType(int i, int j, String s)
   throws java.sql.SQLException;

void defineColumnType(int i, int j, int k) 
   throws java.sql.SQLException;

void describe() 
   throws java.sql.SQLException;

void setAutoRefetch(boolean b) 
   throws java.sql.SQLException;

void setAutoRollback(int i) 
   throws java.sql.SQLException;
   (Deprecated)

void setRowPrefetch(int i) 
   throws java.sql.SQLException;

void setWaitOption(int i) 
   throws java.sql.SQLException;
   (Deprecated)

Table 6-4 OracleResultSet Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleResultSet

extends

java.sql.ResultSet

(This interface is deprecated.)

boolean getAutoRefetch()
   throws java.sql.SQLException;

int getFirstUserColumnIndex() 
   throws java.sql.SQLException;

void closeStatementOnClose() 
   throws java.sql.SQLException;

void setAutoRefetch(boolean b) 
   throws java.sql.SQLException;

java.sql.ResultSet getCursor(int n) 
   throws java.sql.SQLException;

java.sql.ResultSet getCURSOR(String s) 
   throws java.sql.SQLException;

Table 6-5 OracleCallableStatement Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleCallableStatement

extends

java.sql.CallableStatement

(This interface is deprecated.)

void clearParameters() 
   throws java.sql.SQLException;

void registerIndexTableOutParameter(int i, 
      int j, int k, int l) 
   throws java.sql.SQLException;

void registerOutParameter
   (int i, int j, int k, int l) 
   throws java.sql.SQLException;

java.sql.ResultSet getCursor(int i) 
   throws java.sql.SQLException;

java.io.InputStream getAsciiStream(int i)
   throws java.sql.SQLException;

java.io.InputStream getBinaryStream(int i)
   throws java.sql.SQLException;

java.io.InputStream getUnicodeStream(int i)
   throws java.sql.SQLException;

Table 6-6 OraclePreparedStatement Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OraclePreparedStatement

extends

OracleStatement and java.sql.PreparedStatement

(This interface is deprecated.)

int getExecuteBatch() 
   throws java.sql.SQLException;

void defineParameterType(int i, int j, int k) 
   throws java.sql.SQLException;

void setDisableStmtCaching(boolean b) 
   throws java.sql.SQLException;

void setExecuteBatch(int i) 
   throws java.sql.SQLException;

void setFixedCHAR(int i, String s) 
   throws java.sql.SQLException;

void setInternalBytes(int i, byte[] b, int j)
   throws java.sql.SQLException;

Table 6-7 OracleArray Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleArray

extends

java.sql.Array

(This interface is deprecated.

public ArrayDescriptor getDescriptor() 
   throws java.sql.SQLException;

public Datum[] getOracleArray() 
   throws SQLException;

public Datum[] getOracleArray(long l, int i) 
   throws SQLException;

public String getSQLTypeName()
   throws java.sql.SQLException;

public int length()
   throws java.sql.SQLException;

public double[] getDoubleArray()
   throws java.sql.SQLException;

public double[] getDoubleArray(long l, int i)
   throws java.sql.SQLException;

public float[] getFloatArray()
   throws java.sql.SQLException;

public float[] getFloatArray(long l, int i)
   throws java.sql.SQLException;

public int[] getIntArray()
   throws java.sql.SQLException;

public int[] getIntArray(long l, int i)
   throws java.sql.SQLException;

public long[] getLongArray()
   throws java.sql.SQLException;

public long[] getLongArray(long l, int i)
   throws java.sql.SQLException;

weblogic.jdbc.vendor.oracle.OracleArray

extends

java.sql.Array

(continued)

(This interface is deprecated.)

public short[] getShortArray()
   throws java.sql.SQLException;

public short[] getShortArray(long l, int i)
   throws java.sql.SQLException;

public void setAutoBuffering(boolean flag)
   throws java.sql.SQLException;

public void setAutoIndexing(boolean flag)
   throws java.sql.SQLException;

public boolean getAutoBuffering()
   throws java.sql.SQLException;

public boolean getAutoIndexing()
   throws java.sql.SQLException;

public void setAutoIndexing(boolean flag, int i)
   throws java.sql.SQLException;

Table 6-8 OracleStruct Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleStruct

extends

java.sql.Struct

(This interface is deprecated.)

public Object[] getAttributes() 
   throws java.sql.SQLException;

public Object[] getAttributes(java.util.Dictionary map)
   throws java.sql.SQLException; 

public Datum[] getOracleAttributes() 
   throws java.sql.SQLException;

public oracle.sql.StructDescriptor getDescriptor() 
   throws java.sql.SQLException;

public String getSQLTypeName() 
   throws java.sql.SQLException;

public void setAutoBuffering(boolean flag) 
   throws java.sql.SQLException;

public boolean getAutoBuffering() 
   throws java.sql.SQLException;

Table 6-9 OracleRef Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleRef

extends

java.sql.Ref

(This interface is deprecated.)

public String getBaseTypeName() 
   throws SQLException;

public oracle.sql.StructDescriptor getDescriptor() 
   throws SQLException;

public oracle.sql.STRUCT getSTRUCT() 
   throws SQLException;

public Object getValue() 
   throws SQLException;

public Object getValue(Map map) 
   throws SQLException;

public void setValue(Object obj) 
   throws SQLException;

Table 6-10 OracleThinBlob Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleThinBlob

extends

java.sql.Blob

(This interface is deprecated.)

int getBufferSize()throws java.sql.Exception

int getChunkSize()throws java.sql.Exception

int putBytes(long, int, byte[])throws java.sql.Exception

int getBinaryOutputStream()throws java.sql.Exception

Table 6-11 OracleThinClob Interface (Deprecated)

Extends Method Signature

weblogic.jdbc.vendor.oracle.OracleThinClob

extends

java.sql.Clob

(This interface is deprecated.)

public OutputStream getAsciiOutputStream()
   throws java.sql.Exception;

public Writer getCharacterOutputStream()
   throws java.sql.Exception;

public int getBufferSize() throws java.sql.Exception;

public int getChunkSize() throws java.sql.Exception;

public char[] getChars(long l, int i)
   throws java.sql.Exception;

public int putChars(long start, char myChars[])
   throws java.sql.Exception;

public int putString(long l, String s)
         throws java.sql.Exception;  

Using Batching with the Oracle Thin Driver

In some situations, the Oracle Thin driver may not send updates to the DBMS if a batch size has not be reached and waits until the statement is closed.

When a Prepared Statement is closed, WebLogic Server returns the statement to a standard JDBC state rather than closing it. It is then put back into the cache for the connection so it can be redelivered the next time it is needed.

To make sure all your updates are delivered, you need to call sendBatch() explicitly after the last use of the statement, before closing it or closing the connection:

Programming with Oracle Virtual Private Databases

An Oracle Virtual Private Database (VPD) is an aggregation of server-enforced, application-defined fine-grained access control, combined with a secure application context in the Oracle database server. To use VPDs in your WebLogic Server application, you would typically do the following:

  1. Create a JDBC data source in your WebLogic Server configuration that uses the Oracle Thin driver. See Chapter 2, "Using WebLogic JDBC in an Application," or "Create JDBC data sources" in the Oracle WebLogic Server Administration Console Help.

    Note:

    If you are using an XA-enabled version of the JDBC driver, you must set KeepXAConnTillTxComplete=true. See "JDBC Data Source: Configuration: Connection Pool" in the Oracle WebLogic Server Administration Console Help.
  2. Do the following in your application:

    import weblogic.jdbc.extensions.WLConnection
    // get a connection from a WLS JDBC data source
    Connection conn = ds.getConnection();
    // Get the underlying vendor connection object
    oracle.jdbc.OracleConnection orConn = (oracle.jdbc.OracleConnection)
    (((WLConnection)conn).getVendorConnection());
    // Set CLIENT_IDENTIFIER (which will be accessible from 
    // USERENV naming context on the database server side)
    orConn.setClientIdentifier(clientId);
    /* perform application specific work, preferably using conn instead of orConn */
    // clean up connection before returning to WLS JDBC data source
    orConn.clearClientIdentifier(clientId);
    // As soon as you are finished with vendor-specific calls,  
    // nullify the reference to the physical connection. 
    orConn = null; 
    // close the pooled connection
    conn.close();
    

    Note:

    This code uses an underlying physical connection from a pooled (logical) connection.

Oracle VPD with WebLogic Server

WebLogic Server provides support for the oracle.jdbc.OracleConnection.setClientIdentitfier and oracle.jdbc.OracleConnection.clearClientIndentifier methods without using the underlying physical connection from a pooled connection. To use VPDs in your WebLogic Server application, you would typically do the following:

import weblogic.jdbc.vendor.oracle.OracleConnection;
// get a connection from a WLS JDBC data source
Connection conn = ds.getConnection();
// cast to the Oracle extension and set CLIENT_IDENTIFIER
// (which will be accessible from USERENV naming context on
//  the database server side)
 ((weblogic.jdbc.vendor.oracle.OracleConnection)conn).setClientIdentifier(clientId);
/* perform application specific work */
// clean up connection before returning to WLS JDBC data source
((OracleConnection)conn).clearClientIdentifier(clientId);
// close the connection
conn.close();