12
Sample Applications

This chapter presents sample applications that highlight a range of SQLJ features, from basic features to advanced features and Oracle extensions, categorized as follows:

• Demo Directories

• Properties Files

• Basic Samples

• Object, Collection, and ORAData Samples

• Advanced Samples

• Performance Enhancement Samples

• Applet Sample

• Server-Side Sample

• JDBC Versus SQLJ Sample Code

Demo Directories

This chapter contains a subset of SQLJ demos that can be found in the following directory and its subdirectories after installation:

[Oracle Home]/sqlj/demo

This directory and its subdirectories are organized as follows:

• demo directory (top-level)--properties files, basic samples, and advanced samples

• demo/Objects subdirectory--object and collection samples

• demo/server subdirectory--server-side sample

• demo/applets subdirectory--applet samples

• demo/jdbc20 subdirectory--ISO SQLJ and JDBC 2.0 samples

• demo/jpub--JPublisher samples

• demo/components--samples of how to create your own semantics-checkers and profile customizers

Please refer directly to the demo directories for the full set of SQLJ and JPublisher sample applications.

Note: Listings in this chapter were copied directly from the demo directory and subdirectories without alteration.

Properties Files

This section consists of two properties files--one for the SQLJ runtime connection and one for translator option settings. These files are located in the following directory:

[Oracle Home]/sqlj/demo

Runtime Connection Properties File

The sample applications in this chapter use the Oracle.connect() method, a convenient way to create an instance of the DefaultContext class and establish it as your default connection. This method offers several signatures; the signature used in the samples takes a properties file--connect.properties--to specify connection parameters. Here are sample contents of that file:

# Users should uncomment one of the following URLs or add their own.
# (If using Thin, edit as appropriate.)
#sqlj.url=jdbc:oracle:thin:@localhost:1521:ORCL
sqlj.url=jdbc:oracle:oci:@

# User name and password here (edit to use different user/password)
sqlj.user=scott
sqlj.password=tiger

The version of this file in [Oracle Home]/sqlj/demo is configured to use the JDBC OCI driver and scott/tiger schema. This is appropriate for the sample applications in this chapter, presuming you have a client installation as described in Chapter 2, "Getting Started".

For other uses, you must edit the file appropriately for your particular database connection.

SQLJ Translator Properties File

A SQLJ translator properties file, such as sqlj.properties below and in the demo directory, can be used to specify translator options in translating the SQLJ demo applications. As is, the file does not enable online semantics-checking. To enable it, "uncomment" the sqlj.user entries or add new sqlj.user entries, as appropriate. An appropriate checker, either offline or online as applicable, will be chosen for you by the default OracleChecker class.

In general, this properties file shows how to set numerous options, but settings are commented out.

For information about SQLJ properties files, see "Properties Files for Option Settings".

###
### Settings to establish a database connection for online checking
###

### turn on checking by uncommenting user 
### or specifying the -user option on the command line
#sqlj.user=scott
sqlj.password=tiger

### add additional drivers here
#sqlj.driver=oracle.jdbc.OracleDriver<,driver2...>

### Oracle JDBC-OCI URL (9i driver)
#sqlj.url=jdbc:oracle:oci:@
#
### Oracle9i JDBC-OCI8 URL (8i/8.0.x drivers)
#sqlj.url=jdbc:oracle:oci8:@
#
### Oracle9i JDBC-OCI7 URL (7.3.x drivers)
#sqlj.url=jdbc:oracle:oci7:@

### Oracle JDBC-Thin URL
#sqlj.url=jdbc:oracle:thin:@<host>:<port>:<oracle_sid>
#sqlj.url=jdbc:oracle:thin:@localhost:1521:orcl

### Warning settings
###  Note: All settings must be specified TOGETHER on a SINGLE line.

# Report portability warnings about Oracle-specific extensions to SQLJ
#sqlj.warn=portable

# Turn all warnings off
#sqlj.warn=none

# Turn informational messages on
#sqlj.warn=verbose

###
### Online checker
###

### Force Oracle 7.3 features only (with Oracle 9i JDBC and 9i database)
#sqlj.online=oracle.sqlj.checker.Oracle8To7JdbcChecker

### Force Oracle 7.3 features only (with Oracle 8.0 JDBC and 8.0 database)
#sqlj.online=oracle.sqlj.checker.Oracle7JdbcChecker

### JDBC-generic checker:
#sqlj.online=sqlj.semantics.JdbcChecker

###
### Offline checker
###

### Force Oracle 7.3 features only (with Oracle 9i JDBC)
#sqlj.offline=oracle.sqlj.checker.Oracle8To7OfflineChecker

### Force Oracle 7.3 features only (with Oracle 8.0 JDBC)
#sqlj.offline=oracle.sqlj.checker.Oracle7OfflineChecker

### JDBC-generic checker:
#sqlj.offline=sqlj.semantics.OfflineChecker

###
### Re-use online checking results on correct statements
###
#sqlj.cache=on

###
### Settings for the QueryDemo example
###
### shows how to set options for a particular connection context
###
#sqlj.user@QueryDemoCtx=scott
#sqlj.password@QueryDemoCtx=tiger
#sqlj.url@QueryDemoCtx=jdbc:oracle:oci:@
#sqlj.url@QueryDemoCtx=jdbc:oracle:thin:@<host>:<port>:<oracle_sid>

Basic Samples

This section presents examples that demonstrate some of the basic essentials of SQLJ, including iterators and host expressions. The following samples are included:

• Named Iterator--NamedIterDemo.sqlj

• Positional Iterator--PosIterDemo.sqlj

• Host Expressions--ExprDemo.sqlj

These samples are located in the following directory:

[Oracle Home]/sqlj/demo

Before beginning, connect to the database following the procedures described in "Set Up the Runtime Connection". Note that this includes creating the following SALES table:

CREATE TABLE SALES (
      ITEM_NUMBER NUMBER,
      ITEM_NAME CHAR(30),
      SALES_DATE DATE, 
      COST NUMBER, 
      SALES_REP_NUMBER NUMBER,
      SALES_REP_NAME CHAR(20));

Named Iterator--NamedIterDemo.sqlj

This example demonstrates the use of a named iterator.

For information about named iterators (and positional iterators as well), see "Multi-Row Query Results--SQLJ Iterators".

// ------------------ Begin of file NamedIterDemo.sqlj -----------------------
//
// Invoke the SQLJ translator with the following command:
//    sqlj NamedIterDemo.sqlj
// Then run as 
//    java NamedIterDemo

/* Import useful classes.
**
** Note that java.sql.Date (and not java.util.Date) is being used.
*/

import java.sql.Date;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

/* Declare an iterator.
**
** The comma-separated terms appearing in parentheses after the class name
** serve two purposes: they correspond to column names in the query results 
** that later occupy instances of this iterator class, and they provide
** names for the accessor methods of the corresponding column data.
**
** The correspondence between the terms and column names is case-insensitive,
** while the correspondence between the terms and the generated accessor names
** is always case-sensitive.
*/

#sql iterator SalesRecs(
      int item_number,
      String item_name,
      Date sales_date,
      double cost,
      Integer sales_rep_number,
      String sales_rep_name );


class NamedIterDemo
{

  public static void main( String args[] )
  {
    try
    {
      NamedIterDemo app = new NamedIterDemo();
      app.runExample();
    } 
    catch( SQLException exception )
    {
      System.err.println( "Error running the example: " + exception );
    }

    try { Oracle.close(); } catch (SQLException e) { }
  }


  /* Initialize database connection.
  **
  ** Before any #sql blocks can be executed, a connection to a database
  ** must be established.  The constructor of the application class is a
  ** convenient place to do this, since it is executed once, and only
  ** once, per application instance.
  */

  NamedIterDemo() throws SQLException
  {
    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(getClass(), "connect.properties");
  }


  void runExample() throws SQLException
  {
    System.out.println();
    System.out.println( "Running the example." );
    System.out.println();


    /* Reset the database for the demo application.
    */

    #sql { DELETE FROM SALES };


    /* Insert a row into the cleared table.
    */

    #sql
    {
      INSERT INTO SALES VALUES(
            101,'Relativistic redshift recorder',
            TO_DATE('22-OCT-1997','dd-mon-yyyy'),
            10999.95,
            1,'John Smith')
    };


    /* Insert another row in the table using bind variables.
    */

    int     itemID = 1001;
    String  itemName = "Left-handed hammer";
    double  totalCost = 79.99;

    Integer salesRepID = new Integer(358);
    String  salesRepName = "Jouni Seppanen";
    Date    dateSold = new Date(97,11,6);

    #sql { INSERT INTO SALES VALUES( :itemID,:itemName,:dateSold,:totalCost,
          :salesRepID,:salesRepName) };


    /* Instantiate and initialize the iterator.
    **
    ** The iterator object is initialized using the result of a query.
    ** The query creates a new instance of the iterator and stores it in
    ** the variable 'sales' of type 'SalesRecs'.  SQLJ translator has
    ** automatically declared the iterator so that it has methods for
    ** accessing the rows and columns of the result set.
    */

    SalesRecs sales;

    #sql sales = { SELECT item_number,item_name,sales_date,cost,
          sales_rep_number,sales_rep_name FROM sales };


    /* Print the result using the iterator.
    **
    ** Note how the next row is accessed using method 'next()', and how
    ** the columns can be accessed with methods that are named after the
    ** actual database column names.
    */

    while( sales.next() )
    {
      System.out.println( "ITEM ID: " + sales.item_number() );
      System.out.println( "ITEM NAME: " + sales.item_name() );
      System.out.println( "COST: " + sales.cost() );
      System.out.println( "SALES DATE: " + sales.sales_date() );
      System.out.println( "SALES REP ID: " + sales.sales_rep_number() );
      System.out.println( "SALES REP NAME: " + sales.sales_rep_name() );
      System.out.println();
    }


    /* Close the iterator.
    **
    ** Iterators should be closed when you no longer need them.
    */

    sales.close() ;
  }

}

Positional Iterator--PosIterDemo.sqlj

This example demonstrates the use of a positional iterator.

For information about positional iterators (and named iterators as well), see "Multi-Row Query Results--SQLJ Iterators".

// ---------------------- Begin of file PosIterDemo.sqlj ---------------------
//
// Invoke the SQLJ translator as follows:
//      sqlj PosIterDemo.sqlj
// Then run the program using
//      java PosIterDemo


import java.sql.* ;                   // JDBC classes
import oracle.sqlj.runtime.Oracle;    // Oracle class for connecting

/* Declare a ConnectionContext class named PosIterDemoCtx. Instances of this 
   class can be used to specify where SQL operations should execute. */
#sql context PosIterDemoCtx;

/* Declare a positional iterator class named FetchSalesIter.*/
#sql iterator FetchSalesIter (int, String, Date, double);

class PosIterDemo {

  private PosIterDemoCtx ctx = null;  // holds the database connection info

  /* The constructor sets up a database connection. */
  public PosIterDemo() {
    try 
    {  
      /* if you're using a non-Oracle JDBC Driver, add a call here to
         DriverManager.registerDriver() to register your Driver
      */

      // get a context object based on the URL, user, and password
      // specified in your connect.properties file
      ctx = new PosIterDemoCtx(Oracle.getConnection(getClass(),
                           "connect.properties"));
    } 
    catch (Exception exception) 
    { System.err.println (
             "Error setting up database connection: " + exception);
    }
   }

  //Main method
  public static void main (String args[])
  {
    PosIterDemo posIter = new PosIterDemo();

    try 
    { 
      //Run the example
      posIter.runExample() ; 

      //Close the connection
      posIter.ctx.close() ;
    } 
    catch (SQLException exception)
    { System.err.println (
      "Error running the example: " + exception ) ;
    }

    try { Oracle.close(); } catch (SQLException e) { }
  } //End of method main


  //Method that runs the example
  void runExample() throws SQLException
  {

    /* Reset the database for the demo application.    */
    #sql [ctx] { DELETE FROM SALES 
                   -- Deleting sales rows

               };

    insertSalesRecord
    ( 250, "widget1", new Date(97, 9, 9), 12.00, 
      new Integer(218), "John Doe"
    ) ;
    
    insertSalesRecord
    ( 267, "thing1", new Date(97, 9, 10), 700.00, 
      new Integer(218), "John Doe"
    ) ;
   
    insertSalesRecord
    ( 270, "widget2", new Date(97, 9, 10), 13.00, 
      null, "Jane Doe" // Note: Java null is same as SQL null
    ) ;
    
    System.out.println("Sales records before delete") ;
    printRecords(fetchSales()) ;

    // Now delete some sales records
    Date delete_date;
    #sql [ctx] { SELECT MAX(sales_date) INTO :delete_date
                 FROM SALES };

    #sql [ctx] { DELETE FROM SALES WHERE sales_date = :delete_date };

    System.out.println("Sales records after delete") ;
    printRecords(fetchSales()) ;
   
  } //End of method runExample


  //Method to select all records from SALES through a positional iterator
  FetchSalesIter fetchSales() throws SQLException {
    FetchSalesIter f;

    #sql [ctx]  f = { SELECT item_number, item_name, sales_date, cost
                      FROM sales };
    return f;
  }

  //Method to print rows using a FetchSalesIter
  void printRecords(FetchSalesIter salesIter) throws SQLException
  {
    int item_number = 0; 
    String item_name = null;
    Date sales_date = null;
    double cost = 0.0;

    while (true) 
    {
      #sql { FETCH :salesIter 
             INTO :item_number, :item_name, :sales_date, :cost 
           };
      if (salesIter.endFetch()) break;

      System.out.println("ITEM NUMBER: " + item_number) ;
      System.out.println("ITEM NAME: " + item_name) ;
      System.out.println("SALES DATE: " + sales_date) ;
      System.out.println("COST: " + cost) ;
      System.out.println() ;
    }
    
    //Close the iterator since we are done with it.
    salesIter.close() ;

  } //End of method runExample


  //Method to insert one row into the database
  void insertSalesRecord(
    int item_number,
    String item_name,
    Date sales_date,
    double cost,
    Integer sales_rep_number,
    String sales_rep_name)

  throws SQLException
  {
    #sql [ctx] {INSERT INTO SALES VALUES 
                (:item_number, :item_name, :sales_date, :cost,
                 :sales_rep_number, :sales_rep_name
                )
    } ;
  } //End of method insertSalesRecord

} //End of class PosIterDemo

