SQLJ: PL/SQL Stored Procedure Sample

Table of Content

• Overview of the Sample Application
• Required Software
• Notations Used
• 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 SQLJ.

SQLJ enables applications programmers to embed SQL operations in Java code. A SQLJ program is a Java program containing embedded SQL statements that comply with the ISO standard SQLJ Language Reference syntax.

SQLJ consists of a translator and a runtime component (translator.jar/zip and runtime12ee.jar/zip) and is smoothly integrated into the development environment. The translation, compilation, and customization take place in a single step when the front-end utility sqlj is run. The translation process replaces embedded SQL with calls to the SQLJ runtime, which implements the SQL operations. When the end user runs the SQLJ application, the runtime is invoked to handle the SQL operations.

SQLJ runs on top of JDBC. To access an Oracle database, you would typically use an Oracle JDBC driver. In order to run SQLJ programs, apart from SQLJ classes, JDBC classes should be present in the system CLASSPATH. SQLJ code is written and saved in *.sqlj files and should be translated to *.java files before compiling using the front-end utility sqlj.

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 OTN_FLIGHT_DEPARTURES table and populates the Flight codes combo box. The user can select a flight code, and enter a departure time.

When the user presses the button "CALL PL/SQL STORED PROCEDURE", the PL/SQL stored procedure, namely nxt_flight_time_and_num_routes, is invoked that returns the next departure time for the flight and also the total number of routes available for the flight code. All these details are displayed in TextFields.

Here is the code for calling a PL/SQL procedure. You can find more details of the code in SqljPlsqlSample.sqlj file under src/oracle/otnsamples/sqlj/plsql folder. Look into Description of Sample Files section for folder and file details.

 /**
  *  This Method accesses PL/SQL Stored Procedure viz..
  *  nxt_flight_time_and_num_routes using SQLJ.
  *  Note that if there is no next immediate flight departure time for a flight,
  *  then the procedure returns -1 into flight_time variable.
  **/
  private void callPlsqlProc(String FlightCode, int DepartureTime) {
    int NumRoutes = 0;
    try {
      // Embedded SQL: This SQLJ Statement calls a PL/SQL Stored Procedure:
      // nxt_flight_time_and_num_routes. Note that :
      //  a) Class Variable FlightCode is binded as IN parameter.
      //  b) Class Variable DepartureTime is binded as IN OUT parameter.
      //  c) Class Variable NumRoutes is binded as OUT parameter.

      //  To invoke PL/SQL Stored Procedure, the following SQLJ Call may be used.
      //  #sql result = { VALUES(fn(:arg1,:arg2)) };
      #sql{CALL nxt_flight_time_and_num_routes(:IN FlightCode,
                                               :INOUT DepartureTime, 
                                               :OUT NumRoutes) };
    }catch (SQLException ex) {// Catch SQL Errors.
       ..........
    }
  }			
			

