PL/SQL Stored Procedure Sample

Table of Content

• Overview of the Sample Application
• Required Software
• Application Set-up and Configuration
• Running the Application 
• From Oracle9i JDeveloper 
• From JDK for Windows
• From JDK for Linux
• Description of Sample Files

This application show how to access PLSQL stored procedures from the database using JDBC.

Working of the Sample

When the application is invoked, it displays the connection status and errors messages,if any, in the status bar. If the connection was successful, the application retrieves flight codes from FLIGHT_DEPARTURES table and populates the Flight codes combo box. The user can selects a flight code, and enters a departure time.

When the user presses the "CALL PL/SQL STORED PROCEDURE", a PLSQL stored procedure, namely NXT_FLIGHT_TIME_and_NUROUTES, is invoked that returns the next departure time for the flight and also the total number of routes available for the flight code. These informations are displayed in TextFields.

Here is the code for calling a PLSQL procedure. You can find more details of the code in PLSQLSample.java file under src/oracle/otnsamples/jdbc/plsql folder. Look into Description of Sample Files section for folder and file details.

private void callPlsqlProc(String flightCode, int departureTime) {
......................
......................

  // Call PLSQL Stored Procedure
  // Prepare callable Statement to call PL/SQL Stored Procedure
  // Note that to invoke a PL/SQL Stored Function, you can still 
  // use a Callable Statement which takes following form :
  // CallableStatement stmt =
  //     connection.prepareCall("begin ? = fn(?,?,..); end;");
  stmt = connection.prepareCall(
      "begin nxt_flight_time_and_num_routes(?,?,?); end;");  
  // Binds the parameter types
  stmt.setString(1, flightCode); // Bind 1st parameter
  stmt.setInt(2, departureTime); // Bind 2nd parameter
  // 2nd parameter is IN OUT parameter
  stmt.registerOutParameter(2, Types.INTEGER);
  // 3rd parameter is OUT paremeter
  stmt.registerOutParameter(3, Types.INTEGER);    
  // Execute the callable statement
  stmt.execute();
  departureTime = stmt.getInt(2); // Get Next DepartureTime
  numRoutes = stmt.getInt(3);     // Get total number of routes
......................
......................

• Oracle9i JDeveloper or higher ( Note: JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or
  JDK1.2.x (can be downloaded from here )
• Oracle9i (Database) or higher running SQL*Net TCP/IP listener. This can be downloaded from here.
• Oracle9i JDBC Driver. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it could be downloaded from here .
  

The section has instructions for step-by-step instructions to set-up this application. This will be discussed in the following subsections

Install the sample

Database setup

Install the sample

 Step 1. Unjar  PLSQLSample.jar using Winzip, or using the following command:

> jar xvf PLSQLSample.jar 

This creates a directory PLSQLSample.

 Step 2. Edit Connection.properties file. Change the hostname, SID, port number, user name and password to those of the database instance where you have created the tables and the PLSQL procedure.

Database Setup

The application requires the database tables used in the sample to be installed. The application also requires the PL/SQL Stored Procedure nxt_flight_time_and_num_routes to be present in the database. The parameters to the function are as follows:

To create the tables and the PLSQL procedure, run SQL*Plus, connect to your database as any user, and execute the plsqlSample.sql located at PLSQLSample\config directory as shown below:

sql>@plsqlSample.sql

This creates the tables FLIGHT_DEPARTURES and FLIGHT_ROUTES and the PLSQL procedure.

This sample application can be run in 3 different ways listed below.

• From Oracle9i JDeveloper
• From JDK for Windows
• From JDK for Linux

From Oracle9i JDeveloper

• Open Oracle9i JDeveloper and use File/Open option to select the plsqlSample.jws from the PLSQLSample directory.
• Next, select Project/Make plsqlSample.jpr from main menu.
• Now, select Run/Run plsqlSample.jpr from main menu to run the application.

From JDK for Windows

This section will describe steps to run the application from console using JDK on Windows. The sample can be run either manually or using a script file .

Run application using batch File: run.bat provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.bat from the command prompt, from PLSQLSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set before running run.bat file.
Example:

D:\PLSQLSample> set JDBC_HOME=d:\oracle9i\jdbc\lib
D:\PLSQLSample> set JAVA_HOME=d:\jdk1.3.1
D:\PLSQLSample> run

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar
• Also add PLSQLSample directory where Connection.properties exists and current directory to the CLASSPATH
  Example: 
  D:\PLSQLSample>set CLASSPATH=D:\oracle9i\jdbc\lib\classes12.zip;D:\PLSQLSample;.
  
• From the directory PLSQLSample\src\oracle\otnsamples\jdbc\plsql, compile all the java files using javac:
  Example: 
  D:\PLSQLSample\src\oracle\otnsamples\jdbc\plsql>javac -d . *.java
  
• Run the class file using java from the same PLSQLSample\src\oracle\otnsamples\jdbc\plsql directory
  Example:
  D:\PLSQLSample\src\oracle\otnsamples\jdbc\plsql>
  java oracle.otnsamples.jdbc.plsql.PLSQLSample

From JDK for Red Hat Linux Advanced Server release 2.1

This section will describe steps to run the application from console using JDK on Linux. The sample can be run either manually or using a script file .

Run application using batch File: run.sh provided:

By setting few environment variables, the sample application could be directly run by just executing the batch file: run.sh from the command prompt, from PLSQLSample directory. Environmental variables JAVA_HOME and JDBC_HOME have to be set, else the user will be prompted to enter values.

• Go to PLSQLSample directory and from the $ prompt use the command below to give execute permission to the file.
  $chmod 777 run.sh
• Now run the file:
  $sh run.sh

Running the application manually:

• Set CLASSPATH to include Oracle9i JDBC Driver file: classes12.zip or classes12.jar or ojdbc14.jar
• Also add PLSQLSample directory where Connection.properties exists and current directory to the CLASSPATH.
  Example:
  $export CLASSPATH=/home1/jdbc/lib/classes12.zip:/home1/PLSQLSample:.
• From the directory PLSQLSample/src/oracle/otnsamples/jdbc/plsql, compile all the java files using javac:
  Example:
  $javac -d . *.java
• Run the class file using java from the same PLSQLSample/src/oracle/otnsamples/jdbc/plsql directory.
  Example:
  $java oracle.otnsamples.jdbc.plsql.PLSQLSample

Please enter your comments about this sample in the OTN Sample Code Discussion Forum.