BEA WebLogic Enterprise 4.2    Developer Center     

        HOME   |   SITE MAP   |   SEARCH   |   CONTACT   |   GLOSSARY  |   PDF FILES |   WHAT'S NEW 
 
        DEVELOPING APPLICATIONS    |   TABLE OF CONTENTS   |   PREVIOUS TOPIC   |   NEXT TOPIC   |   INDEX 

Using the jdbcKona/Oracle Driver

This chapter provides general guidelines for using the jdbcKona/Oracle Type 2 driver. For general notes about and an example of using the jdbcKona drivers, see Chapter 1, "Using the jdbcKona Drivers."

Data Type Mapping

Mapping of types between Oracle and the jdbcKona/Oracle driver are provided in the following table.

Oracle jdbcKona/Oracle driver

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

Note that when the PreparedStatement.setBoolean method is invoked, this method converts a VARCHAR type to "1" or "0" (string), and it converts a NUMBER type to 1 or 0 (number).

Note that the PreparedStatement.setBoolean method converts a VARCHAR type to "1" or "0" (string), and it converts a NUMBER type to 1 or 0 (number).

Connecting the jdbcKona/Oracle Driver to an Oracle DBMS

In general, to make a DBMS connection, you perform the following steps:

  1. Load the proper jdbcKona driver.

    The most efficient way to do this is to invoke the Class.forName().newInstance() method with the name of the driver class, which properly loads and registers the jdbcKona driver, as in the following example: 

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

  2. Request a JDBC connection by invoking the DriverManager.getConnection method, which takes as its parameters the URL of the driver and other information about the connection.

Note that both steps describe the jdbcKona driver, but in a different format. The full package name is period-separated, and the URL is colon-separated. The URL must include at least weblogic:jdbc:oracle, and may include other information, including server name and database name.

There are several variations on this basic pattern, which are described here for Oracle. For a full code example, see "Implementing a WLE Java Application Using the jdbcKona Drivers" on page 1-6.

Method 1

The simplest way to connect to an Oracle DBMS is by passing the URL of the driver that includes the name of the server, along with a username and a password, as arguments to the DriverManager.getConnection method, as in the following jdbcKona/Oracle example: 

Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
  DriverManager.getConnection("jdbc:weblogic:oracle:DEMO",
                             "scott",
                              "tiger");

In the preceding example, DEMO is the V2 alias of an Oracle database. Note that invoking the Class.forName().newInstance() method properly loads and registers the driver.

Method 2

You can also pass a java.util.Properties object with parameters for connection as an argument to the DriverManager.getConnection method. The following example shows how to connect to the DEMO database: 

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

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

If you do not supply a server name (DEMO in the preceding example), the system looks for an environment variable (ORACLE_SID in the case of Oracle). You can also add the server name to the URL, using the following format: 

"jdbc:weblogic:oracle:DEMO"

When you use the preceding format, you do not need to provide a "server" property.

Other Properties You Can Set for the jdbcKona/Oracle Driver

There are other properties that you can set for the jdbcKona/Oracle driver, which are covered later in this document. The jdbcKona/Oracle driver also allows setting a property -- allowMixedCaseMetaData -- to the boolean true. This property sets up the connection to use mixed case letters in invocation to DatabaseMetaData methods. Otherwise, Oracle defaults to uppercase letters for database metadata.

The following 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");

Connection conn =
        DriverManager.getConnection("jdbc:weblogic:oracle",
                                                                                                            props);

If you do not set this property, the jdbcKona/Oracle driver defaults to the Oracle default, which uses uppercase letters for database metadata.

General Notes

Always invoke the Connection.close method to close the connection when you have finished working with it. Closing objects releases resources on the remote DBMS and within your application, as well as being good programming practice. Other jdbcKona objects on which you should invoke the close method after final use include:

Waiting for Oracle DBMS Resources

The jdbcKona/Oracle driver supports the Oracle oopt() C API, 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.

You can set whether a client waits for DBMS resources, or receives an immediate exception. The following is an example: 

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

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)
          DriverManager.getConnection("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 object, and then invoke the waitOnResources method.

This functionality is described in section 4-97 of The OCI Functions for C, published by Oracle Corporation.

Autocommit

The default transaction mode for JDBC 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 following statement: 

Connection.setAutoCommit(false);

Using Oracle Blobs

The jdbcKona/Oracle driver supports two new properties to support Oracle Blob chunking:

Support for Oracle Array Fetches

With WLE Java, the jdbcKona/Oracle driver supports Oracle array fetches. With this feature support, invoking the ResultSet.next method the first time gets an array of rows and stores it in memory, rather than retrieving a single row. Each subsequent invocation of the next method reads a row from the rows in memory until they are exhausted, and only then does the next method 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. The following is an example of setting this property to 300, which means that invocations to the next method hit the database only 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");

Class.forName("weblogic.jdbc.oci.Driver").newInstance();
Connection conn =
    DriverManager.getConnection("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. 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 statement are of type LONG, the cache size will be temporarily reset to 1 (one) for the ResultSet object associated with that select statement.

Using Stored Procedures

The following sections describe how to use stored procedures:

Syntax for Stored Procedures in the jdbcKona/Oracle Driver

The syntax for stored procedures in Oracle was altered in the jdbcKona/Oracle driver examples to match the JDBC specification. (All of the examples also show native Oracle SQL, commented out, just above the correct usage; the native Oracle syntax works as it did in the past.) You can read more about stored procedures for the jdbcKona drivers in Chapter 1, "Using the jdbcKona Drivers."

Note that Oracle does not natively support binding to "?" values in an SQL statement. Instead it uses ":1", ":2", and so forth. We allow you to use either in your SQL with the jdbcKona/Oracle driver.

Binding a Parameter to an Oracle Cursor

BEA Systems, Inc. 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 ResultSet objects in an organized way. The ResultSet objects are determined at run time in the stored procedure. An example procedure follows.

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, construct CallableStatement objects with the stored procedures and register the output parameter as data type java.sql.Types.OTHER. When you retrieve the data into a ResultSet object, use the output parameter index as an argument for the getResultSet method. For example: 

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

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

Using CallableStatement

The default length of a string bound to an OUTPUT parameter of a CallableStatement object is 128 characters. If the value you assign to the bound parameter exceeds that length, you 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. The following 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();

DatabaseMetaData Methods

DatabaseMetaData is implemented in its entirety in the jdbcKona/Oracle driver. There are some variations that are specific to Oracle, which are as follows:

jdbcKona/Oracle and the Oracle 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.

The jdbcKona/Oracle driver reliably converts the values in a column to the Java type requested when a WLE Java 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. The jdbcKona/Oracle driver guarantees to return a Java object that 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 the jdbcKona/Oracle driver must decide what sort of object to return based on each value. This means that one ResultSet object may return multiple Java types from the getObject method for a given NUMBER column. A table full of integer values may all be returned as Integer from the getObject method, 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 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 the jdbcKona/Oracle driver always returns a Java object appropriate for the given precision and scale, regardless of the values shown in the following table. The following table shows the Java objects returned for each qualified NUMBER column.

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

Copyright © 1999 BEA Systems, Inc. All Rights Reserved.
Required browser version: Netscape Communicator version 4.0 or higher, or Microsoft Internet Explorer version 4.0 or higher.
Last update: July 01, 1999.