A
Performance and Debugging

This appendix discusses features, utilities, and tips to enhance performance of your SQLJ application and to debug your SQLJ source code at runtime. The following topics are discussed:

• Performance Enhancement Features

• AuditorInstaller Customizer for Debugging

• Additional SQLJ Debugging Considerations

Performance Enhancement Features

Oracle SQLJ includes features to enhance your performance by making data access more efficient. These include the following:

• row prefetching--Query results are sent to your application in groups of rows, instead of one at a time.

• update batching--Database updates, inserts, and deletes are sent to the database in batches, instead of one at a time.

• statement caching--Prepared statements are saved in memory and reused, to avoid repeated processing in the server.

• column definitions--Column types and sizes are predefined, possibly saving round trips to the database.

• parameter size definitions--Sizes of host variables are predefined for more efficient memory usage.

For information about underlying Oracle JDBC support for these features, see the Oracle9i JDBC Developer's Guide and Reference.

Your application might also benefit if you use Oracle-specific code generation through the SQLJ translator -codegen=oracle setting. The generated code will be optimized with direct calls to Oracle JDBC, eliminating the overhead of intermediate calls to the SQLJ runtime (which in turn would call JDBC). For information, see "Oracle-Specific Code Generation (No Profiles)".

Note: Neither Oracle SQLJ nor Oracle JDBC supports batch fetches, which is the fetching of sets of rows into arrays of values. You may, however, be able to use Oracle row prefetching to obtain some of the benefits of batch fetching.

In addition to the preceding Oracle SQLJ (and JDBC) performance enhancements, you can employ optimizer hints in the SQL operations within a SQLJ program, as you can in any Oracle SQL operations.

