3 Key Programming Considerations

Overview of Oracle JDBC Drivers

Oracle provides the following JDBC drivers:

• Oracle Call Interface (OCI) driver: For client-side use with an Oracle client installation.

• Thin driver: A pure Java driver for client-side use, particularly with applets. It does not require an Oracle client installation.

• Server-side Thin driver: Is functionally the same as the client-side Thin driver, but is for code that runs inside Oracle Database instance and needs to access a remote server.

• Server-side internal driver: For code that runs inside the target server, that is, inside Oracle Database instance that it must access.

Oracle Database 12c Release 1 (12.1) provides client-side drivers compatible with JDK 6 and JDK 7.

Core JDBC Functionality

The core functionality of all Oracle JDBC drivers is the same. They support the same feature set, syntax, programming interfaces, and Oracle extensions.

All Oracle JDBC drivers are supported by the oracle.jdbc.OracleDriver class.

JDBC OCI Driver

Oracle JDBC OCI driver accesses the database by calling the OCI directly from Java, providing the highest compatibility with the different Oracle Database versions. These drivers support installed Oracle Net adapters, including interprocess communication (IPC), named pipes, TCP/IP, and IPX/SPX.

The use of native methods to call C entry points makes the OCI driver dependent on the Oracle platform, requiring an Oracle client installation that includes Oracle Net. Therefore it is not suitable for applets.

Connection strings for the OCI driver are of the following form, where tns is an optional TNS alias or full TNS specification:

jdbc:oracle:oci:@<tns>

JDBC Thin Driver

Oracle JDBC Thin driver is a platform-independent, pure Java implementation that uses Java sockets to connect directly to Oracle Database from any Oracle or non-Oracle client. It can be downloaded into a browser simultaneously with the Java applet being run.

The JDBC Thin driver supports only TCP/IP protocol and requires a TNS listener to be listening on TCP/IP sockets from the database server. When the JDBC Thin driver is used with an applet, the client browser must have the capability to support Java sockets.

Connection strings for the JDBC Thin driver are typically of the following form:

jdbc:oracle:thin:@host:port/servicename

In Oracle Database 12c Release 1 (12.1), connection strings using SIDs are deprecated, but are still supported for backward compatibility:

jdbc:oracle:thin:@host:port:sid

JDBC Server-Side Thin Driver

Oracle JDBC server-side Thin driver offers the same functionality as the client-side JDBC Thin driver, but runs inside the database and accesses a remote server. This is useful in accessing one Oracle Database instance from inside another, such as from a Java stored procedure.

Connection strings for the server-side Thin driver are the same as for the client-side Thin driver.

JDBC Server-Side Internal Driver

Oracle JDBC server-side internal driver provides support for any Java code that runs inside the target Oracle Database instance where the SQL operations are to be performed. The server-side internal driver enables Oracle Java virtual machine (JVM) to communicate directly with the SQL engine. This driver is the default JDBC driver for SQLJ code running as a stored procedure, stored function, or trigger in Oracle Database 12c Release 1 (12.1).

Connection strings for the server-side internal driver are of the following form:

jdbc:oracle:kprb:

If your SQLJ code uses the default connection context, then SQLJ automatically uses this driver for code running in Oracle JVM.

Driver Selection for Translation

Use SQLJ option settings, either on the command line or in a properties file, to choose the driver manager class and specify a driver for translation.

Use the SQLJ -driver option to choose any driver manager class other than OracleDriver, which is the default.

Specify the particular JDBC driver to choose, such as JDBC Thin or JDBC OCI for Oracle Database, as part of the connection URL you specify in the SQLJ -url option.

You will typically, but not necessarily, use the same driver that you use in your source code for the run-time connection.

Driver Selection and Registration for Run Time

To connect to the database at run time, you must register one or more drivers that will understand the URLs you specify for any of your connection instances, whether they are instances of the sqlj.runtime.ref.DefaultContext class or of any connection context classes that you declare.

If you are using an Oracle JDBC driver and create a default connection using the Oracle.connect() method, then SQLJ handles this automatically. The Oracle.connect() method registers the oracle.jdbc.OracleDriver class.

If you are using an Oracle JDBC driver, but do not use Oracle.connect(), then you must manually register the OracleDriver class, as follows:

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

If you are not using an Oracle JDBC driver, then you must register some appropriate driver class, as follows:

DriverManager.registerDriver(new mydriver.jdbc.driver.MyDriver());

In any case, you must also set your connection URL, user name, and password.

Single Connection or Multiple Connections Using DefaultContext

This section discusses scenarios where you will use connection instances of only the DefaultContext class.

This is typical if you are using a single connection, or multiple connections that use SQL entities with the same names and data types.

Single Connection

For a single connection, use one instance of the DefaultContext class specifying the database URL, user name, and password, when you construct your DefaultContext object.

You can use the connect() method of the oracle.sqlj.runtime.Oracle class to accomplish this. Calling this method automatically initializes the default connection context instance. This method has several signatures, including ones that allow you to specify user name, password, and URL, either directly or using a properties file. In the following example, the properties file connect.properties is used:

Oracle.connect(MyClass.class, "connect.properties");

If you use connect.properties, then you must edit it appropriately and package it with your application. In this example, you must also import the oracle.sqlj.runtime.Oracle class.

Alternatively, you can specify user name, password, and URL directly:

Oracle.connect("jdbc:oracle:thin:@localhost:5221/myservice", "HR", "hr");

In this example, the connection will use the JDBC Thin driver to connect the HR user with the password, hr, to a database on the computer, localhost, through port 5221, where myservice is the name of the database service for the connection.

Either of these examples creates a special static instance of the DefaultContext class and installs it as your default connection. It is not necessary to do anything with this DefaultContext instance directly.

Once you have completed these steps, you do not need to specify the connection for any of the SQLJ executable statements in your application, if you want them all to use the default connection.

Note that in using a JDBC Thin driver, the URL must include the host name, port number, and service name (or SID, which is deprecated in Oracle Database 12c Release 1 (12.1)), as in the preceding example. Also, the database must have a listener running at the specified port. In using the JDBC OCI driver, no service name (or SID) is required if you intend to use the default account of the client, as will be the case in examples in this document. Alternatively, you can use name-value pairs.

