Sample Illustrating PLSQL procedure returning REF Cursor and batch Update support in Oracle9i SQLJ

Table Of Contents 

• Overview of the Sample Application
• Notations used
• Required Software
• Application Set-up and Configuration
• Database Setup
• Deploying and Running the Application 
• Using Oracle9i JDeveloper 
• From command line
• Description of Sample Files

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 both 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.exe.

This sample illustrates how to access a REF Cursor returned from a PL/SQL procedure in Oracle9i database from a java application. It also demonstrates the Batch update feature of Oracle9i SQLJ. Update batching (referred to as batch updates in the Sun Microsystems JDBC 2.0 specification) allows UPDATE, DELETE, and INSERT statements that are batchable and compatible to be collected into a batch and sent to the database for execution at once, saving round trips to the database. Update batching is typically used for an operation that is executed repeatedly within a loop.

Sample Application Scenario

             This sample application makes use of the product_information table of Order Entry (oe) schema that is shipped with Oracle9i database. Using this application, user can carry out following operations :

• Randomly change the quantity on hand for any product.
• Do a batch Update.
  

Here is the code usage for using REF Cursor and batch update feature of Oracle9i SQLJ. You can find more details of the code in BatchingAndREFCursorSample.sqlj file under src/oracle/otnsamples/sqlj/batchingandrefcursor folder. Look into Description of Sample Files section for folder and file details.

public class BatchingAndREFCursorSample {


// Iterator which holds the data returned as part of the calling the PLSQL stored procedure.
#sql public static iterator ProdIter(int PRODUCT_ID,String PRODUCT_NAME,
                                       int QUANTITY_ON_HAND, int WAREHOUSE_ID);

private ProdIter iterator = null;  // Iterator type object

 /**
  *  Creates a database connection in SQLJ through Connection.properties. Please
  *  substitute the  connection parameters with appropriate values in
  *  Connection.properties file

  */
  private void dbConnection(){
    try{

      // Connects to the database.
      Oracle.connect(BatchingAndREFCursorSample.class,"../../../../Connection.properties");

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

/**
 * This method calls the PLSQL stored procedure 'GETPRODUCTDETAILS' and stores
 *  the result in a named iterator. Then the iterator is read and the JTable is<
 *  populated with the product information.

 */

  private void populateProducts(){
   try{
      // IMP : This statement calls the PLSQL stored procedure 'GETPRODUCTDETAILS'
     // stores the result in iterator, which is an object of ProdIter
      #sql { call GETPRODUCTDETAILS(:OUT iterator) };


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

     // Loop through the iterator, obtain individual values and add to the
     // GUI JTable. The set of statements within the while loop demonstrates
     // how to access the data from an iterator.

       while (iterator.next()){
           int    pId  = iterator.PRODUCT_ID();       // Product Id

           String name = iterator.PRODUCT_NAME();     // Product Name
           int    qty  = iterator.QUANTITY_ON_HAND(); // Quantity on hand
           int    wId  = iterator.WAREHOUSE_ID();     // Warehouse Id
       }
       iterator.close(); // Close the iterator

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

    }catch(Exception ex){ //Trap SQL Errors
...........................................
    }finally{
  }


/**
 *  This method creates a batch of 'UPDATE' queries, creates an execution
 *  context, sets it to batching mode and then updates the database as a batch.
 *  This gives a significant improvement in the performance as the network round
 *  trip is reduced.

 */
 private void batchUpdate(){
   try{

     // Create an execution context and set it to batching mode
     ExecutionContext ec1 = new ExecutionContext();
     ec1.setBatching(true);

     // Loop through the number of times the user has updated different product
     // quantities and set them on batch. This updated information about product
     // quantity is stored in the variable dataForBatchUpdate, a Hashtable which
     // keeps track of the changes made by the user which will be used for
     // batch update and reset once the update is done.
      Enumeration enum =  gui.dataForBatchUpdate.elements();
      while(enum.hasMoreElements()){
		
       // Update the product details retrieved from the ArrayList
        ArrayList temp       = (ArrayList)enum.nextElement();
        Integer wareHouseId = (Integer)temp.get(2);


        // Get the individual values from the created ArrayList.
        int product_id       = ((Integer)temp.get(0)).intValue();
        int quantity_on_hand = ((Integer)temp.get(1)).intValue();
        int warehouse_id     =  wareHouseId .intValue();

        // Create the query for updating individual product details.
        #sql [ec1] {update inventories set quantity_on_hand = :quantity_on_hand where
                    product_id = :product_id and warehouse_id = :warehouse_id };

        // Remove the elements from the Hashtable
        gui.dataForBatchUpdate.remove(product_id+""+wareHouseId);
     }

     // The statement below execute the above statements in a batch and
     // commits the transaction.
     #sql [ec1] { COMMIT };
...........................................

    }catch(SQLException ex){       // Trap SQL Errors


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

    }
  }

The following notations are used through out this document

Notation	Description
<SAMPLE_HOME>	Folder where the BatchingAndREFCursorSample will be unzipped.
<JAVA_HOME>	Folder where JAVA is installed.
<JDBC_LIB>	Folder where the Oracle JDBC driver exists.
<SQLJ_LIB>	Folder where the Oracle SQLJ translator and runtime files exists.
<SQLJ_EXE_HOME>	Folder where the SQLJ executable exists.

Required Software

Back To Top

• Oracle9i JDeveloper ( Note: Oracle9i JDeveloper is Oracle's Visual Java Development Tool and can be downloaded from here )
  or JDK1.3.x. This can be downloaded here .
• Oracle9i Database or later running SQL*Net TCP/IP listener. This can be downloaded from OTN site.
• Oracle9i v9.0.2 or later SQLJ translators and runtime, downloadable from OTN site.
• Oracle9i v9.0.2 or higher JDBC Drivers, downloadable from OTN site.
  Note : If Oracle9i client is already installed on your system then these drivers need not be downloaded separately.
  
  

• For setting up the environment variables in different platforms, please refer environment set up document.

• Unjar the provided BatchingAndREFCursorSample.jar using the following command.
  

  > jar xvf BatchingAndREFCursorSample.jar

  Note: You wil