//End of file PosIterDemo.sqlj

Host Expressions--ExprDemo.sqlj

This example demonstrates the use of host expressions.

For information about host expressions, see "Java Host Expressions, Context Expressions, and Result Expressions".

import java.sql.Date;
import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;


class ExprDemo
{

  public static void main( String[] arg )
  {
    try
    {
      new ExprDemo().runExample();
    }
    catch( SQLException e )
    {
      System.err.println( "Error running the example: " + e );
    }

    try
    {
      Oracle.close();
    } 
    catch( SQLException e ) { }
  }


  ExprDemo() throws SQLException
  {
    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(getClass(), "connect.properties");
  }


  int[] array;
  int indx;

  Integer integer;


  class Demo
  {
    int field = 0;
  }


  Demo obj = new Demo();

  int total;


  void printArray()
  {
    System.out.print( "array[0.." + (array.length-1) + "] = { " );

    int i;

    for( i=0;i<array.length;++i )
    {
      System.out.print( array[i] + "," );
    }

    System.out.println( " }" );
  }


  void printIndex()
  {
    System.out.println( "indx = " + indx );
  }


  void printTotal()
  {
    System.out.println( "total = " + total );
  }


  void printField()
  {
    System.out.println( "obj.field = " + obj.field );
  }


  void printInteger()
  {
    System.out.println( "integer = " + integer );
  }


  void runExample() throws SQLException
  {
    System.out.println();
    System.out.println( "Running the example." );
    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      Expressions 'indx++' and 'array[indx]' are evaluated in that order.
      Because 'indx++' increments the value of 'indx' from 1 to 2, the
      result will be stored in 'array[2]':

      Suggested Experiments:

       - Try preincrement operator instead of post-increment
       - See what happens if the array index goes out of bounds as a result
         of being manipulated in a host expression

    */

    array = new int[] { 1000,1001,1002,1003,1004,1005 };
    indx = 1;

    #sql { SELECT :(indx++) INTO :(array[indx]) FROM DUAL };

    printArray();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      Expressions 'array[indx]' and 'indx++' are evaluated in that order.
      The array reference is evaluated before the index is incremented,
      and hence the result will be stored in 'array[1]' (compare with the
      previous example):

    */

    array = new int[] { 1000,1001,1002,1003,1004,1005 };
    indx = 1;

    #sql { SET :(array[indx]) = :(indx++) };

    printArray();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      Expressions 'x.field' and 'y.field' both refer to the same variable,
      'obj.field'.  If an attempt is made to assign more than one results
      in what is only one storage location, then only the last assignment
      will remain in effect (so in this example 'obj.field' will contain 2
      after the execution of the SQL statement):

    */

    Demo x = obj;
    Demo y = obj;

    #sql { SELECT :(1), :(2) INTO :(x.field), :(y.field) FROM DUAL };

    printField();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      All expressions are evaluated before any are assigned.  In this
      example the 'indx' that appears in the second assignment will be
      evaluated before any of the assignments take place.  In particular,
      when 'indx' is being used to assign to 'total', its value has not
      yet been assigned to be 100.

      The following warning may be generated, depending on the settings
      of the SQLJ translator:

        Warning: Repeated host item indx in positions 1 and 3 in SQL
        block. Behavior is vendor-defined and non portable.

    */

    indx = 1;
    total = 0;

    #sql
    {
      BEGIN
        :OUT indx := 100;
        :OUT total := :IN (indx);
      END;
    };

    printIndex();
    printTotal();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      Expression 'indx++' in the following example is evaluated exactly
      once, despite appearing inside a SQL loop construct.  Its old value
      before increment is used repeatedly inside the loop, and its value
      is incremented only once, to 2.

    */

    indx = 1;
    total = 0;

    #sql
    {
      DECLARE
        n NUMBER;
        s NUMBER;
      BEGIN
        n := 0;
        s := 0;
        WHILE n < 100 LOOP
          n := n + 1;
          s := s + :IN (indx++);
        END LOOP;
        :OUT total := s;
      END;
    };

    printIndex();
    printTotal();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

      In the next example there are two assignments to the same variable,
      each inside a different branch of an SQL 'if..then..else..end if'
      construct, so that only one of those will be actually executed at
      run-time.  However, assignments to OUT variable are always carried
      out, regardless of whether the SQL code that manipulates the return
      value has been executed or not.

      In the following example, only the first assignment is executed by
      the SQL; the second assignment is not executed.  When the control
      returns to Java from the SQL statement, the Java variable 'integer'
      is assigned twice: first with the value '1' it receives from the
      first SQL assignment, then with a 'null' value it receives from the
      second assignment that is never executed.  Because the assignments
      occur in this order, the final value of 'integer' after executing
      this SQL statement is undefined.

      The following warning may be generated, depending on the settings
      of the SQLJ translator:

        Warning: Repeated host item indx in positions 1 and 3 in SQL
        block. Behavior is vendor-defined and non portable.
      
      Suggested experiments:

       - Use a different OUT-variable in the 'else'-branch
       - Vary the condition so that the 'else'-branch gets executed

    */

    integer = new Integer(0);

    #sql
    {
      BEGIN
        IF 1 > 0 THEN
          :OUT integer := 1;
        ELSE
          :OUT integer := 2;
        END IF;
      END;
    };

    printInteger();

    System.out.println();

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    */

  }
}

Object, Collection, and ORAData Samples

This section has examples showing support of user-defined objects and collections through oracle.sql.ORAData implementations, and general use of the ORAData interface. (This interface is discussed in "Custom Java Classes".) The following samples are included:

• Definition of Object and Collection Types

• Oracle Objects--ObjectDemo.sqlj

• Oracle Nested Tables--NestedDemo1.sqlj and NestedDemo2.sqlj

• Oracle VARRAYs--VarrayDemo1.sqlj and VarrayDemo2.sqlj

• General Use of ORAData--BetterDate.java

The object and collection samples are located in the following directory:

[Oracle Home]/sqlj/demo/Objects

For a full discussion of objects and collections, see Chapter 6, "Objects and Collections"

For examples of object support through a java.sql.SQLData implementation, see the Oracle9i JPublisher User's Guide and the Oracle9i JDBC Developer's Guide and Reference.

Also see samples in the demo/jpub directory.

Definition of Object and Collection Types

The following SQL script defines Oracle object types, Oracle collection types (both nested tables and VARRAYs), and tables used in the object, nested table, and VARRAY sample applications below. In particular, it defines the following:

• object types PERSON and ADDRESS for the objects demo

• object types MODULE_T and PARTICIPANT_T for the nested tables demos

• nested table type MODULETBL_T

• VARRAY type PHONE_ARRAY

Here is the script:

/*** Using UDTs in SQLJ ***/
SET ECHO ON;
/**
Consider two types, person and address, and a typed table for 
person objects, that are created in the database using the following 
SQL script.
**/

/*** Clean up ***/
DROP TABLE EMPLOYEES
/
DROP TABLE PERSONS
/
DROP TABLE projects
/
DROP TABLE participants
/
DROP TYPE PHONE_ARRAY FORCE
/
DROP TYPE PHONE_TAB FORCE
/
DROP TYPE PERSON FORCE
/
DROP TYPE ADDRESS FORCE
/
DROP TYPE moduletbl_t FORCE
/
DROP TYPE module_t FORCE
/
DROP TYPE participant_t FORCE
/

/*** Create an address ADT ***/
CREATE TYPE address AS OBJECT
( 
  street        VARCHAR(60),
  city          VARCHAR(30),
  state         CHAR(2),
  zip_code      CHAR(5)
)
/
show errors 

/*** Create a person ADT containing an embedded Address ADT ***/
CREATE TYPE person AS OBJECT
( 
  name    VARCHAR(30),
  ssn     NUMBER,
  addr    address
)
/
show errors 

/*** Create a typed table for person objects ***/
CREATE TABLE persons OF person
/
show errors 
CREATE TYPE PHONE_ARRAY IS VARRAY (10) OF varchar2(30)
/
show errors 
CREATE TYPE participant_t AS OBJECT (
  empno   NUMBER(4),
  ename   VARCHAR2(20),
  job     VARCHAR2(12),
  mgr     NUMBER(4),
  hiredate DATE,
  sal      NUMBER(7,2),
  deptno   NUMBER(2)) 
/
show errors 
CREATE TYPE module_t  AS OBJECT (
  module_id  NUMBER(4),
  module_name VARCHAR2(20), 
  module_owner REF participant_t , 
  module_start_date DATE, 
  module_duration NUMBER )
/
show errors 
create TYPE moduletbl_t AS TABLE OF module_t;
/
show errors 

/*** Create a relational table with two columns that are REFs 
     to person objects, as well as a column which is an Address ADT. ***/

CREATE TABLE  employees
( empnumber            INTEGER PRIMARY KEY,
  person_data     REF  person,
  manager         REF  person,
  office_addr          address,
  salary               NUMBER,
  phone_nums           phone_array
)
/
CREATE TABLE projects (
  id NUMBER(4),
  name VARCHAR(30),
  owner REF participant_t,
  start_date DATE,
  duration NUMBER(3),
  modules  moduletbl_t  ) NESTED TABLE modules STORE AS modules_tab ;

CREATE TABLE participants  OF participant_t ;

/*** Now let's put in some sample data
     Insert 2 objects into the persons typed table ***/

INSERT INTO persons VALUES (
            person('Wolfgang Amadeus Mozart', 123456,
            address('Am Berg 100', 'Salzburg', 'AU','10424')))
/
INSERT INTO persons VALUES (
            person('Ludwig van Beethoven', 234567,
            address('Rheinallee', 'Bonn', 'DE', '69234')))
/

/** Put a row in the employees table **/

INSERT INTO employees (empnumber, office_addr, salary, phone_nums) VALUES 
            (1001,
             address('500 Oracle Parkway', 'Redwood City', 'CA', '94065'),
             50000,
             phone_array('(408) 555-1212', '(650) 555-9999'));
/

/** Set the manager and person REFs for the employee **/

UPDATE employees 
  SET manager =  
    (SELECT REF(p) FROM persons p WHERE p.name = 'Wolfgang Amadeus Mozart')
/

UPDATE employees 
  SET person_data =  
    (SELECT REF(p) FROM persons p WHERE p.name = 'Ludwig van Beethoven')
/