The following URL will connect to the default account of the client:

jdbc:oracle:oci:@

Multiple Connections

For multiple connections, you can create and use additional instances of the DefaultContext class, while optionally still using the default connection.

You can use the Oracle.getConnection() method to instantiate DefaultContext, as in the following examples.

First, consider a case where you want most statements to use the default connection, but other statements to use a different connection. You must create one additional instance of DefaultContext:

DefaultContext ctx = Oracle.getConnection (
   "jdbc:oracle:thin:@localhost2:5221/myservice2", "bill", "lion");

When you want to use the default connection, it is not necessary to specify a connection context:

#sql { SQL operation };

This is actually a shortcut for the following:

#sql [DefaultContext.getDefaultContext()] { SQL operation };

When you want to use the additional connection, specify ctx as the connection:

#sql [ctx] { SQL operation };

Next, consider situations where you want to use multiple connections, where each of them is a named DefaultContext instance. This enables you to switch your connection back and forth.

The following statements establish multiple connections to the same schema (in case you want to use multiple Oracle Database sessions or transactions, for example). Instantiate the DefaultContext class for each connection you will need:

DefaultContext ctx1 = Oracle.getConnection
   ("jdbc:oracle:thin:@localhost1:5221/myservice1", "HR", "hr");
DefaultContext ctx2 = Oracle.getConnection
   ("jdbc:oracle:thin:@localhost1:5221/myservice1", "HR", "hr");

This creates two connection context instances that would use the same schema, connecting to HR/hr using service myservice1 on the computer localhost1, using Oracle JDBC Thin driver.

Now, consider a case where you would want multiple connections to different schemas. Again, instantiate the DefaultContext class for each connection you will need:

DefaultContext ctx1 = Oracle.getConnection
   ("jdbc:oracle:thin:@localhost1:5221/myservice1", "HR", "hr");
DefaultContext ctx2 = Oracle.getConnection
   ("jdbc:oracle:thin:@localhost2:5221/myservice2", "bill", "lion");

This creates two connection context instances that use Oracle JDBC Thin driver but use different schemas. The ctx1 object connects to HR/hr using service myservice1 on the computer localhost1, while the ctx2 object connects to bill/lion using service myservice2 on the computer localhost2.

There are two ways to switch back and forth between these connections for the SQLJ executable statements in your application:

• If you switch back and forth frequently, then you can specify the connection for each statement in your application:

  #sql [ctx1] { SQL operation };
  ...
  #sql [ctx2] { SQL operation };
  

  Note:

  Include the square brackets around the connection context instance name; they are part of the syntax.

• If you use either of the connections several times in a row within your code flow, then you can periodically use the static setDefaultContext() method of the DefaultContext class to reset the default connection. This method initializes the default connection context instance. This way, you can avoid specifying connections in your SQLJ statements.

  DefaultContext.setDefaultContext(ctx1);
  #sql { SQL operation };   // These three statements all use ctx1
  #sql { SQL operation };
  #sql { SQL operation };
  ...
  DefaultContext.setDefaultContext(ctx2);
  #sql { SQL operation };   // These three statements all use ctx2
  #sql { SQL operation };
  #sql { SQL operation };
  

  Note:

  Because the preceding statements do not specify connection contexts, at translation time they will all be checked against the default connection context.

Closing Connections

It is advisable to close your connection context instances when you are done, preferably in a finally clause of a try block (in case your application terminates with an exception).

The DefaultContext class, as well as any connection context classes that you declare, includes a close() method. Calling this method closes the SQLJ connection context instance and, by default, also closes the underlying JDBC connection instance and the physical connection.

In addition, the oracle.sqlj.runtime.Oracle class has a static close() method to close the default connection only. In the following example, presume ctx is an instance of any connection context class:

...
finally
{
   ctx.close();
}
...

Alternatively, if the finally clause is not within a try block in case a SQL exception is encountered:

...
finally
{
   try { ctx.close(); } catch(SQLException ex) {...}
}
...

Or, to close the default connection, the Oracle class also provides a close() method:

...
finally
{
   Oracle.close();
}
...

Always commit or roll back any pending changes before closing the connection. Whether there would be an implicit COMMIT operation as the connection is closed is not specified in the JDBC standard and may vary from vendor to vendor. For Oracle, there is an implicit COMMIT when a connection is closed, and an implicit ROLLBACK when a connection is garbage-collected without being closed, but it is not advisable to rely on these mechanisms.

Multiple Connections Using Declared Connection Context Classes

For multiple connections that use different sets of SQL entities, it is advantageous to use connection context declarations to define additional connection context classes. Having a separate connection context class for each set of SQL entities that you use enables SQLJ to do more rigorous semantics-checking of your code.

More About the Oracle Class

The Oracle SQLJ implementation provides the oracle.sqlj.runtime.Oracle class to simplify the process of creating and using instances of the DefaultContext class.

The static connect() method initializes the default connection context instance, instantiating a DefaultContext object and installing it as your default connection. You do not need to assign or use the DefaultContext instance returned by connect(). If you had already established a default connection, then connect() returns null.

The static getConnection() method simply instantiates a DefaultContext object and returns it. You can use the returned instance as desired.

Both methods register Oracle JDBC driver manager automatically if the oracle.jdbc.OracleDriver class is found in the CLASSPATH. The static close() method closes the default connection.

Signatures of the Oracle.connect() and Oracle.getConnection() Methods

Both the method have signatures that take the following parameter sets as input:

• URL (String), user name (String), password (String)

• URL (String), user name (String), password (String), auto-commit flag (boolean)

• URL (String), java.util.Properties object containing properties for the connection

• URL (String), java.util.Properties object, auto-commit flag (boolean)

• URL (String) fully specifying the connection, including user name and password

  The following is an example of the format of a URL string specifying user name (HR) and password (hr) when using Oracle JDBC drivers, in this case the JDBC Thin driver:

  "jdbc:oracle:thin:HR/hr@localhost:5221/myservice"
  

• URL (String), auto-commit flag (boolean)