Oracle SQL allows you to tune your SQL statements by using /*+ or --+ comment notation to pass hints to the Oracle SQL optimizer. The SQLJ translator recognizes and supports these optimizer hints, passing them at runtime as part of your SQL statement.

You can also define cost and selectivity information for a SQLJ stored function, as for any other stored function, using the extensibility features for SQL optimization in Oracle9i. During SQL execution, the optimizer invokes the cost and selectivity methods for the stored function, evaluates alternate strategies for execution, and chooses an efficient execution plan.

For information about the Oracle optimizer, see the Oracle9i SQL Reference.

Note that using Oracle performance extensions in your code requires the following:

• Use one of the Oracle JDBC drivers.

• Customize the profiles appropriately, or use Oracle-specific code generation.

  The default customizer, oracle.sqlj.runtime.util.OraCustomizer, is recommended. Alternatively, you can avoid profiles through the -codegen=oracle translator setting.

• Use the Oracle SQLJ runtime when your application runs.

(The Oracle SQLJ runtime and an Oracle JDBC driver are required by your application whenever you customize profiles with the Oracle customizer, even if you do not actually use Oracle extensions in your code.)

Row Prefetching

Standard JDBC receives the results of a query one row at a time, with each row requiring a separate round trip to the database (or middle-tier database cache). Row prefetching allows you to receive the results more efficiently, in groups of multiple rows each.

Use the setFetchSize() method of an ExecutionContext instance to set the number of rows to be prefetched whenever you execute a SELECT statement (for SQLJ statements using the particular ExecutionContext instance).

The getFetchSize() method of an ExecutionContext instance returns the current prefetch size, as an int value.

Here is an example of setting the prefetch size to 20 by getting the default execution context instance of the default connection context instance and calling the setFetchSize() method:

DefaultContext.getDefaultContext().getExecutionContext().setFetchSize(20);

(It is also possible to set the prefetch size directly on the underlying OracleConnection object, using the JDBC API, but in SQLJ this is discouraged.)

To specify the number of rows to prefetch for queries that use a given connection context instance, use the underlying JDBC Connection instance cast to an Connection. Following is an example that sets the prefetch value to 20 for your default connection:

((Connection)DefaultContext.getDefaultContext().getConnection()).setDefaultRowPrefetch(20);

Each additional connection context instance you use must be set separately, as desired. For example, if ctx is an instance of a declared connection context class, set its prefetch value as follows:

((Connection)ctx.getConnection()).setDefaultRowPrefetch(20);

There is no maximum row-prefetch value. The default is 10 in JDBC, and this is inherited by SQLJ. This value is effective in typical circumstances, although you might want to increase it if you receive a large number of rows.

See "Prefetch Demo--PrefetchDemo.sqlj" for a sample application showing row prefetching through SQLJ and update batching through JDBC.

Statement Caching

SQLJ offers a statement caching feature that improves performance by saving executable statements that are used repeatedly, such as in a loop or in a method that is called repeatedly. When a statement is cached before it is re-executed, the code does not have to be reparsed, the statement object does not have to be recreated, and the parameter size definitions do not have to be recalculated.

There is also an underlying JDBC statement cache. In some cases, the SQLJ and JDBC statement caches are used separately, and in other circumstances SQLJ uses the JDBC cache instead of maintaining its own. Details are described later in this section.

Oracle JDBC Support for Statement Caching

Statement caching is a standard SQLJ feature that does not require any particular JDBC driver; however, using a driver that implements the interface sqlj.runtime.profile.ref.ClientDataSupport allows more robust caching. Oracle9i JDBC drivers implement this interface, providing the following features:

• a separate cache for each database connection, instead of a single static cache for the entire application

• the ability to share cached statements between multiple instances of a connection context class that share the same underlying connection

When a single cache is used, as is the case with a generic JDBC driver that does not implement ClientDataSupport, a statement executed in one connection can cause a cached statement from another connection to be flushed (if the statement cache size--the maximum number of statements that can be cached--is exceeded).

Note: If you use Oracle-specific code generation, through the SQLJ translator -codegen=oracle setting, then SQLJ uses the JDBC statement cache instead of a separate SQLJ statement cache. See "Connection Context Methods for Statement Cache Size" below for information about how to control this cache.

See the Oracle9i JDBC Developer's Guide and Reference for information about Oracle JDBC statement caching.

Oracle Customizer Option for Statement Cache Size

With standard SQLJ code generation, SQLJ statement caching is enabled in your application by default when you use the Oracle customizer, which is typically executed as part of Oracle SQLJ translation.

The default SQLJ statement cache size is 5, meaning a maximum of five statements are cached per connection. You can alter the statement cache size or disable statement caching (by setting the cache size to 0) through the Oracle customizer stmtcache option. For information, see "Oracle Customizer Statement Cache Size Option (stmtcache)".

If you use multiple connection context classes and, therefore, have multiple profiles, you can set their statement cache sizes individually by running SQLJ (actually, the customizer) separately for each profile.

At runtime, the appropriate SQLJ profile determines the statement cache size for a connection. This would be the profile that corresponds to the first connection context class instantiated for this connection. Its statement cache size setting, if any, is determined according to how you set the Oracle customizer stmtcache option when you customized the profile. The runtime statement cache size for a connection is set when the first statement on that connection is executed.

Connection Context Methods for Statement Cache Size

If you use Oracle-specific code generation (through the SQLJ translator -codegen=oracle setting), there is no profile customization and therefore the stmtcache option is unavailable. To alter statement cache size (from the default of five statements) or disable statement caching (with a setting of 0), you must use method calls in your code instead. See "Oracle-Specific Code Generation (No Profiles)" for description of Oracle-specific code generation.

The following Oracle-specific (non-standard) static methods have been added to the sqlj.runtime.ref.DefaultContext class, and are also included in any connection context classes you declare:

• public static void setDefaultStmtCacheSize(int)--Set the default statement cache size for all connection contexts. This becomes the initial statement cache size for any subsequently created instance of any connection context class, not just the class upon which you call the method. The method call does not affect connection context instances that already exist.

  Without calling this method, the default statement cache size is five statements.

• public static int getDefaultStmtCacheSize()--Retrieve the current default statement cache size for connection contexts.

  Important: Be aware, however, that with Oracle-specific code generation there are important interactions between SQLJ statement caching and JDBC statement caching. See "Interaction Between JDBC Statement Caching and SQLJ Statement Caching" and "Interaction Between JDBC and SQLJ Statement Caching Status and Size" below.

And the following Oracle-specific instance methods have also been added to the DefaultContext class and are included in any other connection context classes:

• public void setStmtCacheSize(int)--Set the statement cache size for the underlying connection of the particular connection context instance (overrides the default).

• public int getStmtCacheSize()--Retrieve the current statement cache size for the underlying connection of the connection context instance.

  Note: These methods delegate to methods of the same name on the underlying OracleConnection object.

Interaction Between JDBC Statement Caching and SQLJ Statement Caching

By default, when statement caching is enabled, Oracle JDBC uses an implicit statement caching mechanism. (See the Oracle9i JDBC Developer's Guide and Reference for more information about this mechanism.) This section discusses interaction between JDBC and SQLJ statement caching. Also see "Interaction Between JDBC and SQLJ Statement Caching Status and Size" below.

If you use standard code generation for your SQLJ application (through the translator default -codegen=iso setting), then this JDBC implicit cache will be enabled and is independent of SQLJ statement caching. So with standard code generation, be aware of the following:

• SQLJ and JDBC use different physical statement caches.

• Setting the Oracle customizer stmtcache option affects SQLJ statement caching only, not JDBC implicit statement caching. This includes the circumstance of setting stmtcache=0, which disables SQLJ statement caching but does not disable JDBC implicit statement caching.

If you use Oracle-specific code generation for your SQLJ application (through the translator -codegen=oracle setting), then a unified caching scheme is used. In this circumstance, be aware that SQLJ and JDBC use the same physical statement cache; namely, the underlying JDBC cache. (SQLJ uses the JDBC cache in explicit mode, while JDBC uses it in implicit mode. See the Oracle9i JDBC Developer's Guide and Reference for information about the differences between these modes.)

Also remember that SQLJ and JDBC statement caching functionality have different semantics and behaviors. As noted earlier, SQLJ statement caching applies only to single statements used repeatedly, such as in a loop or through repeated calls to the same method. Consider the following example:

...
#sql { same SQL operaton }; // occurrence #1
...
Java code
...
#sql { same SQL operaton }; // occurrence #2
...
Java code
...
#sql { same SQL operaton }; // occurrence #3
...

Assume the three SQL operations are identical, including white space.

SQLJ caching would consider these three occurrences of the same SQL operation to be three different statements. They will occupy three separate slots in the cache. JDBC implicit caching, however, would recognize these as identical statements, using only a single cache slot for all three. The statement would be reused for occurrence #2 and occurrence #3.

Interaction Between JDBC and SQLJ Statement Caching Status and Size

This section discusses further interaction between JDBC and SQLJ statement caching when using Oracle-specific code generation, particularly regarding the size of the cache and whether caching is enabled (non-zero cache size). Also see "Interaction Between JDBC Statement Caching and SQLJ Statement Caching" above.

In particular, there are additional considerations regarding statement cache size and the related static connection context class methods:

• setDefaultStmtCacheSize(int)

and:

• int getDefaultStmtCacheSize()

These methods set and get the global, static default minimum size for the JDBC statement cache. Whenever the actual JDBC statement cache size on the underlying JDBC connection is smaller than the default size of the connection context class, the SQLJ runtime will attempt to increase the JDBC statement cache size to the default value.

If, on the other hand, the actual JDBC statement cache size is larger, then the SQLJ runtime will not attempt to perform a change in the cache size. The SQLJ runtime checks the actual JDBC cache size against the default size set whenever it creates a SQLJ connection context instance.

It is important to note that these methods have the same effect regardless of the context class on which they are issued, since they modify or report the same underlying static field.

As an example, assume the following connection context class declarations:

#sql context CtxtA;
#sql context CtxtB;

In this case, each of the following three code instructions has the same effect--that whenever a new SQLJ connection context instance is subsequently created, it will not try to enable JDBC statement caching:

sqlj.runtime.ref.DefaultContext.setDefaultStmtCacheSize(0);

or:

CtxtA.setDefaultStmtCacheSize(0);

or:

CtxtB.setDefaultStmtCacheSize(0);

In order to disable JDBC statement caching for the underlying JDBC connection conn, you must issue the following:

((oracle.jdbc.OracleConnection)conn).setStmtCacheSize(0);
sqlj.runtime.ref.DefaultContext.setDefaultStmtCacheSize(0);

If you do not issue the second instruction, then whenever a SQLJ connection context instance is created on the underlying connection conn, JDBC statement caching would be enabled on conn according to the default statement cache size (which is initialized to 5).

On the other hand, in order to turn on JDBC statement caching with a cache size of at least 10 for any SQLJ connection context instances that are created subsequently, it is sufficient to issue the following:

sqlj.runtime.ref.DefaultContext.setDefaultStmtCacheSize(10);

Important: If a SQLJ connection context instance is created on an underlying JDBC pooled connection, then SQLJ will not be able to change the JDBC statement cache size. In this case, the desired JDBC statement cache size must be set explicitly on the underlying physical connection(s).

Statement Caching Limitations and Notes

Using a statement cache, even of size 1, will improve the performance of almost any SQLJ application. Be aware of the following, however:

• There is no benefit if each statement is executed only once.

• Try to avoid interleaving statements executed once with statements executed multiple times. The statements being executed only once would needlessly take up space in the statement cache, which becomes an issue when you reach the statement cache size limit. As an alternative, if you use standard code generation you can use a separate connection context class for statements that are executed only once, and disable statement caching for that connection context class.

• Distinct statements with identical SQL operations are treated the same way as any distinct statements--each is processed and cached separately. As an alternative, put the SQL operation in a method, and call the method repeatedly, instead of using distinct statements.

• Be careful in choosing an appropriate statement cache size. If it is too small, then the cache might fill up, resulting in statements being flushed before they are re-executed. If it is too large, then database resources or program resources may be exhausted.

Statement Caching Notes

Also be aware of the following general notes regarding statement caching.

• If you use Oracle-specific code generation, using separate SQLJ connection context instances to have separate statement caching behavior will not work if the connection contexts share the same underlying JDBC connection instance. This is because under Oracle-specific generation, SQLJ uses the JDBC statement cache.

• For Oracle applications, the statement cache size plus the maximum number of open JDBC statements in your application (both directly and through SQLJ) should total less than the maximum number of cursors available for a session, because the maximum number of cursors defines the maximum number of statements that can be open simultaneously.

  With standard code generation, consider both the SQLJ statement cache size, as determined by the -P-Cstmtcache option (default 5), and the JDBC implicit statement cache size (default 5). With Oracle-specific code generation, consider only the JDBC implicit statement cache size (there is no separate SQLJ cache in this case).

• Using a statement cache generally does not change the execution semantics of an operation itself, although there are some scenarios where it does. For example, if you have a statement that throws an exception when its resources are released, then using a cache would mean that the exception would not be thrown until the connection is closed or the statement is flushed from the cache (which happens when the cache size is exceeded).

Update Batching

Update batching (referred to as batch updates in the Sun Microsystems JDBC 2.0 specification) allows UPDATE, DELETE, and INSERT statements that are batchable and compatible (as discussed below) to be collected into a batch and sent to the database for execution at once, saving round trips to the database. This feature is included in the JDBC 2.0 and SQLJ specifications, and is therefore supported by both Oracle9i JDBC and Oracle9i SQLJ. Update batching is typically used for an operation that is executed repeatedly within a loop.

In SQLJ, update batching is tied to execution context usage. This feature is enabled or disabled in each execution context, independently of any other execution context, and each execution context instance maintains its own batch.

Important: Be aware of the following for update batching: You must use Oracle-specific code generation or customize your application with the Oracle customizer. It is highly advisable to disable auto-commit mode. This gives you control of what to commit and what to roll back in case of an error during batch execution.

Batchable and Compatible Statements

Two criteria determine whether a statement can be added to an existing batch of statements:

• Whether it is batchable. You cannot batch some kinds of statements under any circumstances.

• Whether it is compatible with statements in the existing batch.

Batchable

For Oracle9i SQLJ, the following kinds of statements are batchable:

• UPDATE

• INSERT

• DELETE

Note the following restriction, however:

• UPDATE and INSERT statements with one or more stream host expressions are not batchable.

In future releases or other implementations of SQLJ, additional kinds of statements might be batchable (such as stored procedure calls or DDL statements).

Compatible

In Oracle9i SQLJ, only multiple instances of the same statement are compatible. This can occur in one of two circumstances:

• A statement is executed repeatedly in a loop.

• A statement is executed in a method, and the method is called repeatedly.

In future releases or other implementations of SQLJ, additional kinds of statements might be compatible (such as instances of different statements that have no host expressions).

Enabling and Disabling Update Batching

SQLJ performs update batching separately for each execution context instance. Each one can have update batching enabled independently of your other execution context instances, and each maintains its own batch.

To enable or disable update batching for a particular execution context instance, use the setBatching() method of that execution context instance. This method takes boolean input, as follows:

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

or:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(false);
...

Update batching is disabled by default.

Note: The setBatching() method does not affect an existing statement batch. Neither enabling nor disabling update batching causes an existing batch to be executed or canceled.

Use the isBatching() method of an execution context instance to determine if update batching is enabled for that execution context, as in the next example.

ExecutionContext ec = new ExecutionContext();
...
boolean batchingOn = ec.isBatching();

This does not, however, indicate whether a batch is currently pending.

Explicit and Implicit Batch Execution

You can explicitly execute a pending update batch as desired, but it might also be implicitly executed under certain circumstances.

Note: It is important to be aware of what happens when an exception occurs in the middle of a batch execution. See "Error Conditions During Batch Execution".

Explicit

Use the executeBatch() method of the execution context instance to explicitly execute an update batch. This method returns an int array of update counts with meanings as described in "Execution Context Update Counts".

Following is an example of explicitly executing a batch:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...
double[] sals = ...;
String[] empnos = ...;
for (int i = 0; i < empnos.length; i++)
{
   #sql [ec] { UPDATE emp SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
int[] updateCounts = ec.executeBatch();
...

Notes: If you invoke executeBatch() when the execution context instance has no pending batch, then the method returns null.

Implicit

When a pending update batch exists, it is implicitly executed in the following circumstances:

• An executable statement is encountered that is not batchable. In this case the existing batch is executed first, then the non-batchable statement is executed.

• An update statement is encountered that is batchable, but is not compatible with the statements in the existing batch (in other words, is not an instance of the same statement). In this case the batch is executed, then a new batch is created, starting with the incompatible statement.

• A predefined batch limit--that is, a specified number of statements--is reached. This is discussed in "Setting a Batch Limit".

Following is an example. First one batch is created and executed implicitly when an unbatchable statement is encountered, then a new batch is created and executed implicitly when a batchable, but incompatible, statement is encountered:

ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...
/* Statements in the following loop will be placed in a batch */
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec] { UPDATE emp SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}