/* now we insert data into the PARTICIPANTS and PROJECTS tables */
INSERT INTO participants VALUES (
participant_T(7369,'ALAN 
SMITH','ANALYST',7902,to_date('17-12-1980','dd-mm-yyyy'),800,20)) ;


INSERT INTO participants VALUES (
participant_t(7499,'ALLEN 
TOWNSEND','ANALYST',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,30));


INSERT INTO participants VALUES (
participant_t(7521,'DAVID 
WARD','MANAGER',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,30));


INSERT INTO participants VALUES (
participant_t(7566,'MATHEW 
JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,20));


INSERT INTO participants VALUES (
participant_t(7654,'JOE 
MARTIN','MANAGER',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,30));


INSERT INTO participants VALUES (
participant_t(7698,'PAUL 
JONES','Director',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,30));


INSERT INTO participants VALUES (
participant_t(7782,'WILLIAM 
CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,10));


INSERT INTO participants VALUES (
participant_t(7788,'SCOTT 
MANDELSON','ANALYST',7566,to_date('13-JUL-87','dd-mm-yy')-85,3000,20));


INSERT INTO participants VALUES (
participant_t(7839,'TOM 
KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,10));


INSERT INTO participants VALUES (
participant_t(7844,'MARY TURNER','SR 
MANAGER',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,30));


INSERT INTO participants VALUES (
participant_t(7876,'JULIE ADAMS','SR ANALYST',7788,to_date('13-JUL-87', 
'dd-mm-yy')-51,1100,20));


INSERT INTO participants VALUES (
participant_t(7900,'PAMELA JAMES','SR 
ANALYST',7698,to_date('3-12-1981','dd-mm-yyyy'),950,30));


INSERT INTO participants VALUES (
participant_t(7902,'ANDY 
FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,20));


INSERT INTO participants VALUES (
participant_t(7934,'CHRIS MILLER','SR 
ANALYST',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,10));


INSERT INTO projects VALUES ( 101, 'Emarald', null, '10-JAN-98',  300, 
     moduletbl_t( module_t ( 1011 , 'Market Analysis', null, '01-JAN-98', 100),
                module_t ( 1012 , 'Forecast', null, '05-FEB-98',20) ,
                module_t ( 1013 , 'Advertisement', null, '15-MAR-98', 50),
                module_t ( 1014 , 'Preview', null, '15-MAR-98',44),
                module_t ( 1015 , 'Release', null,'12-MAY-98',34) ) ) ;

update projects set owner=(select ref(p) from participants p where p.empno = 
7839) where id=101 ;

update the ( select modules from projects a where a.id = 101 )  
set  module_owner = ( select ref(p) from participants p where p.empno = 7844) 
where module_id = 1011 ;

update the ( select modules from projects where id = 101 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7934) 
where module_id = 1012 ;

update the ( select modules from projects where id = 101 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7902) 
where module_id = 1013 ;

update the ( select modules from projects where id = 101 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7876) 
where module_id = 1014 ;

update the ( select modules from projects where id = 101 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7788) 
where module_id = 1015 ;

INSERT INTO projects VALUES ( 500, 'Diamond', null, '15-FEB-98', 555, 
       moduletbl_t ( module_t ( 5001 , 'Manufacturing', null, '01-MAR-98', 120),
                   module_t ( 5002 , 'Production', null, '01-APR-98',100),
                   module_t ( 5003 , 'Materials', null, '01-MAY-98',200) ,
                   module_t ( 5004 , 'Marketing', null, '01-JUN-98',10) ,
                   module_t ( 5005 , 'Materials', null, '15-FEB-99',50),
                   module_t ( 5006 , 'Finance ', null, '16-FEB-99',12),
                   module_t ( 5007 , 'Budgets', null, '10-MAR-99',45))) ;

update projects set owner=(select ref(p) from participants p where p.empno = 
7698) where id=500 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7369) 
where module_id = 5001 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7499) 
where module_id = 5002 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7521) 
where module_id = 5004 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7566) 
where module_id = 5005 ;

update the ( select modules from projects where id = 500 ) 
set  module_owner = ( select ref(p) from participants p where p.empno = 7654) 
where module_id = 5007 ;

COMMIT
/
QUIT

Oracle Objects--ObjectDemo.sqlj

Following is the ObjectDemo.sqlj source code. This uses definitions from the preceding SQL script in "Definition of Object and Collection Types", which begins.

Use of objects is discussed in "Strongly Typed Objects and References in SQLJ Executable Statements".

import java.sql.SQLException;
import java.sql.DriverManager;
import java.math.BigDecimal;
import oracle.sqlj.runtime.Oracle;

public class ObjectDemo
{

/* Global variables */

static String uid      = "scott";              /* user id */
static String password = "tiger";              /* password */
static String url      = "jdbc:oracle:oci:@"; /* Oracle's OCI driver */

public static void main(String [] args)
{

  System.out.println("*** SQLJ OBJECT DEMO ***");

  try {

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    /* Connect to the database */
    Oracle.connect(ObjectDemo.class, "connect.properties");


  /* DML operations on single objects */

     selectAttributes(); /* Select Person attributes */
     updateAttributes(); /* Update Address attributes */

     selectObject();     /* Select a person object */
     insertObject();     /* Insert a new person object */
     updateObject();     /* Update an address object */

     selectRef();        /* Select Person objects via REFs */
     updateRef();        /* Update Person objects via REFs */

        #sql { rollback work };

  }
  catch (SQLException exn)
  {
     System.out.println("SQLException: "+exn);
  }
  finally
  {
    try
    {
      #sql { rollback work };
    }
    catch (SQLException exn)
    {
      System.out.println("Unable to roll back: "+exn);
    }
  }

  System.out.println("*** END OF SQLJ OBJECT DEMO ***");
}

/**
Iterator for selecting a person's data.
*/

#sql static iterator PData (String name, String address, int ssn);


/**
Selecting individual attributes of objects 
*/
static void selectAttributes()

{
  /*
   Select individual scalar attributes of a person object 
   into host types such as int, String
   */

        String name;
        String address;
        int ssn;

        PData iter;

        System.out.println("Selecting person attributes.");
  try {
    #sql iter =
    {
    select p.name as "name", p.ssn as "ssn", 
           p.addr.street || ', ' || p.addr.city 
                         || ', ' || p.addr.state 
                         || ', ' || p.addr.zip_code as "address"
    from persons p
    where p.addr.state = 'AU' OR p.addr.state = 'CA' };

    while (iter.next())
    {
        System.out.println("Selected person attributes:");
        System.out.println("name = "    + iter.name());
        System.out.println("ssn = "     + iter.ssn());
        System.out.println("address = " + iter.address() );
    }
  } catch (SQLException exn) {
        System.out.println("SELECT failed with "+exn);
  }
}


/**
Updating individual attributes of an object
*/

static void updateAttributes()
{

  /* 
   * Update a person object to have a new address.  This example
   * illustrates the use of constructors in SQL to create object types
   * from scalars.
   */

  String name       = "Ludwig van Beethoven";
  String new_street = "New Street";
  String new_city   = "New City";
  String new_state  = "WA";
  String new_zip    = "53241";
  
  System.out.println("Updating person attributes..");

  try { #sql {

       update persons 
       set addr = Address(:new_street, :new_city, :new_state, :new_zip)
       where name = :name };

        System.out.println("Updated address attribute of person."); 

      } catch (SQLException exn) {

        System.out.println("UPDATE failed with "+exn);
  }
}


/**
Selecting an object 
*/

static void selectObject()
{
    /*
     * When selecting an object from a typed table like persons 
     * (as opposed to an object column in a relational table, e.g.,
     * office_addr in table employees), you have to use the VALUE
     * function with a table alias.
     */

    Person p;
    System.out.println("Selecting the Ludwig van Beethoven person object.");

    try { #sql {
            select value(p) into :p
            from persons p
            where p.addr.state = 'WA'  AND p.name = 'Ludwig van Beethoven' };

    printPersonDetails(p);

    /* 
     * Memory for the person object was automatically allocated,
     * and it will be automatically garbage collected when this
     * method returns.
     */

   } catch (SQLException exn) { 
           System.out.println("SELECT failed with "+exn);
   }
   catch (Exception exn)
   {
      System.out.println("An error occurred");
      exn.printStackTrace();
   }
}

/**
Inserting an object
*/

static void insertObject() 
{

       String new_name   = "NEW PERSON";
       int    new_ssn    = 987654;
       String new_street = "NEW STREET";
       String new_city   = "NEW CITY";
       String new_state  = "NS";
       String new_zip    = "NZIP";

  /*
   * Insert a new person object into the persons table
   */
       try {
         #sql {
           insert into persons
           values (person(:new_name, :new_ssn,
                  address(:new_street, :new_city, :new_state, :new_zip)))
          };

          System.out.println("Inserted person object NEW PERSON."); 

   } catch (SQLException exn) { System.out.println("INSERT failed with "+exn); }
}

/**
Updating an object 
*/

static void updateObject() 
{

       Address addr;
       Address new_addr;
       int empno = 1001;

try {
  #sql {
           select office_addr
           into :addr
           from employees
           where empnumber = :empno };
  System.out.println("Current office address of employee 1001:");

  printAddressDetails(addr);

     /* Now update the street of address */

        String street ="100 Oracle Parkway";
        addr.setStreet(street);

     /* Put updated object back into the database */

   try
   {
      #sql {
         update employees
         set office_addr = :addr
         where empnumber = :empno };
     
     System.out.println
            ("Updated employee 1001 to new address at Oracle Parkway.");

      /* Select new address to verify update */
      
      try
      {
       #sql {
            select office_addr
            into :new_addr
            from employees
            where empnumber = :empno };
      
        System.out.println("New office address of employee 1001:");
        printAddressDetails(new_addr);

      } catch (SQLException exn) {
          System.out.println("Verification SELECT failed with "+exn);
      }
      
   } catch (SQLException exn) {
       System.out.println("UPDATE failed with "+exn); 
   }

} catch (SQLException exn) {
    System.out.println("SELECT failed with "+exn);
}


  /* No need to free anything explicitly. */

}

/**
Selecting an object via a REF
*/

static void selectRef()
{ 

  String name = "Ludwig van Beethoven";
  Person mgr;

  System.out.println("Selecting manager of "+name+" via a REF.");

  try { 
     #sql {
      select deref(manager)
      into :mgr
        from employees e
          where e.person_data.name = :name
      } ;

     System.out.println("Current manager of "+name+":");
     printPersonDetails(mgr);

  } catch (SQLException exn) {
     System.out.println("SELECT REF failed with "+exn); }
}


/**
Updating a REF to an object
*/

static void updateRef()
{

  int empno = 1001;
  String new_manager = "NEW PERSON";

  System.out.println("Updating manager REF.");
  try {
     #sql {
       update employees
        set manager = (select ref(p) from persons p where p.name = :new_manager)
       where empnumber = :empno };

     System.out.println("Updated manager of employee 1001. Selecting back");

   } catch (SQLException exn) {
       System.out.println("UPDATE REF failed with "+exn);
   }

  /* Select manager back to verify the update */
  Person manager;

  try { 
     #sql {
      select deref(manager)
      into :manager
        from employees e
          where empnumber = :empno
      } ;

     System.out.println("Current manager of "+empno+":");
     printPersonDetails(manager);

  } catch (SQLException exn) {
     System.out.println("SELECT REF failed with "+exn); }

}

/**
Utility functions
*/

/**** Print the attributes of a person object ****/

static void printPersonDetails(Person p) throws SQLException
{
   if (p == null) {
      System.out.println("NULL Person");
      return;

   }

   System.out.print("Person ");
   System.out.print( (p.getName()==null) ? "NULL name" : p.getName() );
   System.out.print
        ( ", SSN=" + ((p.getSsn()==null) ? "-1" : p.getSsn().toString()) );
   System.out.println(":");
   printAddressDetails(p.getAddr());
}

/**** Print the attributes of an address object ****/

static void printAddressDetails(Address a) throws SQLException
{

  if (a == null)  {
    System.out.println("No Address available.");
    return;
   }

   String street = ((a.getStreet()==null) ? "NULL street" : a.getStreet()) ;
   String city = (a.getCity()==null) ? "NULL city" : a.getCity();
   String state = (a.getState()==null) ? "NULL state" : a.getState();
   String zip_code = (a.getZipCode()==null) ? "NULL zip" : a.getZipCode();

   System.out.println("Street: '" + street + "'");
   System.out.println("City:   '" + city   + "'");
   System.out.println("State:  '" + state  + "'");
   System.out.println("Zip:    '" + zip_code + "'" );
}



/**** Populate a person object with data ****/

static Person createPersonData(int i) throws SQLException
{
     Person p = new Person();

   /* create and load the dummy data into the person */
   p.setName("Person " + i);
   p.setSsn(new BigDecimal(100000 + 10 * i));

   Address a = new Address();
   p.setAddr(a);
   a.setStreet("Street " + i);
   a.setCity("City " + i);
   a.setState("S" + i);
   a.setZipCode("Zip"+i);

  /* Illustrate NULL values for objects and individual attributes */

  if (i == 2)
    {
      /* Pick this person to have a NULL ssn and a NULL address */
      p.setSsn(null); 
      p.setAddr(null);
    }
  return p;
}

}

Oracle Nested Tables--NestedDemo1.sqlj and NestedDemo2.sqlj

Following is the source code for NestedDemo1.sqlj and NestedDemo2.sqlj. These use definitions from the SQL script in "Definition of Object and Collection Types".

Use of nested tables is discussed in "Strongly Typed Collections in SQLJ Executable Statements".

NestedDemo1.sqlj

// --------------Begin of NestedDemo1.sqlj -------------------------

// Import Useful classes 

import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;
import oracle.sql.* ;
import oracle.sqlj.runtime.Oracle;

public class NestedDemo1
{
 //  The Nested Table is accessed using the ModuleIter 
 //  The ModuleIter is defined as Named Iterator 

  #sql public static iterator ModuleIter(int moduleId , 
                                         String moduleName , 
                                         String moduleOwner);

 // Get the Project Details using the ProjIter defined as 
 // Named Iterator. Notice the use of ModuleIter below:

  #sql public static iterator ProjIter(int id, 
                                       String name, 
                                       String owner, 
                                       Date start_date, 
                                       ModuleIter modules);

   public static void main(String[] args) 
   {
     try {
       /* if you're using a non-Oracle JDBC Driver, add a call here to
          DriverManager.registerDriver() to register your Driver
       */

       /* Connect to the database */
       Oracle.connect(NestedDemo1.class, "connect.properties");
       
       listAllProjects();  // uses named iterator
     } catch (Exception e) { 
          System.err.println( "Error running ProjDemo: " + e );
     }
   }
   
   
   public static void listAllProjects() throws SQLException
   {
     System.out.println("Listing projects...");

     // Instantiate and initilaise the iterators 

     ProjIter projs = null;
     ModuleIter  mods = null;
     #sql projs = {SELECT a.id, 
                          a.name, 
                          initcap(a.owner.ename) as "owner", 
                          a.start_date,
                          CURSOR (
                          SELECT b.module_id AS "moduleId",
                                 b.module_name AS "moduleName",
                                 initcap(b.module_owner.ename) AS "moduleOwner"
                           FROM TABLE(a.modules) b) AS "modules"  
                    FROM projects a };
   
     // Display Project Details 
   
     while (projs.next()) {
       System.out.println();
       System.out.println( "'" + projs.name() + "' Project Id:" 
                + projs.id() + " is owned by " +"'"+ projs.owner() +"'"
                + " start on "  
                + projs.start_date());
              
    // Notice below the modules from the Projiter are assigned to the module
    // iterator variable 
       mods = projs.modules() ;
       System.out.println ("Modules in this Project are : ") ;

    // Display Module details 
       while(mods.next()) { 
         System.out.println ("  "+ mods.moduleId() + " '"+ 
                              mods.moduleName() + "' owner is '" +
                              mods.moduleOwner()+"'" ) ;
       }                    // end of modules 
       mods.close();
     }                      // end of projects 
     projs.close();
   }
}

NestedDemo2.sqlj

// --------------Begin of NestedDemo2.sqlj -------------------------
// Demonstrate DML on Nested Tables in SQLJ 
// Import Useful classes 

import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import java.sql.*;
import oracle.sql.*;
import oracle.sqlj.runtime.Oracle;


public class NestedDemo2
{
   #sql public static iterator ModIter(int, String, String) ;

   static ModuletblT mymodules=null;

   public static void main(String[] args) 
   {
     try { 
       /* if you're using a non-Oracle JDBC Driver, add a call here to
          DriverManager.registerDriver() to register your Driver
       */

       /* get connect to the database */
       Oracle.connect(NestedDemo2.class, "connect.properties");
       
       cleanupPreviousRuns();
       /* 
       // insert new project into Projects table 
       // get the owner details from 'participant'
       */

       String ProjName ="My project";
       int projid = 123;
       String Owner = "MARY TURNER";
       insertProject(projid, ProjName, Owner); // insert new project

       /* 
       // Insert another Project 
       // Both project details and Nested table details are inserted 
       */ 
       projid = 600;
       insertProject2(projid);        

       /* Insert a new module for the above project  */ 
       insertModules(projid); 

       /* Update the nested table row */
       projid=600;
       String moduleName = "Module 1";
       String setownerto = "JULIE ADAMS";
       assignModule(projid, moduleName, setownerto); 

      /* delete all the modules for the given project  
      // which are unassigned 
      */ 
        
       projid=600;
       deleteUnownedModules(projid);
 
      /* Display Modules  for 500 project */

       getModules(500) ;

       // Example to use nested table as host variable using a
       // JPub-generated SQL 'Array' type

       getModules2(600);


     } catch (Exception e) { 
          System.err.println( "Error running ProjDemo: " + e );
     }
   }
   
  /* insertProject 
  // inserts  into projects table 
  */

  public static void insertProject(int id, String projectName, String ownerName)
                               throws SQLException 
  {
    System.out.println("Inserting Project '" + id + " "+projectName +
         "'  owner is '" + ownerName + "'");

    try {
      #sql { INSERT INTO Projects(id, name,owner,start_date,duration) 
             SELECT :id, :projectName, ref(p), '12-JAN-97', 30 
             FROM participants p WHERE ename = :ownerName };

    } catch ( Exception e) {
      System.out.println("Error:insertProject");
      e.printStackTrace();
    }
  }

  /* insert Project 2
  // Insert Nested table details along with master details 
  */

  public static void insertProject2(int id)  throws Exception 
  {
    System.out.println("Inserting Project with Nested Table details..");
    try {
      #sql { INSERT INTO Projects(id,name,owner,start_date,duration, modules) 
             VALUES ( 600, 'Ruby', null, '10-MAY-98',  300, 
            moduletbl_t(module_t(6001, 'Setup ', null, '01-JAN-98', 100),
                        module_t(6002, 'BenchMark', null, '05-FEB-98',20) ,
                        module_t(6003, 'Purchase', null, '15-MAR-98', 50),
                        module_t(6004, 'Install', null, '15-MAR-98',44),
                        module_t(6005, 'Launch', null,'12-MAY-98',34))) };
    } catch ( Exception e) {
      System.out.println("Error:insertProject2");
      e.printStackTrace();
    }

    // Assign project owner to this project 

    try {
      #sql { UPDATE Projects pr
          SET owner=(SELECT ref(pa) FROM participants pa WHERE pa.empno = 7698)
             WHERE pr.id=600 };
    } catch ( Exception e) {
      System.out.println("Error:insertProject2:update");
      e.printStackTrace();
    }
  }

  /* insertModules 
  // Illustrates accessing the nested table using the TABLE construct 
  */
  public static void insertModules(int projId)  throws Exception 
  {
    System.out.println("Inserting Module 6009 for Project " + projId); 
    try {
      #sql { INSERT INTO TABLE(SELECT modules FROM projects  
                             WHERE id = :projId) 
             VALUES (6009,'Module 1', null, '12-JAN-97', 10)};

    } catch(Exception e) {
      System.out.println("Error:insertModules");
      e.printStackTrace();
    }
  }

  /* assignModule 
  // Illustrates accessing the nested table using the TABLE construct 
  // and updating the nested table row 
  */
  public static void assignModule
                     (int projId, String moduleName, String modOwner)
                     throws Exception 
  {
    System.out.println("Update:Assign '"+moduleName+"' to '"+ modOwner+"'");

    try {
      #sql {UPDATE TABLE(SELECT modules FROM projects WHERE id=:projId) m
             SET m.module_owner=(SELECT ref(p) 
             FROM participants p WHERE p.ename= :modOwner) 
             WHERE m.module_name = :moduleName };
    } catch(Exception e) {
      System.out.println("Error:insertModules");
      e.printStackTrace();
    }
  }

  /* deleteUnownedModules 
  // Demonstrates deletion of the Nested table element 
  */

  public static void deleteUnownedModules(int projId)
  throws Exception 
  {
    System.out.println("Deleting Unowned Modules for Project " + projId);
    try {
      #sql { DELETE TABLE(SELECT modules FROM projects WHERE id=:projId) m
             WHERE m.module_owner IS NULL };
    } catch(Exception e) {
      System.out.println("Error:deleteUnownedModules");
      e.printStackTrace();
    }
  }

  public static void getModules(int projId)
  throws Exception 
  {
    System.out.println("Display modules for project " + projId ) ;

    try {
      ModIter  miter1 ;
      #sql miter1={SELECT m.module_id, m.module_name, m.module_owner.ename 
                   FROM TABLE(SELECT modules 
                        FROM projects WHERE id=:projId) m };
        int mid=0;
        String mname =null;
        String mowner =null;
      while (true) 
      {
        #sql { FETCH :miter1 INTO :mid, :mname, :mowner } ;
        if (miter1.endFetch()) break;
        System.out.println ( mid + " " + mname + " "+mowner) ;
      }
    } catch(Exception e) {
      System.out.println("Error:getModules");
      e.printStackTrace();
    }
  }

  public static void getModules2(int projId)
  throws Exception 
  {
    System.out.println("Display modules for project " + projId ) ;

    try {
      #sql {SELECT modules INTO :mymodules 
                            FROM projects  WHERE id=:projId };
      showArray(mymodules) ;
    } catch(Exception e) {
      System.out.println("Error:getModules2");
      e.printStackTrace();
    }
  }

  public static void showArray(ModuletblT a) 
  {
    try {
      if ( a == null )
        System.out.println( "The array is null" );
      else {
        System.out.println( "printing ModuleTable array object of size "
                            +a.length());
        ModuleT[] modules = a.getArray();

        for (int i=0;i<modules.length; i++) {
          ModuleT module = modules[i];
          System.out.println("module "+module.getModuleId()+
                ", "+module.getModuleName()+
                ", "+module.getModuleStartDate()+
                ", "+module.getModuleDuration());
        }
      }
    }
    catch( Exception e ) {
      System.out.println("Show Array") ;
      e.printStackTrace();
    }
  }
  /* clean up database from any previous runs of this program */
  private static void cleanupPreviousRuns()
  {
    try {
      #sql {delete from projects where id in (123, 600)};
    } catch (Exception e) {
      System.out.println("Exception at cleanup time!") ;
      e.printStackTrace();
    }
  }
}