• A java.lang.Class object for the class relative to which the properties file is loaded, name of properties file (String)

• A java.lang.Class object, name of properties file (String), auto-commit flag (boolean)

• A java.lang.Class object, name of properties file (String), user name (String), password (String)

• A java.lang.Class object, name of properties file (String), user name (String), password (String), auto-commit flag (boolean)

• JDBC connection object (Connection)

• SQLJ connection context object

These last two signatures inherit an existing database connection. When you inherit a connection, you will also inherit the auto-commit setting of that connection.

The auto-commit flag specifies whether SQL operations are automatically committed. For the Oracle.connect() and Oracle.getConnection() methods only, the default is false. If that is the setting you want, then you can use one of the signatures that does not take auto-commit as input. However, anytime you use a constructor to create an instance of a connection context class, including DefaultContext, you must specify the auto-commit setting. In the Oracle JDBC implementation, the default for the auto-commit flag is true.

Optional Oracle.close() Method Parameters

In using the Oracle.close() method to close the default connection, you have the option of specifying whether or not to close the underlying physical database connection. By default it is closed. This is relevant if you are sharing this physical connection between multiple connection objects, either SQLJ connection context instances or JDBC connection instances.

You can keep the underlying physical connection open as follows:

Oracle.close(ConnectionContext.KEEP_CONNECTION);

You can close the underlying physical connection (default behavior) as follows:

Oracle.close(ConnectionContext.CLOSE_CONNECTION);

KEEP_CONNECTION and CLOSE_CONNECTION are static constants of the ConnectionContext interface.

More About the DefaultContext Class

The sqlj.runtime.ref.DefaultContext class provides a complete default implementation of a connection context class. As with classes created using a connection context declaration, the DefaultContext class implements the sqlj.runtime.ConnectionContext interface. The DefaultContext class has the same class definition that would have been generated by the SQLJ translator from the declaration:

#sql public context DefaultContext;

DefaultContext Methods

The following are the key methods of the DefaultContext class:

• getConnection()

  Gets the underlying JDBC connection object. This is useful if you want to have JDBC code in your application, which is one way to use dynamic SQL operations. You can also use the setAutoCommit() method of the underlying JDBC connection object to set the auto-commit flag for the connection.

• setDefaultContext()

  Sets the default connection your application uses. This is a static method and takes a DefaultContext instance as input. SQLJ executable statements that do not specify a connection context instance will use the default connection that you define using this method or the Oracle.connect() method.

• getDefaultContext()

  Returns the DefaultContext instance currently defined as the default connection for your application. This is a static method.

• close()

  Closes the connection context instance.

The getConnection() and close() methods are specified in the sqlj.runtime.ConnectionContext interface.

DefaultContext Constructors

It is typical to instantiate DefaultContext using the Oracle.connect() or Oracle.getConnection() method. However, if you want to create an instance directly, then there are five constructors for DefaultContext. The different input parameter sets for these constructors are:

• URL (String), user name (String), password (String), auto-commit (boolean)

• URL (String), java.util.Properties object, auto-commit (boolean)

• URL (String fully specifying connection and including user name and password), auto-commit setting (boolean)

  The following is an example of the format of a URL specifying user name and password when using Oracle JDBC drivers, in this case the JDBC Thin driver:

  "jdbc:oracle:thin:HR/hr@localhost:5221/myservice"
  

• JDBC connection object (Connection)

• SQLJ connection context object

The last two signatures inherit an existing database connection. When you inherit a connection, you will also inherit the auto-commit setting of that connection.

Following is an example of constructing a DefaultContext instance:

DefaultContext defctx = new DefaultContext
   ("jdbc:oracle:thin:@localhost:5221/myservice", "HR", "hr", false);

Notes About Connection Context Constructors:

Optional DefaultContext close() Method Parameters

When you close a connection context instance, you have the option of specifying whether or not to close the underlying physical connection. By default it is closed. This is relevant if you are sharing the physical connection between multiple connection objects, either SQLJ connection context instances or JDBC connection instances. The following examples presume a DefaultContext instance defctx.

To keep the underlying physical connection open, use the following:

defctx.close(ConnectionContext.KEEP_CONNECTION);

To close the underlying physical connection, which is the default behavior, use the following:

defctx.close(ConnectionContext.CLOSE_CONNECTION);

KEEP_CONNECTION and CLOSE_CONNECTION are static constants of the ConnectionContext interface.

Connection for Translation

If you want to use online semantics-checking during translation, then you must specify a database connection for SQLJ to use. These are referred to as exemplar schemas.

You can use different connections for translation and run time. In fact, it is often necessary or preferable to do so. It might be necessary if you are not developing the application in the same kind of environment that it will run in. But even if the run-time connection is available during translation, it might be preferable to create an account with a narrower set of resources so that your online checking will be tighter. This would be true if your application uses only a small subset of the SQL entities available in the run-time connection. Your online checking would be tighter and more meaningful if you create an exemplar schema consisting only of SQL entities that your application actually uses.

Use the SQLJ translator connection options, either on the command line or in a properties file, to specify a connection for translation.

Connection for Customization

Generally, Oracle customization does not require a database connection. However, the Oracle SQLJ implementation does support customizer connections. This is useful in two circumstances:

• If you are using Oracle customizer with the optcols option enabled, then a connection is required. This option allows iterator column type and size definitions for performance optimization.

• If you are using SQLCheckerCustomizer, a specialized customizer that performs semantics-checking on profiles, then a connection is required if you are using an online checker, which is true by default.

For Oracle-specific code generation, the SQLJ translator has an -optcols option with the same functionality. The SQLCheckerCustomizer is invoked through Oracle customizer harness verify option. Use the customizer harness user, password, url, and driver options to specify connection parameters for whatever customizer you are using, as appropriate.

Wrapper Classes for NULL-Handling

SQLJ consistently enforces retrieving SQL NULL as Java null, in contrast to JDBC, which retrieves NULL as 0 or false for certain data types. Therefore, do not use Java primitive types in SQLJ for output variables in situations where a SQL NULL may be received, because Java primitive types cannot take null values.

