by Boris Milrud

Part II: SQLJ Language Elements SQLJ is the evolving industry standard language that allows you to write Oracle stored procedures with database-independent code that can be easily ported to other Java-enabled database platforms. Get the lowdown on connection contexts, iterators, executable statements, and host expressions so you can put some actual SQLJ to use.

n my first article I described what SQLJ is, compared to PL/SQL and JDBC, and examined SQLJ's benefits. In this article, I explore the fundamentals of the SQLJ programming language so you can get ready to put some actual SQLJ to use.

A SQLJ program is a regular Java program with embedded SQL statements that begin with a #sql token and end with a semicolon. There are two categories of SQLJ statements: declarations and executable statements.

Declarations declare connection contexts and iterators. Connection contexts are used to establish the database connection, while iterators are used to store result sets returned by SQL queries; more on those later. Executable statements execute embedded SQL statements and PL/SQL blocks. Because the SQLJ program will be translated and run via JDBC, any SQLJ statement supported by a JDBC driver (see below) may be embedded within a SQLJ executable statement. Executable statements may also contain host expressions, which are used to exchange information between the Java program and the database via Java variables.

Oracle JDBC Drivers
Oracle provides the following JDBC drivers:

• Client-side Thin driver is a 100% Java driver for client-side use without an Oracle installation. Oracle recommends using it with applets. It can be downloaded to the browser while the Java applet runs.
  
  
• OCI drivers (OCI8 and OCI7) are for client-side use with an Oracle client installation. The Oracle JDBC OCI drivers access the database by calling the Oracle Call Interface (OCI) directly from Java, providing the highest compatibility with the different Oracle 7, 8, and 8i versions. These drivers require an Oracle client installation with Net8.
  
  
• Server-side Thin driver offers the same functionality as the client-side Thin driver but runs inside an Oracle database and accesses a remote database. This is useful in accessing a remote Oracle server from an Oracle server acting as a middle tier, or—more generally—to access one Oracle server from inside another, such as from any Java stored procedure or EJB.
  
  
• Server-side Internal driver, called KPRB (Kernel Program Bundled), provides support for any Java code that runs inside the target Oracle database where the SQL operations are to be performed. The server-side Internal driver allows the JServer JVM to communicate directly with the SQL engine. This is a default JDBC driver for SQLJ code running as a stored procedure, stored function, trigger, EJB, or CORBA object in the Oracle 8i/9i server.
  
  The KPRB JDBC driver is very light and efficient, and it is specifically optimized for running inside Oracle JServer. That's the driver you will be using to write your SQLJ stored procedure.

Let's examine the described SQLJ elements, one by one: connection contexts, iterators, executable statements, and host expressions.

Connection Contexts
For a single connection, you could use one instance of the DefaultContext class and specify the database URL, username, and password when you construct the DefaultContext object. The easiest way is to use the connect() method of the oracle.sqlj.runtime.Oracle class provided by Oracle Corp. In this example, we are going to use the JDBC Thin driver to connect user "scott" with password "tiger" to a database on server MYSERVER through port 1521, where ORCL is the SID of the database to connect to:

Oracle.connect("jdbc:oracle:thin@MYSERVER:1521:ORCL", "scott", "tiger");

It creates an instance of the DefaultContext class and installs it as your default connection. It is not necessary to do anything with the instance of DefaultContext directly.

For multiple connections, you can create and use additional instances of the DefaultContext class by using the Oracle.getConnection() method. In this example, you will use the Oracle OCI8 driver with MYSERVER_ORCL as an Oracle service name, created in the TNSNames.ora file for an ORCL instance:

DefaultContext myContext1 = Oracle.getConnection
("jdbc:oracle:oci8@MYSERVER_ORCL", "scott", "tiger");
DefaultContext myContext2 = Oracle.getConnection
("jdbc:oracle:oci8@MYSERVER_ORCL ", "tom", "bear");

This code creates two connection context instances that both use the same Oracle OCI8 driver, but different schemas. You can perform SQL operations in both schemas by specifying the connection for each statement:

#sql [myContext1] { SQL statement };
...
#sql [myContext2] { SQL statement };

At the end of the program, we need to commit or roll back any pending changes and close the connection(s) in a FINALLY clause and TRY/CATCH block:

finally
{
   #sql [myContext1] { commit };
   myContext1.close();

   #sql [myContext2] { commit };
   myContext2.close();
}
...

Next: Iterators, Executable Statements, and Host Expressions