SQLJ: REF CURSOR Sample Application

Table Of Contents 

• 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 sample shows how to retrieve and process a REF CURSOR 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.

REF CURSOR, an Oracle-specific datatype identifies a reference to a cursor variable. A cursor variable holds the memory location (address) of a query work area rather than the contents of the area. So, declaring a cursor variable creates a pointer. In SQL, a pointer has the datatype REF x where REF is short for REFERENCE and x represents the entity that is being referenced. Since many cursor variables might exist to point to many work areas, REF CURSOR can be thought of as a category or "datatype specifier" that identifies many different cursor variables.

This sample application simulates an Airlines display form in which the Airline code, Name and Partner details are displayed by the application. It creates a table OTN_AIRLINES and a PLSQL function that returns a REF CURSOR.This PLSQL function is called from the sample, and the REF CURSOR returned by the function is processed using SQLJ clauses.

Working of the Sample

The sample application uses a database table OTN_AIRLINES. When the application is invoked, the table is created if it does not exist. It also creates a PLSQL package called DEMO_REFCURSOR, having a function called getRefCursor that returns a REF CURSOR. The status of the connection is shown in the status bar. Errors if any, are shown in the status bar.

When the user clicks on the "Get first 5 rows" button, the application makes a call to the PLSQL function: DEMO_REFCURSOR.getRefCursor to get the REF CURSOR it returns. The REF CURSOR is then accessed and the first 5 records of the REF CURSOR are displayed in the table area.

When the user clicks on the "Get Next 5 rows" button, the next 5 records from the REF CURSOR are displayed in the Table area.

Here is the code usage for obtaining a REF CURSOR from SQLJ. You can find more details of the code in SqljRefcursorSample.sqlj file under src/oracle/otnsamples/sqlj/refcursor folder. Look into Description of Sample Files section for folder and file details.

   .........
   .........				

  /** Database Connection Object */
  private Connection connection = null;
     
  /** Database Connection Context object. **/
  private DefaultContext connContext = null;

  private Connection dbConnection() {
    try {

      // Load the properties file to get the connection information.
      Properties prop = loadParams("Connection");

      // Create a OracleDataSource instance.
      OracleDataSource ods = new OracleDataSource();
      .........

      .........

      // Create a connection  object.
      connection = ods.getConnection();
      
      // get a default context using above connection to execute SQLJ statement.
      connContext = new DefaultContext(connection);         

      //Set the above connection context as the default context for this
      //application.
      DefaultContext.setDefaultContext(connContext);
  } catch(Exception ex){ //Catch exceptions.
      .........
  }
  return connection;
 }			  

			  

 /**
  * Create PLSQL package demo_refcursor, with the function getRefCursor
  * which returns a refsursor pointing to all rows in the OTN_AIRLINES
  * table.
  **/
  private void setupPlsqlPackage() {
    try {
      // Create Package Header.
      #sql {CREATE OR REPLACE PACKAGE demo_refcursor AS 
          TYPE airline_refcursor IS REF CURSOR RETURN OTN_AIRLINES%ROWTYPE; 
          FUNCTION getRefCursor RETURN airline_refcursor;
          END demo_refcursor;

      };

      // Create Package Body.
      #sql {CREATE OR REPLACE PACKAGE BODY demo_refcursor AS 
          FUNCTION getRefCursor RETURN airline_refcursor IS 
             airlines_rc airline_refcursor;
          BEGIN 
             OPEN airlines_rc FOR SELECT * FROM OTN_AIRLINES;
             RETURN airlines_rc;
          END getRefCursor;
          END demo_refcursor;
      };

      ............
      } catch (SQLException ex) {  // Catch SQL errors.
          ...........
      }
  }
			

Processing REF CURSOR in SQLJ:

/** The result set object that represents the REFCURSOR returned by the PLSQL procedure demo_refcursor.getRefCursor(). **/ private AirlineIter airlines = null; ............... /** * Call the plsql function getRefCursor to get a REFCURSOR pointing to all rows * in the OTN_AIRLINES table. The obtained REFCURSOR is cast to a SQLJ * iterator. **/ private void getFirstFive() { try { // If resultset is open, close it. if (airlines != null) airlines.close(); #sql airlines = {VALUES (demo_refcursor.getRefCursor())}; // Display the next five records from the newly opened resultset. displayNextFive(); } catch (SQLException ex) { // Catch SQL errors. ............. } } /** * Displays the Next five records from the current instance of iterator, held * in the member variable airlines. **/ private void displayNextFive() { try { if (!airlines.next()) { // No more rows in resultset. ............. return; } ........... for (int i=0; i<5; i++) { String code = airlines.code(); String name = airlines.name(); String partner = airlines.partner(); ................ if (!airlines.next()) { // No more rows in resultset. ................... return; } } } catch (SQLException ex) { // Catch SQL errors. ................ } }

• Oracle9i JDeveloper ( Note: Oracle9i 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.

• Unjar the provided SqljRefcursorSample.jar using the following command 

> jar xvf SqljRefcursorSample.jar

Note: You will find jar.exe in JDK_HOME\bin. Ensure JDK_HOME\bin is present in your system path. (JDK_HOME is the root directory of the JDKx.x installation). This creates a folder SqljRefcursorSample with all the source files.

• Edit SqljRefcursorSample/Connection.properties file in your favourite editor. Change the HostName, Port, SID, UserName and Password to connect to your own database.

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 menu option to select the SqljRefcursorSample.jws from the SqljRefcursorSample directory.
• Next, select Project/Make SqljRefcursorSample.jpr from main menu.
• Now, select Run/Run SqljRefcursorSample.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 Batch 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 SqljRefcursorSample directory. Environmental variables JAVA_HOME, SQLJ_EXE_HOME, SQLJ_LIB and JDBC_LIB 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:\SqljRefcursorSample> set SQLJ_EXE_HOME=d:\sqlj\bin
D:\SqljRefcursorSample> set SQLJ_LIB=d:\sqlj\lib
D:\SqljRefcursorSample> set JDBC_LIB=d:\oracle9i\jdbc\lib
D:\SqljRefcursorSample> set JAVA_HOME=d:\jdk1.3.1
D:\SqljRefcursorSample> 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
  • SqljRefcursorSample directory where Connection.properties exists(the current directory).
    Example: 
    D:\SqljRefcursorSample>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:\SqljRefcursorSample>set PATH=.;d:\jdk1.3.1\bin;d:\sqlj\bin;%PATH%
  
• From the directory SqljRefcursorSample, translate all *.sqlj files to *.java files using sqlj:
  Example: 
  D:\SqljRefcursorSample>sqlj -compile=false src\oracle\otnsamples\sqlj\refcursor\*.sqlj
• From the same SqljRefcursorSample directory, now compile all *.java files using javac:
  Example: 
  D:\SqljRefcursorSample>javac -d . src\oracle\otnsamples\sqlj\refcursor\*.java
  
• Run the class file using java from the same SqljRefcursorSample directory.
  Example:
  D:\SqljRefcursorSample>java oracle.otnsamples.sqlj.refcursor.SqljRefcursorSample
  

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 script file: run.sh provided. (For Bourne Shell):

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 SqljRefcursorSample 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 SqljRefcursorSample directory and from the $ prompt, use the command below to run the script file:
  $sh run.sh
  

Running the application manually:

• Set CLASSPATH to include:
  • Oracle9i JDBC Driver file: classes12.jar/zip
  • Oracle9i SQLJ classes: translator.jar/zip and runtime12ee.jar/zip
  • SqljRefcursorSample 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 SqljRefcursorSample, translate all *.sqlj files to *.java files using sqlj:
  Example: 
  $sqlj -compile=false /src/oracle/otnsamples/sqlj/refcursor/*.sqlj
• From the directory SqljRefcursorSample, now compile all the java files using javac:
  Example:
  $javac -d . /src/oracle/otnsamples/sqlj/refcursor/SqljRefcursorSample/*.java
• Run the class file using java from the same SqljRefcursorSample directory.
  Example:
  $java oracle.otnsamples.sqlj.refcursor.SqljRefcursorSample