/* a SELECT is unbatchable so causes the batch to be executed */
double avg;
#sql [ec] { SELECT avg(sal) INTO :avg FROM emp };

/* Statements in the following loop will be placed in a new batch */
double[] comms = ...;
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec] { UPDATE emp SET comm = :(comms[i]) WHERE empno = :(empnos[i]) };
}

/* the following update is incompatible with the second batch, so causes it to 
be executed */
int smithdeptno = ...;
#sql [ec] { UPDATE emp SET deptno = :smithdeptno WHERE ename = 'Smith' };

To obtain the update count array for a batch executed implicitly, invoke the getBatchUpdateCounts() method of the execution context instance. This returns the update counts for the last batch to be executed successfully in this execution context instance. The following code statement could be inserted after the SELECT and after the last UPDATE:

int[] updateCounts = ec.getBatchUpdateCounts();

The meanings of these update counts are described in "Execution Context Update Counts".

Note: If no update batch has been executed successfully for the execution context instance, then getBatchUpdateCounts() returns null.

Canceling a Batch

To cancel the batch that is pending in an execution context, use the cancel() method of the execution context instance. You can, for example, cancel a batch that has been executed, but not yet committed, in the event that an exception occurred during batch execution. Following is an example:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...
double[] sals = ...;
String[] empnos = ...;
for (int i = 0; i < empnos.length; i++)
{
   #sql [ec] { UPDATE emp SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
   if (!check(sals[i], empnos[i])) //assume "check" is a user-supplied function
   {
      ec.cancel();
      throw new SQLException("Process canceled.");
   }
}

