|
|
|
|
|
|
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 Using the jdbcKona Drivers.
Mapping of types between Oracle and the jdbcKona/Oracle driver are provided in the following table.
Data Type Mapping
Oracle |
jdbcKona/Oracle driver |
|---|---|
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).
In general, to make a DBMS connection, you perform the following steps.
Note:
See the section Obtaining Connections from a WLE Connection Pool for more information about an alternative way of connecting to the DBMS.
Connecting the jdbcKona/Oracle Driver to an Oracle DBMS
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 for NT and Solaris systems:
Class.forName("weblogic.jdbc20.oci734.Driver").newInstance();
On an HP-UX system, the example is as follows:
Class.forName("weblogic.jdbc20.oci804.Driver").newInstance();
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 Using the jdbcKona Drivers.
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.jdbc20.oci734.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle:DEMO",
"scott",
"tiger");
Note: On an HP-UX system, the first line of the previous example would be as follows:
Class.forName("weblogic.jdbc20.oci804.Driver").newInstance();
In the example, DEMO is the V2 alias of an Oracle database. Note that invoking the Class.forName().newInstance() method properly loads and registers the driver.
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.jdbc20.oci734.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle",
props);
Note: On an HP-UX system, the Class.forName line in the previous example would be as follows:
Class.forName("weblogic.jdbc20.oci804.Driver").newInstance();
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.
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.
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:
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.
Note:
In the driver classpath examples, the format is:
weblogic.jdbc20.ociXXX.Driver
Where XXX
is the version of the Oracle database: 734
for version 7.3.4, or 804
for version 8.0.4 (HP-UX systems), or 815
for version 8.1.5.
java.util.Properties props = new java.util.Properties();
Note:
On an HP-UX system, the Class.forName
line in the previous example would be as follows:
Class.forName("weblogic.jdbc20.oci804.Driver").newInstance();
The waitOnResources()
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.jdbc20.oci[version].Connection
object, and then invoke the waitOnResources
method (where [version]
is 734, or 804, or 815).
This functionality is described in section 4-97 of The OCI Functions for C, published by Oracle Corporation.
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); The jdbcKona/Oracle driver supports two new properties to support Oracle Blob chunking:
Waiting for Oracle DBMS Resources
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "goldengate");
Class.forName("weblogic.jdbc20.oci734.Driver").newInstance();
// You must cast the Connection as a
// weblogic.jdbc20.ociXXX.Connection
// to take advantage of this extension
Connection conn =
(weblogic.jdbc.oci734.Connection)
DriverManager.getConnection("jdbc:weblogic:oracle", props);
// After constructing the Connection object, immediately call
// the waitOnResources method
conn.waitOnResources(true);Autocommit
Using Oracle Blobs
This property affects the buffer size of input streams bound to a PreparedStatement object. Blob chunking requires an Oracle 7.3.x or higher Oracle Server; to use this property, you must be connected to an Oracle Server that supports this feature.
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 0 (zero), which means that BLOB chunking is turned off.
This property sets the size of output streams associated with a JDBC ResultSet object. The mechanism for piecewise selects does not have the same use restrictions as that for Blob inserts, so this property is set to 65534 by default. It is not necessary to turn this property off.
Set this property to the size of the desired output stream, in bytes.
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();
Note:
On an HP-UX system, the Class.forName
line in the previous example would be as follows:
Class.forName("weblogic.jdbc20.oci804.Driver").newInstance();
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.
The following sections describe how to use stored procedures:
Support for Oracle Array Fetches
props.put("user", "scott");
props.put("password", "tiger");
props.put("server", "DEMO");
props.put("weblogic.oci.cacheRows", "300");
Class.forName("weblogic.jdbc20.oci734.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle",
props);Using Stored Procedures
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 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.
BEA Systems, Inc. has created an extension to JDBC, weblogic.jdbc20.oci[version].CallableStatement,
that allows you to bind a parameter for a stored procedure to an Oracle cursor (where [version]
is 734
, or 804
, or 815
. 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 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.jdbc20.oci734.CallableStatement cstmt = Note that the default size of an Oracle-stored procedure string is 256K.
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 = DatabaseMetaData is implemented in its entirety in the jdbcKona/Oracle driver. There are some variations that are specific to Oracle, which are as follows:
Syntax for Stored Procedures in the jdbcKona/Oracle Driver
Binding a Parameter to an Oracle Cursor
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;
/
(weblogic.jdbc20.oci734.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.jdbc20.oci734.CallableStatement cstmt2 =
(weblogic.jdbc20.oci734.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.jdbc20.oci734.CallableStatement cstmt3 =
(weblogic.jdbc20.oci734.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); Using CallableStatement
conn.prepareCall("BEGIN testproc(?); END;");
cstmt.registerOutputParameter(1, Types.VARCHAR, 256);
cstmt.execute();
System.out.println(cstmt.getString());
cstmt.close();DatabaseMetaData Methods
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.
jdbcKona/Oracle and the Oracle NUMBER Column
Column Definition |
Returned by getObject() |
|---|---|
|
|
|
Copyright © 1999 BEA Systems, Inc. All rights reserved.
|