This pertains to result expressions, output or input-output host expressions, and iterator column types. If the receiving Java type is primitive and an attempt is made to retrieve a SQL NULL, then a sqlj.runtime.SQLNullException is thrown and no assignment is made.

To avoid the possibility of NULL being assigned to Java primitives, use the following wrapper classes instead of primitive types:

• java.lang.Boolean

• java.lang.Byte

• java.lang.Short

• java.lang.Integer

• java.lang.Long

• java.lang.Double

• java.lang.Float

In case you must convert back to a primitive value, each of these wrapper classes has an xxxValue() method. For example, intValue() returns an int value from an Integer object and floatValue() returns a float value from a Float object. For example, presuming intobj is an Integer object:

int j = intobj.intValue();

Examples of NULL-Handling

The following examples show the use of the java.lang wrapper classes to handle NULL.

Example: Null Input Host Variable

In the following example, a Float object is used to pass a null value to the database:

int empno = 7499;
Float commission = null;

#sql { UPDATE employees SET commission_pct = :commission WHERE employee_id = :empno };

You cannot use the Java primitive type float to accomplish this.

Example: Null Iterator Rows

In the following example, a Double column type is used in an iterator to allow for the possibility of null data.

For each employee in the employee table whose salary is at least $50,000, the employee name (FIRST_NAME) and commission (COMMISSION_PCT) are selected into the iterator. Then each row is tested to determine if the COMMISSION_PCT field is, in fact, null. If so, then it is processed accordingly.

#sql iterator EmployeeIter (String first_name, Double commission);

EmployeeIter ei;
#sql ei = { SELECT first_name, commission_pct FROM employees WHERE salary >= 50000 };

while (ei.next())
{
   if (ei.commission_pct() == null) 
      System.out.println(ei.first_name() + " is not on commission.");
}
ei.close();
...

...WHERE :x IS NULL

SQLJ and JDBC Exception-Handling Requirements

Because SQLJ executable statements result in JDBC calls through sqlj.runtime, and JDBC requires SQL exceptions to be caught or thrown, SQLJ also requires SQL exceptions to be caught or thrown in any block containing SQLJ executable statements. Your source code will generate errors during compilation if you do not include appropriate exception-handling.

Handling SQL exceptions requires the SQLException class, which is included in the standard JDBC java.sql.* package.

Example: Exception Handling

This example demonstrates the basic exception-handling required in SQLJ applications. The code declares a main method with a try/catch block and another method, which throws SQLException when an exception is encountered. The code is as follows:

    /* Import SQLExceptions class.  The SQLException comes from
       JDBC. Executable #sql clauses result in calls to JDBC, so methods
       containing executable #sql clauses must either catch or throw
       SQLException.
    */
import java.sql.* ;
import oracle.sqlj.runtime.Oracle;

    // iterator for the select

#sql iterator MyIter (String ITEM_NAME);

public class TestInstallSQLJ 
{
    //Main method
  public static void main (String args[]) 
  {
    try { 

    // Set the default connection to the URL, user, and password
    // specified in your connect.properties file
      Oracle.connect(TestInstallSQLJ.class, "connect.properties");

      TestInstallSQLJ ti = new TestInstallSQLJ();

    // This method throws SQLException. Therefore, it ic called within a try block
      ti.runExample();

    } catch (SQLException e) { 
      System.err.println("Error running the example: " + e);
    }

  } //End of method main

  //Method that runs the example
  void runExample() throws SQLException
  {
      //Issue SQL command to clear the SALES table
    #sql { DELETE FROM SALES };
    #sql { INSERT INTO SALES(ITEM_NAME) VALUES ('Hello, SQLJ!')};

    MyIter iter;
    #sql iter = { SELECT ITEM_NAME FROM SALES };

    while (iter.next()) {
      System.out.println(iter.ITEM_NAME());
    }
  }
}

Processing Exceptions

This section discusses ways to process and interpret exceptions in your SQLJ application. During run time, exceptions may be raised from any of the following:

• SQLJ run time

• JDBC driver

• RDBMS

Printing Error Text

The example in the previous section showed how to catch SQL exceptions and output the error messages. Part of that code is as follows:

...
try {
...
} catch (SQLException e) { 
      System.err.println("Error running the example: " + e); 
}
...

This will print the error text from the SQLException object.

You can also retrieve error information using the getMessage(), getErrorCode(), and getSQLState() methods the SQLException class.

Printing the error text, as in this example, prints the error message with some additional text, such as SQLException.

Retrieving SQL States and Error Codes

The java.sql.SQLException class and subclasses include the getMessage(), getErrorCode(), and getSQLState() methods. Depending on where the exception or error originated and how they are implemented there, the following methods provide additional information:

• String getMessage()

  If the error originates in the SQLJ run time or JDBC driver, then this method returns the error message with no prefix. If the error originates in the RDBMS, then it returns the error message prefixed by the ORA number.

• int getErrorCode()

  If the error originates in the SQLJ run time, then this method returns no meaningful information. If the error originates in the JDBC driver or RDBMS, then it returns the five-digit ORA number as an integer.

• String getSQLState()

  If the error originates in the SQLJ run time, then this method returns a string with a five-digit code indicating the SQL state. If the error originates in the JDBC driver, then it returns no meaningful information. If the error originates in the RDBMS, then it returns the five-digit SQL state. Your application should have appropriate code to handle null values returned.

The following example prints the error message and also checks the SQL state:

...
try {
...
} catch (SQLException e) { 
      System.err.println("Error running the example: " + e); 
      String sqlState = e.getSQLState();
      System.err.println("SQL state = " + sqlState); 
}
...

Using SQLException Subclasses

For more specific error-checking, use any available and appropriate subclasses of the java.sql.SQLException class.

SQLJ provides the sqlj.runtime.NullException class, which is a subclass of java.sql.SQLException. You can use this exception in situations where a NULL might be returned into a Java primitive variable.

For batch-enabled environments, there is also the standard java.sql.BatchUpdateException subclass. Refer to "Error Conditions During Batch Execution" for further information.

When you use a subclass of SQLException, catch the subclass exception before catching SQLException, as in the following example:

...
try {
...
} catch (SQLNullException ne) {
     System.err.println("Null value encountered: " + ne); }
  catch (SQLException e) { 
     System.err.println("Error running the example: " + e); }
...

This is because a subclass exception can also be caught as a SQLException. If you catch SQLException first, then execution will not proceed to the part where you have coded special processing for the subclass exception.

Overview of Transactions

A transaction is a sequence of SQL operations that Oracle treats as a single unit. A transaction begins with the first executable SQL statement after any of the following:

• Connection to the database

• COMMIT (committing data updates, either automatically or manually)

• ROLLBACK (canceling data updates)

A transaction ends with a COMMIT or ROLLBACK operation.

Automatic Commits Versus Manual Commits

In using SQLJ or JDBC, you can either have your data updates automatically committed or commit them manually. In either case, each COMMIT operation starts a new transaction. You can specify that changes be committed automatically by enabling the auto-commit flag. This can be done either when you define a SQLJ connection or by using the setAutoCommit() method of the underlying JDBC connection object of an existing connection. You can use manual control by disabling the auto-commit flag and using SQLJ COMMIT and ROLLBACK statements.

Enabling auto-commit may be more convenient, but gives you less control. For example, you have no option to roll back changes. In addition, some SQLJ or JDBC features are incompatible with auto-commit mode. For example, you must disable the auto-commit flag for update batching or SELECT FOR UPDATE syntax to work properly.

Specifying Auto-Commit as You Define a Connection

When you use the Oracle.connect() or Oracle.getConnection() method to create a DefaultContext instance and define a connection, the auto-commit flag is set to false by default. However, there are signatures of these methods that enable you to set this flag explicitly. The auto-commit flag is always the last parameter.

The following is an example of instantiating DefaultContext and using the default false setting for auto-commit mode:

Oracle.getConnection
   ("jdbc:oracle:thin:@localhost:5221/myservice", "HR", "hr");

Alternatively, you can specify a true setting as follows:

Oracle.getConnection
   ("jdbc:oracle:thin:@localhost:5221/myservice", "HR", "hr", true);

If you use a constructor to create a connection context instance, either of DefaultContext or of a declared connection context class, then you must specify the auto-commit setting. Again, it is the last parameter, as in the following example:

DefaultContext ctx = new DefaultContext
   ("jdbc:oracle:thin:@localhost:5221/myservice", "HR", "hr", false);

If you have reason to create a JDBC Connection instance directly, then the auto-commit flag is set to true by default if your program runs on a client, or false by default if it runs in the server. You cannot specify an auto-commit setting when you create a JDBC Connection instance directly, but you can use the setAutoCommit() method to alter the setting.

Modifying Auto-Commit in an Existing Connection

There is typically no reason to change the auto-commit flag setting for an existing connection, but you can if you desire. You can do this by using the setAutoCommit() method of the underlying JDBC connection object.

You can retrieve the underlying JDBC connection object by using the getConnection() method of any SQLJ connection context instance, whether it is an instance of the DefaultContext class or of a connection context class that you declared.

You can accomplish these two steps at once, as follows:

ctx.getConnection().setAutoCommit(false);

or:

ctx.getConnection().setAutoCommit(true);

In these examples, ctx is a SQLJ connection context instance.

Using Manual COMMIT and ROLLBACK

If you disable the auto-commit flag, then you must manually commit any data updates. To commit any changes that have been executed since the last COMMIT operation, use the SQLJ COMMIT statement, as follows:

#sql { COMMIT };

To roll back any changes that have been executed since the last COMMIT operation, use the SQLJ ROLLBACK statement, as follows:

#sql { ROLLBACK };

Effect of Commits and Rollbacks on Iterators and Result Sets

COMMIT and ROLLBACK operations do not affect open result sets and iterators. The result sets and iterators will still be open. Usually, all that is relevant to their content is the state of the database at the time of execution of the SELECT statements that populated them.

This also applies to UPDATE, INSERT, and DELETE statements that are executed after the SELECT statements. Execution of these statements does not affect the contents of open result sets and iterators.

Consider a situation where you SELECT, then UPDATE, and then COMMIT. A nonsensitive result set or iterator populated by the SELECT statement will be unaffected by the UPDATE and COMMIT.

As a further example, consider a situation where you UPDATE, then SELECT, and then ROLLBACK. A nonsensitive result set or iterator populated by the SELECT will still contain the updated data, regardless of the subsequent ROLLBACK.

Using Savepoints

The JDBC 3.0 specification added support for savepoints. A savepoint is a defined point in a transaction that you can roll back to, if desired, instead of rolling back the entire transaction. The savepoint is the point in the transaction where the SAVEPOINT statement appears.

In Oracle9i Database Release 2 (9.2), SQLJ first included Oracle-specific syntax to support savepoints. In Oracle Database 12c Release 1 (12.1), SQLJ adds support for ISO SQLJ standard savepoint syntax.

Support for ISO SQLJ Standard Savepoint Syntax

In ISO SQLJ standard syntax, use a string literal in a SAVEPOINT statement to designate a name for a savepoint. This can be done as follows:

#sql { SAVEPOINT savepoint1 };

If you want to roll back changes to that savepoint, then you can refer to the specified name later in a ROLLBACK TO statement, as follows:

#sql { ROLLBACK TO savepoint1 };

Use a RELEASE SAVEPOINT statement if you no longer need the savepoint:

#sql { RELEASE SAVEPOINT savepoint1 };

Savepoints are saved in the SQLJ execution context, which has methods that parallel the functionality of these three statements.

Because any COMMIT operation ends the transaction, this also releases all savepoints of the transaction.

Oracle SQLJ Savepoint Syntax

In addition to the ISO SQLJ standard syntax, the following Oracle-specific syntax for savepoints is supported. Note that the Oracle syntax uses string host expressions, rather than string literals.

You can set a savepoint as follows:

#sql { SET SAVEPOINT :savepoint };

The host expression, savepoint in this example, is a variable that specifies the name of the savepoint as a Java String.

You can roll back to a savepoint as follows:

#sql { ROLLBACK TO :savepoint };

To release a savepoint, use the following SQLJ statement:

#sql { RELEASE :savepoint };

Code Considerations and Limitations with Oracle-Specific Code Generation

When coding a SQLJ application where Oracle-specific code generation will be used, be aware of the following programming considerations and restrictions:

• To use a nondefault statement cache size, you must include appropriate method calls in your code, because Oracle customizer stmtcache option is unavailable. Refer to "SQLJ Usage Changes with Oracle-Specific Code Generation".

• Do not mix Oracle-specific generated code with ISO SQLJ standard generated code in the same application. However, if Oracle-specific code and ISO SQLJ standard code must share the same connection, do one of the following:

  • Ensure that the Oracle-specific code and ISO standard code use different SQLJ execution context instances. Refer to "Execution Contexts" for information about SQLJ execution contexts.

  • Place a transaction boundary, that is, as a manual COMMIT or ROLLBACK statement, between the two kinds of code.

  This limitation regarding mixing code is especially significant for server-side code, because all Java code running in a given session uses the same JDBC connection and SQLJ connection context.

  See Also:

  "Server-Side Considerations with Oracle-Specific Code Generation"

• Do not rely on side effects in parameter expressions when values are returned from the database. Oracle-specific code generation does not create temporary variables for evaluation of OUT parameters, IN OUT parameters, SELECT INTO variables, or return arguments on SQL statements.

  For example, avoid statements such as the following:

  #sql { SELECT * FROM EMPLOYEES INTO :(x[i++]), :(f_with_sideffect()[i++]),
                                :(a.b[i]) };
  

  or:

  #sql x[i++] = { VALUES f(:INOUT (x[i++]), :OUT (f_with_sideffect())) };
  

  Evaluation of arguments is performed in place in the generated code. This may result in different behavior than when evaluation is according to ISO SQLJ standards.

  See Also:

  "Evaluation of Java Expressions at Run Time" and "Examples of Evaluation of Java Expressions at Run Time (ISO Code Generation)"

• Type maps for Oracle object functionality assumes that the corresponding Java classes implement the java.sql.SQLData interface, given that JPublisher-generated Java classes do not otherwise require a type map. If you use type maps for Oracle object functionality, then your iterator declarations and connection context declarations must specify the same type maps. Specify this through the with clause.

  For example, if you declare a connection context class as follows:

  #sql context TypeMapContext with (typeMap="MyTypeMap");
  

  and you populate an iterator instance from a SQLJ statement that uses an instance of this connection context class, as follows:

  TypeMapContext tmc = new TypeMapContext(...);
  ...
  MyIterator it;
  #sql [tmc] it = ( SELECT pers, addr FROM tab WHERE ...);
  

  then the iterator declaration is required to have specified the same type map, as follows:

  #sql iterator MyIterator with (typeMap="MyTypeMap") 
                (Person pers, Address addr);
  

  See Also:

  "Custom Java Class Requirements" and "Declaration WITH Clause"

  Note:

  The reason for this restriction is that with Oracle-specific code generation, all iterator getter methods are fully generated as Oracle JDBC calls during translation. To generate the proper calls, the SQLJ translator must know whether an iterator will be used with a particular type map.

SQLJ Usage Changes with Oracle-Specific Code Generation

Some options that were previously available only as Oracle customizer options are useful with Oracle-specific code generation as well. Because profile customization is not applicable with Oracle-specific code generation, these options have been made available through other means.

To alter the statement cache size or disable statement caching when generating Oracle-specific code, use method calls in your code instead of using the customizer stmtcache option. The sqlj.runtime.ref.DefaultContext class, as well as any connection context class you declare, now has the following static methods:

• setDefaultStmtCacheSize(int)

• int getDefaultStmtCacheSize()

It also has the following instance methods:

• setStmtCacheSize(int)

• int getStmtCacheSize()

By default, statement caching is enabled.

In addition, the following options are available as front-end Oracle SQLJ translator options as well as Oracle customizer options:

• -optcols: Enable iterator column type and size definitions to optimize performance.

• -optparams: Enable parameter size definitions to optimize JDBC resource allocation. This option is used in conjunction with optparamdefaults.

• -optparamdefaults: Set parameter size defaults for particular data types. This option is used in conjunction with optparams.

• -fixedchar: Enable CHAR comparisons with blank padding for WHERE clauses.

Be aware of the following:

• Use the -optcols option only if you are using online semantics-checking, where you have used the SQLJ translator -user, -password, and -url options appropriately to request a database connection during translation.

• The functionality of the -optcols, -optparams, and -optparamdefaults options, including default values, is the same as for the corresponding customizer options.

Server-Side Considerations with Oracle-Specific Code Generation

Consider the following if your SQLJ code will run in the server:

• The server-side SQLJ translator no longer supports ISO standard generated code. SQLJ source code that is loaded into the server and compiled there will always be translated with the default -codegen=oracle setting.

  Therefore, to use ISO standard generated code in the server, you must translate and compile the SQLJ code on a client and then load the individual components into the server.

  See Also:

  "Translating SQLJ Source on a Client and Loading Components"

• The caution against mixing Oracle-specific generated code with ISO standard generated code applies to server-side Java code that calls a Java stored procedure or stored function, even if the stored procedure is invoked through a PL/SQL wrapper. This constitutes a recursive call-in. By default, the ExecutionContext object is shared by both the calling module and the called module. Therefore, both modules should be translated with the same -codegen setting.

  If you want to ensure interoperability with code that has been translated with ISO standard code generation, then it is advisable to explicitly instantiate execution context instances, as in the following example:

  public static method() throws SQLException
  {
     Execution Context ec = new ExecutionContext();
     ...
     try {
        ...
        #sql [ec] { SQL operation };
        ...
     } finally { ec.close(); }
  }
  

Advantages and Disadvantages of Oracle-Specific Code Generation

Oracle-specific code generation offers following advantages over ISO standard code generation:

• Applications run more efficiently. The code calls JDBC application programming interfaces (APIs) directly, placing run-time performance directly at the JDBC level. The role of the intermediate SQLJ run-time layer is greatly reduced during program execution.

• Applications are smaller in size.