try 
{ 
   int[] updateCounts = ec.executeBatch();
} catch ( SQLException exception) { ec.cancel(); }
...

When you cancel a batch, the next batchable statement will start a new batch.

Notes: Calling cancel() will also cancel any statement currently executing. Canceling a batch does not disable update batching.

Execution Context Update Counts

In Oracle9i SQLJ, the array of update counts returned by the executeBatch() method or the getBatchUpdateCounts() method of an execution context instance does not contain counts of the number of rows updated by the batched statements, but simply values indicating whether each statement was successful. So its functionality differs from that of the single update count returned by the getUpdateCount() method of the execution context instance when batching is not enabled, which is described in "Status Methods".

As statements are batched, and after batch execution, the single update count returned by getUpdateCount() is also affected.

Value Available from getUpdateCount()

In a batch-enabled environment, the value available from the getUpdateCount() method of the execution context instance is modified after each statement is encountered. It will be updated with one of several ExecutionContext class static int constant values, as follows:

• NEW_BATCH_COUNT--Indicates that a new batch was created for the last statement encountered.

• ADD_BATCH_COUNT--Indicates that the last statement encountered was added to an existing batch.

• EXEC_BATCH_COUNT--Indicates that the pending batch was executed, either explicitly or implicitly, after the last statement was encountered.

If you refer to these constants, use the qualified names:

ExecutionContext.NEW_BATCH_COUNT
ExecutionContext.ADD_BATCH_COUNT
ExecutionContext.EXEC_BATCH_COUNT

Values Available from executeBatch() or getBatchUpdateCounts()

After a batch has been executed, either explicitly or implicitly, the array of values returned by executeBatch() or getBatchUpdateCounts() indicates only whether the statements executed successfully. There is an array element for each batched statement. As per the JDBC 2.0 specification, a value of -2 for an array element indicates that the corresponding statement completed successfully, but that the number of rows it affected is unknown.

Checking all the array values after execution of a batch would not be meaningful. As currently implemented, the only useful test of this array would be to verify the number of statements that were in the batch prior to execution, by checking the number of elements in the array after a successful execution (essentially, after a batch execution that does not produce an exception).

Note that the update counts array is not modified as statements are batched, only as the batch is executed.

Setting a Batch Limit

