Note:

• In Oracle Database 11g Release 1 (11.1), Oracle JDBC drivers no longer support JDK 1.4.x or earlier versions.

• You can find a complete, up-to-date list of supported databases athttp://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm.

Note:

Check the InstalledDirectories and Files

Installing the Oracle Java products creates, among other things, the following directories:

• ORACLE_HOME/jdbc

• ORACLE_HOME/jlib

Check whether or not the following directories and files have been created and populated in theORACLE_HOME/jdbc directory:

• demo

  This directory contains a compressed file,demo.zip ordemo.tar. When you uncompress this compressed file, thesamples directory and theSamples-Readme.txt file are created. Thesamples directory contains sample programs, including examples of how to use SQL92 and Oracle SQL syntax, PL/SQL blocks, streams, user-defined types, additional Oracle type extensions, and Oracle performance extensions.

• doc

  This directory contains thejavadoc.zip file, which is the Oracle JDBC application programming interface (API) documentation.

• lib

  Thelib directory contains the following required Java classes:

  • orai18n.jar andorai18n-mapping.jar

    Contain classes for globalization and multibyte character sets support

  • ojdbc5.jar,ojdbc5_g.jar,ojdbc6.jar, andojdbc6_g.jar

    Contain the JDBC driver classes for use with JDK 1.5 and JDK 1.6

    Note:

    • In Oracle Database 11g Release 1 (11.1), support for a version of JDK earlier than version 1.5 has been removed. Also, theojdbc14.jar andclasses12.jar files are no longer shipped. Instead, you can use theojdbc5.jar andojdbc6.jar files, which are shipped with Oracle Database 11g.

    • If you are using JSE 6 and later, then there isno need to explicitly load the JDBC driver. This means that the Java run-time loads the driver when needed and you neednot includeClass.forName("oracle.jdbc.OracleDriver") ornew oracle.jdbc.OracleDriver() in your code. But if you are using J2SE 5, then you need to load the JDBC driver explicitly.

• Readme.txt

  This file contains late-breaking and release-specific information about the drivers, which may not have been included in other documentation on the product.

Check whether or not the following directories have been created and populated in theORACLE_HOME/jlib directory:

• jta.jar andjndi.jar

  These files contain classes for the Java Transaction API (JTA) and the Java Naming and Directory Interface (JNDI). These are required only if you are using JTA features for distributed transaction management or JNDI features for naming services.

  Note:

  These files can also be obtained from the Sun Microsystems Web site. However, it is recommended that you use the versions supplied by Oracle, which have been tested with the Oracle drivers.

Check the Environment Variables

This section describes theenvironment variables that must be set for the JDBC OCI driver and the JDBC Thin driver, focusing on the Sun Solaris, Linux, and Microsoft Windows platforms.

You must set theCLASSPATH environment variable for your installed JDBC OCI or Thin driver. Include the following in theCLASSPATH environment variable:

ORACLE_HOME/jdbc/lib/ojdbc5.jarORACLE_HOME/jlib/orai18n.jar

JDBC OCI Driver

If you are installing the JDBC OCI driver, then you must also set the following value for the library path environment variable:

• On Sun Solaris or Linux, set theLD_LIBRARY_PATH environment variable as follows:

  ORACLE_HOME/lib

  This directory contains thelibocijdbc11.so shared object library.

  Note:

  If you are running a 32-bit Java Virtual Machine (JVM) against a 64-bit client or database, then you must also addORACLE_HOME/lib32 to theLD_LIBRARY_PATH environment variable.

• On Microsoft Windows, set thePATH environment variable as follows:

  ORACLE_HOME\bin

  This directory contains theocijdbc11.dll dynamic link library.

All of the JDBC OCI demonstration programs can be run in the Instant Client mode by including the JDBC OCI Instant Client data shared library on the library path environment variable.

JDBC Thin Driver

If you are installing the JDBC Thin driver, then you do not have to set any other environment variables. However, to use the JDBC server-side Thin driver, you need to set permission.

Setting Permission for the Server-Side Thin Driver

The JDBC server-side Thin driver opens a socket for its connection to the database. Because Oracle Database enforces the Java security model, a check is performed for aSocketPermission object.

To use the JDBC server-side Thin driver, the connecting user must be granted the appropriate permission. The following is an example of how the permission can be granted for the userSCOTT:

CREATE ROLE jdbcthin;CALL dbms_java.grant_permission('JDBCTHIN', 'java.net.SocketPermission', '*', 'connect');GRANT jdbcthin TO SCOTT;

