You may use your own custom connection context interface instead of the standard ConnectionContext interface using the IMPLEMENTS clause during the declaration step:

#sql public context myInterfCtx implements myCtxInterface;

where myCtxInterface contains your interface definition, which may be a more restrictive version (i.e., hide some functions) of ConnectionContext, and then instantiate it as follows:

myInterfCtx myConCtx1 =
     new myInterfCtx(("jdbc:oracle:thin:@localhost:1521/
myservice", <user>, <pass>, <autocommit>);

DataSource is the standard/recommended mechanism for establishing a JDBC connection. Similarly, SQLJ supports the datasource mechanism in two ways: associate a connection context with a JDBC datasource, and use SQLJ-specific datasources.

The sqlj.runtime.ConnectionContextFactory interface acts as a base interface for SQLJ datasource and specifies getDefaultContext() and getContext() methods:

getDefaultContext() returns a sqlj.runtime.ref.DefaultContext instance for the SQLJ default context (with signatures that enable you to specify auto-commit setting, user, and password for the underlying JDBC connection):

getContext() returns a sqlj.runtime.ConnectionContext instance of the user-declared connection context class (with signatures that enable you to specify auto-commit setting, user, and password for the underlying JDBC connection):

When a parameter is not specified, it is obtained from the underlying datasource that generates the connection (Note: auto-commit defaults to false when not set to true for the underlying datasource).

The oracle.sqlj.runtime.client package contains the OracleSqljXADataSource class.

Here is a code fragment using SQLJ datasources:

//Initialize the data source
SqljXADataSource sqljDS = new OracleSqljXADataSource();
sqljDS.setUser("scott");
sqljDS.setPassword("tiger");
sqljDS.setServerName("myserver");
sqljDS.setDatabaseName("ORCL");
sqljDS.setDataSourceName("jdbc/OracleSqljXADS");

//Bind the data source to JNDI
Context ctx = new InitialContext();
ctx.bind("jdbc/OracleSqljXADS");

int i;
#sql [ctx] { select empno into :i from emp where ename =
'SCOTT' };
System.out.println("SCOTT's emp# is : "+i);

For Oracle SQLJ code, statement caching of the underlying connection is enabled through methods on the connection context interface and classes.

The following static methods in the sqlj.runtime.ref.DefaultCon-text class and user-defined context classes set the default statement cache size for all connection contexts classes and their instances:

The following instance methods in the sqlj.runtime.ref.DefaultContext class and user-defined context classes set the cache size for the current connection context instance; this setting overrides the default setting.

Example:

System.out.println ("MaxFieldSize: " +
conCtx.getExecutionContext().getMaxFieldSize());

setMaxFieldSize(int): sets the maximum field-size.

int getMaxRows(): Returns the maximum number of rows that can be contained in SQLJ iterators or JDBC result sets created using this execution context instance (default is 0, i.e., no limit).

setMaxRows(int): Sets the maximum rows.

int getQueryTimeout(): Returns the timeout interval, in seconds, for any SQL operation that uses this execution context instance. A SQL exception is thrown if a SQL operation exceeds this limit (default is 0, i.e., no timeout limit).

setQueryTimeout(int): Sets the query timeout limit.

int getFetchSize(): Retrieves the current fetch size (previously set using setFetchSize()), for iterator objects generated from this ExecutionContext object.

setFetchSize(int): Sets the number of rows that should be fetched when more rows are needed from iterators.

int getFetchDirection(): Retrieves the default fetch direction for scrollable iterator objects that are generated from this ExecutionContext object (default is sqlj.runtime.ResultSetIterator.FETCH_FORWARD).

setFetchDirection(int): Sets the direction in which rows of scrollable iterator objects are processed (default is sqlj.runtime.ResultSetIterator.FETCH_FORWARD).

Notes:

getNextResultSet(int): is not implemented in Oracle SQLJ.

ResultSets in Oracle SQLJ are always bound as explicit arguments.

Multithreading in SQLJ depends on the multithreading support in the underlying JDBC driver. In Oracle JDBC, methods are synchronized on the connection (i.e., the driver sends a command to the server on behalf of a thread and then waits for its completion before the connection/socket can be used by another thread. Simultaneous multithreading is possible when threads are not sharing the same connection—in other words, it is not recommended!

Weakly typed iterators are interoperable/compatible with JDBC result sets (hence, familiar to JDBC aficionados); however, they do not benefit from the SQLJ semantics and type checking (see strongly typed iterators next).

ResultSet Iterators are instances of the sqlj.runtime.ResultSetIterator class, which implements the ResultSetIterator interface; this interface specifies the following methods:

ResultSet iterators are mainly used in Oracle SQLJ as host expressions, cursors in FETCH statements, and for accessing the JDBC ResultSet, as follows:

sqlj.runtime.ResultSetIterator RSetIter;
ResultSet JdbcRs;

#sql RSetIter = { SELECT ename FROM emp };
JdbcRs = RSetIter.getResultSet();
while (JdbcRs.next()) {
    System.out.println("ename: " + JdbcRs.getString(1));
}
RSetIter.close();

Scrollable iterators are another flavor of weakly typed iterators.

Like many things in SQLJ, strongly typed iterators enforce semantics checking of the queries; however, the minor downside is that these are not interoperable with JDBC. In order to define a strongly typed iterator, you must declare: its class, the names of the columns, and/or the Java types of the columns. Declaring both the name and the Java type of each column defines a “named iterator,” while declaring just the column types defines a “positional iterator.”

Named iterators are instances of user-declared classes; the SQLJ translator generates the corresponding classes implementing the ResultSetIterator and sqlj.runtime.NamedIterator interfaces. The beauty of named iterators, in contrast to JDBC getxxx()/getXXX(), reside, in the fact that the column names serve as getters or accessors for the retrieval of the value in the columns; in addition, the order of appearance of the column names in the SELECT statement and the iterator declaration does not matter.

The following code fragments illustrate named iterator declaration and usage:

//Fragment #1
#sql iterator namedIter1 (int empno, String ename, Double
sal, float comm);

namedIter1 nIter1
#sql nIter1 = { SELECT ename, empno, sal, comm FROM
employees};
...
while (nIter1.next()) {
...
}
nIter1.close();
//Fragment #2