Oracle VARRAYs--VarrayDemo1.sqlj and VarrayDemo2.sqlj

Following is the source code for VarrayDemo1.sqlj and VarrayDemo2.sqlj. These examples use definitions from the SQL script in "Definition of Object and Collection Types".

Use of VARRAYs is discussed in "Strongly Typed Collections in SQLJ Executable Statements".

VarrayDemo1.sqlj

import java.sql.SQLException;
import java.sql.DriverManager;
import java.math.BigDecimal;
import oracle.sqlj.runtime.Oracle;

public class VarrayDemo1
{

/* Global variables */

static String uid      = "scott";             /* user id */
static String password = "tiger";             /* password */
static String url      = "jdbc:oracle:oci:@"; /* Oracle's OCI driver */

public static void main(String [] args) throws SQLException
{

  System.out.println("*** SQLJ VARRAY DEMO #1 ***");

  try {

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    /* Connect to the database */
    Oracle.connect(VarrayDemo1.class, "connect.properties");
      
    /* create a new VARRAY object and insert it into the DBMS */
    insertVarray();

    /* get the VARRAY object and print it */
    selectVarray();

  }
  catch (SQLException exn)
  {
     System.out.println("SQLException: "+exn);
  }
  finally
  {
    try
    {
      #sql { rollback work };
    }
    catch (SQLException exn)
    {
      System.out.println("Unable to roll back: "+exn);
    }
  }

  System.out.println("*** END OF SQLJ VARRAY DEMO #1 ***");
}

private static void selectVarray() throws SQLException
{
  PhoneArray ph;
  #sql {select phone_nums into :ph from employees where empnumber=2001};
  System.out.println(
    "there are "+ph.length()+" phone numbers in the PhoneArray.  They are:");

  String [] pharr = ph.getArray();
  for (int i=0;i<pharr.length;++i) 
    System.out.println(pharr[i]);

}

// creates a varray object of PhoneArray and inserts it into a new row
private static void insertVarray() throws SQLException
{
  PhoneArray phForInsert = consUpPhoneArray();

  // clean up from previous demo runs
  #sql {delete from employees where empnumber=2001};

  // insert the PhoneArray object
  #sql {insert into employees (empnumber, phone_nums)
        values(2001, :phForInsert)};
}

private static PhoneArray consUpPhoneArray()
{
  String [] strarr = new String[3];
  strarr[0] = "(510) 555.1111";
  strarr[1] = "(617) 555.2222";
  strarr[2] = "(650) 555.3333";
  return new PhoneArray(strarr);
}
}

VarrayDemo2.sqlj

import java.sql.SQLException;
import java.sql.DriverManager;
import java.math.BigDecimal;
import oracle.sqlj.runtime.Oracle;

#sql iterator StringIter (String s);
#sql iterator intIter(int value);

public class VarrayDemo2
{

/* Global variables */

static String uid      = "scott";              /* user id */
static String password = "tiger";              /* password */
static String url      = "jdbc:oracle:oci:@"; /* Oracle's OCI driver */


public static void main(String [] args) throws SQLException
{
  System.out.println("*** SQLJ VARRAY  DEMO #2 ***");

  try {

    StringIter si = null;

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    /* Connect to the database */
    Oracle.connect(VarrayDemo2.class, "connect.properties");
      
    #sql si = {select column_value s from
               table(select phone_nums from employees where empnumber=1001)};

    while(si.next())
      System.out.println(si.s());
  }
  catch (SQLException exn)
  {
     System.out.println("SQLException: "+exn);
  }
  finally
  {
    try
    {
      #sql { rollback work };
    }
    catch (SQLException exn)
    {
      System.out.println("Unable to roll back: "+exn);
    }
  }

  System.out.println("*** END OF SQLJ VARRAY DEMO #2 ***");
}
}

General Use of ORAData--BetterDate.java

This example shows a class that implements the ORAData interface to provide a customized representation of Java dates.

Note: This is not a complete application--there is no main() method.

import java.util.Date;
import oracle.sql.ORAData;
import oracle.sql.DATE;
import oracle.sql.ORADataFactory;
import oracle.jdbc.OracleTypes;

// a Date class customized for user's preferences:
//      - months are numbers 1..12, not 0..11
//      - years are referred to via four-digit numbers, not two.

public class BetterDate extends java.util.Date
             implements ORAData, ORADataFactory {
  public static final int _SQL_TYPECODE = OracleTypes.DATE;
  
  String[]monthNames={"JAN", "FEB", "MAR", "APR", "MAY", "JUN",
                      "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"};
  String[]toDigit={"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"};

  static final BetterDate _BetterDateFactory = new BetterDate();

  public static ORADataFactory getORADataFactory() { return _BetterDateFactory;}

  // the current time...
  public BetterDate() {
    super();
  }

  public oracle.sql.Datum toDatum(java.sql.Connection conn) {
    return new DATE(toSQLDate());
  }

  public oracle.sql.ORAData create(oracle.sql.Datum dat, int intx) {
    if (dat==null) return null;
    DATE DAT = ((DATE)dat);
    java.sql.Date jsd = DAT.dateValue();
    return new BetterDate(jsd);
  }
   
  public java.sql.Date toSQLDate() {
    java.sql.Date retval;
    retval = new java.sql.Date(this.getYear()-1900, this.getMonth()-1,
             this.getDate());
    return retval;
  }
  public BetterDate(java.sql.Date d) {
    this(d.getYear()+1900, d.getMonth()+1, d.getDate());
  }
  private static int [] deconstructString(String s) {
    int [] retval = new int[3];
    int y,m,d; char temp; int offset;
    StringBuffer sb = new StringBuffer(s);
    temp=sb.charAt(1);
    // figure the day of month
    if (temp < '0' || temp > '9') {
      m = sb.charAt(0)-'0';
      offset=2;
    } else {
      m = (sb.charAt(0)-'0')*10 + (temp-'0');
      offset=3;
    }

    // figure the month
    temp = sb.charAt(offset+1);
    if (temp < '0' || temp > '9') {
      d = sb.charAt(offset)-'0';
      offset+=2;
    } else {
      d = (sb.charAt(offset)-'0')*10 + (temp-'0');
      offset+=3;
    }

    // figure the year, which is either in the format "yy" or "yyyy"
    // (the former assumes the current century)
    if (sb.length() <= (offset+2)) {
      y = (((new BetterDate()).getYear())/100)*100 +
          (sb.charAt(offset)- '0') * 10 +
          (sb.charAt(offset+1)- '0');
    } else {
      y = (sb.charAt(offset)- '0') * 1000 +
          (sb.charAt(offset+1)- '0') * 100 +
          (sb.charAt(offset+2)- '0') * 10 +
          (sb.charAt(offset+3)- '0');
    }
    retval[0]=y;
    retval[1]=m;
    retval[2]=d;
//    System.out.println("Constructing date from string as: "+d+"/"+m+"/"+y);
    return retval;
  }
  private BetterDate(int [] stuff) {
    this(stuff[0], stuff[1], stuff[2]);
  }
  // takes a string in the format: "mm-dd-yyyy" or "mm/dd/yyyy" or
  // "mm-dd-yy" or "mm/dd/yy" (which assumes the current century)
  public BetterDate(String s) {
    this(BetterDate.deconstructString(s));
  }

  // years are as '1990', months from 1..12 (unlike java.util.Date!), date
  // as '1' to '31' 
  public BetterDate(int year, int months, int date) {
    super(year-1900,months-1,date);
  }
  // returns "Date: dd-mon-yyyy"
  public String toString() { 
    int yr = getYear();
    return getDate()+"-"+monthNames[getMonth()-1]+"-"+
      toDigit[(yr/1000)%10] + 
      toDigit[(yr/100)%10] + 
      toDigit[(yr/10)%10] + 
      toDigit[yr%10];
//    return "Date: " + getDate() + "-"+getMonth()+"-"+(getYear()%100);
  }
  public BetterDate addDays(int i) {
    if (i==0) return this;
    return new BetterDate(getYear(), getMonth(), getDate()+i);
  }
  public BetterDate addMonths(int i) {
    if (i==0) return this;
    int yr=getYear();
    int mon=getMonth()+i;
    int dat=getDate();
    while(mon<1) { 
      --yr;mon+=12;
    }
    return new BetterDate(yr, mon,dat);
  }
  // returns year as in 1996, 2007
  public int getYear() {
    return super.getYear()+1900;
  }
  // returns month as 1..12
  public int getMonth() {
    return super.getMonth()+1;
  }
  public boolean equals(BetterDate sd) {
    return (sd.getDate() == this.getDate() &&
            sd.getMonth() == this.getMonth() &&
            sd.getYear() == this.getYear());
  }
  // subtract the two dates; return the answer in whole years
  // uses the average length of a year, which is 365 days plus
  // a leap year every 4, except 100, except 400 years =
  // = 365 97/400 = 365.2425 days = 31,556,952 seconds
  public double minusInYears(BetterDate sd) {
    // the year (as defined above) in milliseconds
    long yearInMillis = 31556952L;
    long diff = myUTC()-sd.myUTC();
    return (((double)diff/(double)yearInMillis)/1000.0);
  }
  public long myUTC() {
    return Date.UTC(getYear()-1900, getMonth()-1, getDate(),0,0,0);
  }
  
  // returns <0 if this is earlier than sd
  // returns = if this == sd
  // else returns >0
  public int compare(BetterDate sd) {
    if (getYear()!=sd.getYear()) {return getYear()-sd.getYear();}
    if (getMonth()!=sd.getMonth()) {return getMonth()-sd.getMonth();}
    return getDate()-sd.getDate();
  }
}

