Corporate Info | News | Solutions | Products | Partners | Services | Events | Download | How To Buy

Using WebLogic jDriver for Oracle

Introduction

What's in this document

Overview of BEA jdbcKona

Software requirements

Installation

Implementing with WebLogic jDriver for Oracle

Importing packages

Setting CLASSPATH

Oracle client library versions, URLs, and Driver class names

JDBC 1.x

JDBC 2.0

Connecting to an Oracle DBMS

Connection example

Connecting using a DataSource object

Setting properties for WebLogic JDBC use

About the Connection object

Setting Autocommit

Making a simple SQL query

Inserting, updating, and deleting records

Creating and using stored procedures and functions

Disconnecting and closing objects

Code example

Working with ResultSets from stored procedures

Support for JDBC Extended SQL

Additional Oracle features

Allowing mixed case metadata

Data types

WebLogic jDriver for Oracle and Oracle's NUMBER column

Using Oracle Long raw data types

Waiting on Oracle resources

Autocommit

Transaction isolation levels

Codeset support

Support for Oracle array fetches

Using stored procedures

Binding a parameter to an Oracle cursor

Notes on using CallableStatement

Notes on DatabaseMetaData methods

JDBC 2.0 for Oracle

Overview

Configuration

Driver class

Driver URL

Overview of BLOBs and CLOBs

Transaction boundaries

BLOBs

Connection properties

Import statements

Initializing a BLOB field

Writing binary data to a BLOB

Writing a BLOB object

Reading BLOB data

Other methods

CLOBs

Codeset support

Initializing a CLOB field

Writing ASCII data to a CLOB

Writing Unicode data to a CLOB

Writing CLOB objects

Reading CLOB data

Other methods

Character and ASCII Streams

Unicode character Streams

ASCII character streams

Batch updates

Using Batch updates

Clearing the batch

Update counts

Using with JDBC 1

New date methods

Resources

Code Examples

Introduction

WebLogic jDriver for Oracle is a new name for the product previously known as jdbcKona/Oracle.

What's in this document

This document covers using the WebLogic jDriver for Oracle JDBC driver for the Oracle Database management system (DBMS). This driver is a Type-2 JDBC driver and requires the installation of Oracle client libraries on the machine accessing the Oracle Server.

This document also includes general notes and an implementation guide for using JDBC, and information on using Oracle-specific features with WebLogic jDriver for Oracle. The discussions and examples in this document assume that the reader is familiar with Java, general DBMS concepts, and Structured Query Language (SQL).

This document also covers recent updates to WebLogic jDriver for Oracle, as of version 5, that include new JDBC 2.0 for Oracle features such as Blob and Clob support and Batch updates.

If you are using WebLogic JDBC, BEA's multitier JDBC implementation, you should also refer to the Developers Guide Using WebLogic JDBC for more information.

Overview of BEA jDrivers

The BEA WebLogic jDrivers include a Type-2 native JDBC driver for Oracle and Type 4 JDBC drivers for Informix and Microsoft SQL Server. The Type-2 driver employs client libraries supplied by the database vendor while the Type-4 drivers are 100% pure Java; they connect to the database server at the wire level without vendor-supplied client libraries.

For more information, see WebLogic JDBC Options.

Software requirements

For details on the platforms, operating systems, JVMs, DBMS versions, and client libraries supported by the WebLogic jDrivers see Platform support for Weblogic jDrivers.

Installation

For installation instructions, see Installing WebLogic jDriver for Oracle.

Implementing with WebLogic jDriver for Oracle

This example follows a simple application through the basic steps:

- Importing JDBC packages

- Connecting to an Oracle DBMS.

- Making a simple SQL query

- Inserting, updating, and deleting records

- Creating and using stored procedures and functions

- Disconnecting and closing objects

- Code example.

Importing packages

The classes that you import into your application should include:

    import java.sql.*;
    import java.util.Properties; // required only if using a Properties
                                 // object to set connection parameters
    import weblogic.common.*;

The WebLogic jDriver for Oracle driver implements the java.sql interface. You write your application using the java.sql classes. You do not need to import the JDBC driver class; instead, you load the driver inside the application. This allows you to select an appropriate driver at runtime. You can even decide what DBMS to connect to after the program is compiled.

Setting CLASSPATH

When running a WebLogic jDriver for Oracle client using the driver provided with WebLogic Server you must put the following directory in your CLASSPATH:

    weblogic/classes

(where weblogic is the directory containing your WebLogic Server installation)

If you are running a stand-alone version of WebLogic jDriver for Oracle, place the following directory in your classpath:

    weblogic/oci/classes

(where weblogic is the directory containing your WebLogic jDriver for Oracle installation)

For more information on setting your CLASSPATH and other environment issues, see Setting your development environment

Oracle client library versions, URLs, and Driver class names

Depending on your platform, which version of the Oracle client libraries you are using, and whether or not you require access to JDBC 2.0 features (Blobs, Clobs, Batch updates), you need to use a different driver class name and URL.

You must also specify the correct driver version in your system's path. For more information, see Setting your path and client libraries.

JDBC 1.x

Driver class: weblogic.jdbc.oci.Driver

URL: jdbc:weblogic:oracle

JDBC 2.0

Driver class: weblogic.jdbc20.oci.Driver

URL: jdbc20:weblogic:oracle

Connecting to an Oracle DBMS

These steps explain how to set up your application to connect to your Oracle DBMS using WebLogic jDriver for Oracle. In general, connecting happens in three steps:

1. Load the JDBC driver by calling Class.forName().newInstance() with the full class name of the WebLogic jDriver for Oracle JDBC driver class, and casting it to a java.sql.Driver object. This procedure properly loads and registers the JDBC driver. For example:

       Driver myDriver = (Driver)Class.forName
        ("weblogic.jdbc.oci.Driver").newInstance();

2. Create a java.util.Properties object describing the connection. This object contains name-value pairs containing information such as user name, password, database name, server name, and port number. For example:

   Properties props = new Properties();
   props.put("user", "scott");
   props.put("password", "secret");
   props.put("server", "DEMO");

   The server name ("DEMO" in the example above) refers to an entry in the tnsnames.ora file, which is located in your Oracle client installation and defines host names and other information about an Oracle database. If you do not supply a server name, the system will look for an environment variable (ORACLE_SID in the case of Oracle). You may also choose to add the server name to the URL, with this format:

       "jdbc:weblogic:oracle:DEMO"

   in which case you do not need to provide a "server" property.

   You can also set properties in a single URL, for use with products like PowerSoft's PowerJ. For details, see Using URLs with WebLogic products.

3. Create a JDBC Connection object, which becomes an integral piece in your JDBC operations, by calling the Driver.connect() method, which takes as its parameters the URL of the driver and the java.util.Properties object you created in step 2. For example:

       Connection conn =
         myDriver.connect("jdbc:weblogic:oracle", props);

In steps 1 and 3, you are describing the JDBC driver: in the first step, you use the full package name of the driver. Note that it is dot-delimited. In the third step, you identify the driver with its URL, which is colon-delimited. The URL must include at least jdbc:weblogic:mssqlserver4, and may include other information, including the server host name and the database name.

Note: The examples in this document use the driver name weblogic.jdbc.oci.Driver and the URL jdbc:weblogic:oracle. These are intended for use where JDBC 2.0 features are not required. You may also use the driver name weblogic.jdbc20.oci.Driver and the URL jdbc20:weblogic:oracle if you need to use JDBC 2.0 features. For more information on JDBC 2.0, see JDBC 2.0 for Oracle

There are additional restrictions regarding the use of JDBC 2.0. For more information, see Oracle client library versions, URLs, and Driver class names

Connection example