#sql iterator namedIter2 (int empno, String ename);
#sql context Context;

namedIter2    nIter2;
Context ctx;
...
#sql [ctx] nIter2 = { select ename, empno from employees } ;
while (nIter2.next()) {
...
};
nIter1.close();

Positional iterators are instances of user-declared classes; at translation time, the SQLJ translator generates classes that implement the ResultSetIterator and sqlj.runtime.PositionedIterator interfaces. With positional iterators, the column names are not specified, only the types are. The data is retrieved using FETCH INTO, in the same order as the columns appear in the SELECT statement; however, the data types of the table columns must match the types of the iterator columns.

The PositionedIterator interface specifies the endFetch() method for determining if the last row has been reached.

The following code fragments define positional iterators:

// Fragment#3

#sql iterator posIter2 (int, String);
#sql context Context;
posIter2  pIter2;
Context ctx;
String ename;
int empno;
#sql [ctx] pIter2 = { select ename, empno from emp where empno = 7788}
;
while (true) {
  #sql [ctx] FETCH :pIter2 INTO :ename, :empno};
  if (pIter2.endFetch()) break;
  System.out.println("ename = " + ename + ", emp# = " + empno);
}
pIter2.close();

//Fragment #4

#sql iterator posIter1 (int, String, Double, float);

posIter1 pIter1
#sql pIter1 = { SELECT ename, empno, sal, comm FROM employees};
...
while (pIter1.next()) {

 // retrieve and process the row

};
pIter1.close();

//Fragment #5

#sql iterator posIter2 (int, String);
#sql context Context;
posIter2    pIter2;
Context ctx;
...
#sql [ctx] pIter2 = { select ename, empno from employees } ;
while (pIter2.next()) {

// retrieve and process the row

};
pIter1.close();