Note thatJDBCTHIN in thegrant_permission call must be in uppercase. The asterisk (*) is a pattern. You can restrict the user by granting permission to connect to only specific computers or ports.

Ensure that the Java Code Can Be Compiled and Run

To further ensure that Java is set up properly on your client system, go to thesamples directory under theORACLE_HOME/jdbc/demo directory. Now, type the following commands on the command line, one after the other, to see if the Java compiler and the Java interpreter run without error. :

javacjava

Each of the preceding commands should display a list of options and parameters and then exit. Ideally, verify that you can compile and run a simple test program, such asjdbc/demo/samples/generic/SelectExample.

Determine the Version of the JDBC Driver

You can determine the version of the JDBC driver that you installed, by calling thegetDriverVersion method of theOracleDatabaseMetaData class.

The following sample code shows how to determine the driver version:

import java.sql.*;import oracle.jdbc.*;import oracle.jdbc.pool.OracleDataSource;class JDBCVersion{  public static void main (String args[]) throws SQLException  {    OracleDataSource ods = new OracleDataSource();    ods.setURL("jdbc:oracle:thin:scott/tiger@host:port:service");    Connection conn = ods.getConnection();    // Create Oracle DatabaseMetaData object    DatabaseMetaData meta = conn.getMetaData();    // gets driver info:    System.out.println("JDBC driver version is " + meta.getDriverVersion());  }}

You can also determine the version of the JDBC driver by executing the following commands:

• java -jar ojdbc5.jar

• java -jar ojdbc6.jar

Test JDBC and the Database Connection

Thesamplesdirectory contains sample programs for a particular Oracle JDBC driver. One of the programs,JdbcCheckup.java, is designed to test JDBC and the database connection. The program queries for the user name, password, and the name of the database to which you want to connect. The program connects to the database, queries for the string "Hello World", and prints it to the screen.

Go to thesamples directory, and compile and run theJdbcCheckup.java program. If the results of the query print without error, then your Java and JDBC installations are correct.

AlthoughJdbcCheckup.java is a simple program, it demonstrates several important functions by performing the following:

• Imports the necessary Java classes, including JDBC classes

• Creates aDataSource instance

• Connects to the database

• Runs a simple query

• Prints the query results to your screen

TheJdbcCheckup.java program, which uses the JDBC OCI driver, is as follows:

/* * This sample can be used to check the JDBC installation. * Just run it and provide the connect information. It will select * "Hello World" from the database. */// You need to import the java.sql and JDBC packages to use JDBCimport java.sql.*;import oracle.jdbc.*;import oracle.jdbc.pool.OracleDataSource;// We import java.io to be able to read from the command lineimport java.io.*;class JdbcCheckup{  public static void main(String args[]) throws SQLException, IOException  {    // Prompt the user for connect information    System.out.println("Please enter information to test connection to                           the database");    String user;    String password;    String database;    user = readEntry("user: ");    int slash_index = user.indexOf('/');    if (slash_index != -1)    {      password = user.substring(slash_index + 1);      user = user.substring(0, slash_index);    }    else      password = readEntry("password: ");    database = readEntry("database(a TNSNAME entry): ");    System.out.print("Connecting to the database...");    System.out.flush();    System.out.println("Connecting...");    // Open an OracleDataSource and get a connection    OracleDataSource ods = new OracleDataSource();    ods.setURL("jdbc:oracle:oci:@" + database);    ods.setUser(user);    ods.setPassword(password);    Connection conn = ods.getConnection();    System.out.println("connected.");    // Create a statement    Statement stmt = conn.createStatement();    // Do the SQL "Hello World" thing    ResultSet rset = stmt.executeQuery("select 'Hello World' from dual");    while (rset.next())      System.out.println(rset.getString(1));    // close the result set, the statement and the connection    rset.close();    stmt.close();    conn.close();    System.out.println("Your JDBC installation is correct.");  }  // Utility function to read a line from standard input  static String readEntry(String prompt)  {    try    {      StringBuffer buffer = new StringBuffer();      System.out.print(prompt);      System.out.flush();      int c = System.in.read();      while (c != '\n' && c != -1)      {        buffer.append((char)c);        c = System.in.read();      }      return buffer.toString().trim();    }    catch(IOException e)    {      return "";    }  }}

Note:

Importing Packages

Regardless of which OracleJDBC driver you use, include theimport statements shown inTable 2-1 at the beginning of your program.

The Oracle packages listed as optional provide access to the extended functionality provided by Oracle JDBC drivers, but are not required for the example presented in this section.

Opening a Connection to a Database

First, you must create anOracleDataSource instance. Then, open a connection to the database using theOracleDataSource.getConnection method. The properties of the retrieved connection are derived from theOracleDataSource instance. If you set the URL connection property, then all other properties, includingTNSEntryName,DatabaseName,ServiceName,ServerName,PortNumber,Network Protocol, and driver type are ignored.

Specifying aDatabase URL, User Name, and Password

The following code sets the URL, user name, and password for a data source:

OracleDataSource ods = new OracleDataSource();ods.setURL(url);ods.setUser(user);ods.setPassword(password);

The following example connects userscott with passwordtiger to a database with serviceorcl through port 1521 of the hostmyhost, using the JDBC Thin driver:

OracleDataSource ods = new OracleDataSource();String url = "jdbc:oracle:thin:@//myhost:1521/orcl",ods.setURL(url);ods.setUser("scott");ods.setPassword("tiger");Connection conn = ods.getConnection();

Specifying aDatabase URL that Includes User Name and Password

The following example connects userscott with passwordtiger to a database host whose Transparent Network Substrate (TNS) entry ismyTNSEntry, using the JDBC Oracle Call Interface (OCI) driver. In this case, the URL includes the user name and password and is the only input parameter.

String url = "jdbc:oracle:oci:scott/tiger@myTNSEntry");ods.setURL(url);Connection conn = ods.getConnection();

If you want to connect using the Thin driver, then you must specify the port number. For example, if you want to connect to the database on the hostmyhost that has a TCP/IP listener on port 1521 and the service identifier isorcl, then provide the following code:

String URL = "jdbc:oracle:thin:scott/tiger@//myhost:1521/orcl");ods.setURL(URL);Connection conn = ods.getConnection();