• Oracle9i JDeveloper or higher ( Note: JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.2.x or above. This can be downloaded from here.
• Oracle9i (Database) or higher running SQL*Net TCP/IP listener. This can be downloaded from here.
• Oracle9i SQLJ Translators, Release 9.2.0.1.0. This can be downloaded from here.
• Oracle JDBC Driver, Release 2 (9.2.0.1). This can be downloaded from here .
  

Note: Both JDBC and SQLJ libraries are available with Oracle Database or client installation and need not be downloaded separately.

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  SqljPlsqlSample.jar using Winzip, or using the following command:

> jar xvf SqljPlsqlSample.jar 

This creates a directory SqljPlsqlSample

 Step 2. Edit Connection.properties file. Change the HostName, Port, SID, UserName and Password to connect to the database instance where you will create the tables and the PL/SQL 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 procedure are as follows:

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

sql>@plsqlSample.sql

This creates the tables OTN_FLIGHT_DEPARTURES and OTN_FLIGHT_ROUTES and the PL/SQL 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 SqljPlsqlSample.jws from the SqljPlsqlSample directory.
• Next, select Project/Make SqljPlsqlSample.jpr from main menu.
• Now, select Run/Run SqljPlsqlSample.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 SqljPlsqlSample directory. Environmental variables JAVA_HOME and JDBC_LIB, SQLJ_LIB, SQLJ_EXE_HOME have to be set before running run.bat file. Please look into Notations sections for more details on these environmental variables.

Note: If you have already run the sample application using JDeveloper, you will need to delete *.generated.java files that would have been created by JDeveloper.
Example:

D:\SqljPlsqlSample> set SQLJ_EXE_HOME=d:\sqlj\bin
D:\SqljPlsqlSample> set SQLJ_LIB=d:\sqlj\lib
D:\SqljPlsqlSample> set JDBC_LIB=d:\oracle9i\jdbc\lib
D:\SqljPlsqlSample> set JAVA_HOME=d:\jdk1.3.1
D:\SqljPlsqlSample> run

Running the application manually:

• Set CLASSPATH to include:
  • Oracle9i JDBC Driver file: classes12.jar/zip
  • Oracle9i SQLJ files: translator.jar/zip and runtime12ee.jar/zip
  • SqljPlsqlSample directory where Connection.properties exists(the current directory).
    Example: 
    D:\SqljPlsqlSample>set CLASSPATH=d:\sqlj\lib\translator.jar;d:\sqlj\lib\runtime12ee.jar;
    D:\oracle9i\jdbc\lib\classes12.zip;.
    
• Make sure that Java and SQLJ(where sqlj is found) are in the PATH.
  Example: D:\SqljPlsqlSample>set PATH=.;d:\jdk1.3.1\bin;d:\sqlj\bin;%PATH%
  
• From the directory SqljPlsqlSample, translate all *.sqlj files to *.java files using sqlj:
  Example: 
  D:\SqljPlsqlSample>sqlj -compile=false -status src\oracle\otnsamples\sqlj\plsql\*.sqlj
• From the same SqljPlsqlSample directory, now compile all *.java files using javac:
  Example: 
  D:\SqljPlsqlSample>javac -d . src\oracle\otnsamples\sqlj\plsql\*.java
  
• Run the class file using java from the same SqljPlsqlSample directory.
  Example:
  D:\SqljPlsqlSample>java oracle.otnsamples.sqlj.plsql.SqljPlsqlSample
  

From JDK for Linux

This section will describe steps to run the application from console using JDK on Red Hat Linux Advanced Server Release 2.1. 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 script file: run.sh from the command prompt, from SqljPlsqlSample directory. The user will be prompted to enter the environmental variables JAVA_HOME, SQL_EXE, SQLJ_LIB and JDBC_LIB when the script is run. Please look into Notations sections for more details on these environmental variables.

• Go to SqljPlsqlSample directory and from the $ prompt, use the command below to run the script file:
  $sh run.sh

Running the application manually:

• Oracle9i JDBC Driver file: classes12.jar/zip
• Oracle9i SQLJ classes: translator.jar/zip and runtime12ee.jar/zip
• SqljPlsqlSample directory where Connection.properties exists(the current directory).
  Example:
  $export CLASSPATH=/home1/idcotn/download/jdbc/lib/classes12.zip:
  /home1/idcotn/download/sqlj/lib/translator.jar:
  /home1/idcotn/download/sqlj/lib/runtime12ee.jar:.
• Make sure that Java and SQLJ (where sqlj is found) are in the PATH.
  Example: $export PATH=/usr/java/jdk1.3.1_02/bin:/home1/idcotn/download/sqlj/bin:$PATH
  
• From the directory SqljPlsqlSample, translate all *.sqlj files to *.java files using sqlj:
  Example: 
  $sqlj -compile=false -status /src/oracle/otnsamples/sqlj/plsql/*.sqlj
• From the directory SqljPlsqlSample, now compile all the java files using javac:
  Example:
  $javac -d . /src/oracle/otnsamples/sqlj/plsql/SqljPlsqlSample/*.java
• Run the class file using java from the same SqljPlsqlSample directory.
  Example:
  $java oracle.otnsamples.sqlj.plsql.SqljPlsqlSample

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