The ISO SQLJ supports the scrollable iterators that are similar/compatible with JDBC Scrollable ResultSet. The sqlj.runtime.Scrollable furnishes the following features:

The IMPLEMENTS SCROLLABLE clause is required for declaring scrollable iterators (i.e., implements sqlj.runtime.Scrollable):

public interface ScrollableResultSetIterator
                 extends ResultSetIterator
                 implements Scrollable
{...}

Instances of sqlj.runtime.ScrollableResultSetIterator class, which extends ResultSetIterator and Scrollable interfaces, can be used with FETCH CURRENT syntax to process result set iterators with SQLJ.

#sql { FETCH CURRENT FROM :sri INTO :i, :name };

#sql public static iterator ScrollNamIter
                           implements sqlj.runtime.Scrollable
                            (int empno, String ename);
ScrollNamIter sni;
#sql sni = { select empno, ename from emp };
int no;
String name;
//
#sql { FETCH :sni INTO :no, :name };
//
#sql { FETCH NEXT FROM :sni INTO :no, :name };
//
#sql { FETCH PRIOR FROM :sni INTO :no, :name };
//
#sql { FETCH FIRST FROM :sni INTO :no, :name };
//
#sql { FETCH LAST FROM :sni INTO :no, :name };
//
#sql { FETCH ABSOLUTE :(6) FROM :sni INTO :no, :name };
//
#sql { FETCH RELATIVE :(-3) FROM :sni INTO :no, :name };
//
#sql { FETCH CURRENT FROM :sni INTO :no, :name };

Oracle.close();

#sql public static iterator ScrollPosIter
                            implements sqlj.runtime.Scrollable
                            (int, String);
ScrollPosIter spi;
#sql spi = { select empno, ename from emp };

int no;
String name;

#sql { FETCH :spi INTO :no, :name };
#sql { FETCH NEXT FROM :spi INTO :no, :name };
#sql { FETCH PRIOR FROM :spi INTO :no, :name };
#sql { FETCH FIRST FROM :spi INTO :no, :name };
#sql { FETCH LAST FROM :spi INTO :no, :name };
#sql { FETCH ABSOLUTE :(6) FROM :spi INTO :no, :name };
#sql { FETCH RELATIVE :(-3) FROM :spi INTO :no, :name };
#sql { FETCH CURRENT FROM :spi INTO :no, :name };

Oracle.close();

Sometimes you may want to add new functionality relative to processing query results. SQLJ lets you extend the base iterator classes, as follows:

Similar to JDBC Scrollable ResultSets covered in Chapter 8, iterators may also be sensitive or insensitive to changes in the underlying data. Sensitivity is specified using the WITH clause during the iterator declaration, as follows:

#sql public static iterator SensRSetIter implements
     sqlj.runtime.Scrollable
     with (sensitivity=SENSITIVE)
     (String ename, int empno);

When sensitivity is not specified, it defaults to INSENSITIVE in the Oracle SQLJ implementation. However, you can use the ISO SQLJ ASENSITIVE, instead of INSENSITIVE.

#sql iterator AsNIter1 with (sensitivity=ASENSITIVE) (int s);
#sql iterator IsNIter2 with (sensitivity=INSENSITIVE) (String t);
#sql iterator AsNIter3 with
(sensitivity=sqlj.runtime.ResultSetIterator.ASENSITIVE) (int s);

#sql iterator IsNIter4 with
(sensitivity=sqlj.runtime.ResultSetIterator.INSENSITIVE) (String t);

User-defined interfaces let you customize the generated classes from which instances of iterators are created. Assume you want to restrict access to sensitive columns of a named iterator, such as date of birth, salary, Social Security number, and so on. By default, a named iterator class generates a getter method for every column involved in the SELECT clause. In order to achieve your goal, you just create an interface, which defines getters only for columns you want to expose. The following interface will generate a CustNamedIter class with only ename and empno accessors:

interface CustNamedIterInterf extends NamedIterator
{
  public String ename () throws SQLException;
  public int empno () throws SQLException;
}