You can specify that each update batch be executed after a predefined number of statements have been batched, before the next statement would be added. Use the setBatchLimit() method of the execution context instance, inputting a positive, non-zero integer as follows:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
ec.setBatchLimit(10);
...
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < 20; i++) 
{
   #sql [ec] { UPDATE emp1 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}

This loop is executed 20 times, with the statements being batched and the batch being executed during the 11th time through the loop (before an 11th statement would be added to the batch). Note that the batch would not be executed a second time in the loop, however. When your application exits the loop, the last ten statements would still be in the batch and would not be executed until another statement is encountered or you execute the batch explicitly.

You can use two special static int constants of the ExecutionContext class as input to the setBatchLimit() method:

• AUTO_BATCH--Allows the SQLJ runtime to determine the batch limit.

• UNLIMITED_BATCH (default)--Specifies that there is no batch limit.

For example:

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

or:

ec.setBatchLimit(ExecutionContext.UNLIMITED_BATCH);
...

To check the current batch limit, use the getBatchLimit() method of the execution context instance.

Batching Incompatible Statements

If you want to batch a statement that is incompatible with statements in an existing batch without implicitly executing the existing batch, then you will have to use a separate execution context instance. Following is an example:

...
ExecutionContext ec1 = new ExecutionContext();
ec1.setBatching(true);
ExecutionContext ec2 = new ExecutionContext();
ec2.setBatching(true);
...
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec1] { UPDATE emp1 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
   #sql [ec2] { UPDATE emp2 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
int[] updateCounts1 = ec1.executeBatch();
int[] updateCounts2 = ec2.executeBatch();
...

Note: This example assumes that the two UPDATE statements are completely independent of each other. Do not batch interdependent statements in different execution contexts because you cannot completely assure the order in which they will be executed.

An alternative is to use a single execution context and separate loops so that all the EMP1 updates are batched and executed prior to the EMP2 updates:

...
ExecutionContext ec = new ExecutionContext();
ec.setBatching(true);
...
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec] { UPDATE emp1 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
for (int i = 0; i < empnos.length; i++) 
{
   #sql [ec] { UPDATE emp2 SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
ec.executeBatch();
...

This example executes the first batch implicitly and the second batch explicitly.

Using Implicit Execution Contexts for Update Batching

All the update batching examples so far have created and specified explicit execution context instances. This is not necessary, however, given that every connection context instance has an implicit execution context instance. For example, you can access the implicit execution context instance of the default connection as follows:

DefaultContext.getDefaultContext().getExecutionContext().setBatching(true);
...
double[] sals = ...;
String[] empnos = ...; 
for (int i = 0; i < empnos.length; i++) 
{
   #sql { UPDATE emp SET sal = :(sals[i]) WHERE empno = :(empnos[i]) };
}
// implicitly execute the batch and commit
#sql { COMMIT };

Or, alternatively, you could execute the batch explicitly:

DefaultContext.getDefaultContext().getExecutionContext().executeBatch();

General Cautions Regarding Update Batching

If you use update batching, especially if you mix statements using an unbatched execution context instance with statements using a batched execution context instance, remember the following points:

• If an unbatched statement depends on a batched statement, be sure the batch is executed prior to the unbatched statement.

• A JDBC COMMIT or ROLLBACK operation--that is, an auto-commit or any explicit use of the commit() method or rollback() method of a JDBC Connection instance--does not execute pending statements in a batch.

  It is important to note, however, that using a SQLJ COMMIT or ROLLBACK statement, such as follows, will execute pending statements in a batch:

  #sql { COMMIT };
  
  

  or:

  #sql { ROLLBACK };
  
  

  This is another reason that you should always commit or roll back changes using #sql syntax, which cleans up both SQLJ resources and JDBC resources.

• When a batch is implicitly executed as a result of an unbatchable or incompatible statement being encountered, the batch is executed before the unbatchable/incompatible statement is executed, but after the input parameters of that statement have been evaluated and passed to the statement.

• If you no longer intend to use a particular batch-enabled execution context instance, then explicitly execute or cancel its pending batch to free resources.

Error Conditions During Batch Execution

In the event that a statement causes an exception in the middle of a batch execution, be aware of the following:

• Batched statements following the statement that caused the exception are not executed.

• Batched statements that had already been executed prior to the exception are not rolled back.

• If the batch where the exception occurred was executed implicitly as the result of another (unbatchable or incompatible) statement being encountered, that statement is not executed.

  Note: Presumably you have disabled auto-commit mode when using update batching. This gives you commit/rollback control in case of an error during batch execution.

When an exception occurs during batch execution under JDBC 2.0, it is typically an instance of the standard java.sql.BatchUpdateException class, a subclass of the java.sql.SQLException class. (Under JDK 1.1.x, the Oracle JDBC drivers support update-batching exceptions with the oracle.jdbc2.BatchUpdateException class.)

The BatchUpdateException class has a getUpdateCounts() method that, for batched statements successfully executed before the exception occurred, returns an array of update counts equivalent to what would be returned by the ExecutionContext class executeBatch() or getBatchUpdateCounts() method.

Recursive Call-ins and Update Batching

As discussed in "Recursive SQLJ Calls in the Server", execution of SQLJ stored procedures where one calls the other can result in situations where the two procedures are simultaneously using the same execution context instance. The update-batching flag (set using the setBatching() method of the execution context instance) would behave in the same way as other execution context attributes--regardless of which stored procedure sets it, it would affect the next executable statement in either stored procedure.

For this reason, update batching is automatically disabled in the server whenever a recursive call-in occurs. The pending batch is executed, and no batching occurs in the recursively invoked procedure.

To avoid this behavior, use explicit execution context instances in batch-enabled stored procedures.

Column Definitions

Oracle SQLJ reflects Oracle JDBC support for column type and size definitions. Depending on the driver implementation, which differs somewhat among the different Oracle JDBC drivers, registering column types and sizes can save a trip to the database for each query. In particular, this is true for the Oracle JDBC Thin driver and use of positional iterators.

Oracle SQLJ Implementation of Column Definitions

If you enable column definitions, Oracle SQLJ takes the following steps to automatically register column types and sizes:

• During customization, or during translation where Oracle-specific code generation is used (-codegen=oracle), Oracle SQLJ connects to a specified database schema to determine types and sizes of columns being retrieved. (With standard SQLJ code generation, the column defaults become part of the SQLJ profile. This can be accomplished during the customization step of source code translation, or during separate customization of an existing profile.)

• When your application executes, the SQLJ runtime will use the column information to register the column types and sizes with the JDBC driver, using a call to the defineColumnType() method available in the Oracle JDBC statement classes.

Customizer and Translator Options for Column Definitions

To enable column definitions, assuming standard SQLJ code generation, set customizer options as follows:

• Enable the Oracle customizer optcols flag (-P-Coptcols on the SQLJ command line).

• Set the user, password, and URL for the database connection during customization (-P-user, -P-password, and -P-url on the SQLJ command line). In addition, set the JDBC driver class (-P-driver on the SQLJ command line) if you are not using the default OracleDriver class.

For information about these customizer options, see the optcols section under "Overview of Customizer-Specific Options", and the user, password, url, and driver sections under "Overview of Customizer Harness Options".

Note: Be aware that user, password, URL, and driver settings for customization are not the same as the settings for semantics-checking during SQLJ translation. They are unrelated.

For Oracle-specific code generation, use the SQLJ translator -optcols option instead, and use the SQLJ translator -user, -password, and -url options as appropriate, as you do for online semantics-checking. See "Column Definitions (-optcols)" and "Connection Options".

Parameter Size Definitions

Oracle JDBC and Oracle SQLJ allow you to optimize JDBC resource allocation by defining parameter sizes--sizes of Java host variables used as any of the following:

• input or output parameters in stored procedure or function calls

• return values from stored function calls

• input or output parameters in SET statements

• input or output parameters in PL/SQL blocks

Oracle SQLJ Implementation of Parameter Size Definitions

Oracle SQLJ implements parameter size definitions through option settings in combination with "hints" embedded in source code comments. For standard SQLJ code generation, Oracle customizer options are available. For Oracle-specific code generation, equivalent SQLJ translator options are available.

Use options and hints as follows:

• Enable parameter size definitions through the SQLJ translator or Oracle customizer parameter definition flag.

• Specify default sizes for particular datatypes through the SQLJ translator or Oracle customizer parameter default size option.

• Override datatype default sizes for particular parameters by embedding hints in source code comments, following a prescribed format.

For any given host variable, when parameter size definitions are enabled, resources are allocated according to the source code hint if there is one. If there is no source code hint, then the default size for the corresponding datatype is used if one was specified. If there is no source code hint or appropriate default size, then maximum resources are allocated according to the JDBC implementation.

When your application executes, the parameter sizes are registered through calls to the defineParameterType() and registerOutParameter() methods available in the Oracle JDBC statement classes.

Note: If you do not enable the parameter definition flag, then parameter size defaults and source code hints will be ignored, and maximum or default resources will be allocated according to the JDBC implementation.

Customizer Options for Parameter Size Definitions

For standard SQLJ code generation, use the following customizer options for parameter size definitions:

• the Oracle customizer optparams flag to enable parameter size definitions (-P-Coptparams on the SQLJ command line)

• the Oracle customizer optparamdefaults option to set default sizes for particular datatypes (-P-Coptparamdefaults=xxxx on the SQLJ command line)

For information about these options, see the sections under "Overview of Customizer-Specific Options".

For Oracle-specific code generation, use the SQLJ translator -optparams and -optparamdefaults options instead. See "Parameter Definitions (-optparams)" and "Parameter Default Size (-optparamdefaults)".

Source Code Hints for Parameter Size Definitions

Embed source code hints for parameter size definitions within your SQLJ statements in the following format (you can add white space within the comment, as desired):

/*(size)*/

The size is in bytes. Hints are ignored if the optparams flag is disabled.

You can override the default parameter size, without specifying a new size (leaving size allocation to the JDBC implementation), as follows:

/*()*/

Here is an example:

byte[] hash;
String name=Tyrone;
String street=2020 Meryl Street;
String city=Wichita;
String state=Kansas;
String zipcode=77777;
#sql hash = { /* (5) */ VALUES (ADDR_HASH(:name /* (20) */, :street /* () */, 
                               :city, :state, :INOUT zipcode /* (10) */ )) };

