Configuring Oracle8 JDBC Drivers

The configuration of the JDBC drivers is covered in the installation, but is so simple it can be summed up in two steps:

1.  Set the PATH to [ORACLE_HOME/BIN], where ORACLE_HOME = your machine’s Oracle home directory (for example, c:\ORANT, C:\ORAWIN95, and so forth).

2.  Set the CLASSPATH to [ORACLE_HOME/JDBC/LIB/classes102.zip or classes111.zip]. The Zip files depend on whether you are using the JDK (Java Development Kit) v1.0.2 or 1.1+.

Testing Connectivity

Here are the three steps for using JDBC/OCI8:

1.  You may specify the JDBC drivers you want to use at a machine level via the parameter jdbc.drivers=my.substitute.Driver in your Java code to make sure the appropriate driver is loaded (refer to #1 in Listing 26.1). Alternatively, you may place the same statement in the ~/.hotjava/properties file on your local machine, which is not recommended for most situations.

2.  Connect to the database by including the statement at #2 in Listing 26.1. Although the example shows one method to connect, there are four alternatives that may be used:

   1. Connection conn =  DriverManager.getConnection 

(“jdbc;oracle;scott/tiger”); 

 

   2. Connection conn =  DriverManager.getConnection 

(“jdbc;oracle:”,”scott”,”tiger”); 

 

   3. Connection conn =  DriverManager.getConnection 

(“jdbc;oracle:@database”,”scott”,”tiger”); 

   4. java.util.Properties info = new java.util.Properties(); 

     info.addProperty (“user”, “scott”); 

     info.addProperty (“password”,”tiger”); 

     Connection conn =  DriverManager.getConnection 

(“jdbc;oracle:”,info); 

3.  Issue queries and receive results using standard JDBC syntax, as demonstrated in #3 and 4 in Listing 26.1.

Listing 26.1. (Numbering is for reference only)

   import java.sql.*; 

 

   class Employee 

   { 

     public static void main (String args []) 

          throws SQLException, ClassNotFoundException 

     { 

   // #1 

       // Load the Oracle JDBC driver 

       Class.forName (“oracle.jdbc.driver.OracleDriver”); 

 

       // Connect to the database 

       // You can put a database name after the @ sign in the 

          connection URL 

       // we have used “local” as our database name.  Scott and tiger 

          are the user 

       // name and password 

   // #2 

       Connection conn =  DriverManager.getConnection 

       (“jdbc;oracle;oci8:@local”, “scott”, “tiger”); 

       Statement stmt = conn.createStatement (); 

   // #3 

       ResultSet rset = stmt.executeQuery (“select username from 

       all_users”); 

   // #4 

       while (rset.next () ) 

            System.out.println (rset.getString (1)); 

       stmt.close (); 

     } 

} 

Capabilities and Limitations

Although JDBC is a universal data access method for Java, there are several capabilities and limitations that are worth mentioning.

Capabilities

Firewalls: Currently, Net8 (including JDBC Thin drivers) has the capability to connect through much of the most popular firewall software (Checkpoint, SunSoft, CISCO Systems, Milkyway Networks, Trusted Information Systems, Raptor, Secure Computing Corporation, and Global Internet). If you require the capability of connecting through a firewall, you should contact the firewall vendor to see what configuration is needed.

Considerations

•  SQLJ & JDBC drivers Make sure all machines are configured with Net8 and the TNSNAMES.ORA or Names Server configuration to connect to the appropriate databases and database versions.

•  JDBC Thin drivers Make sure that any changes to Oracle database listeners, IP addresses, or DNS names are reflected in your Java code. This is due to the static coding of connection properties when using JDBC Thin drivers.

JDBC, General

•  Errors: JDBC errors will be formatted as if they are from an Oracle7 database, even if you are connecting to an Oracle8 database.

•  CursorName: Oracle JDBC drivers do not support the getCursorName and setCursorName calls.

•  Catalog Arguments to DatabaseMetaData Calls: There is no Oracle equivalent of the JDBC catalog arguments to DatabaseMetaData calls. Oracle JDBC drivers ignore catalog arguments.

•  SQL92 Outer Join Escapes: Use the (+) Oracle syntax for outer joins.

•  PL/SQL BOOLEAN and RECORD types: These are not supported in the Oracle JDBC drivers.

•  IEEE 754 Floating-Point Compliance: There can be small differences between the results of computations performed by Oracle and Java.

JDBC Thin

Database Version Support The Thin JDBC driver supports only Oracle7.2 database versions and later; it can also access Oracle8 databases.

Oracle-Specific Functions

Although JDBC covers most functionality needed to access Oracle8, there are some Oracle-specific capabilities built into Oracle’s JDBC drivers.

Refcursors as ResultSets

You can convert a REFCURSOR value returned by a PL/SQL block into a ResultSet. JDBC enables you to call a stored procedure that executes a query and returns a ResultSet. The following example demonstrates the use of REFCURSORs. Make sure to add import oracle.jdbc.driver.*; to the beginning section of your Java code so that the Oracle JDBC driver classes are imported for use in your code.

CallableStatement callStmt; 

ResultSet resultCursor; 

 

// Use a PL/SQL block to open the cursor 

callStmt = conn.prepareCall(“begin open ? for select cust_name from 

customer; end;”); 

 

callStmt.registerOutParameter (1, OracleTypes.CURSOR); 

callStmt.execute (); 

resultCursor = ((OracleCallableStatement)callStmt).getCursor (1); 

 

// Use the cursor like a ResultSet 

while (resultCursor.next ()) 

  {System.out.println (resultCursor.getString (1));}