#sql iterator cni implements CustNamedIterInterf (String emame, int
empno, data dob);

Similarly, you may want to restrict the columns returned by a positional iterator through a user-defined interface:

interface CustPosIterInterf extends PositionedIterator
{
  public String getString () throws SQLException;
  public int getInt () throws SQLException;
}

#sql iterator cpi implements CustPosIterInterf (String, int,
date);

The following instruction declares a connection context class that implements an interface named MyConnCtxtIntfc:

#sql public context MyContext implements
mypackage.MyConnCtxtIntfc;

The current row of a result set can be updated or deleted using a positioned update (i.e., UPDATE ... WHERE CURRENT OF ...) or a positioned delete (i.e., DELETE ... WHERE CURRENT OF ... ) statement that references the cursor name.

In Oracle Database 10g Release 2 (10.2), SQLJ supports positioned update and positioned delete operations; any type of iterator (a named, positional, or scrollable), which implements the sqlj.runtime.ForUpdate interface, can be used as a parameter to the WHERE CURRENT OF clause.

 import sqlj.runtime.ForUpdate;
 #sql iterator PUpdateIter implements ForUpdate
                                (int EMPNO, String ENAME);
 PUpdateIter    pit;
 #sql { UPDATE emp WHERE CURRENT OF :pit SET SAL = SAL * 1.2 }
;

 #sql { DELETE FROM emp WHERE CURRENT OF :c };

However, the following restrictions apply on the iterators in question:

The Oracle SQLJ supports the following standard constants in connection context declarations:

#sql iterator Iter2 with (returnability=false) (String s);

The Oracle SQLJ does not support the following standard constants in connection context declarations:

The SELECT clause includes valid query operations, which return exactly a single row into OUT Java host expressions (returning zero or several rows will throw an exception).

As you can see, the host expressions (covered in Chapter 12) are declared as :OUT parameters:

#sql { SELECT column_name1, column_name2
INTO :OUT host_exp1, :OUT host_exp2
FROM table WHERE condition };

However, it is a general practice to omit the :OUT qualifier because this is implied (using IN, IN/OUT qualifier for a SELECT INTO will throw an exception).

#sql { SELECT column_name1, column_name2
INTO host_exp1,  host_exp2
FROM table WHERE condition };

Examples:

int no;
int pay;
    #sql { SELECT empno, sal INTO :no, :pay
           FROM emp WHERE ename = 'SMITH' } ;
int count;
#sql { SELECT COUNT(*) INTO   :count  FROM   EMP };

The FETCH clause designates the operations of fetching data from a positional iterator (iterators are covered later).

#sql positer = { SELECT empno, sal FROM EMP
                 WHERE ENAME = 'SMITH' OR EMPNO = 7788 ORDER
                 BY EMPNO
               };
while (true) {
   #sql { FETCH :positer INTO :empno, :sal };
   if (positer.endFetch()) break;
   System.out.println("empno = " + empno + ", sal = " + sal);
}
positer.close();

The JDBC semantics for manual COMMIT/ROLLBACK (i.e., for DML when auto-commit is disabled) apply here.

#sql { COMMIT };
#sql { ROLLBACK};

Note that COMMIT and ROLLBACK operations do not impact open result sets and iterators (unless the iterator sensitivity is set to SENSITIVE).

The SAVEPOINT clause designates the ISO SQLJ SAVEPOINT syntax and the Oracle SQLJ SAVEPOINT syntax.

The ISO SQLJ SAVEPOINT syntax takes string literals. As seen previously, execution context store savepoint and furnish methods, which parallel the following methods:

#sql { SAVEPOINT savepoint1 };
#sql { ROLLBACK TO savepoint1 };
#sql { RELEASE SAVEPOINT savepoint1 };

The Oracle SAVEPOINT syntax differs from the ISO SAVEPOINT in three ways: it takes string variables instead of string literals; it uses SET SAVE-POINT instead of SAVEPOINT; and it uses RELEASE instead of RELEASE SAVEPOINT.