Advanced Samples

This section presents examples that demonstrate some of the relatively advanced features of SQLJ. The following samples are included:

• REF CURSOR--RefCursDemo.sqlj

• Multithreading--MultiThreadDemo.sqlj

• Interoperability with JDBC--JDBCInteropDemo.sqlj

• Multiple Connection Contexts--MultiSchemaDemo.sqlj

• Data Manipulation and Multiple Connection Contexts--QueryDemo.sqlj

• Subclassing Iterators--SubclassIterDemo.sqlj

• Dynamic SQL--DynamicDemo.sqlj

These samples are located in the following directory:

[Oracle Home]/sqlj/demo

REF CURSOR--RefCursDemo.sqlj

This example shows the use of a REF CURSOR type in an anonymous block, a stored procedure, and a stored function.

The PL/SQL code used to create the procedure and function is also shown.

For information about REF CURSOR types, see "Support for Oracle REF CURSOR Types".

Definition of REF CURSOR Stored Procedure and Stored Function

This section contains the PL/SQL code that defines the following:

• a stored procedure that returns a REF CURSOR type as an OUT parameter

• a stored function that returns a REF CURSOR type as a result

create or replace package SQLJRefCursDemo as
  type EmpCursor is ref cursor;
  procedure RefCursProc( name VARCHAR,
                         no NUMBER,
                         empcur OUT EmpCursor);

  function RefCursFunc (name VARCHAR, no NUMBER)  return EmpCursor;

end SQLJRefCursDemo;
/

create or replace package body SQLJRefCursDemo is

  procedure RefCursProc( name VARCHAR,
                         no NUMBER,
                         empcur OUT EmpCursor)
     is begin
       insert into emp (ename, empno) values (name, no);
       open empcur for select ename, empno from emp
                       order by empno;
     end;

  function RefCursFunc (name VARCHAR, no NUMBER) return EmpCursor  is
    empcur EmpCursor;
    begin
       insert into emp (ename, empno) values (name, no);
       open empcur for select ename, empno from emp
                       order by empno;
       return empcur;
     end;
end SQLJRefCursDemo;
/
exit
/

REF CURSOR Sample Application Source Code

This application retrieves a REF CURSOR type from the following:

• an anonymous block

• a stored procedure (as an OUT parameter)

• a stored function (as a return value)

A ROLLBACK operation is executed before closing the connection, so the data is not permanently altered.

import java.sql.*;
import oracle.sqlj.runtime.Oracle;

public class RefCursDemo
{
  #sql public static iterator EmpIter (String ename, int empno);

  public static void main (String argv[]) throws SQLException
  {
    String name;  int no;
    EmpIter emps = null;

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    /* Connect to the database */
    Oracle.connect(RefCursDemo.class, "connect.properties");

    try
    {
       name = "Joe Doe"; no = 8100;
       emps = refCursInAnonBlock(name, no);
       printEmps(emps);
   
       name = "Jane Doe"; no = 8200;
       emps = refCursInStoredProc(name, no);
       printEmps(emps);
   
       name = "Bill Smith"; no = 8300;
       emps = refCursInStoredFunc(name, no);
       printEmps(emps);
    }
    finally
    {
      #sql { ROLLBACK };
      Oracle.close();
    }
  }

  private static EmpIter refCursInAnonBlock(String name, int no) 
    throws java.sql.SQLException {
    EmpIter emps = null;    

    System.out.println("Using anonymous block for ref cursor.."); 
    #sql { begin
             insert into emp (ename, empno) values (:name, :no);
             open :out emps for select ename, empno from emp
                           order by empno;
           end;
         };
    return emps;
  }

  private static EmpIter refCursInStoredProc (String name, int no)
    throws java.sql.SQLException {
    EmpIter emps = null;
    System.out.println("Using stored procedure for ref cursor.."); 
    #sql { CALL SQLJREFCURSDEMO.REFCURSPROC (:IN name, :IN no, :OUT emps)
         };
    return emps;
  }

  private static EmpIter refCursInStoredFunc (String name, int no) 
    throws java.sql.SQLException {
    EmpIter emps = null;    
    System.out.println("Using stored function for ref cursor.."); 
    #sql emps = {  VALUES (SQLJREFCURSDEMO.REFCURSFUNC(:name, :no))
                };
    return emps;
  }

  private static void printEmps(EmpIter emps) 
    throws java.sql.SQLException {
    System.out.println("Employee list:");
    while (emps.next()) {
       System.out.println("\t Employee name: " + emps.ename() + 
                          ", id : " + emps.empno());
    }
    System.out.println();
    emps.close();
  }
}

Multithreading--MultiThreadDemo.sqlj

The following is an example of a SQLJ application using multithreading. See "Multithreading in SQLJ" for information about multithreading considerations in SQLJ.

A ROLLBACK operation is executed before closing the connection, so the data is not permanently altered.

import java.sql.SQLException;
import java.util.Random;
import sqlj.runtime.ExecutionContext;
import oracle.sqlj.runtime.Oracle;

/**
  Each instance of MultiThreadDemo is a thread that gives all employees
  a raise of some ammount when run.  The main program creates two such 
  instances and computes the net raise after both threads have completed.
  **/
class MultiThreadDemo extends Thread
{
  double raise;
  static Random randomizer = new Random(); 
 
  public static void main (String args[]) 
  {
    try { 
      /* if you're using a non-Oracle JDBC Driver, add a call here to
         DriverManager.registerDriver() to register your Driver
      */

      // set the default connection to the URL, user, and password
      // specified in your connect.properties file
      Oracle.connect(MultiThreadDemo.class, "connect.properties");
      double avgStart = calcAvgSal();
      MultiThreadDemo t1 = new MultiThreadDemo(250.50);
      MultiThreadDemo t2 = new MultiThreadDemo(150.50);
      t1.start();
      t2.start();
      t1.join();
      t2.join();
      double avgEnd = calcAvgSal();
      System.out.println("average salary change: " + (avgEnd - avgStart));
    } catch (Exception e) { 
      System.err.println("Error running the example: " + e);
    }

    try { #sql { ROLLBACK }; Oracle.close(); } catch (SQLException e) { }
  } 

  static double calcAvgSal() throws SQLException
  {
    double avg;
    #sql { SELECT AVG(sal) INTO :avg FROM emp };
    return avg;
  }

  MultiThreadDemo(double raise)
  {
    this.raise = raise;
  }

  public void run()
  {
    // Since all threads will be using the same default connection
    // context, each run uses an explicit execution context instance to
    // avoid conflict during execution
    try {
      delay();
      ExecutionContext execCtx = new ExecutionContext();
      #sql [execCtx] { UPDATE EMP SET sal = sal + :raise };
      int updateCount = execCtx.getUpdateCount();
      System.out.println("Gave raise of " + raise + " to " + 
                          updateCount + " employees");
    } catch (SQLException e) {
      System.err.println("error updating employees: " + e);
    }
  }

  // delay is used to introduce some randomness into the execution order
  private void delay()
  {
    try {
      sleep((long)Math.abs(randomizer.nextInt()/10000000));
    } catch (InterruptedException e) {}
  }
}

Interoperability with JDBC--JDBCInteropDemo.sqlj

The following example uses JDBC to perform a dynamic query, casts the JDBC result set to a SQLJ iterator, and uses the iterator to view the results. It demonstrates how SQLJ and JDBC can interoperate in the same program.

For information about SQLJ-JDBC interoperability, see "SQLJ and JDBC Interoperability".

import java.sql.*;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;

public class JDBCInteropDemo 
{
  // in this example, we use an iterator that is inner class
  #sql public static iterator Employees ( String ename, double sal ) ;

  public static void main(String[] args) throws SQLException 
  {
    if (args.length != 1) {
      System.out.println("usage: JDBCInteropDemo <whereClause>");
      System.exit(1);
    }
    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */
    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(JDBCInteropDemo.class, "connect.properties");

    try
    {
       Connection conn = DefaultContext.getDefaultContext().getConnection();
       
       // create a JDBCStatement object to execute a dynamic query
       Statement stmt = conn.createStatement();
       String query = "SELECT ename, sal FROM emp WHERE "; 
       query += args[0];
       
       // use the result set returned by executing the query to create
       // a new strongly-typed SQLJ iterator
       ResultSet rs = stmt.executeQuery(query);
       Employees emps;
       #sql emps = { CAST :rs };
       
       while (emps.next()) {
         System.out.println(emps.ename() + " earns " + emps.sal());
       }
       emps.close();
       stmt.close();  
    }
    finally
    {
      Oracle.close();
    }
  }
}

Multiple Connection Contexts--MultiSchemaDemo.sqlj

The following is an example of a SQLJ application using multiple connection contexts. It implicitly uses an instance of the DefaultContext class for operations that use one set of SQL objects, and uses an instance of the declared connection context class DeptContext for operations that use another set of SQL objects.

This example uses the static Oracle.connect() method to establish a default connection, then constructs an additional connection by using the static Oracle.getConnection() method to pass another DefaultContext instance to the DeptContext constructor. As previously mentioned, this is just one of several ways you can construct a SQLJ connection context instance. This example is repeated in "Connection Contexts". You can refer to that section for information about multiple and non-default connection contexts.

import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;

// declare a new context class for obtaining departments
#sql context DeptContext;

#sql iterator Employees (String ename, int deptno);

class MultiSchemaDemo 
{
  public static void main(String[] args) throws SQLException 
  {
    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(MultiSchemaDemo.class, "connect.properties");

    try
    {
       // create a context for querying department info using
       // a second connection
       DeptContext deptCtx = 
         new DeptContext(Oracle.getConnection(MultiSchemaDemo.class, 
                                             "connect.properties"));

       new MultiSchemaDemo().printEmployees(deptCtx);
       deptCtx.close();
    }
    finally
    {
      Oracle.close();
    }
  }

  // performs a join on deptno field of two tables accessed from
  // different connections. 
  void printEmployees(DeptContext deptCtx) throws SQLException
  {
    // obtain the employees from the default context
    Employees emps;
    #sql emps = { SELECT ename, deptno FROM emp }; 

    // for each employee, obtain the department name
    // using the dept table connection context
    while (emps.next()) {
      String dname;
      int deptno = emps.deptno();
      #sql [deptCtx] { 
        SELECT dname INTO :dname FROM dept WHERE deptno = :deptno
      };
      System.out.println("employee: " +emps.ename() +
                         ", department: " + dname);
    }
    emps.close();
  }
}

Data Manipulation and Multiple Connection Contexts--QueryDemo.sqlj

This demo demonstrates programming constructs that you can use to fetch rows of data using SQLJ and also shows the use of multiple connection contexts.

This sample uses the stored procedure GET_SAL, defined as follows:

-- SQL script for the QueryDemo

CREATE OR REPLACE FUNCTION get_sal(name VARCHAR) RETURN NUMBER IS
  sal NUMBER;
BEGIN 
  SELECT sal INTO sal FROM emp WHERE ENAME = name;
  RETURN sal;
END get_sal;
/

EXIT
/

Sample application source code follows.

A ROLLBACK operation is executed before closing the connection, so the data is not permanently altered.

// Source code for the QueryDemo

import java.sql.SQLException;
import oracle.sqlj.runtime.Oracle;
import sqlj.runtime.ref.DefaultContext;

#sql context QueryDemoCtx ;

#sql iterator SalByName (double sal, String ename) ;

#sql iterator SalByPos (double, String ) ;

/**
  This sample program demonstrates the various constructs that may be
  used to fetch a row of data using SQLJ.  It also demonstrates the
  use of explicit and default connection contexts.
  **/
public class QueryDemo
{
  public static void main(String[] args) throws SQLException
  {
    if (args.length != 2) {
      System.out.println("usage: QueryDemo ename newSal");
      System.exit(1);
    }

    /* if you're using a non-Oracle JDBC Driver, add a call here to
       DriverManager.registerDriver() to register your Driver
    */

    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(QueryDemo.class, "connect.properties");

    try
    {
       QueryDemoCtx ctx = 
         new QueryDemoCtx(DefaultContext.getDefaultContext().getConnection());
       String ename = args[0];
       int newSal = Integer.parseInt(args[1]);
   
       System.out.println("before update:");
       getSalByName(ename, ctx);
       getSalByPos(ename);
   
       updateSal(ename, newSal, ctx);
   
       System.out.println("after update:");
       getSalByCall(ename, ctx);
       getSalByInto(ename);
       ctx.close(ctx.KEEP_CONNECTION);
    }
    finally
    {
      #sql { ROLLBACK };
      Oracle.close();
    }
  }

  public static void getSalByName(String ename, QueryDemoCtx ctx) 
    throws SQLException
  {
    SalByName iter = null;
    #sql [ctx] iter = { SELECT ename, sal FROM emp WHERE ename = :ename };
    while (iter.next()) {
      printSal(iter.ename(), iter.sal());
    }
    iter.close();
  }

  public static void getSalByPos(String ename) throws SQLException
  {
    SalByPos iter = null;
    double sal = 0;
    #sql iter = { SELECT sal, ename FROM emp WHERE ename = :ename };
    while (true) {
      #sql { FETCH :iter INTO :sal, :ename };
      if (iter.endFetch()) break;
      printSal(ename, sal);
    }
    iter.close();
  }

  public static void updateSal(String ename, int newSal, QueryDemoCtx ctx)
    throws SQLException
  {
    #sql [ctx] { UPDATE emp SET sal = :newSal WHERE ename = :ename }; 
  }

