Sample Applications

Advanced Samples

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

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 that the database 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 that the database 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 that the database 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 "Subclassing 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());
    }
  }

}

PL/SQL in SQLJ for Dynamic SQL--DynamicDemo.sqlj

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

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

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

public class DynamicDemo
{

  public static void main(String[] args) throws SQLException 
  {
    // set the default connection to the URL, user, and password
    // specified in your connect.properties file
    Oracle.connect(DynamicDemo.class, "connect.properties");

    try
    {
       String table_name = "emp";
   
       // dynamic DDL
       
       String index_name = "sal_index";
       String index_col = "sal";
       dynamicDrop(index_name);
       dynamicCreate(table_name, index_name, index_col);
   
    
       // dynamic DML
       
       String ename; 
       int empno;  double sal;
   
       ename = "julie"; empno = 8455;  sal = 3500;
       dynamicInsert(table_name, ename, empno, sal);
   
       dynamicDelete("empno", "8455");
   
       empno = 7788;  sal = 6000.00;
       staticUpdateReturning(empno, sal);  // try static first :)
   
       empno = 7788; sal = 7000.00;
       dynamicUpdateReturning(empno, sal);  
   
   
       // dynamic 1-row query
       dynamicSelectOne(table_name);
   
       // dynamic multi-row query
       dynamicSelectMany(" sal > 2000.00");
       dynamicSelectMany(null);
    }
    finally
    { 
       #sql { ROLLBACK };
       Oracle.close();
    }
  }


  private static void dynamicDrop (String index_name) throws SQLException {

     System.out.println("Dropping index " + index_name);
     #sql {  begin
                execute immediate 'drop index ' || :index_name;
             end;
          };
  }

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

     String ddl = "create index " + index_name + " on " + table_name + 
                  "(" + index_col + ")";
     #sql {  begin 
                execute immediate :ddl;
             end; 
          };
   }


  private static void dynamicInsert(String which_table, String ename, 
                                    int empno, double sal) 
          throws SQLException {

    System.out.println("dynamic insert on table " + which_table 
                       + " of employee " + ename);
    #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;
         };
  }


  private static void dynamicDelete(String which_col, String what_val) 
          throws SQLException {

    System.out.println("dynamic delete of " + which_col + 
                    " = " + what_val + " or " + which_col + " is null" );

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


  private static void staticUpdateReturning (int empno, double newSal)
          throws SQLException {

    System.out.println("static update-returning for empno " + empno);
   String ename;
   #sql { begin 
            update emp set sal = :newSal 
            where empno = :empno
            returning ename into :OUT ename;   -- :OUT is for SQLJ bind
          end;
        };
   System.out.println("Updated the salary of employee " + ename);

  }


  private static void dynamicUpdateReturning (int empno, double newSal ) 
          throws SQLException {

   System.out.println("dynamic update-returning for empno " + empno);
   String ename;

   #sql { begin
            execute immediate 
            'update emp set sal = :1  where empno = :2 ' || 
            'returning ename into :3'
            using :newSal, :empno, OUT :OUT ename ;
              -- note weird repeated OUT, one for PL/SQL bind, one for SQLJ
          end;
        };
   System.out.println("Updated the salary of employee " + ename);

  }

  private static void dynamicSelectOne(String which_table) 
          throws SQLException  {

    System.out.println("dynamic 1-row query on table " + which_table);

    int countRows;

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

    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(String what_cond)
          throws SQLException {

    System.out.println("dynamic multi-row query on table emp");

    Employees empIter; 

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

    String query = "select ename, sal from emp " + 
                 (((what_cond == null) || (what_cond.equals(""))) ? "" : 
                 (" where " + 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;
    };

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

  };

}