#sql { SET SAVEPOINT :savepoint };
#sql { ROLLBACK TO :savepoint };
#sql { RELEASE :savepoint };

The SET TRANSACTION clause allows setting the access mode and/or the isolation level. Like JDBC, the SET TRANSACTION instruction must be the first statement preceding any DML statements.

Syntax:

#sql { SET TRANSACTION <access_mode>, <ISOLATION LEVEL
isolation_level> };
#sql [context] { SET TRANSACTION <access_mode>,
                 <ISOLATION LEVEL isolation_level> };

Examples:

#sql { SET TRANSACTION READ WRITE };
#sql { SET TRANSACTION READ ONLY};

#sql { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE };
#sql [Ctxt] { SET TRANSACTION ISOLATION LEVEL SERIALIZABLE };
#sql { SET TRANSACTION READ WRITE, ISOLATION LEVEL
SERIALIZABLE };
#sql { SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ
ONLY};
#sql { SET TRANSACTION ISOLATION LEVEL READ COMMITTED, READ
WRITE };

#sql {SET TRANSACTION READ ONLY,ISOLATION LEVEL READ
UNCOMMITTED};
#sql {SET TRANSACTION READ WRITE,ISOLATION LEVEL READ
UNCOMMITTED};

The procedure clause is used for calling a stored procedure (PL/SQL, Java, or SQLJ). You can think of it as JDBC CallableStatement/OracleCallableStatement made simple, and here is the syntax:

#sql { CALL PROC(<PARAM_LIST>) };

Assume we have a basic (and stupid) Java stored procedure that returns the database date (I told you it’s stupid!) as out parameter.

Here is the SQLJ code fragment:

 java.sql.Date x;
 #sql { CALL basicproc(:out x) };
 System.out.println(" The Database Current date is " +
x.toString());

The complete code and script is shown in Listing 11.2.

basicProc.sql
=============
create or replace and resolve java source named basicproc as
import java.sql.Date;
public class basicProc {
  public static void foo(Date d[]) {
  d[0] = new java.sql.Date(System.currentTimeMillis());;
  }
}
/
create or replace procedure basicproc(d OUT DATE) as language java
name 'basicProc.foo(java.sql.Date[])';
/

connect.properties
==================
# 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:inst1
sqlj.url=jdbc:oracle:thin:@inst1
#sqlj.url=jdbc:oracle:oci8:@
#sqlj.url=jdbc:oracle:oci7:@

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

import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;
import java.sql.*;
public class basicProc
{
  public static void main(String[] args)
  {
    try
    {
      Oracle.connect(basicProc.class, "connect.properties");
     java.sql.Date x;
     #sql { CALL basicproc(:out x) };
     System.out.println("The Database Current date is " +
x.toString());
    }
    catch (java.sql.SQLException sqlex)
    {
     sqlex.printStackTrace();
     System.out.println(sqlex);
    }
  }
}
$ sqlj basicProc.sqlj
$ java -Doracle.net.tns_admin=$TNS_ADMIN basicProc
The Database Current date is 2006-01-04
$

The SQL clause refers to embedding valid DML statements (i.e., UPDATE, INSERT, and DELETE). Combine this with the SELECT clause, and you can get a feel for the simplicity, in its whole simplicity: no Statement/OracleStatement or PreparedStatement/OraclePreparedStatement or the like.

#sql { INSERT INTO TAB1 VALUES (PMTS','Kenneth',12,'Parker',NULL) };
#sql { INSERT INTO TAB2 SELECT * FROM TAB1 } ;
// A more comprehensive code fragment
#sql public static context ConCtx;
...
ctx = new ConCtx(getConnect());
...
int count = 0;
#sql [ctx] { SELECT count(*) into :count FROM Tab1 };

