WebLogic jDriver for Oracle (Deprecated)
![]() |
![]() |
![]() |
![]() |
![]() |
![]() |
Note: The WebLogic jDriver for Oracle is deprecated and will be removed in a future release. BEA recommends that you use the BEA WebLogic Type 4 JDBC Oracle driver. For more information, see BEA WebLogic Type 4 JDBC Drivers.
This section walks you through the basic tasks associated with a simple application. It also includes a code example and a list of unsupported methods:
When performing transactions with WebLogic Server, there are differences in some basic tasks, depending on whether you are using local or distributed transactions. These transactions are as follows:
For more information about distributed transactions, see Using WebLogic jDriver for Oracle/XA in Distributed Transactions.
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.*;
import javax.sql.Datasource; // required only if using DataSource
// API to get connections
import javax.naming.*; // required only if using JNDI
// to look up DataSource objects
The WebLogic Server 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.
When running a WebLogic Server client using the driver provided with WebLogic Server you must put the following directory in your CLASSPATH
:
%WL_HOME%\server\lib\weblogic.jar
Which driver class name and URL you use depends on these factors:
You must also specify the correct driver version in your system's path. For more information, see Setting Up the Environment for Using WebLogic jDriver for Oracle.
When using the driver in normal (non-XA) mode:
When using the driver in XA mode:
You make connections from your application to an Oracle DBMS using either a two-tier or multi-tier connection, as described in the following sections.
To make a two-tier connection from your application to an Oracle DBMS using WebLogic Server, complete the following procedure. For more information on connections, see Configuring a Connection Pool with WebLogic Server Software.
java.sql.Driver
object. If you are using an XA driver, use the Datasource API, but not the java.sql.Driver
API. For example:
Driver myDriver = (Driver)Class.forName
("weblogic.jdbc.oci.Driver").newInstance();
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 preceding example) refers to an entry in the tnsnames.ora
file, which is located in your Oracle client installation. The server name defines host names and other information about an Oracle database. If you do not supply a server name, the system looks for an environment variable (ORACLE_SID
in the case of Oracle). You may also add the server name to the URL, using the following format:
"jdbc:weblogic:oracle:DEMO"
If you specify a server with this syntax, you do not need to provide a server property.
You can also set properties in a single URL, for use with products such as PowerSoft's PowerJ.
Driver.connect()
method. This method 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 the following string: jdbc:weblogic:oracle
. It may also include other information, such as the server host name and the database name.
To make a connection from your application to an Oracle DBMS in a WebLogic Server multi-tier configuration, complete the following procedure:
try {
Context ctx = new InitialContext();
javax.sql.DataSource ds
= (javax.sql.DataSource) ctx.lookup ("myDataSource");
} catch (NamingException ex) {
// lookup failed
}
try {
java.sql.Connection conn = ds.getConnection();
} catch (SQLException ex) {
// obtain connection failed
}
For more information, see Configuring and Using DataSources.
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);
The Connection object is an important part of the application. The Connection class has constructors for many fundamental database objects that you use throughout the application; in the examples that follow, for instance, 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.
The defaults for autocommit are described in the following table:.
The most fundamental task in database access is to retrieve data. To retrieve data with WebLogic Server, complete the following three-step procedure:
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();
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 were 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();
Note: When inserting or updating a value for a varchar2, if you try to insert an empty string (""
), Oracle interprets the value as NULL
. If there is a NOT NULL
restriction on the column in which you are inserting the value, the database throws the ORA-01400 error: Cannot insert NULL into
column name
.
When you initially set a value for a variable in a Prepared Statement, the data type is bound to the variable. When you reuse a Prepared Statement, either directly in your application or a cached version from the statement cache, the statement requires that you use the same data type for the variable value. This is an Oracle OCI limitation: you cannot dynamically change a bound variable type. If the data type is not the same as the bound data type, the WebLogic jDriver automatically converts the data type to the bound data type if possible. Automatic conversion is limited to conversions listed in Table B-5 of the JDBC 3.0 Specification. For example, the driver can convert a string to many different types, but a Blob cannot be converted. It must be used as a Blob.
You can download the JDBC 3.0 specification from the JDBC page on the Java Web site.
Note: During data type conversion, it is possible that your data can be truncated. This occurs when the bound data type cannot accommodate the data in a data type used subsequently for the same variable. For example, if you bind a variable with an int
data type and then use the setFloat
method to update a value with several decimal places, the decimal places in the value will be truncated when it is converted to an int
.
By default, WebLogic Server caches Prepared Statements in the statement cache for each connection in a connection pool. When you use a Prepared Statement that is identical to a Prepared Statement in the statement cache, your application will use the cached version of the statement. If the data types for variables in your current application do not match the bound data types for the variables in the cached Prepared Statement, the WebLogic jDriver automatically converts the data types to match the bound data types, as described above. For more information about the statement cache, see Statement Cache in the Administration Console Online Help
The type of transaction you use with WebLogic Server determines how you use stored procedures and functions:
First, 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();
Use a JDBC Statement to create a stored procedure or function, and then 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 Server.
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.
The following example uses a JDBC Statement to create an Oracle stored procedure and then executes the stored procedure with a CallableStatement. The example uses 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();
The following example uses 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();
The 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, set up the three parameters to the CallableStatement:
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);
Execute the stored procedure and check the return value to see if the ResultSet is empty. If it is not, 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();
}
After you 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();
There are occasions when 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 create 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();
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.
Oracle also provides array fetching to its clients, and jDriver for Oracle supports this feature. By default, jDriver for Oracle will array-fetch up to 100 rows from the DBMS. This number can be altered via the property weblogic.oci.cacheRows
.
By using the above methods, a WebLogic JDBC query for 100 rows will make only 4 calls from the client to WebLogic, and for only one of those will WebLogic actually go all the way to the DBMS for data. For more information, see Support for Oracle Array Fetches.
The following code fragments illustrate the structure for a JDBC application. The code example shown here includes retrieving data, displaying metadata, inserting, deleting, and updating data, and calling 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
}
}
}
}
For more Oracle code examples, see the examples.jdbc.oracle package provided with WLS in the samples/examples directory.
Although WebLogic Server supports all JDBC 2.0 methods, the WebLogic jDriver for Oracle does not support all JDBC 2.0 methods. If you need to use these methods, you can use another JDBC driver to connect to your database, such as the Oracle Thin Driver. Table 3-2 lists unsupported JDBC 2.0 methods in the WebLogic jDriver for Oracle.
![]() ![]() |
![]() |
![]() |