A hint for a result expression, such as the result expression hash in the example, must be the first item appearing inside the brackets of the SQLJ statement, as shown. Hints for input and output host variables must immediately follow the variables, as shown.

The example sets parameter sizes as follows:

• hash--5 bytes

• name--20 bytes

• street--override default, but with no setting (leave allocation up to JDBC)

• city--none (use appropriate datatype default, if any)

• state--none (use appropriate datatype default, if any)

• zipcode--10 bytes

  Note: If any parameter size is altered such that its actual size exceeds its registered size at runtime, a SQL exception will be thrown.

AuditorInstaller Customizer for Debugging

Oracle SQLJ provides a special customizer--AuditorInstaller--that will insert sets of debugging statements, known as auditors, into profiles specified on the SQLJ command line. These profiles must already exist from previous customization.

The debugging statements will execute during SQLJ runtime (when someone runs your application), displaying a trace of method calls and values returned.

Use the customizer harness debug option, preceded by -P- as with any general customization option, to insert the debugging statements. (Syntax for this option is discussed in "Invoking AuditorInstaller with the Customizer Harness debug Option".)

Overview of Auditors and Code Layers

When an application is customized, the Oracle customizer implements profiles in layers of code (typically less than five) for different levels of runtime functionality. The deepest layer uses straight Oracle JDBC calls and implements any of your SQLJ statements that can be executed through JDBC functionality. Each higher layer is a specialized layer for some category of SQLJ functionality that is not supported by JDBC and so must be handled specially by the SQLJ runtime. For example, a layer for iterator conversion statements (CAST) is used to convert JDBC result sets to SQLJ iterators. Another layer is used for assignment statements (SET).

At runtime, each SQLJ executable statement is first passed to the shallowest layer and then passed, layer-by-layer, until it reaches the layer that can process it (usually the deepest layer, which executes all JDBC calls).

You can install debugging statements at only one layer during a single execution of AuditorInstaller. Each set of debugging statements installed at a particular layer of code is referred to as an individual auditor. During runtime, an auditor is activated whenever a call is passed to the layer at which the auditor is installed.

Any one of the specialized code layers above the JDBC layer is usually of no particular interest during debugging, so it is typical to install an auditor at either the deepest layer or the shallowest layer. If you install an auditor at the shallowest layer, its runtime debugging output will be a trace of method calls resulting from all your SQLJ executable statements. If you install an auditor at the deepest layer, its runtime output will be a trace of method calls from all your SQLJ executable statements that result in JDBC calls.

Use multiple executions of AuditorInstaller to install auditors at different levels. You might want to do that to install auditors at both the deepest layer and the shallowest layer, for example.

See "AuditorInstaller Depth Option (depth)" for information about how to specify the layer at which to install an auditor.

Invoking AuditorInstaller with the Customizer Harness debug Option

Following are examples of how to specify the Oracle customizer harness debug option to run AuditorInstaller in its default mode:

sqlj -P-debug Foo_SJProfile0.ser Bar_SJProfile0.ser

sqlj -P-debug *.ser

sqlj -P-debug myappjar.jar

The debug option results in the customizer harness instantiating and invoking the following class:

sqlj.runtime.profile.util.AuditorInstaller

This class performs the work of inserting the debugging statements.

The -P-debug option is equivalent to the following:

-P-customizer=sqlj.runtime.profile.util.AuditorInstaller

This overrides the customizer specified in the SQLJ -default-customizer option.

Be aware of the following:

• To run an application with auditors installed, the Oracle SQLJ file translator.zip (or .jar) must be in your classpath. (Normally, running a pre-translated SQLJ application requires only a runtime library.)

• As with any Oracle customizer, help output and an option list will be provided if you specify -P-debug together with -P-help on the SQLJ command line.

• It is important to realize that because the debug option invokes a customizer, and only one customizer can run in any single running of SQLJ, you cannot perform any other customization when you use this option.

• You also cannot use more than one of -P-print, -P-debug, and -P-verify simultaneously, because each of these invokes a specialized customizer.

Command-line syntax

sqlj -P-debug profile_list

Command-line example

sqlj -P-debug Foo_SJProfile*.ser

Properties file syntax

profile.debug 

(You must also specify profiles in the file list.)

Properties file example

profile.debug

Default value

n/a

AuditorInstaller Runtime Output

During runtime, debugging statements placed by AuditorInstaller result in a trace of methods called and values returned. This happens for all profile layers that had debugging statements installed. (There is no means of selective debug output at runtime.)