Creating a Statement Object

Once you connect to the database and, in the process, create aConnection object, the next step is to create aStatement object. ThecreateStatement method of the JDBCConnection object returns an object of the JDBCStatement type. To continue the example from the previous section, where theConnection objectconn was created, here is an example of how to create theStatement object:

Statement stmt = conn.createStatement();

Running a Query and Retrieving a Result Set Object

To query the database, use theexecuteQuery method of theStatement object. This method takes a SQL statement as input and returns a JDBCResultSet object.

To continue the example, once you create theStatement objectstmt, the next step is to run a query that returns aResultSet object with the contents of theename column of a table of employees namedEMP:

ResultSet rset = stmt.executeQuery ("SELECT ename FROM emp");

Processing the Result Set Object

Onceyou run your query, use thenext() method of theResultSet object to iterate through the results. This method steps through the result set row by row, detecting the end of the result set when it is reached.

To pull data out of the result set as you iterate through it, use the appropriategetXXX methods of theResultSet object, whereXXX corresponds to a Java data type.

For example, the following code will iterate through theResultSet object,rset, from the previous section and will retrieve and print each employee name:

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

Thenext() method returnsfalse when it reaches the end of the result set. The employee names are materialized as JavaString values.

Closing the Result Set and Statement Objects

Youmust explicitly close theResultSet andStatement objects after you finish using them. This applies to allResultSet andStatement objects you create when using Oracle JDBC drivers. The drivers do not have finalizer methods. The cleanup routines are performed by theclose method of theResultSet andStatement classes. If you do not explicitly close theResultSet andStatement objects, serious memory leaks could occur. You could also run out of cursors in the database. Closing both the result set and the statement releases the corresponding cursor in the database. If you close only the result set, then the cursor is not released.

For example, if yourResultSet object isrset and yourStatement object isstmt, then close the result set and statement with the following lines of code:

rset.close();stmt.close();

When you close aStatement object that a givenConnection object creates, the connection itself remains open.

Making Changes to the Database

DML Operations

To perform DML (Data Manipulation Language) operations, such as INSERT or UPDATE operations, you can create either aStatement object or aPreparedStatement object.PreparedStatement objects enable you to run a statement with varying sets of input parameters. TheprepareStatement method of the JDBCConnection object lets you define a statement that takes variable bind parameters and returns a JDBCPreparedStatement object with your statement definition.

Use thesetXXX methods on thePreparedStatement object to bind data to the prepared statement to be sent to the database.