  public static void getSalByCall(String ename, QueryDemoCtx ctx) 
    throws SQLException
  {
    double sal = 0;
    #sql [ctx] sal = { VALUES(get_sal(:ename)) };
    printSal(ename, sal);
  }

  public static void getSalByInto(String ename)
    throws SQLException
  {
    double sal = 0;
    #sql { SELECT sal INTO :sal FROM emp WHERE ename = :ename };
    printSal(ename, sal);
  }

  public static void printSal(String ename, double sal)
  {
    System.out.println("salary of " + ename + " is " + sal);
  }
}

Subclassing Iterators--SubclassIterDemo.sqlj

This sample shows the usefulness of subclassing an iterator class, in this case to add behavior that writes all the rows of a query result into a Java vector.

See "Support for Subclassing of Iterator Classes" for a general discussion.

// ----------------- Begin of file SubclassIterDemo.sqlj ----------------------
//
// Invoke the SQLJ translator with the following command:
//    sqlj SubclassIterDemo.sqlj
// Then run as 
//    java SubclassIterDemo

/* Import useful classes.
**
** Note that java.sql.Date (and not java.util.Date) is being used.
*/

import java.util.Vector;
import java.util.Enumeration;
import java.sql.SQLException;

import sqlj.runtime.profile.RTResultSet;
import oracle.sqlj.runtime.Oracle;



public class SubclassIterDemo
{
  // Declare an iterator
  #sql public static iterator EmpIter(int empno, String ename);

  // Declare Emp objects
  public static class Emp
  {
    public Emp(EmpIter iter) throws SQLException
    { m_name=iter.ename(); m_id=iter.empno(); }

    public String getName() { return m_name; }
    public int getId()      { return m_id; }
    
    public String toString() { return "EMP "+getName()+" has ID "+getId(); }

    private String m_name;
    private int m_id;
  }


  // Declare an iterator subclass. In this example we add behavior to add
  // all rows of the query as a Vector.

  public static class EmpColl extends EmpIter
  {
     // We _must_ provide a constructor for sqlj.runtime.RTResultSet
     // This constructor is called in the assignment of EmpColl to a query.
     public EmpColl(RTResultSet rs) throws SQLException
     { super(rs); }

     // Materialize the result as a vector
     public Vector getEmpVector() throws SQLException
     { if (m_v==null) populate(); return m_v; }

     private Vector m_v;
     private void populate() throws SQLException
     {
       m_v = new Vector();
       while (super.next())
       { m_v.addElement(new Emp(this)); }
       super.close();
     }
  } 
       

  public static void main( String args[] )
  {
    try
    {
      SubclassIterDemo app = new SubclassIterDemo();
      app.runExample();
    } 
    catch( SQLException exception )
    {
      System.err.println( "Error running the example: " + exception );
    }
    finally
    {
      try { Oracle.close(); } catch (SQLException e) { }
    }
  }


  /* Initialize database connection.
  **
  */

  SubclassIterDemo() throws SQLException
  {
    Oracle.connect(getClass(), "connect.properties");
  }


  void runExample() throws SQLException
  {
    System.out.println();
    System.out.println( "Running the example." );
    System.out.println();


    EmpColl ec;
    #sql ec = { select ename, empno from emp };

    Enumeration enum = ec.getEmpVector().elements();
    while (enum.hasMoreElements())
    {
      System.out.println(enum.nextElement());
    }
  }

}

Dynamic SQL--DynamicDemo.sqlj

This section shows how to use dynamic SQLJ statements, PL/SQL blocks, and JDBC statements to implement dynamic SQL in a SQLJ application.

A ROLLBACK operation is executed before closing the connection, so the data is not permanently altered.

For information about SQLJ dynamic SQL functionality, see "Support for Dynamic SQL".

import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.Types;

import sqlj.runtime.ResultSetIterator;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;

public class DynamicDemo
{

  private final static int SQLJ   = 1;
  private final static int PL_SQL = 2;
  private final static int JDBC   = 3;

  public static void main(String[] args) throws SQLException 
  {
    sqlj.runtime.ref.DefaultContext.setDefaultStmtCacheSize(0);

    int[] modes = { SQLJ, PL_SQL, JDBC };
    for (int i=0; i<modes.length; i++)
    {
      int mode = modes[i];

      System.out.println("*** Demo of using dynamic SQL through
                         "+printMode(mode)+" ***");

      try
      {
        // set the default connection to the URL, user, and password
        // specified in your connect.properties file
        Oracle.connect(DynamicDemo.class, "connect.properties");

        String table_name = "emp_"+printMode(mode);

        String col_name = "sal";
        String index_name = col_name + "_" + table_name;
   
        // dynamic DDL
       
        dynamicDrop(mode, table_name);
        dynamicCreate(mode, table_name, index_name, col_name);
    
        // dynamic DML
       
        String ename; 
        int empno;  double sal;
   
        ename = "julie"; empno = 8455;  sal = 3500;
        dynamicInsert(mode, table_name, ename, empno, sal);

        ename = "scott"; empno = 7788;  sal = 2500;
        dynamicInsert(mode, table_name, ename, empno, sal);

        ename = "king"; empno = 2167;  sal = 4500;
        dynamicInsert(mode, table_name, ename, empno, sal);
   
        ename = "adams"; empno = 5481;  sal = 1900;
        dynamicInsert(mode, table_name, ename, empno, sal);
   
        dynamicDelete(mode, table_name, "empno", "8455");
   
        empno = 7788; sal = 7000.00;
        dynamicUpdateReturning(mode, table_name, empno, sal);  
   
   
        // dynamic 1-row query
        dynamicSelectOne(mode, table_name);
   
        // dynamic multi-row query
        dynamicSelectMany(mode, table_name, "sal > 2000.00");
        dynamicSelectMany(mode, table_name, null);
      }
      finally
      { 
        #sql { ROLLBACK };
        Oracle.close();
      }
    }
  }

  private static String printMode(int mode)
  {
    switch (mode)
    {
      case SQLJ:   return "SQLJ"; 
      case PL_SQL: return "PL_SQL"; 
      case JDBC:   return "JDBC"; 
    }
    return "UNKNOWN"+mode;
  }

  private static void dynamicDrop(int mode, String table) throws SQLException
  {
     System.out.println("Dropping table " + table);
     try
     {
       switch (mode)
       {
         case SQLJ:
           #sql { drop table :{table} };
           break;

         case PL_SQL:
          #sql {  begin
                    execute immediate 'drop table ' || :table;
                  end;
               };
          break;

         case JDBC:
           PreparedStatement ps = null;
           try 
           {
            ps =
DefaultContext.getDefaultContext().getConnection().prepareStatement("drop
table "+table);
            ps.executeUpdate();
           } finally {
             ps.close();
           }
           break;
       }
     } catch (SQLException exn) {
       // Ignore exception if table did not exist
     }
  }

  private static void dynamicCreate(int mode, String table_name, String
                                    index_name, String index_col)
    throws SQLException
  {
    System.out.println("Creating table " + table_name +
                      " and index " + index_name + " on column " +
                       index_col);

    switch (mode)
    {
      case SQLJ:
        #sql { create table :{table_name} (empno number(4) constraint
               :{"pk_"+table_name} primary key,
                ename varchar2(40), sal number) };
        #sql { create index :{index_name} on :{table_name} (:{index_col}) };
        break;

      case PL_SQL: 
        String ddl = "create table " + table_name + " (empno number(4)
                      constraint pk_"+table_name+" primary key, "+
                     "ename varchar2(40), sal number)";
        #sql {  begin 
                  execute immediate :ddl;
                end; 
             };
        ddl = "create index " + index_name + " on " + table_name + "(" +
               index_col + ")";
        #sql {  begin execute immediate :ddl; end; };
        break;

      case JDBC:
        String j_ddl = "create table " + table_name + " (empno number(4)
                        constraint pk_"+table_name+" primary key, "+
                       "ename varchar2(40), sal number)";
        PreparedStatement ps =
DefaultContext.getDefaultContext().getConnection().prepareStatement(j_ddl);
        ps.executeUpdate();
        ps.close();
        j_ddl = "create index " + index_name + " on " + table_name + "(" +
                index_col + ")";
        ps =
DefaultContext.getDefaultContext().getConnection().prepareStatement(j_ddl);
        ps.executeUpdate();
        ps.close();
        break;
     }
  }


  private static void dynamicInsert(int mode, String which_table,
                                    String ename, int empno, double sal) 
    throws SQLException
  {
    System.out.println("Dynamic insert on table " + which_table 
               + " of employee " + ename);

    switch (mode)
    {
      case SQLJ:
        #sql { insert into :{which_table} (ename,empno,sal)
                           values(:ename, :empno, :sal) };
        break;

      case PL_SQL:
        #sql { begin
         execute immediate 
                'insert into ' || :which_table || 
                '(ename, empno, sal) values( :1, :2, :3)'
               -- note: PL/SQL rule is table | col name cannot be 
               --       a bind argument in USING
               -- also, binds are by position except in dynamic PL/SQL blocks
         using :ename, :empno, :sal;
         end;
        };
        break;

      case JDBC:
        String dml = "insert into "+which_table+"(ename,empno,sal)
                      values(?,?,?)";
        PreparedStatement ps =
DefaultContext.getDefaultContext().getConnection().prepareStatement(dml);
        ps.setString(1,ename);
        ps.setInt(2,empno);
        ps.setDouble(3,sal);
        ps.executeUpdate();
        ps.close();
        break;
    }
  }


  private static void dynamicDelete(int mode, String which_table, String
                                   which_col, String what_val) 
                                   throws SQLException
  {
    System.out.println("Dynamic delete of " + which_col + 
               " = " + what_val + " or " + which_col + " is null" );

    switch (mode)
    {

      case SQLJ:
        #sql { delete from :{which_table}
               where :{which_col} = :{what_val} or :{which_col} is null };
        break;

      case PL_SQL:
        String s = "delete from "+ which_table +" where " + which_col  + " =
                   " + what_val
                   + " or " + which_col + " is null";
        #sql { begin 
                 execute immediate :s; 
               end;
             };
        break;

      case JDBC:
        String js = "delete from "+ which_table +" where " + which_col  + " =
                  " + what_val
                    + " or " + which_col + " is null";
        PreparedStatement ps =
DefaultContext.getDefaultContext().getConnection().prepareStatement(js);
        ps.executeUpdate();
        ps.close();
        break;
    }
  }


  private static void dynamicUpdateReturning (int mode, String table, int
                                             empno, double newSal ) 
                                            throws SQLException
  {
    System.out.println("Dynamic update-returning in table "+table+" for empno
                     " + empno);

    String ename = null;

    switch (mode)
    {
      case SQLJ:   // INTO-clause not supported in dynamic SQLJ, using PL_SQL
      case PL_SQL:
        #sql { begin
                 execute immediate 
                 'update ' || :table || ' set sal = :2  where empno = :3 ' ||
                 'returning ename into :4'
                 using :newSal, :empno, OUT :OUT ename ;
                  -- note weird repeated OUT, one for PL/SQL bind, one for
                     SQLJ
               end;
             };
        break;

      case JDBC:
        String upd_ret = "begin update "+ table 
                         + " set sal = ? where empno = ? returning ename into
                      ?; end;";
        CallableStatement cs =
DefaultContext.getDefaultContext().getConnection().prepareCall(upd_ret);
        cs.setDouble(1,newSal);
        cs.setInt(2,empno);
        cs.registerOutParameter(3,Types.VARCHAR);
        cs.executeUpdate();
        ename = cs.getString(3);
        cs.close();
        break;
   }

   System.out.println("Updated the salary of employee " + ename);
  }

  private static void dynamicSelectOne(int mode, String which_table) 
    throws SQLException
  {
    System.out.println("Dynamic 1-row query on table " + which_table);

    int countRows = -1;

    switch (mode)
    {
      case SQLJ:
        // The following is currently not supported by SQLJ:
        //  #sql { select count(*) from :{which_table} into :OUT countRows };

        ResultSetIterator rsi;
        #sql rsi = { select count(*) from :{which_table} };
        #sql { FETCH :rsi INTO :countRows };
        rsi.close();
        break;

      case PL_SQL:
        #sql { begin 
                 execute immediate 
                   'select count(*) from ' || :which_table
                    into :OUT countRows;  -- :OUT is for SQLJ bind
               end;
             };
        break;

      case JDBC:
        String js = "select count(*) from " + which_table;
        PreparedStatement ps =
DefaultContext.getDefaultContext().getConnection().prepareStatement(js);
        ResultSet rs = ps.executeQuery();
        rs.next();
        countRows = rs.getInt(1);
        rs.close();
        ps.close();
        break;
    }

    System.out.println("Number of rows in table " + which_table + 
                       " is " + countRows);
  }
  


  // a nested iterator class
  #sql public static iterator Employees ( String ename, double sal ) ;


  private static void dynamicSelectMany(int mode, String table, String
                                        what_cond)
                                        throws SQLException
  {
    System.out.println("Dynamic multi-row query on table emp");

    what_cond = (what_cond==null || what_cond.equals(""))
                 ? ""
                 : "where "+what_cond;


    Employees    empIter = null; 
    PreparedStatement ps = null; 

    // table/column names cannot be bind args in dynamic PL/SQL, so 
    // build up query as Java string

    switch(mode)
    {
      case SQLJ:
        #sql empIter = { select ename, sal from :{table} :{what_cond} order
                        by ename};
        break;

      case PL_SQL:
        String query = "select ename, sal from " + table + 
                       " " + what_cond +  " order by ename";
        #sql { begin 
                 open :OUT empIter for -- opening ref cursor with dynamic
                      query
                      :query;
                      -- can have USING clause here if needed 
               end;
             };
        break;

      case JDBC:
        String jquery = "select ename, sal from " + table + 
                        " " + what_cond +  " order by ename";
        ps =
DefaultContext.getDefaultContext().getConnection().prepareStatement(jquery);
        ResultSet rs = ps.executeQuery();
        #sql empIter = { CAST :rs };
    }

    while (empIter.next())
    {
      System.out.println("Employee " + empIter.ename() + 
                         " has salary " + empIter.sal() );
    } 
    empIter.close();

    if (mode==JDBC) ps.close();
  }
}

Performance Enhancement Samples