• No profile files (.ser) are produced. This is especially convenient if you are loading a translated application into the database or porting it to another system, because there are fewer components.

• Translation is faster, because there is no profile customization step.

• During execution, Oracle SQLJ run time and Oracle JDBC driver use the same statement cache resources, so partitioning resources between the two is unnecessary.

• Having the SQL-specific information appear in the Java class files instead of in separate profile files avoids potential security issues.

• You need not have to rewrite your code to take advantage of possible future Oracle JDBC performance enhancements, such as enhancements being considered for execution of static SQL code. Future releases of Oracle SQLJ translator will handle this automatically.

• The use of Java reflection at run time is eliminated, and thus, provides full portability to browser environments.

However. there are a few disadvantages:

• Oracle-specific generated code may not be portable to generic JDBC platforms.

• Profile-specific functionality is not available. For example, you cannot perform customizations at a later date to use Oracle customizer harness -debug, -verify, and -print options.

  See Also:

  "Customizer Harness Options for Connections" and "AuditorInstaller Customizer for Debugging"

Environment Requirements for ISO Standard Code Generation

The Oracle SQLJ implementation, by default, generates Oracle-specific code with direct calls to Oracle JDBC driver instead of generating ISO standard code that calls the SQLJ run time. The following is a typical environment setup for ISO standard code generation:

• SQLJ code generation: -codegen=iso

• SQLJ translation library: translator.jar

• SQLJ run-time library: runtime12.jar with JDK 6 or JDK 7, and Oracle Database 12c Release 1 (12.1)

• JDBC drivers: Oracle Database 12c Release 1 (12.1)ojdbc6.jar or ojdbc7.jar

• JDK version: JDK 6 or JDK 7

SQLJ Translator and SQLJ Run Time

The following section describes the differences in Oracle SQLJ implementation in case of ISO standard code generation:

• SQLJ translator: Along with the .java file, the translator also produces one or more SQLJ profiles for ISO standard code generation. These profiles contain information about the embedded SQL operations. SQLJ then automatically invokes a Java compiler to produce .class files from the .java file.

  See Also:

  "SQLJ Translator"

• SQLJ run time: For ISO standard code generation, the SQLJ run time implements the desired actions of the SQL operations by accessing the database using a JDBC driver. The generic ISO SQLJ standard does not require the SQLJ run time to use a JDBC driver to access the database.

  See Also:

  "SQLJ Run Time"

In addition to the translator and run time, there is a component known as the customizer that plays a role. A customizer tailors SQLJ profiles for a particular database implementation and vendor-specific features and data types. By default, for ISO standard code, the SQLJ front end invokes an Oracle customizer to tailor your profiles for Oracle Database instance and Oracle-specific features and data types.

When you use Oracle customizer during translation, your application will require the SQLJ run time and an Oracle JDBC driver when it runs.

SQLJ Profiles

With ISO standard code generation, SQLJ profiles are serialized Java resources or classes generated by the SQLJ translator, which contain details about the embedded SQL statements. The translator creates these profiles. Then, depending on the translator option settings, it either serializes the profiles and puts them into binary resource files or puts them into .class files.

This section covers the following topics:

• Overview of Profiles

• Binary Portability

SQLJ Translation Steps

For ISO standard code generation (-codegen=iso), the translator processes the SQLJ source code, converts SQL operations to SQLJ run-time calls, and generates Java output code and one or more SQLJ profiles. A separate profile is generated for each connection context class in the source code, where a different connection context class is typically used for each interrelated set of SQL entities that is used in the operations.

Generated Java code is put into a .java output file containing the following:

• Any class definitions and Java code from the .sqlj source file

• Class definitions created as a result of the SQLJ iterator and connection context declarations

  See Also:

  "Overview of SQLJ Declarations"

• A class definition for a specialized class known as the profile-keys class that SQLJ generates and uses in conjunction with the profiles (for ISO standard SQLJ code generation only)

• Calls to the SQLJ run time to implement the actions of the embedded SQL operations

Generated profiles contain information about all the embedded SQL statements in the SQLJ source code, such as actions to take, data types being manipulated, and tables being accessed. When the application is run, the SQLJ run time accesses the profiles to retrieve the SQL operations and passes them to the JDBC driver.

By default, profiles are put into .ser serialized resource files, but SQLJ can optionally convert the .ser files to .class files as part of the translation.

The compiler compiles the generated Java source file and produces Java .class files as appropriate. This includes a .class file for each class that is defined, each of the SQLJ declarations, and the profile-keys class. The JVM then invokes Oracle customizer or other specified customizer to customize the profiles generated.

General SQLJ Notes

Consider the following when translating and running SQLJ applications for ISO specific code generation:

• Along with compiling existing .java files on the command line and making them available for type resolution, as for Oracle-specific code generation, you need to:

  • Customize the existing profiles

  • Customize the Java Archive (JAR) files containing profiles

  See Also:

  "Translator Command Line and Properties Files"

• SQLJ generates profiles and the profile-keys class only if your source code includes SQLJ executable statements.

• If you use Oracle customizer during translation, then your application requires Oracle SQLJ run time and an Oracle JDBC driver when it runs, even if your code does not use Oracle-specific features. You can avoid this by specifying -profile=false when you translate, to bypass Oracle-specific customization.

Summary of Translator Input and Output

We have seen what the SQLJ translator takes as input, what it produces as output, and where it places its output in case of Oracle-specific code generation. This section covers the same topics for ISO standard code generation:

• Translator Input

• Translator Output

• Output File Locations

SQLJ Run-Time Processing

This section discusses run-time processing for ISO standard code during program execution.

For ISO standard SQLJ applications, the SQLJ run time reads the profiles and creates connected profiles, which incorporate database connections. Then the following occurs each time the application must access the database:

1. SQLJ-generated application code uses methods in a SQLJ-generated profile-keys class to access the connected profile and read the relevant SQL operations. There is a mapping between SQLJ executable statements in the application and SQL operations in the profile.

2. The SQLJ-generated application code calls the SQLJ run time, which reads the SQL operations from the profile.

3. The SQLJ run time calls the JDBC driver and passes the SQL operations to the driver.