AuditorInstaller output relates to profiles only; there is currently no mapping to lines in your original .sqlj source file.

Following is a sample portion of AuditorInstaller runtime output. This is what the output might look like for a SQLJ SELECT INTO statement:

oracle.sqlj.runtime.OraProfile@1 . getProfileData (  ) 
oracle.sqlj.runtime.OraProfile@1 . getProfileData returned 
sqlj.runtime.profile.ref.ProfileDataImpl@2 
oracle.sqlj.runtime.OraProfile@1 . getStatement ( 0 ) 
oracle.sqlj.runtime.OraProfile@1 . getStatement returned 
oracle.sqlj.runtime.OraRTStatement@3 
oracle.sqlj.runtime.OraRTStatement@3 . setMaxRows ( 1000 ) 
oracle.sqlj.runtime.OraRTStatement@3 . setMaxRows returned  
oracle.sqlj.runtime.OraRTStatement@3 . setMaxFieldSize ( 3000 ) 
oracle.sqlj.runtime.OraRTStatement@3 . setMaxFieldSize returned  
oracle.sqlj.runtime.OraRTStatement@3 . setQueryTimeout ( 1000 ) 
oracle.sqlj.runtime.OraRTStatement@3 . setQueryTimeout returned  
oracle.sqlj.runtime.OraRTStatement@3 . setBigDecimal ( 1 , 5 ) 
oracle.sqlj.runtime.OraRTStatement@3 . setBigDecimal returned  
oracle.sqlj.runtime.OraRTStatement@3 . setBoolean ( 2 , false ) 
oracle.sqlj.runtime.OraRTStatement@3 . setBoolean returned  
oracle.sqlj.runtime.OraRTStatement@3 . executeRTQuery (  ) 
oracle.sqlj.runtime.OraRTStatement@3 . executeRTQuery returned 
oracle.sqlj.runtime.OraRTResultSet@6 
oracle.sqlj.runtime.OraRTStatement@3 . getWarnings (  ) 
oracle.sqlj.runtime.OraRTStatement@3 . getWarnings returned null 
oracle.sqlj.runtime.OraRTStatement@3 . executeComplete (  ) 
oracle.sqlj.runtime.OraRTStatement@3 . executeComplete returned  
oracle.sqlj.runtime.OraRTResultSet@6 . next (  ) 
oracle.sqlj.runtime.OraRTResultSet@6 . next returned true 
oracle.sqlj.runtime.OraRTResultSet@6 . getBigDecimal ( 1 ) 
oracle.sqlj.runtime.OraRTResultSet@6 . getBigDecimal returned 5 
oracle.sqlj.runtime.OraRTResultSet@6 . getDate ( 7 ) 
oracle.sqlj.runtime.OraRTResultSet@6 . getDate returned 1998-03-28 

There are two lines for each method call--the first showing the call and input parameters, and the second showing the return value.

Note: The classes you see in the oracle.sqlj.runtime package are SQLJ runtime classes with equivalent functionality to similarly named JDBC classes. For example, OraRTResultSet is the SQLJ runtime implementation of the JDBC ResultSet interface, containing equivalent attributes and methods.

AuditorInstaller Options

As with any customizer, AuditorInstaller has its own options that can be set using the -P-C prefix on the SQLJ command line (or profile.C in a SQLJ properties file).

AuditorInstaller supports the following options:

• depth--Specify how deeply you want to go into the layers of runtime functionality in your profiles.

• log--Specify the target file for runtime output of the debugging statements of the installed auditor.

• prefix--Specify a prefix for each line of runtime output that will result from this installation of debugging statements.

• showReturns--Enable the installed auditor to include return arguments in its runtime call tracing.

• showThreads--Enable the installed auditor to include thread names in its runtime call tracing (relevant only for multithreaded applications).

• uninstall--Remove the debugging statements placed into the profiles during the most recent previous invocation of AuditorInstaller on those profiles.

AuditorInstaller Depth Option (depth)

As discussed in "Overview of Auditors and Code Layers", AuditorInstaller can install a set of debugging statements, known as an auditor, at only a single layer of code during any one execution. The AuditorInstaller depth option allows you to specify which layer. Use multiple executions of AuditorInstaller to install auditors at different levels.

Layers are numbered in integers. The shallowest depth is layer 0; a maximum depth of 2 or 3 is typical. The only depth settings typically used are 0 for the shallowest layer or -1 for the deepest layer. In fact, it is difficult to install an auditor at any other particular layer, because the layer numbers used for the various kinds of SQLJ executable statements are not publicized.

The depth option is sometimes used in conjunction with the prefix option. By running AuditorInstaller more than once, with different prefixes for different layers, you can see at runtime what information is coming from which layers.

If you do not set the depth option, or the specification exceeds the number of layers in a given profile, then an auditor will be installed at the deepest layer.

Command-line syntax

-P-Cdepth=n

Command-line example

-P-Cdepth=0

Properties file syntax

profile.Cdepth=n

Properties file example

profile.Cdepth=0

Default value

-1 (deepest layer)

AuditorInstaller Log File Option (log)

Use the log option to specify an output file for runtime output that will result from the auditor that you are currently installing. Otherwise, standard output will be used--debug output will go to wherever SQLJ messages go.

When auditors write messages to an output file, they append; they do not overwrite. Therefore, you can specify the same log file for multiple auditors without conflict (in fact, it is typical in this way to have debug information from all layers of your application go to the same log file).

Command-line syntax

-P-Clog=log_file

Command-line example

-P-Clog=foo/bar/mylog.txt

Properties file syntax

profile.Clog=log_file

Properties file example

profile.Clog=foo/bar/mylog.txt

Default value

empty (use standard output)

AuditorInstaller Prefix Option (prefix)

Use the prefix option to specify a prefix for each line of runtime output resulting from the debugging statements installed during this invocation of AuditorInstaller.

This option is often used in conjunction with the depth option. By running AuditorInstaller multiple times with different prefixes for different layers, you can easily see at runtime what information is coming from which layers.

Command-line syntax

-P-Cprefix="string"

Command-line example

-P-Cprefix="layer 2: "

Properties file syntax

profile.Cprefix="string"

Properties file example

profile.Cprefix="layer 2: "

Default value