This section presents examples that demonstrate Oracle SQLJ row prefetching and update batching to enhance performance.

These samples are located in the following directory:

[Oracle Home]/sqlj/demo

Prefetch Demo--PrefetchDemo.sqlj

This sample has code showing the use of Oracle SQLJ row prefetching, Oracle SQLJ update batching, and Oracle JDBC update batching. (Note that with JDBC 2.0, there is also a standard update-batching paradigm in JDBC.)

The code here does not actually call the Oracle SQLJ update batching method--insertRowsBatchedSQLJ(). That call is commented out. Only the Oracle JDBC update-batching method--insertRowsBatchedJDBC()--is called. But you can compare the code, and you can optionally "comment out" the JDBC update-batching method call and "uncomment" the SQLJ update-batching method call.

For another example of Oracle SQLJ update batching, see "Update Batching--BatchDemo.sqlj".

For information about SQLJ prefetching, see "Row Prefetching". For information about SQLJ update batching, see "Update Batching".

This application uses the following table definition from PrefetchDemo.sql:

DROP TABLE PREFETCH_DEMO;
CREATE TABLE PREFETCH_DEMO (n INTEGER);

Application source code follows:

// Application source code--PrefetchDemo.sqlj
//
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.Connection;
import oracle.jdbc.OraclePreparedStatement;
import sqlj.runtime.ExecutionContext;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;

/**
 Before compiling this demo with online checking, you
 should run the SQL script PrefetchDemo.sql.

 This demo shows how to set different prefetch values for
 SQLJ SELECT statements. It compares SQLJ and JDBC runs.

 Additionally, when creating the data in the PREFETCH_DEMO
 table, we  show how to batch INSERT statements in JDBC.
 SQLJ now also supports batching, and we show the source for
 the equivalent SQLJ batched insert as well.
**/

public class PrefetchDemo
{

  #sql static iterator PrefetchDemoCur (int n);

  public static void main(String[] args) throws SQLException
  {
     System.out.println("*** Start of Prefetch demo ***");

     Oracle.connect(PrefetchDemo.class,"connect.properties");
     OracleConnection conn =
        (OracleConnection) DefaultContext.getDefaultContext().getConnection();
     System.out.println("Connected.");

     try
     {
        try
        {
          #sql { DELETE FROM PREFETCH_DEMO };
        }
        catch (SQLException exn)
        {
          System.out.println("A SQL exception occurred: "+exn);

          System.out.println("Attempting to create the PREFETCH_DEMO table");

          try
          {
             #sql { DROP TABLE PREFETCH_DEMO };
          }
          catch (SQLException ex) { };

          try
          {
             #sql { CREATE TABLE PREFETCH_DEMO (n INTEGER) };
          }
          catch (SQLException ex)
          {
             System.out.println
                   ("Unable to create the PREFETCH_DEMO table: "+exn);
             System.exit(1);
          };
        }
   
        System.out.println
               (">>> Inserting data into the PREFETCH_DEMO table <<<");
   
        // We batch _all_ rows here, so there is only a single roundtrip.
        int numRows = 1000;

        insertRowsBatchedJDBC(numRows, conn);
        // insertRowsBatchedSQLJ(numRows, conn);
   
        System.out.println
               (">>> Selecting data from the PREFETCH_DEMO table <<<");
   
        System.out.println("Default Row Prefetch value is:  " 
                           + conn.getDefaultRowPrefetch());
   
        // We show three row prefetch settings:
        //  1. every row fetched individually
        //  2. prefetching the default number of rows (10)
        //  3. prefetching all of the rows at once
        //
        // each setting is run with JDBC and with SQLJ
   
        int[] prefetch = new int[] { 1, conn.getDefaultRowPrefetch(),
                                     numRows / 10,  numRows };
   
        for (int i=0; i<prefetch.length; i++) 
        {
           selectRowsJDBC(prefetch[i], conn);
           selectRowsSQLJ(prefetch[i], conn, i);
        }
     }
     finally
     { 
       Oracle.close();
     }
  }

  public static void selectRowsSQLJ(int prefetch, OracleConnection conn, int i)
                     throws SQLException
  {
    System.out.print("SQLJ: SELECT using row prefetch "+prefetch+". ");
    System.out.flush();
    conn.setDefaultRowPrefetch(prefetch);

    PrefetchDemoCur c;

    long start = System.currentTimeMillis();

    // Note: In this particular example, statement caching can
    // defeat row prefetch!  Statements are created _with_
    // their prefetch size taken from the connection's prefetch size.
    // The statement will maintain this original prefetch size when
    // it is re-used from the cache.
    //
    // To obtain predictable results, regardless of the cache setting,
    // we must force the use of _different_ select statements for each
    // of the prefetch settings.
    // 
    // To get the seemingly strange behavior above, add the line below
    // and leave statement caching enabled.
    // i=0;

    switch (i % 5) {
      case 0:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 1:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 2:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      case 3:  #sql c = { SELECT n FROM PREFETCH_DEMO }; break;
      default: #sql c = { SELECT n FROM PREFETCH_DEMO }; 
    }

    while (c.next()) { };
    c.close();
    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

  public static void selectRowsJDBC(int prefetch, OracleConnection conn)
                     throws SQLException
  {
    System.out.print("JDBC: SELECT using row prefetch "+prefetch+". ");
    System.out.flush();
    conn.setDefaultRowPrefetch(prefetch);

    long start = System.currentTimeMillis();
    PreparedStatement pstmt =
                      conn.prepareStatement("SELECT n FROM PREFETCH_DEMO");
    ResultSet rs = pstmt.executeQuery();
    while (rs.next()) { };
    rs.close();
    pstmt.close();
    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

  public static void insertRowsBatchedSQLJ(int n, OracleConnection conn)
                     throws SQLException
  {
    System.out.print("SQLJ BATCHED: INSERT "+n+" rows. ");
    System.out.flush();

    long start = System.currentTimeMillis();

    ExecutionContext ec = new ExecutionContext();
    ec.setBatching(true);
    ec.setBatchLimit(n);

    for (int i=1; i<=n; i++)
    {
      #sql [ec] { INSERT INTO PREFETCH_DEMO VALUES(:i) };
    }

    ec.executeBatch();

    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

  public static void insertRowsBatchedJDBC(int n, OracleConnection conn)
                     throws SQLException
  {
    System.out.print("JDBC BATCHED: INSERT "+n+" rows. ");
    System.out.flush();

    long start = System.currentTimeMillis();
    int curExecuteBatch = conn.getDefaultExecuteBatch();
    conn.setDefaultExecuteBatch(n);

    PreparedStatement pstmt = conn.prepareStatement
                              ("INSERT INTO PREFETCH_DEMO VALUES(?)");
    for (int i=1; i<=n; i++)
    {
      pstmt.setInt(1,i);
      pstmt.execute();
    }
    ((OraclePreparedStatement)pstmt).sendBatch();
    pstmt.close();
    conn.setDefaultExecuteBatch(curExecuteBatch);

    long delta = System.currentTimeMillis() - start;

    System.out.println("Done in "+(delta / 1000.0)+" seconds.");
  }

}

Update Batching--BatchDemo.sqlj

This section shows an example of Oracle SQLJ update batching. For a discussion of how this feature works, see "Update Batching".

This sample uses the following table definition:

DROP TABLE BATCH_DEMO;
CREATE TABLE BATCH_DEMO
            (EMPNO     NUMBER(7), 
             ENAME     VARCHAR2(20),
             HIREDATE  DATE,
             SAL       NUMBER(10, 2)
            );

Application code follows:

// Application source code--BatchDemo.sqlj
//
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.*;
import sqlj.runtime.*;
import oracle.sqlj.runtime.*;

/**
 Before compiling this demo with online checking,
 you must run the SQL script BatchDemo.sql.

 This demo shows the SQLJ batch update feature. 
 **/

public class BatchDemo {
 
  public static void main(String[] args) throws java.sql.SQLException
  {

   System.out.println("*** Batch Demo ***");

   try
   {
     Oracle.connect(BatchDemo.class, "connect.properties");
     System.out.println("Connected.");
    
     try
     {
        #sql { DELETE FROM BATCH_DEMO };
     }
     catch (SQLException e)
     {
        System.out.println("A SQL exception occurred: "+e);

        System.out.println("Attempting to create the BATCH_DEMO table");

        try
        {
           #sql { DROP TABLE BATCH_DEMO };
        }
        catch (SQLException ex) { };

        try
        {
           #sql { CREATE TABLE BATCH_DEMO
                    (EMPNO     NUMBER(7), 
                     ENAME     VARCHAR2(20),
                     HIREDATE  DATE,
                     SAL       NUMBER(10, 2)
                    )
                };
        }
        catch (SQLException ex)
        {
           System.out.println("Unable to create the BATCH_DEMO table: "+ex);
           System.exit(1);
        };
     }

     System.out.println(">>> Inserting 100 records <<<<");
     batchUpdate(1,    100, 201, "test0" );
     batchUpdate(10,   100, 401, "test1" );
     batchUpdate(100,  100, 601, "test2" );
     batchUpdate(1000, 100, 801, "test3" );

     System.out.println(">>> Inserting 1000 records <<<<");
     batchUpdate(1,    1000, 2001, "test0" );
     batchUpdate(10,   1000, 4001, "test1" );
     batchUpdate(100,  1000, 6001, "test2" );
     batchUpdate(1000, 1000, 8001, "test3" );

    }
    finally
    {
      Oracle.close();
    }

    System.out.println("*** End of Demo ***");
  }    
    
  public static void batchUpdate
                (int batchSize, int updateSize, int start, String name)
  throws java.sql.SQLException
  {

     if (batchSize==1)
     {
        System.out.print("Inserting one record at a time: ");
        System.out.flush();
     }
     else
     {
        System.out.print("Inserting in batch of "+batchSize+": ")
        System.out.flush();
     }

     long t = System.currentTimeMillis();

     ExecutionContext ec = new ExecutionContext();

     if (batchSize==1)
     {
       ec.setBatching(false);
     }
     else
     {
       ec.setBatchLimit(batchSize);
       ec.setBatching(true);
     }

     for (int i=start; i<start+updateSize; i++)
     {
        #sql [ec] { insert into  batch_demo(empno, ename,hiredate, sal)
                                 values(:i, :(name+"_"+i), sysdate,  :i )
                  };
     }
     #sql {commit};

     System.out.println("Done in "+((System.currentTimeMillis()-t)/1000.0)
                                            +" seconds.");
 }

}

Applet Sample

This section contains a generic sample applet that does not use Oracle-specific features. Both the SQLJ source code and the HTML page are included here, but Java source code for the user interface is not included. The demo in this section is located in the following directory:

[Oracle Home]/sqlj/demo/applets

This directory also includes source for the user interface and a version of the applet that uses Oracle-specific types.

For information about running the generic applet, see the Applet.readme file in the directory noted above. For information about running the Oracle-specific applet, refer to the AppletOracle.readme file.

For general discussion of SQLJ in applets, see "Running SQLJ in Applets".

Generic Applet HTML Page--Applet.html

This section contains the HTML page for the generic applet.

<html>
<head>
<title>SQLJ Applet</title>
</head>
<body>

<h1>SQLJ Applet</h1>

This page contains an example of an applet that uses SQLJ and
Oracle's Thin JDBC driver.<p>

<b>Note:</b>
This applet requires Netscape 4.0X with the JDK1.1 patch, or Netscape 4.5
or later, or Microsoft Internet Explorer 4.0 or later. 
<p>

The source code for the applet is in
<a href="AppletMain.sqlj">AppletMain.sqlj</a>

The source code for the applet's user interface is in
<a href="AppletUI.java">AppletUI.java</a>


<hr>
<!-- Properties of the APPLET tag:
      codebase="<the location of your classfiles or archives>"
      archive="<name of archive file>"

      Below we assume that you have a directory "dist" off of
      the root of your HTML server, and that you have jar-ed up
      the SQLJ runtime, JDBC thin driver, and Applet classes into
      the archive Applet.jar in this directory.

     Applet PARAMeters: adjust these to reflect your settings
      sqlj.url      - the JDBC URL
                      for example "jdbc:oracle:thin:@localhost:1521:orcl"
      sqlj.user     - the user name
      sqlj.password - the user password
 -->
<APPLET code="AppletMain.class"
        codebase="dist"
        archive="Applet.jar"
        width=640 height=480>
<PARAM name="sqlj.url"      
value="jdbc:oracle:thin:@<hostname>:<port>:<oracle_sid>">
<PARAM name="sqlj.user"     value="scott">
<PARAM name="sqlj.password" value="tiger">
</APPLET>

Generic Applet SQLJ Source--AppletMain.sqlj

This section contains the SQLJ source code for the generic applet. If you have access to the demo/applets directory and compare the Oracle-specific source (AppletOracle.sqlj) to this generic source, you will note that the only significant differences are as follows:

• The generic applet declares an iterator, EmpIter, that uses only the Java String type. The Oracle-specific applet declares an iterator, EmpOraIter, that uses oracle.sql.* types.

• The Oracle-specific applet must use the stringValue() method of the oracle.sql.NUMBER class to display numeric data.

/*
 * This applet extends the AppletInterface class that contains the
 * user interface component of the applet.
 *
 * This applet connects to a database to select and display
 * employee information.  It will also delete a select employee
 * from the database.
 */

// SQLJ-specific classes
import java.sql.SQLException;
import java.sql.DriverManager;
import sqlj.runtime.ExecutionContext;
import sqlj.runtime.ref.DefaultContext;
import oracle.jdbc.OracleDriver;
import oracle.sqlj.runtime.Oracle;

// Event handling classes
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;


public class AppletMain extends AppletUI
                        implements ActionListener
{ 

  // Declare a named iterator with several columns from the EMP table

  #sql public static iterator
       EmpIter(String empno, String ename, String job, String sal, String comm);


  // Applet initialization

  private DefaultContext m_ctx = null;

  public void init ()
  { 

    // Create the User Interface

    super.init();


    // Activate the buttons

    Query_button.addActionListener(this);
    Query_button.setActionCommand("query");

    Delete_button.addActionListener(this);
    Delete_button.setActionCommand("delete");


    // Open a connection to the database

    if (m_ctx == null)
    {
      // Connect to the database
      String url      = null;
      String user     = null;
      String password = null;
      try
      {
        url      = getParameter("sqlj.url");
        user     = getParameter("sqlj.user");
        password = getParameter("sqlj.password");
      }
      catch (NullPointerException exn) { }; // permit to call as an application

      try
      {
         if (   url==null || url.equals("") 
             || user==null || user.equals("")
             || password==null || password.equals(""))
         {
           // If the connect properties are not passed as parameters from the
           // HTML file, we pull them out of the connnect.properties resource.
           output.append("Connecting using the connect.properties resource\n");
           m_ctx = Oracle.getConnection(getClass(),"connect.properties");
         }
         else
         {
           output.append("Connecting using the PARAMeters in the HTML file\n");
           output.append("User " + user + " to " + url + "\n");

           DriverManager.registerDriver(new OracleDriver());
           m_ctx = Oracle.getConnection(url, user, password);
         }
         output.append("Connected\n");
      }
      catch (SQLException exn)
      {
         output.append("A SQL exception occurred: "+exn.getMessage()+"\n");
      }
    }
    else
    {
      output.append("Re-using connection.\n");
    }
  }


  // Perform the work

  public void actionPerformed(ActionEvent ev)
  {
    String command = ev.getActionCommand();

    try
    {

      if (command.equals("query"))
      { 
         int numRecords = 0;

         EmpIter ecur;

         // Clear the output area
         output.setText("");

         String x = query_name_field.getText();
         if (x==null || x.equals("") || x.equals("%"))
         {
            // Execute the query
            output.append("Executing: SELECT * FROM EMP\n");
            #sql [m_ctx] ecur = { SELECT * FROM EMP };
            while (ecur.next ())
            {
               output.append(ecur.empno() + "     " + ecur.ename() + "     "
                             + ecur.job() + "     $" + ecur.sal() + "\n");
               numRecords++;
            }
         }
         else
         {
            output.append("Executing: SELECT * FROM EMP WHERE ENAME = '"+
                                query_name_field.getText() + "'\n\n");
            #sql [m_ctx] ecur = { SELECT * FROM EMP 
                         WHERE ENAME = :(query_name_field.getText()) };
            while (ecur.next())
            {
               output.append("Employee's Number:   " + ecur.empno() + "\n");
               output.append("Employee's Name:     " + ecur.ename() + "\n");
               output.append("Employee's Job:      " + ecur.job() + "\n");
               output.append("Employee's Salary:   " + ecur.sal() + "\n");
               output.append("Employee's Commison: " + ecur.comm() + "\n");
               numRecords++;
            }
         }

         // we're done
         output.append(numRecords + " record"+( (numRecords==1)?"":"s" )+
                       " retrieved.\n");
         query_name_field.setText("");

         // ensure that iterator is closed
         ecur.close();
      } 
      else if (command.equals("delete"))
      {
         output.setText("");


         // Use an execution context to get an update count
         ExecutionContext ectx = new ExecutionContext();
         #sql [m_ctx, ectx]
             { DELETE FROM EMP WHERE ENAME = :(delete_name_field.getText()) };
         int numDeleted = ectx.getUpdateCount();

         if (numDeleted==1)
         {
           output.append("Deleted employee "+delete_name_field.getText()+ ".");
         }
         else if (numDeleted==ExecutionContext.EXCEPTION_COUNT)
         {
           output.append("An exception occurred during deletion.");
         }
         else
         {
           output.append("Deleted "+numDeleted+" employees.");
         }
         
         delete_name_field.setText("");
      }
    }
    catch (SQLException e)
    {
        // Report the error
        output.append("A SQL exception occurred:\n"+e.getMessage () + "\n");
    }
  }

  // it is important to rollback (or commit) at the end of the day, and
  // not leave the connection dangling

  public void stop()
  {
    if (m_ctx != null)
    {
      try
      {
         System.out.println("Closing the applet.");
         #sql [m_ctx] { ROLLBACK };
         // or, if you prefer: #sql [m_ctx] { COMMIT }; 

         m_ctx.close(); 
      }
      catch (SQLException exn) { }
      finally { m_ctx = null; }
    }
  };

  // Provide a main entry point so this works both, as an applet, and as
  // an application.
  public static void main(String[] args)
  {
    AppletFrame f = new AppletFrame(new AppletMain(), 600, 350);
  }

}

Server-Side Sample

This section contains a sample that runs in the server. This demo is located in the following directory:

[Oracle Home]/sqlj/demo/server

For a full discussion of SQLJ in the server, see Chapter 11, "SQLJ in the Server".

SQLJ in the Server--ServerDemo.sqlj

This example demonstrates a SQLJ application that runs in the Oracle9i embedded Java virtual machine.

Before trying to run this server-side demo application, refer to README.txt in the following directory:

[Oracle Home]/sqlj/demo/server

//---------------- Start of file ServerDemo.sqlj ----------------

import java.sql.Date;
import java.sql.SQLException;
 
class ServerDemo
{
  public static void main (String argv[]) 
  {
    // Note: No explicit connection setup is required
    //       for server-side execution of SQLJ programs.
   
    try {
      System.out.println("Hello! I'm SQLJ in server!");
    
      Date  today;
      #sql {select sysdate into :today from dual};
      System.out.println("Today is " + today);
 
      System.out.println("End of SQLJ demo.");
   } catch (SQLException e) {
         System.out.println("Error running main: " + e);
     }
  }
}

JDBC Versus SQLJ Sample Code

This section presents a side-by-side comparison of two versions of the same sample code--one version written in JDBC and the other in SQLJ. The objective of this section is to point out the differences in coding requirements between SQLJ and JDBC.

In the sample, two methods are defined: getEmployeeAddress(), which selects from a table and returns an employee's address based on the employee's number, and updateAddress(), which takes the retrieved address, calls a stored procedure, and returns the updated address to the database.

In both versions of the sample code, the following assumptions are made:

• The ObjectDemo.sql SQL script has been run to create the schema in the database and populate the tables. The SQL for this script is in "Definition of Object and Collection Types".

• A PL/SQL stored function UPDATE_ADDRESS(), which updates a given address, exists.

• The Connection object (for JDBC) and default connection context (for SQLJ) have been created previously by the caller.

• Exceptions are handled by the caller.

• The value of the address argument (addr) passed to the updateAddress() method can be null.

Both versions of the sample code reference objects and tables created by the ObjectDemo.sql script.

Note: The JDBC and SQLJ versions of the sample code are only partial samples and cannot run independently (there is no main() method in either).

JDBC Version of the Sample Code

Following is the JDBC version of the sample code, which defines methods to retrieve an employee's address from the database, update the address, and return it to the database. Note that the to-do items in the comment lines indicate where you might want to add additional code to increase the usefulness of the code sample.

import java.sql.*;
import oracle.jdbc.*;

/**
  This is what we have to do in JDBC
  **/
public class SimpleDemoJDBC                                  // line 7
{

//TO DO: make a main that calls this

  public Address getEmployeeAddress(int empno, Connection conn)
    throws SQLException                                     // line 13
  {
    Address addr;
    PreparedStatement pstmt =                               // line 16
      conn.prepareStatement("SELECT office_addr FROM employees" + 
       " WHERE empnumber = ?");
    pstmt.setInt(1, empno);
    OracleResultSet rs = (OracleResultSet)pstmt.executeQuery();
    rs.next();                                              // line 21
     //TO DO: what if false (result set contains no data)?
    addr = (Address)rs.getORAData(1, Address.getORADataFactory());
    //TO DO: what if additional rows? 
    rs.close();                                             // line 25
    pstmt.close();
    return addr;                                            // line 27
  }
  public Address updateAddress(Address addr, Connection conn)
    throws SQLException                                     // line 30
  {
    OracleCallableStatement cstmt = (OracleCallableStatement)
      conn.prepareCall("{ ? = call UPDATE_ADDRESS(?) }");   //line 33
    cstmt.registerOutParameter(1, Address._SQL_TYPECODE, Address._SQL_NAME);
                                                            // line 35
    if (addr == null) {
      cstmt.setNull(2, Address._SQL_TYPECODE, Address._SQL_NAME);
    } else {
      cstmt.setORAData(2, addr);
    } 
    
    cstmt.executeUpdate();                                  // line 42
    addr = (Address)cstmt.getORAData(1, Address.getORADataFactory());
    cstmt.close();                                          // line 44
    return addr;
  }
}   

Line 12:

In the getEmployeeAddress() method definition, you must pass the connection object to the method definition explicitly.

Lines 16-20:

Prepare a statement that selects an employee's address from the EMPLOYEES table on the basis of the employee number. The employee number is represented by a marker variable, which is set with the setInt() method. Note that because the prepared statement does not recognize "INTO" syntax, you must provide your own code to populate the address (addr) variable. Because the prepared statement is returning a custom object, cast the output to an Oracle result set.

Lines 21-23:

Because the Oracle result set contains a custom object of type Address, use the getORAData() method to retrieve it (the Address class can be created by JPublisher). The getORAData() method requires a "factory" object that it can use to create additional custom objects (additional Address objects in this case) as it retrieves the data to populate them. Use the static factory method Address.getORADataFactory() to materialize an Address factory object for the getORAData() method to use.

Because getORAData() returns a Datum, cast the output to an Address object.

Note that the routine assumes a one-row result set. The to-do items in the comment statements indicate that you must write additional code for the cases where the result set contains either no rows or more than one row.

Lines 25-27:

Close the result set and prepared statement objects, then return the addr variable.

Line 29:

In the updateAddress() definition, you must pass the connection object and the Address object explicitly.

The updateAddress() method passes an address object (Address) to the database for update, then fetches it back. The actual updating of the address is performed by the stored function UPDATE_ADDRESS() (the code for this function is not provided in this example).

Line 32-42:

Prepare an Oracle callable statement that takes an address object (Address) and passes it to the UPDATE_ADDRESS() stored procedure. To register an object as an output parameter, you must know the SQL type code and SQL type name of the object.

Before passing the address object (addr) as an input parameter, the program must determine whether addr has a value or is null. Depending on the value of addr, the program calls different setter methods. If addr is null, the program calls setNull(); if addr has a value, the program calls setORAData().

Line 43:

Fetch the return result addr. Because the Oracle callable statement returns a custom object of type Address, use the getORAData() method to retrieve it (the Address class can be created by JPublisher). The getORAData() method requires you to use the factory method Address.getORADataFactory to materialize an instance of an Address object. Because getORAData() returns a Datum object, cast the output to an Address object.

Lines 44, 45:

Close the Oracle callable statement, then return the addr variable.

Coding Requirements of the JDBC Version

Note the following coding requirements for the JDBC version of the sample code:

• The getEmployeeAddress() and updateAddress() definitions must explicitly include the connection object.

• Long SQL strings must be concatenated with the SQL concatenation character ("+").

• You must explicitly manage resources (for example, close result set and statement objects).

• You must cast datatypes as needed.

• You must know the _SQL_TYPECODE and _SQL_NAME values of the factory object and any objects that you are registering as output parameters.

• Null data must be explicitly processed.

• Host variables must be represented by parameter markers in callable and prepared statements.

• If you want to reuse statement objects, for example if you want to repeatedly call getEmployeeAddress() and updateAddress(), then you must code this appropriately. Both Oracle SQLJ and Oracle JDBC support statement caching.

Maintaining JDBC Programs

JDBC programs are potentially expensive to maintain. For example, in the above code sample, if you add another WHERE clause, then you must change the SELECT string. If you append another host variable, then you must increment the index of the other host variables by one. A simple change to one line in a JDBC program might require changes in several other areas of the program.

SQLJ Version of the Sample Code

Following is the SQLJ version of the code, defining methods to retrieve an employee address from the database, update the address, and return it to the database.

import java.sql.*;

/**
  This is what we have to do in SQLJ
  **/
public class SimpleDemoSQLJ                                  // line 6
{
  //TO DO: make a main that calls this

  public Address getEmployeeAddress(int empno)              // line 10
    throws SQLException
  {
    Address addr;                                           // line 13
    #sql { SELECT office_addr INTO :addr FROM employees
           WHERE empnumber = :empno };
    return addr;
  }
                                                            // line 18
  public Address updateAddress(Address addr)
    throws SQLException
  {
    #sql addr = { VALUES(UPDATE_ADDRESS(:addr)) };          // line 22
    return addr;
  }
}

Line 10:

The getEmployeeAddress() method does not require a connection object. SQLJ uses a default connection context instance, which would have been defined previously somewhere in your application.

Lines 13-15:

The getEmployeeAddress() method retrieves an employee address according to employee number. Use standard SQLJ SELECT INTO syntax to select an employee's address from the employee table if their employee number matches the one (empno) passed in to getEmployeeAddress(). This requires a declaration of the Address object (addr) that will receive the data. The empno and addr variables are used as input host variables.

Line 16:

The getEmployeeAddress() method returns the addr object.

Line 19:

The updateAddress() method also uses the default connection context instance.

Lines 19-22:

The address is passed to the updateAddress() method, which passes it to the database. The database updates it and passes it back. The actual updating of the address is performed by the UPDATE_ADDRESS() stored function (the code for this function is not shown here). Use standard SQLJ function-call syntax to receive the address object (addr) output by UPDATE_ADDRESS().

Line 23:

The updateAddress() method returns the addr object.

Coding Requirements of the SQLJ Version

Note the following coding requirements (and lack of requirements) for the SQLJ version of the sample code:

• An explicit connection is not required--SQLJ assumes that a default connection context has been defined previously in your application.

• No datatype casting is required.

• SQLJ does not require knowledge of _SQL_TYPECODE, _SQL_NAME, or factories.

• Null data is processed implicitly.

• No explicit code for resource management (for closing statements or results sets, for example) is required.

• SQLJ embeds host variables, in contrast to JDBC, which uses parameter markers.

• String concatenation for long SQL statements is not required.

• You do not have to register output parameters.

• SQLJ syntax is simpler. For example, SELECT INTO statements are supported and OBDC-style escapes are not used.

• You do not need to implement your own statement cache. By default, SQLJ will automatically cache #sql statements. This results in improved performance, for example, if you repeatedly call getEmployeeAddress() and updateAddress().