4. The SQLJ run time passes any input parameters to the JDBC driver.

5. The JDBC driver executes the SQL operations.

6. If any data is to be returned, then the database sends it to the JDBC driver, which sends it to the SQLJ run time for use by your application.

Deployment Scenarios

We have discussed how to run Oracle-specific SQLJ code in the following scenarios:

• From an applet

• In the server (optionally running the SQLJ translator in the server as well)

There are a few considerations that you need to make while running your ISO standard code from an applet:

• You must package all the SQLJ run-time packages with your applet. The packages are:

  sqlj.runtime
  sqlj.runtime.ref
  sqlj.runtime.profile
  sqlj.runtime.profile.ref
  sqlj.runtime.error
  

  Also package the following if you used Oracle customization:

  oracle.sqlj.runtime
  oracle.sqlj.runtime.error
  

  These packages are included with your Oracle installation in one of several run-time libraries in the ORACLE_HOME/lib directory.

  See Also:

  "Requirements for Using the Oracle SQLJ Implementation"

• Some browsers, such as Netscape Navigator 4.x, do not support resource files with a .ser extension, which is the extension used by the SQLJ serialized object files that are used for profiles. However, the Sun Microsystems Java plug-in supports .ser files.

  Alternatively, if you do not want to use the plug-in, then the Oracle SQLJ implementation offers the -ser2class option to convert .ser files to .class files during translation.

  See Also:

  "Conversion of .ser File to .class File (-ser2class)"

  Note:

  This consideration does not apply to the default Oracle-specific code generation, where no profiles are produced.

• Applets using Oracle-specific features require Oracle SQLJ run time to work. Oracle SQLJ run time consists of the classes in the SQLJ run-time library file under oracle.sqlj.*. Oracle SQLJ runtime.jar library requires the Java Reflection API, java.lang.reflect.*. Most browsers do not support the Reflection API or impose security restrictions, but the Sun Microsystems Java plug-in provides support for the Reflection API.

  With ISO standard code generation, the following SQLJ language features always require the Java Reflection API, regardless of the version of the SQLJ run time you are using:

  • The CAST statement

  • REF CURSOR parameters or REF CURSOR columns being retrieved from the database as instances of a SQLJ iterator

  • Retrieval of java.sql.Ref, Struct, Array, Blob, or Clob objects

  • Retrieval of SQL objects as instances of Java classes implementing the oracle.sql.ORAData or java.sql.SQLData interfaces

    Note:

    • An exception to the preceding is if you use SQLJ in a mode that is fully compatible with ISO. That is, if you use SQLJ in an environment that complies with J2EE and you translate and run your program with the SQLJ runtime12ee.jar library, and you employ connection context type maps as specified by ISO. In this case, instances of java.sql.Ref, Struct, Array, Blob, Clob, and SQLData are being retrieved without the use of reflection.

    • If you use Oracle-specific code generation, then you will eliminate the use of reflection in all of the instances listed.

Java Namespace: Local Variable and Class Naming Restrictions

The Java namespace applies to all standard Java statements and declarations, including the naming of Java classes and local variables. All standard Java naming restrictions apply, and you should avoid the use of Java reserved words.

In addition, SQLJ places minor restrictions on the naming of local variables and classes.

Local Variable Naming Restrictions

Some of the functionality of the SQLJ translator results in minor restrictions in naming local variables. The SQLJ translator replaces each SQLJ executable statement with a statement block, where the SQLJ executable statement is of the standard syntax:

#sql { SQL operation };  

SQLJ may use temporary variable declarations within a generated statement block. The name of any such temporary variables will include the following prefix:

 __sJT_

The following declarations are examples of those that might occur in a SQLJ-generated statement block:

int __sJT_index;
Object __sJT_key;
java.sql.PreparedStatement __sJT_stmt;

The string __sJT_ is a reserved prefix for SQLJ-generated variable names. SQLJ programmers must not use this string as a prefix for the following:

• Names of variables declared in blocks that include executable SQL statements

• Names of parameters to methods that contain executable SQL statements

• Names of fields in classes that contain executable SQL statements, or whose subclasses or enclosed classes contain executable SQL statements

Class Naming Restrictions

Be aware of the following minor restrictions in naming classes in SQLJ applications:

• You must not declare class names that may conflict with SQLJ internal classes. In particular, a top-level class cannot have a name of the following form, where a is the name of an existing class in the SQLJ application:

  a_SJb
  

  where, a and b are legal Java identifiers.

  For example, if your application class is Foo in file Foo.sqlj, then SQLJ generates a profile-keys class called Foo_SJProfileKeys. Do not declare a class name that conflicts with this.

• A class containing SQLJ executable statements must not have a name that is the same as the first component of the name of any package that includes a Java type used in the application. Examples of class names to avoid are java, sqlj, and oracle (case-sensitive). As another example, if your SQLJ statements use host variables whose type is abc.def.MyClass, then you cannot use abc as the name of the class that uses these host variables.

  To avoid this restriction, follow Java naming conventions recommending that package names start in lowercase and class names start in uppercase.

SQLJ Namespace

The SQLJ namespace refers to #sql class declarations and the portion of #sql executable statements outside the curly braces.

Avoid using the following SQLJ reserved words as class names for declared connection context classes or iterator classes, in with or implements clauses, or in iterator column type declaration lists:

• iterator

• context

• with

For example, do not have an iterator class or instance called iterator or a connection context class or instance called context.

However, note that it is permissible to have a stored function return variable whose name is any of these words.

SQL Namespace

The SQL namespace refers to the portion of a SQLJ executable statement inside the curly braces. Standard SQL naming restrictions apply here.

However, note that host expressions follow rules of the Java namespace, not the SQL namespace. This applies to the name of a host variable and to everything between the outer parentheses of a host expression.

File Name Requirements and Restrictions

SQLJ source files have the .sqlj file name extension. If the source file declares a public class (maximum of one), then the base name of the file must match the name of this class (case-sensitive). If the source file does not declare a public class, then the file name must still be a legal Java identifier, and it is recommended that the file name match the name of the first defined class.

For example, if you define the public class MySource in your source file, then your file name must be:

MySource.sqlj