empty

AuditorInstaller Return Arguments Option (showReturns)

Use the showReturns option to enable or disable the display of return arguments as part of the runtime call tracing. This is enabled by default.

The following few lines show sample output with showReturns enabled (default):

oracle.sqlj.runtime.OraRTStatement@3 . executeComplete (  ) 
oracle.sqlj.runtime.OraRTStatement@3 . executeComplete returned  
oracle.sqlj.runtime.OraRTResultSet@6 . next (  ) 
oracle.sqlj.runtime.OraRTResultSet@6 . next returned true 
oracle.sqlj.runtime.OraRTResultSet@6 . getBigDecimal ( 1 ) 
oracle.sqlj.runtime.OraRTResultSet@6 . getBigDecimal returned 5 
oracle.sqlj.runtime.OraRTResultSet@6 . getDate ( 7 ) 
oracle.sqlj.runtime.OraRTResultSet@6 . getDate returned 1998-03-28 

With showReturns disabled, the output would appear as follows:

oracle.sqlj.runtime.OraRTStatement@3 . executeComplete (  ) 
oracle.sqlj.runtime.OraRTResultSet@6 . next (  ) 
oracle.sqlj.runtime.OraRTResultSet@6 . getBigDecimal ( 1 ) 
oracle.sqlj.runtime.OraRTResultSet@6 . getDate ( 7 ) 

Instead of both a call line and a return line for each method call, there is only a call line.

Command-line syntax

-P-CshowReturns=true/false

Command-line example

-P-CshowReturns=false

Properties file syntax

profile.CshowReturns=true/false

Properties file example

profile.CshowReturns=false

Default value

true

AuditorInstaller Thread Names Option (showThreads)

Use the showThreads option to enable or disable the display of thread names as part of the runtime call tracing (relevant only for multithreaded applications). This is disabled by default.

When this option is enabled, thread names prefix the method names in the trace output.

Command-line syntax

-P-CshowThreads=true/false

Command-line example

-P-CshowThreads=true

Properties file syntax

profile.CshowThreads=true/false

Properties file example

profile.CshowThreads=false

Default value

false

AuditorInstaller Uninstall Option (uninstall)

Use the uninstall option to remove debugging statements placed during previous invocations of AuditorInstaller. Each time you use the uninstall option, it will remove the auditor most recently installed.

To remove all auditors from a profile, run AuditorInstaller repeatedly until you get a message indicating that the profile was unchanged.

Command-line syntax

-P-Cuninstall

Command-line example

-P-Cuninstall

Properties file syntax

profile.Cuninstall

Properties file example

profile.Cuninstall

Default value

false

Full Command-Line Examples

Following are some full SQLJ command-line examples showing the specification of AuditorInstaller options.

Insert a set of debugging statements, or auditor, into the deepest layer (which is the default layer), with runtime output to standard output:

sqlj -P-debug MyApp_SJProfile*.ser

Insert an auditor into the deepest layer, with runtime output to log.txt:

sqlj -P-debug -P-Clog=foo/bar/log.txt MyApp_SJProfile*.ser

Insert an auditor into the deepest layer, with runtime output to standard output, showing thread names but not return arguments:

sqlj -P-debug -P-CshowThreads=true -P-CshowReturns=false MyApp_SJProfile*.ser

Insert an auditor into layer 0 (the shallowest layer). Send runtime output to log.txt; prefix each line of runtime output with "Layer 0: " (the following command is a single wrap-around line):

sqlj -P-debug -P-Clog=foo/bar/log.txt -P-Cdepth=0 -P-Cprefix="Layer 0: " 
MyApp_SJProfile*.ser

Uninstall an auditor (this uninstalls the auditor most recently installed; do it repeatedly to uninstall all auditors):

sqlj -P-debug -P-Cuninstall MyApp_SJProfile*.ser

Additional SQLJ Debugging Considerations

In addition to the AuditorInstaller discussed under "AuditorInstaller Customizer for Debugging", there are other considerations to be aware of regarding debugging:

• If you are running SQLJ from the command line, then the -linemap option (or -jdblinemap option if you are using the jdb debugger) can aid in debugging your SQLJ code.

• The embedded server-side translator has an option that will aid in debugging your Java code in general, but not your SQLJ code in particular.

• SQLJ is integrated into the Oracle JDeveloper integrated development environment, allowing access to JDeveloper's debugging facilities.

SQLJ -linemap Flag

The -linemap flag instructs SQLJ to map line numbers from a SQLJ source code file to locations in the corresponding .class file. (This will be the .class file created during compilation of the .java file generated by the SQLJ translator.) As a result of this, when Java runtime errors occur, the line number reported by the Java virtual machine (JVM) is the line number in the SQLJ source code, making it much easier to debug.

If you are using the Sun Microsystems jdb debugger, then use the -jdblinemap option instead of the -linemap option. The options are equivalent, except that -jdblinemap does some special processing, necessitated by the fact that jdb does not support Java source files with file name extensions other than the .java extension.

For more information, see "Line-Mapping to SQLJ Source File (-linemap)" and "Line-Mapping to SQLJ Source File for jdb Debugger (-jdblinemap)".

Note: If you are translating in the server, class schema objects created during server-side translation automatically reference line numbers that map to the SQLJ source code. This is equivalent to enabling the -linemap option when you translate on a client.

Server-Side debug Option

If you are loading SQLJ source into the server and using the server-side embedded translator to translate it, the server-side debug option instructs the server-side compiler to output debugging information when a .sqlj or .java source file is compiled in the server. This is equivalent to using the -g option when running the standard javac compiler on a client. This does not aid in debugging your SQLJ code in particular, but aids in debugging your Java code in general.

See "Option Support in the Server Embedded Translator" for more information about this option and information about how to set options in the server.

For general information about debugging in the Oracle JVM, see the Oracle9i Java Developer's Guide.

Developing and Debugging in JDeveloper

Oracle SQLJ is fully integrated into the Oracle JDeveloper visual programming tool.

JDeveloper also includes an integrated debugger that supports SQLJ. SQLJ statements, as with standard Java statements, can be debugged in-line as your application executes. Reported line numbers are according to the line numbers in your SQLJ source code (as opposed to in the generated Java code).

See "SQLJ in JDeveloper and Other IDEs" for an introduction to JDeveloper.