Oracle® Database Express Edition 2 Day Developer Guide 10g Release 2 (10.2) Part Number B25108-01 |
|
|
View PDF |
This section provides an example of the use of a PL/SQL stored procedure with JDBC.
This section does not provide detailed information about Java and JDBC, or their use with Oracle Database Express Edition. For a brief summary of JDBC and links to resources for JDBC, see "Oracle Java Database Connectivity (JDBC)" .
This section contains the following topics:
Oracle Java Database Connectivity (JDBC) is an API that enables Java to send SQL statements to an object-relational database such as Oracle Database XE.
To run the Java program in Example D-1, you need to have Oracle Database XE and the full Java 2 Software Development Kit, Standard Edition (J2SE SDK), installed on your computer. Note that the Oracle Database XE Client is included in Oracle Database Express Edition.
See Also:
Oracle Database Express Edition 2 Day Plus Java Developer Guide for information about using Java to access and modify data in Oracle Database XE
Information about the JDBC API at
Information about the Oracle JDBC Drivers at
http://www.oracle.com/technology/tech/java/sqlj_jdbc/index.html
Information about installing Java at
Example D-1 shows the Java code that searches for and displays employee information based on the first and last names of an employee. The Java program accepts user input for the first and last names from command line arguments. The arguments can be the full names or substrings of the first and last names of the employee. When the program is executed, a connection is made to Oracle Database XE, the get_emp_info
procedure is called to search for the employee records that match the input strings, and then the results are displayed.
The PL/SQL get_emp_info
procedure is created in Example 5-15. The procedure determines the result set for the cursor variable (REF
CURSOR
) that is passed to the Java program. The package specification in Example 5-13 defines the cursor variable (my_refcur_typ
) that is declared in the get_emp_info
procedure. A cursor variable can be passed as a parameter to other packages, procedures, and functions. For information about cursor variables (REF
CURSOR
s) see "Cursor Variables (REF CURSORs)". For information about using types in package specifications, see "Accessing Types in Packages".
The Java program in Example D-1 is intended only to be an illustration of the use of a PL/SQL stored procedure with JDBC. It does not include error checking or many other Java features.
Save the Java program in Example D-1 as EmpSearch.java
. Before running the Java program in Example D-1, the PL/SQL get_emp_info
procedure in Example 5-15 must be created by the HR
user.
Example D-1 Creating a Java Program for Use With a PL/SQL Procedure
import java.sql.*; import oracle.jdbc.pool.OracleDataSource; import oracle.jdbc.*; public class EmpSearch { public static void main (String args[]) throws SQLException { // check whether there are two command-line arguments before proceeding if ( args.length < 2) { System.out.println("Enter both a first and last name as command-line arguments."); System.out.println("You can enter a complete name or an initial substring."); System.out.println("For example: java EmpSearch j doe"); } else { // connect to a local XE database as user HR OracleDataSource ods = new OracleDataSource(); ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1521/XE"); Connection conn = ods.getConnection(); // call the PL/SQL procedures with the three parameters // the first two string parameters (1 and 2) are passed to the procedure // as command-line arguments // the REF CURSOR parameter (3) is returned from the procedure String jobquery = "begin get_emp_info(?, ?, ?); end;"; CallableStatement callStmt = conn.prepareCall(jobquery); callStmt.registerOutParameter(3, OracleTypes.CURSOR); callStmt.setString(1, args[0]); callStmt.setString(2, args[1]); callStmt.execute(); // return the result set ResultSet rset = (ResultSet)callStmt.getObject(3); // determine the number of columns in each row of the result set ResultSetMetaData rsetMeta = rset.getMetaData(); int count = rsetMeta.getColumnCount(); // print the results, all the columns in each row while (rset.next()) { String rsetRow = ""; for (int i=1; i<=count; i++){ rsetRow = rsetRow + " " + rset.getString(i); } System.out.println(rsetRow); } } } }
Before you attempt to compile the Java program, make sure the CLASSPATH
has been set. For example, on a Windows computer you could use the following value for CLASSPATH
, if Oracle Database XE is installed in the default location.
.;C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib\ojdbc14.jar;C:\oraclexe\app\oracle\ product\10.2.0\server\jlib\orai18n.jar;
Compile the EmpSearch.java
Java program. For example, on a Windows computer you could use one of the following examples, depending on whether the path to javac
has been set.
javac EmpSearch.java
c:\j2sdk1.4.2_04\bin\javac EmpSearch.java
Run the program with valid command-line arguments. For example, on a Windows computer you could use one of the following examples, depending on whether the path to java
has been set. With the d
and gr
command-line arguments, the PL/SQL procedure searches for employees whose first name starts with d
and last name starts with gr
.
java EmpSearch d gr
c:\j2sdk1.4.2_04\bin\java EmpSearch d gr