// Add 100 more rows
for (int i = 0; i < 100; i++) {
#sql [ctx] { INSERT INTO Tab1 values(:(i+count)) };


#sql { UPDATE EMP SET ENAME = 'Joe Bloe' WHERE EMPNO = 7788 };
#sql [ctx] { UPDATE EMP SET SAL=4500.0 WHERE EMPNO = 7788 } ;

The PL/SQL clause consists of using anonymous PL/SQL blocks such as BEGIN...END, or DECLARE ... BEGIN ... END, within SQLJ statements.

  // Example #1
  char name ;
  #sql { BEGIN SELECT ename INTO :out name FROM emp
               WHERE ename='KING'; END; };
  System.out.printlm("Name: " + name);

  // Example #2
  String pay;
  #sql { BEGIN EXECUTE IMMEDIATE
        :("update emp set sal=3500 where ename='KING'"); END;
};
  #sql { SELECT sal into :pay FROM emp WHERE ename='KING' };

  // Example #3

      a = 0;
      b = 0;
      c = 0;

      #sql
      {
        BEGIN
          :OUT a := 45345;
          :OUT b := 676;
          :OUT c := 9999;
        END;
      };

      System.out.println("a = " + a + ", b = " + b + ", c = "
+ c + "."  );

This is a very powerful technique. If you are familiar with PL/SQL, you can use this technique as a smooth transition to database programming with Java; JDBC might be a little bit intimidating. If you are unfamiliar, you can still use this same technique as a wrapper to anonymous PL/SQL block, provided you know what this block is doing.

The query clause consists of selecting data into a SQLJ iterator, covered in section 11.2.1.

 #sql iter = { SQLJ clause };
 #sql [conctx, exectx]  iter = { SQLJ clause };

A function clause is used for calling a stored function. The standard syntax is shown as follows:

#sql result = { VALUES (<Function> (<paramlist>))};

The Oracle-SQLJ also supports the following alternative syntax:

#sql result = { VALUES <Function> (<paramlist>)};

Assume a stored function, which is a modified version of the basic Java stored procedure (just for the sake of calling a function from SQLJ):

SQL> create or replace and resolve java source named basicfunc as
  2  import java.sql.Date;
  3  public class basicFunc {
  4
  5    public static String foo(Date d) {
  6      return d.toString();
  7    }
  8  }
  9  /

Java created.

SQL>
SQL> create or replace function basicfunc(d DATE) return VARCHAR2 as
language java
  2  name 'basicFunc.foo(java.sql.Date) return java.lang.String';
  3  /

Function created.

In SQL*Plus you invoke the function as follows:

SQL> select basicfunc(sysdate) from dual;
BASICFUNC(SYSDATE)
-----------------------------------------
2006-01-04

SQL>

Here is the equivalent SQLJ code fragment:

java.sql.Date x = new
java.sql.Date(System.currentTimeMillis());
#sql datstr = { VALUES basicfunc(:x) };
System.out.println(" The Curent date is " + datstr);

The complete code and script are shown in Listing 11.3.

connect.properties
===========
# 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:inst1
sqlj.url=jdbc:oracle:thin:@inst1
#sqlj.url=jdbc:oracle:oci8:@
#sqlj.url=jdbc:oracle:oci7:@

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

==============================

import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;

import java.sql.*;
public class basicFunc
{
  public static void main(String[] args)
  {
    try
    {
      Oracle.connect(basicFunc.class, "connect.properties");
      String datstr;
      java.sql.Date x = new java.sql.Date(System.currentTimeMillis());
      #sql datstr = { VALUES basicfunc(:IN x) };
      System.out.println("The System Curent date is " + datstr);
    }
    catch (java.sql.SQLException sqlex)
    {
      sqlex.printStackTrace();
      System.out.println(sqlex);
    }
  }
}
$ sqlj basicFunc.sqlj
$ java -Doracle.net.tns_admin=$TNS_ADMIN basicFunc
The System Curent date is 2006-01-04
$

The iterator conversion clause consists of converting a JDBC result set into an iterator using the CAST operator:

#sql iter = { CAST :rset };

Ths simple statement performs the following operations:

The dynamic expressions are called meta bind expressions. These are Java Strings, or String-valued Java expressions, optionally appended with a constant or static SQL expression, which is used during translation for syntax and semantics checking.