The following example shows how to use a prepared statement to runINSERT operations that add two rows to theEMP table.

    // Prepare to insert new names in the EMP tablePreparedStatement pstmt = null;try{    pstmt = conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");    // Add LESLIE as employee number 1500    pstmt.setInt (1, 1500);          // The first ? is for EMPNO    pstmt.setString (2, "LESLIE");   // The second ? is for ENAME    // Do the insertion    pstmt.execute ();    // Add MARSHA as employee number 507    pstmt.setInt (1, 507);           // The first ? is for EMPNO    pstmt.setString (2, "MARSHA");   // The second ? is for ENAME    // Do the insertion    pstmt.execute ();}finally{                if(pstmt!=null)    // Close the statement    pstmt.close();}

DDL Operations

To perform data definition language (DDL) operations, you can create either aStatement object or aPreparedStatement object. The following example shows how to create a table in the database using aStatement object.

//create table EMP with columns EMPNO and ENAMEString query;Statement stmt=null;try{    query="create table EMP " +          "(EMPNO int, " +          "ENAME varchar(50))";    stmt = conn.createStatement();    stmt.executeUpdate(query);    }finally{     //close the Statement object     stmt.close();    }

If your code involves reexecuting a DDL operation, then, before reexecuting the statement, you must prepare it again. The following example shows how to prepare your DDL statements before any reexecution:

//PreparedStatement pstmt = null;PreparedStatement tstmt = null;try{    pstmt = conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");     // Add LESLIE as employee number 1500    pstmt.setInt (1, 1500);          // The first ? is for EMPNO    pstmt.setString (2, "LESLIE");   // The second ? is for ENAME    // Do the insertion    pstmt.execute ();        tstmt = conn.prepareStatement("truncate table EMP");     tstmt.executeUpdate();      // Add MARSHA as employee number 507    pstmt.setInt (1, 507);           // The first ? is for EMPNO    pstmt.setString (2, "MARSHA");   // The second ? is for ENAME    // Do the insertion    pstmt.execute ();     tstmt.close();    tstmt = conn.prepareStatement("truncate table EMP");     tstmt.executeUpdate();    }finally{if(pstmt!=null)    // Close the statement     pstmt.close();}

Committing Changes

By default, data manipulation language (DML) operations are committed automatically as soon as they are run. This is known as theauto-commit mode. However, you can disable auto-commit mode with the following method call on theConnection object:

conn.setAutoCommit(false);

If you disable the auto-commit mode, then you must manually commit or roll back changes with the appropriate method call on theConnection object:

conn.commit();

or:

conn.rollback();

ACOMMIT orROLLBACK operation affects all DML statements run since the lastCOMMIT orROLLBACK.

Closing the Connection

Youmust close the connection to the database after you have performed all the required operations and no longer require the connection. You can close the connection by using theclose method of theConnection object, as follows:

conn.close();

PL/SQL Stored Procedures

Oracle JDBC drivers support the processing of PL/SQL stored procedures and anonymous blocks. They support PL/SQL block syntax and most of SQL92 escape syntax. The following PL/SQL calls would work with any Oracle JDBC driver:

// SQL92 syntaxCallableStatement cs1 = conn.prepareCall                       ( "{call proc (?,?)}" ) ; // stored procCallableStatement cs2 = conn.prepareCall                       ( "{? = call func (?,?)}" ) ; // stored func// PL/SQL block syntaxCallableStatement cs3 = conn.prepareCall                       ( "begin proc (?,?); end;" ) ; // stored procCallableStatement cs4 = conn.prepareCall                       ( "begin ? := func(?,?); end;" ) ; // stored func

As an example of using the Oracle syntax, here is a PL/SQL code snippet that creates a stored function. The PL/SQL function gets a character sequence and concatenates a suffix to it:

create or replace function foo (val1 char)return char asbegin   return val1 || 'suffix';end;

The function invocation in your JDBC program should look like the following:

OracleDataSource ods = new OracleDataSource();ods.setURL("jdbc:oracle:oci:@<hoststring>");ods.setUser("scott");ods.setPassword("tiger");Connection conn = ods.getConnection();CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");cs.registerOutParameter(1,Types.CHAR);cs.setString(2, "aa");cs.executeUpdate();String result = cs.getString(1);

Java Stored Procedures

You can use JDBC to call Java stored procedures through the SQL and PL/SQL engines. The syntax for calling Java stored procedures is the same as the syntax for calling PL/SQL stored procedures, presuming they have been properly published. That is, you have written call specifications to publish them to the Oracle data dictionary. Applications can call Java stored procedures using the Native Java Interface for direct invocation ofstatic Java methods.

See Also:

• Appendix C, "JDBC Error Messages"

• Oracle Database Error Messages

Note: