Advanced Samples

This section presents examples that demonstrate some of the more advanced features of 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)

  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;
  
      dbConnect();
  
      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);
     }
  
    private static void dbConnect() throws SQLException {
      // set the default connection to the URL, user, and password
      // specified in your connect.properties file
      Oracle.connect(RefCursDemo.class, "connect.properties");
    }
  
  
    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.

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);
    }
  } 

  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");
    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();  
  }
}

Multiple Connection Schemas--MultiSchemaDemo.sqlj

The following is an example of a SQLJ application using multiple connection contexts. It implicitly uses an object of the DefaultContext class for one type of schema and uses an instance of the declared connection context class DeptContext for another type of schema.

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");

    // 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();
  }

  // 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 constructs that you can use to fetch rows of data using SQLJ and also shows the use of multiple connection contexts.

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");

    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);
  }

  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);
  }
}

Prefetch Demo--PrefetchDemo.sqlj

This sample shows the use of row-prefetching through SQLJ and insert-batching through JDBC.

For information about prefetching and batching, see "Row Prefetching".

import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.OracleConnection;
import oracle.jdbc.driver.OraclePreparedStatement;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;

/**
 Before executing this demo, or compiling this demo with
 online checking, you must 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.
 Note that SQLJ currently does not support JDBC batching.
 However, it is always possible to interoperate with JDBC
 when it is necessary to exploit batching.
**/

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
     {
       #sql { DELETE FROM PREFETCH_DEMO };
     }
     catch (SQLException exn)
     {
       System.out.println("A SQL exception occurred: "+exn);
       System.out.println
            ("You probably forgot to run the PrefetchDemo.sql script");
       System.out.println("Run the script and then try again.");
       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);

     System.out.println(">>> Selecting data from the PREFETCH_DEMO table <<<");

     System.out.println("Default Row Prefetch value is:  " 
                        + conn.getDefaultRowPrefetch());

     // We show four row prefetch settings:
     //  1. every row fetched individually
     //  2. prefetching the default number of rows (10)
     //  4. 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);
     }

  }

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

    PrefetchDemoCur c;

    long start = System.currentTimeMillis();
    #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 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.");
  }

}

---