This example shows how to use a Properties object to connect to a database named "myDB."

    Properties props = new Properties();
    props.put("user",         "scott");
    props.put("password",     "secret");
    props.put("db",           "myDB");

    Driver myDriver = (Driver)
      Class.forName("weblogic.jdbc.oci.Driver").newInstance();
    Connection conn =
      myDriver.connect("jdbc:weblogic:oracle", props);

Connecting using a DataSource object

If you are using WebLogic jDriver for Oracle as a middle-tier driver with WebLogic Server, you can also obtain a connection by using a DataSource object. For more information, see Using a DataSource object to get a connection.

Setting properties for WebLogic JDBC use

If you are using WebLogic JDBC in a multitier environment with the WebLogic jDriver for Oracle driver, you will set connection properties in a slightly different way. See Using WebLogic JDBC for more information.

About the Connection object

The Connection object is an important part of the application. The Connection class has constructors for many fundamental database objects that you will use throughout the application, and in the examples that follow, you will see the Connection object conn used frequently. Connecting to the database completes the initial portion of the application.

You should call the close() method on the Connection object as soon as you finish working with it, usually at the end of a class.

Setting Autocommit

The default transaction mode for JDBC assumes autocommit to be true. With WebLogic jDriver for Oracle, setting autocommit to false will improve performance.

Making a simple SQL query

The most fundamental task in database access is to retrieve data. With WebLogic jDriver for Oracle, this is a three-step process:

1. Create a Statement to send a SQL query to the DBMS

2. Execute the Statement

3. Retrieve the results into a ResultSet In this example, we execute a simple query on the Employee table (alias "emp") and display data from three of the columns. We also access and display metadata about the table from which the data was retrieved. Note that we close the Statement at the end.

       Statement stmt = conn.createStatement();
       stmt.execute("select * from emp");
       ResultSet rs = stmt.getResultSet();

       while (rs.next()) {
         System.out.println(rs.getString("empid") + " - " +
                            rs.getString("name")  + " - " +
                            rs.getString("dept"));
         }

       ResultSetMetaData md = rs.getMetaData();

       System.out.println("Number of columns: " +
             md.getColumnCount());
       for (int i = 1; i <= md.getColumnCount(); i++) {
           System.out.println("Column Name: "     +
             md.getColumnName(i));
           System.out.println("Nullable: "        +
             md.isNullable(i));
           System.out.println("Precision: "       +
             md.getPrecision(i));
           System.out.println("Scale: "           +
             md.getScale(i));
           System.out.println("Size: "            +
             md.getColumnDisplaySize(i));
           System.out.println("Column Type: "     +
             md.getColumnType(i));
           System.out.println("Column Type Name: "+
             md.getColumnTypeName(i));
           System.out.println("");
         }

       stmt.close();

JDBC ResultSets have a basic functionality for dealing with data on the record level. You may find managing data easier with BEA's dbKona, which features a higher level of abstraction. dbKona's objects, like TableDataSet, QueryDataSet, and Record, as well as other features like automatic SQL generation and client-side results management, sit on top of JDBC. Check the Developers Guide Using dbKona, for more information.

Inserting, updating, and deleting records

We illustrate three common database tasks in this step: inserting, updating, and deleting records from a database table. We use a JDBC PreparedStatement for these operations; we create the PreparedStatement, then execute it and close it.

A PreparedStatement (subclassed from JDBC Statement) allows you to execute the same SQL over and over again with different values. PreparedStatements use the JDBC "?" syntax.

    String inssql =
       "insert into emp(empid, name, dept) values (?, ?, ?)";
    PreparedStatement pstmt = conn.prepareStatement(inssql);
    for (int i = 0; i < 100; i++) {
      pstmt.setInt(1, i);
      pstmt.setString(2, "Person " + i);
      pstmt.setInt(3, i);
      pstmt.execute():
    }
      pstmt.close();

We also use a PreparedStatement to update records. In this example, we add the value of the counter "i" to the current value of the "dept" field.

    String updsql =
       "update emp set dept = dept + ? where empid = ?";
    PreparedStatement pstmt2 = conn.prepareStatement(updsql);
     for (int i = 0; i < 100; i++) {
      pstmt2.setInt(1, i);
      pstmt2.setInt(2, i);
      pstmt2.execute();
    }
    pstmt2.close();

Finally, we use a PreparedStatement to delete the records that added and then updated.

    String delsql = "delete from emp where empid = ?";
    PreparedStatement pstmt3 = conn.prepareStatement(delsql);
    for (int i = 0; i < 100; i++) {
      pstmt3.setInt(1, i);
      pstmt3.execute();
    }
    pstmt3.close();

Creating and using stored procedures and functions

You can use WebLogic jDriver for Oracle to create, use, and drop stored procedures and functions. First, we execute a series of Statements to drop a set of stored procedures and functions from the database.

    Statement stmt = conn.createStatement();
    try {stmt.execute("drop procedure proc_squareInt");}
    catch (SQLException e) {//code to handle the exception goes here;}
    try {stmt.execute("drop procedure func_squareInt");}
    catch (SQLException e) {//code to handle the exception goes here;}
    try {stmt.execute("drop procedure proc_getresults");}
    catch (SQLException e) {//code to handle the exception goes here;}
    stmt.close();

We use a JDBC Statement to create a stored procedure or function, and then we use a JDBC CallableStatement (subclassed from Statement) with the JDBC "?" syntax to set IN and OUT parameters.

Note that Oracle does not natively support binding to "?" values in a SQL statement. Instead it uses ":1", ":2", etc. You can use either syntax in your SQL with WebLogic jDriver for Oracle.

Stored procedure input parameters are mapped to JDBC IN parameters, using the CallableStatement.setXXX() methods, like setInt(), and the JDBC PreparedStatement "?" syntax. Stored procedure output parameters are mapped to JDBC OUT parameters, using the CallableStatement.registerOutParameter() methods and JDBC PreparedStatement "?" syntax. A parameter may be both IN and OUT, which requires both a setXXX() and a registerOutParameter() call to be done on the same parameter number.

In this example, we use a JDBC Statement to create an Oracle stored procedure; then we execute the stored procedure with a CallableStatement. We use the registerOutParameter() method to set an output parameter for the squared value.

    Statement stmt1 = conn.createStatement();
    stmt1.execute
      ("CREATE OR REPLACE PROCEDURE proc_squareInt " +
      "(field1 IN OUT INTEGER, field2 OUT INTEGER) IS " +
      "BEGIN field2 := field1 * field1; field1 := " +
      "field1 * field1; END proc_squareInt;");
    stmt1.close();

    // Native Oracle SQL is commented out here
    // String sql = "BEGIN proc_squareInt(?, ?); END;";

    // This is the correct syntax as specified by JDBC
    String sql = "{call proc_squareInt(?, ?)}";
    CallableStatement cstmt1 = conn.prepareCall(sql);

    // Register out parameters
    cstmt1.registerOutParameter(2, java.sql.Types.INTEGER);
    for (int i = 0; i < 5; i++) {
      cstmt1.setInt(1, i);
      cstmt1.execute();
      System.out.println(i + " " + cstmt1.getInt(1) + " "
       + cstmt1.getInt(2));
    } cstmt1.close();

In the next example, we use similar code to create and execute a stored function that squares an integer.

    Statement stmt2 = conn.createStatement();
    stmt2.execute("CREATE OR REPLACE FUNCTION func_squareInt " +
                  "(field1 IN INTEGER) RETURN INTEGER IS " +
                  "BEGIN return field1 * field1; " +
                  "END func_squareInt;");
    stmt2.close();

    // Native Oracle SQL is commented out here
    // sql = "BEGIN ? := func_squareInt(?); END;";

    // This is the correct syntax specified by JDBC
    sql = "{ ? = call func_squareInt(?)}";
    CallableStatement cstmt2 = conn.prepareCall(sql);

    cstmt2.registerOutParameter(1, Types.INTEGER);
    for (int i = 0; i < 5; i++) {
      cstmt2.setInt(2, i);
      cstmt2.execute();
      System.out.println(i + " " + cstmt2.getInt(1) +
                         " " + cstmt2.getInt(2));
    }
    cstmt2.close();

This next example uses a stored procedure named sp_getmessages (the code for this stored procedure is not included with this example) This stored procedure takes a message number as an input parameter, looks up the message number in a table containing the message text and returns the message text in a ResultSet as an output parameter. Note that you must process all ResultSets returned by a stored procedure using the Statement.execute() and Statement.getResult() methods before OUT parameters and return status are available.

First, we set up the three parameters to the CallableStatement:

1. Parameter 1 (output only) is the stored procedure return value

2. Parameter 2 (input only) is the msgno argument to sp_getmessage

3. Parameter 3 (output only) is the message text return for the message number

          String sql = "{ ? = call sp_getmessage(?, ?)}";
           CallableStatement stmt = conn.prepareCall(sql);

           stmt.registerOutParameter(1, java.sql.Types.INTEGER);
           stmt.setInt(2, 18000);           // msgno 18000
           stmt.registerOutParameter(3, java.sql.Types.VARCHAR);

We execute the stored procedure and check the return value to see if the ResultSet is empty. If it is not, we use a loop to retrieve and display its contents.

      boolean hasResultSet = stmt.execute();
      while (true)
      {
        ResultSet rs = stmt.getResultSet();
        int updateCount = stmt.getUpdateCount();
        if (rs == null && updateCount == -1) // no more results
          break;
        if (rs != null) {
          // Process the ResultSet until it is empty
          while (rs.next()) {
            System.out.println
            ("Get first col by id:" + rs.getString(1));
          }
        } else {
          // we have an update count
          System.out.println("Update count = " +
           stmt.getUpdateCount());
        }
        stmt.getMoreResults();
      }

Once we finish processing the ResultSet, the OUT parameters and return status are available.

      int retstat = stmt.getInt(1);
      String msg = stmt.getString(3);

      System.out.println("sp_getmessage: status = " +
                         retstat + " msg = " + msg);
      stmt.close();

Disconnecting and closing objects

There are occasions on which you will want to call the commit() method to commit changes you've made to the database before you close the connection.

When autocommit is set to true (the default JDBC transaction mode) each SQL statement is its own transaction. After we created the Connection for these examples, however, we set autocommit to false; in this mode, the Connection always has an implicit transaction associated with it, and any call to the rollback() or commit() methods will end the current transaction and start a new one. Calling commit() before close() ensures that all of the transactions are completed before closing the Connection.

Just as you close Statements, PreparedStatements, and CallableStatements when you have finished working with them, you should always call the close() method on the connection as final cleanup in your application, in a try {} block, and you should catch exceptions and deal with them appropriately. The final two lines of this example include a call to commit and then a call to close the connection.

      conn.commit();
      conn.close();

Code example

Here are selected portions of the code used in these examples to give you an overall idea of structure for a JDBC application. The code example shown here includes retrieving data, displaying metadata, inserting, deleting, and updating data, and stored procedures and functions. Note the explicit calls to close() for each JDBC-related object, and note also that we close the Connection itself in a finally {} block, with the call to close() wrapped in a try {} block.

    package examples.jdbc.oracle;

    import java.sql.*;
    import java.util.Properties;
    import weblogic.common.*;

    public class test {
      static int i;
      Statement stmt = null;

      public static void main(String[] argv) {
        try {
          Properties props = new Properties();
          props.put("user",              "scott");
          props.put("password",          "tiger");
          props.put("server",            "DEMO");

        Driver myDriver = (Driver) Class.forName
          ("weblogic.jdbc.oci.Driver").newInstance();

        Connection conn =
          myDriver.connect("jdbc:weblogic:oracle", props);

        }
        catch (Exception e)
          e.printStackTrace();
        }

        try {
          // This will improve performance in Oracle
          // You'll need an explicit commit() call later
          conn.setAutoCommit(false);

          stmt = conn.createStatement();
          stmt.execute("select * from emp");
          ResultSet rs = stmt.getResultSet();

          while (rs.next()) {
            System.out.println(rs.getString("empid") + " - " +
                               rs.getString("name")  + " - " +
                               rs.getString("dept"));
          }

          ResultSetMetaData md = rs.getMetaData();

          System.out.println("Number of Columns: " +
             md.getColumnCount());
          for (i = 1; i <= md.getColumnCount(); i++) {
            System.out.println("Column Name: "     +
              md.getColumnName(i));
            System.out.println("Nullable: "        +
              md.isNullable(i));
            System.out.println("Precision: "       +
              md.getPrecision(i));
            System.out.println("Scale: "           +
              md.getScale(i));
            System.out.println("Size: "            +
              md.getColumnDisplaySize(i));
            System.out.println("Column Type: "     +
              md.getColumnType(i));
            System.out.println("Column Type Name: "+
              md.getColumnTypeName(i));
            System.out.println("");
          }
          rs.close();
          stmt.close();

          Statement stmtdrop = conn.createStatement();
          try {stmtdrop.execute("drop procedure proc_squareInt");}
          catch (SQLException e) {;}
          try {stmtdrop.execute("drop procedure func_squareInt"); }
          catch (SQLException e) {;}
          try {stmtdrop.execute("drop procedure proc_getresults"); }
          catch (SQLException e) {;}
          stmtdrop.close();

          // Create a stored procedure
          Statement stmt1 = conn.createStatement();
          stmt1.execute
           ("CREATE OR REPLACE PROCEDURE proc_squareInt " +
           "(field1 IN OUT INTEGER, " +
           "field2 OUT INTEGER) IS " +
           "BEGIN field2 := field1 * field1; " +
           "field1 := field1 * field1; " +
           "END proc_squareInt;");
          stmt1.close();

          CallableStatement cstmt1 =
            conn.prepareCall("BEGIN proc_squareInt(?, ?); END;");
          cstmt1.registerOutParameter(2, Types.INTEGER);
          for (i = 0; i < 100; i++) {
            cstmt1.setInt(1, i);
            cstmt1.execute();
            System.out.println(i + " " + cstmt1.getInt(1) +
                               " " + cstmt1.getInt(2));
          }
          cstmt1.close();

          // Create a stored function
          Statement stmt2 = conn.createStatement();
          stmt2.execute
           ("CREATE OR REPLACE FUNCTION func_squareInt " +
           "(field1 IN INTEGER) RETURN INTEGER IS " +
           "BEGIN return field1 * field1; END func_squareInt;");
          stmt2.close();

          CallableStatement cstmt2 =
            conn.prepareCall("BEGIN ? := func_squareInt(?); END;");
          cstmt2.registerOutParameter(1, Types.INTEGER);
          for (i = 0; i < 100; i++) {
            cstmt2.setInt(2, i);
            cstmt2.execute();
            System.out.println(i + " " + cstmt2.getInt(1) +
                               " " + cstmt2.getInt(2));
          }
          cstmt2.close();

          // Insert 100 records
          System.out.println("Inserting 100 records...");
          String inssql =
            "insert into emp(empid, name, dept) values (?, ?, ?)";
          PreparedStatement pstmt = conn.prepareStatement(inssql);

          for (i = 0; i < 100; i++) {
            pstmt.setInt(1, i);
            pstmt.setString(2, "Person " + i);
            pstmt.setInt(3, i);
            pstmt.execute();
          }
          pstmt.close();

          // Update 100 records
          System.out.println("Updating 100 records...");
          String updsql =
           "update emp set dept = dept + ? where empid = ?";
          PreparedStatement pstmt2 = conn.prepareStatement(updsql);

          for (i = 0; i < 100; i++) {
            pstmt2.setInt(1, i);
            pstmt2.setInt(2, i);
            pstmt2.execute();
          }
          pstmt2.close();

          // Delete 100 records
          System.out.println("Deleting 100 records...");
          String delsql = "delete from emp where empid = ?";
          PreparedStatement pstmt3 = conn.prepareStatement(delsql);

          for (i = 0; i < 100; i++) {
            pstmt3.setInt(1, i);
            pstmt3.execute();
          }
          pstmt3.close();

          conn.commit();
        }
        catch (Exception e) {
          // Deal with failures appropriately
        }
        finally {
          try {conn.close();}
          catch (Exception e) {
            // Catch and deal with exception
          }
        }
      }
    }

Working with ResultSets from stored procedures

Executing stored procedures may return multiple ResultSets. When you process ResultSets returned by a stored procedure, using Statement.execute() and Statement.getResultSet() methods, you must process all ResultSets returned before any of the OUT parameters or the return status codes are available.

Support for JDBC Extended SQL

The JavaSoft JDBC specification includes SQL Extensions, also called SQL Escape Syntax. All WebLogic jDrivers support Extended SQL. Extended SQL provides access to common SQL extensions in a way that is portable between DBMSs.

For example, the function to extract the day name from a date is not defined by the SQL standards. For Oracle, the SQL is:

      select to_char(date_column, 'DAY') from table_with_dates

The equivalent function for Sybase and Microsoft SQL Server is:

      select datename(dw, date_column) from table_with_dates

Using Extended SQL, you can retrieve the day name for both DBMSs as follows:

      select {fn dayname(date_column)} from table_with_dates

Here's an example that demonstrates several features of Extended SQL:

      String query =
      "-- This SQL includes comments and " +
          "JDBC extended SQL syntax.\n" +
      "select into date_table values( \n" +
      "      {fn now()},        -- current time \n" +
      "      {d '1997-05-24'},  -- a date       \n" +
      "      {t '10:30:29' },   -- a time       \n" +
      "      {ts '1997-05-24 10:30:29.123'},  -- a timestamp\n" +
      "     '{string data with { or } will not be altered}'\n" +
      "-- Also note that you can safely include" +
         " { and } in comments or\n" +
      "-- string data.";
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(query);

Extended SQL is delimited with curly braces ("{}") to differentiate it from common SQL. Comments are preceded by two hyphens, and are ended by a new line ("\n"). The entire Extended SQL sequence, including comments, SQL, and Extended SQL, is placed within double quotes and passed to the execute() method of a Statement object. Here is Extended SQL used as part of a CallableStatement:

       CallableStatement cstmt =
        conn.prepareCall("{ ? = call func_squareInt(?)}");

This example shows that you can nest extended SQL expressions:

       select {fn dayname({fn now()})}

You can retrieve lists of supported Extended SQL functions from a DatabaseMetaData object. This example shows how to list all the functions a JDBC driver supports:

       DatabaseMetaData md = conn.getMetaData();
       System.out.println("Numeric functions:     " +
          md.getNumericFunctions());
      System.out.println("\nString functions:    " +
          md.getStringFunctions());
      System.out.println("\nTime/date functions: " +
          md.getTimeDateFunctions());
      System.out.println("\nSystem functions:    " +
          md.getSystemFunctions());
      conn.close();

Refer to chapter 11 of the JDBC 1.2 specification at JavaSoft for a description of Extended SQL.

Additional Oracle features

Allowing mixed case metadata

WebLogic jDriver for Oracle also allows setting the property allowMixedCaseMetaData. When set to the boolean true, this property sets up the Connection to use mixed case in calls to DatabaseMetaData methods. If this property is set to false, Oracle defaults to UPPERCASE for database metadata. Here is an example of setting up the properties to include this feature:

    Properties props = new Properties();
    props.put("user",                   "scott");
    props.put("password",               "tiger");
    props.put("server",                 "DEMO");
    props.put("allowMixedCaseMetaData", "true");

    Driver myDriver = (Driver)
      Class.for.Name(weblogic.jdbc.oci.Driver).newInstance();

    Connection conn =
       myDriver.connect("jdbc:weblogic:oracle", props);

If you do not set this property, WebLogic jDriver for Oracle defaults to the Oracle default, which uses UPPERCASE for database metadata.

Data types

The table below shows the recommended mapping between Oracle data types and Java types. There are additional possibilities for representing Oracle data types in Java. Calling the getObject() method when processing result sets will return the default Java data type for the Oracle column being queried.

Figure 2-1 Mapping of types between Oracle and WebLogic jDriver for Oracle.

####

Oracle | WebLogic jDriver for Oracle
Varchar | String
Number | Tinyint
Number | Smallint
Number | Integer
Number | Long
Number | Float
Number | Numeric
Number | Double
Long | Longvarchar
RowID | String
Date | Timestamp
Raw | (var)Binary
Long raw | Longvarbinary
Char | (var)Char
Boolean* | Number OR Varchar
MLS label | String
Blob | Blob
Clob | Clob

*Note that when PreparedStatement.setBoolean() is called, it will convert a VARCHAR type to "1" or "0" (string), and it will convert a NUMBER type to 1 or 0 (number).

WebLogic jDriver for Oracle and Oracle's NUMBER column

Oracle provides a column type called NUMBER, which can be optionally specified with a precision and a scale, in the forms NUMBER(P) and NUMBER(P,S). Even in the simple unqualified NUMBER form, this column can hold all number types from small integer values to very large floating point numbers, with high precision.

WebLogic jDriver for Oracle reliably converts the values in a column to the Java type requested when a WebLogic jDriver for Oracle application asks for a value from such a column. Of course, if a value of 123.456 is asked for with getInt(), the value will be rounded.

The method getObject(), however, poses a little more complexity. WebLogic jDriver for Oracle guarantees to return a Java object which will represent any value in a NUMBER column with no loss in precision. This means that a value of 1 can be returned in an Integer, but a value like 123434567890.123456789 can only be returned in a BigDecimal.

There is no metadata from Oracle to report the maximum precision of the values in the column, so WebLogic jDriver for Oracle must decide what sort of object to return based on each value. This means that one ResultSet may return multiple Java types from getObject() for a given NUMBER column. A table full of integer values may all be returned as Integer from getObject(), whereas a table of floating point measurements may be returned primarily as Double, with some Integer if any value happens to be something like "123.00". Oracle does not provide any information to distinguish between a NUMBER value of "1" and a NUMBER of "1.0000000000".

There is some more reliable behavior with qualified NUMBER columns, that is, those defined with a specific precision. Oracle's metadata provides these parameters to the driver so WebLogic jDriver for Oracle will always return a Java object appropriate for the given precision and scale, regardless of the values in the table.

####

Column definition | Returned by getObject()
NUMBER(P <= 9) | Integer
NUMBER(P <= 18) | Long
NUMBER(P = 19) | BigDecimal
NUMBER(P <=16, S 0) | Double
NUMBER(P = 17, S 0) | BigDecimal

Using Oracle Long raw data types

There are two properties available for use with WebLogic jDriver for Oracle in support of Oracle's chunking of Blobs, Clobs, Long, and Long raw data types. Although Blob and Clob data types are only supported with Oracle Version 8 and JDBC 2.0, these properties also apply to Oracle's Long raw datatype, which is available in Oracle Version 7 and JDBC 1.x. For more information on theses properties, see Connection properties in the JDBC 2.0 section

Waiting on Oracle resources

Note: The waitOnResources() method is not supported when using the Oracle 8 API. (For more information on your Oracle client, server, and API versions, see Setting your path and client libraries.)

The WebLogic jDriver for Oracle driver supports Oracle's oopt() C functionality, which allows a client to wait until resources become available. The Oracle C function sets options in cases where requested resources are not available; for example, whether to wait for locks.

The developer can set whether a client will wait for DBMS resources, or will receive an immediate exception. Here's an example from the example examples/jdbc/oracle/waiton.java:

    java.util.Properties props = new java.util.Properties();
    props.put("user",     "scott");
    props.put("password", "tiger");
    props.put("server",   "myserver");

    Driver myDriver = (Driver)
      Class.forName("weblogic.jdbc.oci.Driver").newInstance();

    // You must cast the Connection as a weblogic.jdbc.oci.Connection
    // to take advantage of this extension

    Connection conn =(weblogic.jdbc.oci.Connection)
      myDriver.connect("jdbc:weblogic:oracle", props);

    // After constructing the Connection object, immediately call
    // the waitOnResources method

    conn.waitOnResources(true);

Note that use of this method can cause several error return codes while waiting for internal resources that are locked for short durations.

To take advantage of this feature, you must first cast your Connection object as a weblogic.jdbc.oci.Connection, and then call the waitOnResources() method.

This functionality is described in section 4-97 of The OCI Functions for C.

Autocommit

The default transaction mode for JDBC WebLogic jDriver for Oracle assumes autocommit to be true. You will improve the performance of your programs by setting autocommit to false after creating a Connection object with the statement:

       Connection.setAutoCommit(false);

Transaction isolation levels

WebLogic jDriver for Oracle supports the following transaction isolation levels:

- SET TRANSACTION ISOLATION LEVEL READ COMMITTED

- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

The Oracle DBMS only supports these two isolation levels. Unlike other JDBC drivers, WebLogic jDriver for Oracle throws an exception if you try to use an isolation level that is unsupported. Some drivers silently ignore attempts to set an unsupported isolation level.

The READ_UNCOMMITTED transaction isolation level is not supported.

Codeset support

JDBC and your WebLogic jDriver for Oracle driver handle character strings in Java as Unicode strings. Since the Oracle DBMS uses a different codeset, the driver must convert character strings from Unicode to the codeset used by Oracle. The WebLogic jDriver for Oracle will examine the value stored in the Oracle environment variable NLS_LANG and select a codeset for the JDK to use for the conversion, using the mapping shown in the table below. If the NLS_LANG variable is not set, or is set to a codeset not recognized by the JDK, the driver will not be able to determine the correct codeset. (Consult your Oracle documentation for information on the correct syntax for setting NLS_LANG.)

If you are converting codesets, you should pass the following property to the WebLogic jDriver for Oracle with the Driver.connect() method when you establish the connection in your code:

    props.put("weblogic.oci.min_bind_size", 660);

This property defines the minimum size of buffers to be bound. The default is 2000 bytes, which is also the maximum value. If you are converting codesets, you should use this property to reduce the bind size to a maximum of 660, one-third of the maximum 2000 bytes, since Oracle codeset conversion triples the buffer to allow for expansion.

WebLogic jDriver for Oracle provides a way to set the codeset from within your Java code. By setting a connection property, weblogic.codeset, you can override the value stored in the NLS_LANG environment variable. For example, to use the cp932 codeset, create a Properties object and set the weblogic.codeset property before calling Driver.connect(), as in this example:

    java.util.Properties props = new java.util.Properties();
    props.put("weblogic.codeset", "cp932");
    props.put("user", "scott");
    props.put("password", "tiger");

    String connectUrl = "jdbc:weblogic:oracle";

    Driver myDriver = (Driver)
     Class.forName("weblogic.jdbc.oci.Driver").newInstance();

    Connection conn =
         myDriver.connect(connectUrl, props);

Note that codeset support can vary with different JVMs. Check the documentation for the JDK you are using to determine if a particular codeset is supported. You can find the List of supported encodings for JDK 1.1 at the JavaSoft website.

Table 2-1 Mapping of NLS_LANG settings to JDK codesets

####

NLS_LANG | JDK codeset
us7ascii | ASCII
ja16sjis | SJIS
us8pc437 | Cp437
we8ebcdic37 | Cp1046
we8ebcdic500 | Cp500
we8pc850 | Cp850
we8iso8859p1 | ISO8859_1
ee8iso8859p2 | ISO8859_2
se8iso8859p3 | ISO8859_3
nee8iso8859p4 | ISO8859_4
cl8iso8859p5 | ISO8859_5
ar8iso8859p6 | ISO8859_6
el8iso8859p7 | ISO8859_7
iw8iso8859p8 | ISO8859_8
we8iso8859p9 | ISO8859_9
ne8iso8859p10 | ISO8859_10
ru8pc866 | Cp866
ee8pc852 | Cp852
ru8pc855 | Cp855
tr8pc857 | Cp857
cl8maccyrillic | MacCyrillic
we8pc860 | Cp860
is8pc861 | Cp861
ee8mswin1250 | Cp1250
cl8mswin1251 | Cp1251
el8mswin1253 | Cp1253
n8pc865 | Cp865
ee8macce | MacCentralEurope
ee8maccroatian | MacCroatian
tr8macturkish | MacTurkish
is8macicelandic | MacIceland
el8macgreek | MacGreek
we8macroman8 | MacRoman
th8macthai | MacThai
el8pc737 | Cp737
lt8pc772 | Cp772
lt8pc774 | Cp774
cdn8pc863 | Cp863
ja16euc | EUC_JP
ko16ksc5601 | EUC_KR
zht16big5 | Big5
al24utffss | UTF8
utf8 | UTF8

Support for Oracle array fetches

WebLogic jDriver for Oracle supports Oracle array fetches. Calling ResultSet.next() the first time will get an array of rows and store it in memory, rather than retrieving a single row. Each subsequent call to next() will read a row from the rows in memory until they are exhausted, and only then will next() go back to the database.

You set a property (java.util.Property) to control the size of the array fetch. The property is weblogic.oci.cacheRows; it is set by default to 100. Here's an example of setting this property to 300, which means that calls to next() only hit the database once for each 300 rows retrieved by the client.

    Properties props = new Properties();
    props.put("user",                   "scott");
    props.put("password",               "tiger");
    props.put("server",                 "DEMO");
    props.put("weblogic.oci.cacheRows", "300");

    Driver myDriver = (Driver)
      Class.forName("weblogic.jdbc.oci.Driver").newInstance();

    Connection conn = myDriver.connect("jdbc:weblogic:oracle", props);

You can improve client performance and lower the load on the database server by taking advantage of this JDBC extension. Caching rows in the client, however, requires client resources. You should tune your application for the best balance between performance and client resources, depending upon your network configuration and your application.

If any columns in a SELECT are of type LONG, the cache size will be temporarily reset to one (1) for the ResultSet associated with that select statement.

Using stored procedures

Binding a parameter to an Oracle cursor

WebLogic has created an extension to JDBC (weblogic.jdbc.oci.CallableStatement) that allows you to bind a parameter for a stored procedure to an Oracle cursor. You can create a JDBC ResultSet object with the results of the stored procedure. This allows you to return multiple ResultSets in an organized way. The ResultSets are determined at run time in the stored procedure.

Here is an example. First define the stored procedures as follows:

    create or replace package
    curs_types as
    type EmpCurType is REF CURSOR RETURN emp%ROWTYPE;
    end curs_types;
    /

    create or replace procedure
    single_cursor(curs1 IN OUT curs_types.EmpCurType,
    ctype in number) AS BEGIN
      if ctype = 1 then
        OPEN curs1 FOR SELECT * FROM emp;
      elsif ctype = 2 then
        OPEN curs1 FOR SELECT * FROM emp where sal  2000;
      elsif ctype = 3 then
        OPEN curs1 FOR SELECT * FROM emp where deptno = 20;
      end if;
    END single_cursor;
    /
    create or replace procedure
    multi_cursor(curs1 IN OUT curs_types.EmpCurType,
                 curs2 IN OUT curs_types.EmpCurType,
                 curs3 IN OUT curs_types.EmpCurType) AS
    BEGIN
        OPEN curs1 FOR SELECT * FROM emp;
        OPEN curs2 FOR SELECT * FROM emp where sal  2000;
        OPEN curs3 FOR SELECT * FROM emp where deptno = 20;
    END multi_cursor;
    /

In your Java code, you'll construct CallableStatements with the stored procedures and register the output parameter as data type java.sql.Types.OTHER. When you retrieve the data into a ResultSet, use the output parameter index as an argument for the getResultSet() method.

      weblogic.jdbc.oci.CallableStatement cstmt =
        (weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
                           "BEGIN OPEN ? " +
                           "FOR select * from emp; END;");
      cstmt.registerOutParameter(1, java.sql.Types.OTHER);

      cstmt.execute();
      ResultSet rs = cstmt.getResultSet(1);
      printResultSet(rs);
      rs.close();
      cstmt.close();

      weblogic.jdbc.oci.CallableStatement cstmt2 =
        (weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
                           "BEGIN single_cursor(?, ?); END;");
      cstmt2.registerOutParameter(1, java.sql.Types.OTHER);

      cstmt2.setInt(2, 1);
      cstmt2.execute();
      rs = cstmt2.getResultSet(1);
      printResultSet(rs);

      cstmt2.setInt(2, 2);
      cstmt2.execute();
      rs = cstmt2.getResultSet(1);}
      printResultSet(rs);

      cstmt2.setInt(2, 3);
      cstmt2.execute();
      rs = cstmt2.getResultSet(1);
      printResultSet(rs);
      cstmt2.close();

      weblogic.jdbc.oci.CallableStatement cstmt3 =
        (weblogic.jdbc.oci.CallableStatement)conn.prepareCall(
                             "BEGIN multi_cursor(?, ?, ?); END;");
      cstmt3.registerOutParameter(1, java.sql.Types.OTHER);
      cstmt3.registerOutParameter(2, java.sql.Types.OTHER);
      cstmt3.registerOutParameter(3, java.sql.Types.OTHER);

      cstmt3.execute();

      ResultSet rs1 = cstmt3.getResultSet(1);
      ResultSet rs2 = cstmt3.getResultSet(2);
      ResultSet rs3 = cstmt3.getResultSet(3);

For the full code for this example, including the printResultSet() method, see the examples in the examples/jdbc/oracle/ directory.

Note that the default size of an Oracle stored procedure string is 256K.

Notes on using CallableStatement

The default length of a string bound to an OUTPUT parameter of a CallableStatement is 128 characters. If the value you assign to the bound parameter exceeds that length, you'll get the following error:

    ORA-6502: value or numeric error

You can adjust the length of the value of the bound parameter by passing an explicit length with the scale argument to the CallableStatement.registerOutputParameter() method. Here is a code example that binds a VARCHAR that will never be larger than 256 characters:

      CallableStatement cstmt =
        conn.prepareCall("BEGIN testproc(?); END;");

      cstmt.registerOutputParameter(1, Types.VARCHAR, 256);
      cstmt.execute();
      System.out.println(cstmt.getString());
      cstmt.close();

Notes on DatabaseMetaData methods

There are some variations in the implementation of DatabaseMetaData methods that are specific to Oracle and are detailed here:

- As a general rule, the String catalog argument is ignored in all DatabaseMetaData methods.

- In the DatabaseMetaData.getProcedureColumns() method:

  - The String catalog argument is ignored.

  - The String schemaPattern argument accepts only exact matches (no pattern matching).

  - The String procedureNamePattern argument accepts only exact matches (no pattern matching).

  - The String columnNamePattern argument is ignored.

JDBC 2.0 for Oracle

Overview

The JDBC 2.0 specification defines new functionality that was not available in earlier versions of JDBC, some of which is not yet supported by current DBMS products and therefore has not been implemented in this driver.

New JDBC 2.0 features implemented in this driver:

- BLOBs (Binary Large Objects).
  WebLogic jDriver for Oracle can now handle this Oracle data type.

- CLOBs (Character Large Objects)
  WebLogic jDriver for Oracle can now handle this Oracle data type.

- Character Streams, for both ASCII and Unicode characters.
  A better way to handle characters streams-as streams of characters, not byte arrays.

- Batch Updates
  You can now send multiple statements to the database as a single unit.

The above features are available in addition to all of the existing JDBC functionality previously available in the WebLogic jDriver for Oracle. All of your existing code for previous drivers will work with this new driver. .

Configuration

Since JDBC 2.0 is a Java 2 specification, you must run WebLogic jDriver for Oracle in a Java 2 environment. This means using one of the BEA supported Java Virtual Machines, such as JDK 1.2.x to run your JDBC clients. For a complete list of supported configurations, see the WebLogic Platform support page.

If you will be using JDBC 2.0 features of WebLogic jDriver for Oracle in a multitier environment with the WebLogic RMI, T3, JTS, or pool drivers, then you must run your WebLogic Server in a JDK 1.2.x JVM.

Driver class

The class name for the WebLogic jDriver for Oracle JDBC 2.0 driver is:

    weblogic.jdbc20.oci.Driver

Driver URL

The URL for the WebLogic jDriver for Oracle JDBC 2.0 driver is:

    jdbc20:weblogic:oracle

Overview of BLOBs and CLOBs

The BLOB (Binary Large Object) and CLOB (Character Large Object) data types were made available with the release of Oracle version 8. The JDBC 2.0 specification and WebLogic jDriver for Oracle also support these data types. This section contains information on using these data types. Note that since BLOB/CLOB's are not Serializable thay cannot be used in conjunction with WebLogic's RMI Driver. Also, in WebLogic 5.1, EJB CMP does not support BLOB/CLOB's.

Transaction boundaries

BLOBs or CLOBs in Oracle behave differently than other data types in regards to transactional boundaries (statements issued before a SQL commit or rollback statement). in that a BLOB or CLOB will be come inactive as soon as a transaction is committed. If AutoCommit is set to TRUE, the transaction will be automatically committed after each command issued on the connection, including SELECT statements. For this reason you will need to set AutoCommit to false if you need to have a BLOB or CLOB available across multiple SQL statements. You will then need to manually commit (or rollback) the transactions at the appropriate time. To set AutoCommit to false, issue the following command:

    conn.setAutoCommit(false); // where conn is your connection object

BLOBs

The BLOB data type, available with Oracle version 8, allows you to store and retrieve large binary objects in an Oracle table. Although BLOBs are defined as part of the JDBC 2.0 specification, the specification does not provide methods to update BLOB columns in a table. The BEA WebLogic implementation of BLOBs, however, does provide this functionality by means of an extension to JDBC 2.0.

Connection properties

weblobic.oci.selectBlobChunkSize
  This property sets the size of an internal buffer used for sending bytes or characters to an I/O stream. When the Chunk size is reached, the driver will perform an implicit flush() operation, which will cause the data to be sent to the DBMS.
  Explicitly setting this value can be useful in controlling memory usage on the client.
  If the value of this property is not explicitly set, a default value of 65534 will be used.
  Set this property by passing it to the Connection object as a property. For example, this code fragment sets weblobic.oci.selectBlobChunkSize to 1200:
  Properties props = new Properties();
  props.put("user", "scott");
  props.put("password", "tiger");
  props.put("server", "DEMO");
  props.put ("weblobic.oci.selectBlobChunkSize","1200");
  Driver myDriver = (Driver)
  Class.forName("weblogic.jdbc20.oci.Driver").newInstance();
  Connection conn =
  driver.connect("jdbc20:weblogic:oracle:myServer", props);

weblogic.oci.insertBlobChunkSize
  This property specifies the buffer size (in bytes) of input streams used internally by the driver.
  Set this property to a positive integer to insert Blobs into an Oracle DBMS with the Blob chunking feature. By default, this property is set to zero (0), which means that BLOB chunking is turned off.

Import statements

To use the BLOB functionality described in this section, import the following classes in your client code:

    import java.sql.*;
    import java.util.*;
    import java.io.*;
    import weblogic.jdbc20.common.*;

Initializing a BLOB field

When you first insert a row containing a BLOB data type, you must insert the row with an "empty" BLOB before the field can be updated with real data. You can insert an empty BLOB with the Oracle EMPTY_BLOB() function.

To initialize a BLOB field:

1. Create a table with one or more columns defined as a BLOB data type.

2. Insert a new row with an empty BLOB column, using the Oracle EMPTY_BLOB() function:

       stmt.execute("INSERT into myTable values (1,EMPTY_BLOB()");

3. Obtain a "handle" to the BLOB column:

       java.sql.Blob myBlob = null;
       Statement stmt2 = conn.createStatement();
       stmt2.execute("SELECT myBlobColumn from myTable");
       ResultSet rs = stmt2.getResultSet();
       rs.next() {
         myBlob = rs.getBlob("myBlobColumn");
         // do something with the BLOB
       }

4. You can now write data to the BLOB. Continue with the next section, Writing binary data to a BLOB.

Writing binary data to a BLOB

To write binary data to a BLOB column:

1. Obtain a handle to the BLOB field as described above, in Initializing a BLOB field, step 3.

2. Create a InputStream object containing the binary data.

       java.io.InputStream is = // create your input stream

3. Create an output stream to which you write your BLOB data. Note that you must cast your BLOB object to weblogic.jdbc20.common.OracleBlob.

       java.io.OutputStream os =
       ((weblogic.jdbc20.common.OracleBlob) myBlob).getBinaryOutputStream();

4. Write the input stream containing your binary data to the output stream. The write operation is finalized when you call the flush() method on the OutputStream object.

       byte[] inBytes = new byte[65534]; // see note below
           int numBytes = is.read(inBytes);
       while (numBytes > 0) {
         os.write(inBytes, 0, numBytes);
         numBytes = is.read(inBytes);
       }
       os.flush();

   Note: The value [65534] in the above code presumes that you have not set the weblogic.oci.select.BlobChunkSize property whose default is 65534. If you have set this property, setting the byte[] value to match the value set in the weblogic.oci.select.BlobChunkSize property will provide the most efficient handling of the data. For more information on this property, see Connection properties.

5. Clean up:

       os.close();
       pstmt.close();
       conn.close();

Writing a BLOB object

Writing a BLOB object to a table is performed with Prepared Statements. For example, to write the myBlob object to the table myOtherTable:

    PreparedStatement pstmt = conn.preparedStatement(
        "UPDATE myOtherTable SET myOtherBlobColumn = ? WHERE id = 12");

    pstmt.setBlob(1, myBlob);

Reading BLOB data

When you retrieve a BLOB column with the getBlob() method and then use a ResultSet from a SQL SELECT statement, only a pointer to the BLOB data is returned; the binary data is not actually transferred to the client until the getBinaryStream() method is called and the data is read into the stream object.

To read BLOB data from an Oracle table:

1. Execute a SELECT statement:

       stmt2.execute("SELECT myBlobColumn from myTable");

2. Use the results from the SELECT statement.

       int STREAM_SIZE = 10;
       byte[] r = new byte[STREAM_SIZE];

       ResultSet rs = stmt2.getResultSet();
       java.sql.Blob myBlob = null;
       while (rs.next) {
         myBlob = rs.getBlob("myBlobColumn");

        java.io.InputStream readis = myBlob.getBinaryStream();

        for (int i=0 ; i < STREAM_SIZE ; i++) {
             r[i] = (byte) readis.read();
            System.out.println("output [" + i + "] = " + r[i]);
        }

3. Clean up:

       rs.close();
       stmt2.close();

   Note: You can also use a CallableStatement to generate a ResultSet. This ResultSet can then be used as shown above. See your JDK documentation under java.sql.CallableStatment for details.

Other methods

The following methods of the java.sql.Blob interface are also implemented in the WebLogic jDriver for Oracle JDBC 2.0 driver. For details, see your JDK documentation:

- getBinaryStream()

- getBytes()

- length()

The position() method is not implemented.

CLOBs

The CLOB data type, available with Oracle version 8, enables storage of large character strings in an Oracle table. Since the JDBC 2.0 specification does not include functionality to directly update CLOB columns, BEA has implemented the methods getAsciiOutputStream() (for ASCII data) and getCharacterOutputStream() (for Unicode data) to insert or update a CLOB.

Codeset support

Depending on which version of the Oracle Server and client you are using you may need to set one of the following properties by passing them to the Connection object when you establish your connection the DBMS in your Java client code.

weblogic.codeset
  This property allows you to set a codeset from within your Java code. This codeset overrides the value of any codeset specified with the Oracle environment variable NLS_LANG.

weblogic.oci.ncodeset
  This property sets the National codeset used by the Oracle server. This codeset overrides the value of any codeset specified with the Oracle environment variable NLS_NCHAR.

weblogic.oci.codeset_width
  This property tells the WebLogic jDriver for Oracle which type you are using. Note the following restrictions on codeset use:
  Oracle Version 8.0.5: only fixed width character sets are supported
  Oracle Version 8.1.5 and later: character sets may be either fixed or variable width.
  Possible Values:
  0 (for variable-width codesets)
  1 (for fixed-width codesets; this is the default value)
  2 or 3 (the width, in bytes of the codeset)

weblogic.oci.ncodeset_width
  If you are using one of Oracle's National codesets, specify the width of that codeset with this property. Note the following restrictions on codeset use:
  Oracle Version 8.0.5: only fixed width character sets are supported
  Oracle Version 8.1.5 and later: character sets may be either fixed or variable width.
  Possible Values:
  0 (for variable-width codesets)
  1 (for fixed-width codesets; this is the default value)
  2 or 3 (the width, in bytes, of the codeset)

weblogic.oci.use_clob_unicode_io
  When using CLOBS in conjunction with a character set on the Oracle server (this applies to Client version 8.1.5 only), WebLogic jDriver for Oracle converts the characters from Unicode to the character set in use by the database. The Oracle server then converts this data back into Unicode. This obvious inefficiency can be avoided by setting this property to true, in which case all communication will be made in Unicode, preventing unnecessary and expensive character set translations from occurring. The default value for this property is false.

Initializing a CLOB field

When you first insert a row containing a CLOB data type, you must insert the row with an "empty" CLOB before the field can be updated with real data. You can insert an empty CLOB with the Oracle EMPTY_CLOB() function.

To initialize a CLOB column:

1. Create a table with one or more columns defined as a CLOB data type.

2. Insert a new row with an empty CLOB column, using the Oracle EMPTY_CLOB() function:

       stmt.execute("INSERT into myTable VALUES (1,EMPTY_CLOB()");

3. Obtain an object for the CLOB column:

       java.sql.Clob myClob = null;
       Statement stmt2 = conn.createStatement();
       stmt2.execute("SELECT myClobColumn from myTable");
       ResultSet rs = stmt2.getResultSet();
       while (rs.next) {
         myClob = rs.getClob("myClobColumn");
       }

4. You can now write character data to the CLOB. If your data is in the ASCII format, Continue with the next section, Writing ASCII data to a CLOB. If your character data is in Unicode format, see Writing Unicode data to a CLOB

Writing ASCII data to a CLOB

To write ASCII character data to a CLOB column:

1. Obtain a "handle" to the CLOB as described above, in Initializing a CLOB field, step 3.

2. Create an object containing the character data:

       String s = // some ASCII data

3. Create an ASCII output stream to which you write your CLOB characters. Note that you must cast your CLOB object to weblogic.jdbc20.common.OracleClob.

       java.io.OutputStream os =
       ((weblogic.jdbc20.common.OracleClob) myclob).getAsciiOutputStream();

4. Write the input stream containing your ASCII data to the output stream. The write operation is finalized when you call the flush() method on the OutputStream object.

       byte[] b = s.getBytes("ASCII");

       os.write(b);
       os.flush();

5. Clean up:

       os.close();
       pstmt.close();
       conn.close();

Writing Unicode data to a CLOB

To write Unicode character data to a CLOB column:

1. Obtain a "handle" to the CLOB as described above, in Initializing a CLOB field, step 3.

2. Create an object containing the character data:

       String s = // some Unicode character data

3. Create a character output stream to which you write your CLOB characters. Note that you must cast your CLOB object to weblogic.jdbc20.common.OracleClob.

       java.io.Writer wr =
       ((weblogic.jdbc20.common.OracleClob) myclob).getCharacterOutputStream();

4. Write the input stream containing your ASCII data to the output stream. The write operation is finalized when you call the flush() method on the OutputStream object.

       char[] b = s.toCharArray(); // converts 's' to a character array

       wr.write(b);
       wr.flush();

5. Clean up:

       wr.close();
       pstmt.close();
       conn.close();

Writing CLOB objects

Writing a CLOB object to a table is performed with Prepared Statements. For example, to write the myClob object to the table myOtherTable:

    PreparedStatement pstmt = conn.preparedStatement(
        "UPDATE myOtherTable SET myOtherClobColumn = ? WHERE id = 12");

    pstmt.setClob(1, myClob);

Reading CLOB data

When a CLOB column is retrieved using a result set from a SQL SELECT statement, only a pointer to the CLOB data is returned; the actual data is not transferred to the client with the result set until the getAsciiStream() method is called and the characters are read in to the stream.

To read CLOB data from an Oracle table:

1. Execute a SELECT statement:

       java.sql.Clob myClob = null;
       Statement stmt2 = conn.createStatement();
       stmt2.execute("SELECT myClobColumn from myTable");

2. Use the results from the SELECT statement:

       ResultSet rs = stmt2.getResultSet();

       while (rs.next) {
         myClob = rs.getClob("myClobColumn");
         java.io.InputStream readClobis =
             myReadClob.getAsciiStream();
         char[] c = new char[26];
         for (int i=0 ; i < 26  ; i++) {
             c[i] = (char) readClobis.read();
             System.out.println("output [" + i + "] = " + c[i]);
         }
       }

3. Clean up:

       rs.close();
       stmt2.close();

   Note: You can also use a CallableStatement to generate a ResultSet. This ResultSet can then be used as shown above. See your JDK documentation under java.sql.CallableStatment for details.

Other methods

The following methods of the java.sql.Clob interface are also implemented in the WebLogic jDriver for Oracle JDBC 2.0 driver. For details, see your JDK documentation:

- getSubString()

- length()

The position() method is not implemented.

Character and ASCII Streams

Some new methods are included in the JDBC 2.0 specification that allow character and ASCII streams to be manipulated as characters rather than as bytes, as was implemented in earlier versions. The following methods for handling character and ASCII streams are implemented in WebLogic jDriver for Oracle.

Unicode character Streams

getCharacterStream()
  The java.sql.ResultSet interface uses this method for reading Unicode streams as the Java type java.io.Reader. This method replaces the deprecated getUnicodeStream() method.

setCharacterStream()
  The java.sql.PreparedStatement interface uses this method for writing a java.io.Reader object. This method replaces the deprecated setUnicodeStream() method.

ASCII character streams

getAsciiStream()
  The java.sql.ResultSet interface uses this method for reading ASCII streams as the Java type java.io.InputStream.

setAsciiStream()
  The java.sql.PreparedStatement interface uses this method for writing a java.io.InputStream object.

For details on using these methods, consult your JDK documentation.

Batch updates

Batch updates are a new feature of JDBC 2.0 that allows you to send multiple SQL update statements to the DBMS as a single unit. Depending on the application, this can provide improved performance over sending multiple update statements individually. The Batch update feature is available in the Statement interface and requires the use of SQL statements that return an update count and do not return a result set. Using Batch updates with the callableStatement or preparedStatement is not supported.

The following SQL statements can be used with Batch updates:

- INSERT INTO

- UPDATE

- DELETE

- CREATE TABLE

- DROP TABLE

- ALTER TABLE

Using Batch updates

This is the basic procedure for using Batch updates:

1. Get a connection using the WebLogic jDriver for Oracle JDBC 2.0 driver as described in Connecting to an Oracle DBMS. For this example, the connection object is called "conn".

2. Create a statement object using the createStatement() method. For example:

       Statement stmt = conn.createStatement();

3. Use the addBatch() method to add SQL statements to the batch. These statements will not be sent to the DBMS until the executeBatch() method is called. For example:

       stmt.addBatch("INSERT INTO batchTest VALUES ('JOE', 20,35)");
       stmt.addBatch("INSERT INTO batchTest VALUES ('Bob', 30,44)");
       stmt.addBatch("INSERT INTO batchTest VALUES ('Ed',  34,22)");

4. Use the executeBatch() method to send the batch to the DBMS for processing. For example:

       stmt.executeBatch();

   If any of the statements fail an exception will be thrown, and none of the statements will be executed.

Clearing the batch

You may clear a batch of statements that was created using addBatch() method with the clearBatch() method. For example:

    stmt.clearBatch();

Update counts

According to the JDBC 2.0 specification, the executeBatch() method should return an array of Integers containing the number of rows updated for each Statement. The Oracle DBMS, however, does not supply this information to the driver. The Oracle DBMS will instead return -2 for all updates.

Using with JDBC 1

If you are running your client application (or WebLogic Server, if WebLogic jDriver for Oracle is being used in the middle tier) under JDK 1.1, you may use Batch updates by casting your Statement object to weblogic.jdbc.oci.Statement. For example:

    weblogic.jdbc.oci.Statement stmt =
      (weblogic.jdbc.oci.Statement) conn.createStatement();

New date methods

The following methods have a new signature which takes a java.util.Calendar object as a parameter. java.util.Calendar allows you to specify time zone and location information that is used to translate dates. Consult your JDK API guide for details on using the java.util.Calendar class.

java.sql.ResultSet.getDate(int columnIndex, Calendar cal)
  (returns a java.sql.Date object)

java.sql.PreparedStatement.setDate
(int parameterIndex, Date x, Calendar cal)

java.sql.CallableStatement.getDate
(int parameterIndex, Calendar cal)
  (returns a java.sql.Date object)

Resources

For more information about

- using BLOBS and CLOBS, see the article Using SQL3 Datatypes, from Sun's Java Tutorial.

- WebLogic Server and JNDI, see Using WebLogic JNDI.

- installing WebLogic JDBC drivers, see Installing JDBC Drivers.

- the JDBC 2.0 specification, see New Features in the JDBC 2.0 API.

Code Examples

Oracle examples

Examples using JDBC 2.0 features
  Batch Updates
  Blobs and Clobs

Copyright © 2000 BEA Systems, Inc. All rights reserved. Required browser: Netscape 4.0 or higher, or Microsoft Internet Explorer 4.0 or higher.