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