:{ Java_bind_expression }

or:

:{ Java_bind_expression :: SQL_replacement_code }

Meta bind expressions may be used as substitutes for the following:^[1]

In the previous descriptions, we’ve embedded the meta bind expressions directly into the SQL clause that is submitted to the SQL Translator; this is the usual way. You may also use anonymous PL/SQL block and JDBC for implementing dynamic SQL.

A context expression is a valid Java expression, including local variables, declared parameters, class fields, and array elements, that defines the name of a connection context instance or an execution context instance.

#sql [context_array[thread]]  result = {
VALUES(foo(:bar,:baz)) };

Code fragment:

 #sql context myCtx;
 #sql iterator myCurs (Double sal, int ename, String ename);
 // array of connection context
 myCtx [] ctx – new myCtx[2];
 ctx[0] = new Ctx(getConnect());
 // get an instance of iterator
 myCurs [] c = new myCurs[ 2 ];
 #sql [ctx[0]] c[0] = { SELECT SAL, EMPNO, ENAME FROM EMP
WHERE EMPNO = 7788 };

A result expression is any legal Java expression, including local variables, declared parameters, class fields, and array elements, that can receive query results or function return (i.e., appears on the left side of an equals sign).

#sql [context] result_array[i] = { VALUES(foo(:bar,:baz)) };

Code fragment:

 #sql context myCtx;
 #sql iterator myCurs (Double sal, int ename, String ename);

 myCtx ctx = new Ctx(getConnect());
 // array of iterator instances
 myCurs [] c = new myCurs[ 2 ];
 #sql [ctx] c[0] = { SELECT SAL, EMPNO, ENAME FROM EMP WHERE
EMPNO = 7788 };

As we have seen when discussing JDBC, the Oracle JDBC brings extraordinary connection services in RAC/Grid environments, including implicit connection caching, fast connection fail-over, and runtime connection load balancing. SQLJ can benefit from all of these by just delegating connection concerns to JDBC. Listing 11.5 illustrates this point:

import sqlj.runtime.ref.DefaultContext;
import java.sql.*;
import oracle.sqlj.runtime.*;
import oracle.jdbc.pool.OracleDataSource;

public class myInterop
{
  public static void main(String[] args) throws java.sql.SQLException
  {
    try {
      // Create an OracleDataSource
      OracleDataSource ods = new OracleDataSource();

     // Set the URL, using TNS Alias with JDBC-Thin
     String url = "jdbc:oracle:thin:scott/tiger@inst1";
     ods.setURL(url);
     Connection conn = ods.getConnection(); // Retrieve a connection
     conn.setAutoCommit(false); // Disable Auto Commit

     // Create/Set SQLJ context from JDBC connection
     // From here let's speak SQLJ
    DefaultContext.setDefaultContext(new DefaultContext(conn));
    System.out.println("Create a dummy function.");
    #sql { CREATE OR REPLACE FUNCTION R(dummy integer) RETURN INTEGER AS
            i INTEGER;
           BEGIN
                 SELECT SAL INTO i FROM EMP WHERE ENAME = 'SMITH';
                 RETURN i;
           END; };

    int start_sal;
    #sql start_sal = { VALUE R(100) };
    System.out.println("Starting pay: " + start_sal );

    int raise = 300;
    #sql { UPDATE EMP SET SAL = SAL + :raise WHERE ENAME = 'SMITH' };
    System.out.println(" Giving Raise to  SMITH ");
    int end_sal;
    #sql end_sal = { VALUE R(100) };
    System.out.println("Ending pay: " + end_sal +
                       "  - Raise amt is "+ (end_sal-start_sal));
   conn.close();
  } catch (SQLException e) {
        e.printStackTrace();
  }
 }
}


$ sqlj myInterop.sqlj
$ java -Doracle.net.tns_admin=$TNS_ADMIN myInterop
Create a dummy function.
Starting pay: 800
Giving Raise to SMITH
Ending pay: 1100  - Raise amt is 300
$