Stored Procedures

A stored procedure is part of the logic of your application that executes on a database server. Depending on the DBMS, you code procedures in a SQL dialect or a programming language. Although the International Standards Organization (ISO) released a standard for procedures (or persistent stored modules) in 1996, database companies coalesced around Java as a stored procedure language.

Stored procedures use distributed processing. When a SQL client invokes a stored procedure, control is transferred to the server. The server executes the procedure and optionally returns data to the client. Procedures can return output parameters, and sometimes query result sets. Stored procedures minimize network round-trips between the client and server and they improve performance by decreasing network latency. Database administrators also use procedures to provide security. For example, you can give a user the right to execute a procedure that processes an order without giving the user rights to view or change order tables.
 
 

Hello World - An Introduction to Java Stored Procedures

Here is an example of writing "Hello world". Define a class, Hello, with one method, Hello.world(), that returns the string "Hello world": 

public class Hello
{
   public static String world ()
   {
      return "Hello world";
   }
}

Compile the class on your client workstation. Using Sun's JDK, for example, you invoke the Java compiler, javac, as follows: 

javac Hello.java

Normally, it is a good idea to specify your CLASSPATH on the javac command line, especially when writing shell scripts or make files. The Java compiler produces a java binary file--in this case, Hello.class. Now you must load the class on the Oracle8i server using loadjava. 

loadjava -user scott/tiger Hello.class

This simplest of examples uses the default oci8 connection to the database. You must specify the username and password. By default, the logon schema (in this case, "scott") will contain the Hello class.

Keep in mind where this Java code will execute. If you execute Hello.class on your client workstation, then it will search the CLASSPATH you provide for all supporting core classes it must execute. One obvious class needed is String, which is part of the java.lang package. String must be found in the CLASSPATH (or, more typically, in a .jar or .zip file specified as part of the CLASSPATH).

In this case, you have loaded Hello.class to the server, where it is stored in the database as a Java schema object. When you execute the world() method of the Hello.class on the server, it will find the necessary supporting classes, such as String, using a resolver--in this case, the default resolver. The default resolver looks for classes in the current schema first and then in SYS schema. All core class libraries, including the java.lang package, are found in SYS schema. You may need to specify different resolvers, and you can force resolution to occur when you use loadjava, to determine if there are any problems earlier, rather than at runtime. Refer to Appendix A, "Tools", for more details on loadjava if you want to learn more than the basics necessary for this example.

To invoke a Java static method with a SQL CALL, you must publish it with a call specification. A call specification defines for SQL which arguments the method takes and which SQL types it returns. It would be simple to generate call specifications, but there is no need to have each Java method specified for interaction with SQL in this manner. It makes this simple example appear more complex, but in practice, few methods are exposed in this manner. Oracle's JDeveloper product, for example, automates call-specification creation.

In SQL*Plus, connect to the database and define a top-level call specification for Hello.world(): 

connect scott/tiger
create or replace function HELLOWORLD return VARCHAR2 as
   language java name 'Hello.world () return java.lang.String';
myString varchar2;
call HELLOWORLD() into :myString;
print myString;

The call HELLOWORLD() into :myString statement is a new way to make a top-level call in Oracle8i. The Oracle-specific select HELLOWORLD from DUAL also works. Note that SQL and PL/SQL see no difference between a stored procedure written in Java, PL/SQL, or any other language. The call specification provides a means to tie inter-language calls together in a consistent manner. Again, do not be intimidated or feel burdened by call specifications because they are necessary only for entry points invoked with triggers or SQL and PL/SQL calls. Furthermore, JDeveloper can automate the task of writing call specifications if you want. Finally, if you are developing server Java code using CORBA and EJB, you do not use call-specifications.

 

 

Accessing and Manipulating SQL Data Using JDBC and SQLJ

JDBC is an industry-standard API developed by Sun Microsystems that allows you to embed SQL statements as Java method arguments. JDBC is based on the X/Open SQL Call Level Interface and complies with the SQL92 Entry Level standard. Each vendor, such as Oracle, creates its JDBC implementation by implementing the interfaces of the Sun Microsystems java.sql package. As discussed in Chapter 1, Oracle offers three JDBC drivers that implement these standard interfaces: 1) the JDBC Thin driver, a 100% pure Java solution you can use for either client-side applications or applets and requires no Oracle client installation; 2) the JDBC OCI drivers (OCI 8 or OCI 7), which you use for client-side applications and requires an Oracle client installation; and 3) the server-side JDBC driver embedded in the Oracle8i server. 

For the developer, using JDBC is a step-by-step process of creating a statement object of some type for your desired SQL operation, assigning any local variables that you want to bind to the SQL operation, and then executing the operation. This process is sufficient for many applications but becomes cumbersome for any complicated statements. 

SQLJ offers an industry-standard way to embed any static SQL operation directly into Java source code in one simple step, without requiring the individual steps of JDBC. (Dynamic SQL operations, where the operations are not known until runtime, require JDBC. In typical applications, however, this represents a minority of the SQL operations.) Oracle SQLJ complies with ANSI standard X3H2-98-320. 

SQLJ consists of a translator (a precompiler that supports standard SQLJ programming syntax) and a runtime component. After creating your SQLJ source code in a .sqlj file, you process it with the translator, which translates your SQLJ source code to standard Java source code, with SQL operations converted to calls to the SQLJ runtime. In the Oracle SQLJ implementation, the translator then invokes a Java compiler to compile the Java source. When your Oracle SQLJ application runs, the SQLJ runtime calls JDBC to communicate with the database. 

 

JDBC Code vs. SQLJ Code

The following is an example of a simple operation, first in JDBC code and then SQLJ code. 

JDBC: 

 
// (Presume you already have a JDBC Connection object conn)
// Define Java variables
String name;
int id=37115;
float salary=20000;
// Set up JDBC prepared statement.
PreparedStatement pstmt = conn.prepareStatement
  ("select ename from emp where empno=? and sal>?");
pstmt.setInt(1, id);
pstmt.setFloat(2, salary);
// Execute query; retrieve name and assign it to Java variable.
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
  name=rs.getString(1);
  System.out.println("Name is: " + name);
}
// Close result set and statement objects.
rs.close()
pstmt.close();
 
The first three lines define the Java variables name,id, and salary. The next line then defines something known as a prepared statement (this presumes you have already established a connection to the database so that you can use the prepareStatement() method of the connection object). You can use a prepared statement whenever values within the SQL statement must be dynamically set (you can use the same prepared statement repeatedly with different variable values). The question marks in the prepared statement are placeholders for Java variables and are given values in the pstmt.setInt() and pstmt.setFloat() lines of code. The first "?" is set to the int variable id (with a value of 37115). The second "?" is set to the float variablesalary (with a value of 20000). Then the query is executed and returns the data into a JDBC result set object. (You can use result sets to gather query data.) Finally, the data of interest (the name) is retrieved from the result set and printed. A result set usually contains multiple rows of data, although this example has only one row.

Now, by comparison, here is some SQLJ code that performs the same task. Note that all SQLJ statements, both declarations and executable statements, start with the #sql token. 

SQLJ: 

 
String name;
int id=37115;
float salary=20000;
#sql {select ename into :name from emp where empno=:id and sal>:salary);
System.out.println("Name is: " + name);
 
SQLJ, in addition to allowing SQL statements to be directly embedded in Java code, supports Java host expressions (also known as bind expressions) to be used directly in the SQL statements. In the simplest case, a host expression is a simple variable as in this example, but more complex expressions are allowed as well. Each host expression is preceded by ":" (colon); this example uses Java host expressions name, id, and salary. In SQLJ, because of its host expression support, you do not need a result set or equivalent when you are returning only a single row of data. 

SQLJ also allows you to catch errors in your SQL statements before runtime. JDBC code, being pure Java, is compiled directly. The compiler has no knowledge of SQL, however, so it is unaware of any SQL errors. By contrast, when you translate SQLJ code, the translator analyzes the embedded SQL statements semantically and syntactically, catching SQL errors during development instead of allowing an end-user to catch them when running the application. 

SQLJ Example

This section presents a complete example of a simple SQLJ program: 
 
import java.sql.*;
import sqlj.runtime.ref.DefaultContext;
import oracle.sqlj.runtime.Oracle;
#sql iterator MyIter (String ename, int empno, float sal);
 
public class MyExample 
{
  public static void main (String args[]) throws SQLException 
   {
     Oracle.connect
        ("jdbc:oracle:thin:@oow11:5521:sol2", "scott", "tiger");
 
     #sql { insert into emp (ename, empno, sal)
        values ('SALMAN', 32, 20000) };
     MyIter iter;
 
     #sql iter={ select ename, empno, sal from emp };
     while (iter.next()) {
        System.out.println
           (iter.ename()+" "+iter.empno()+" "+iter.sal());
     }
  }
}
 
The first SQLJ statement is an iterator declaration. SQLJ uses a strongly typed version of JDBC result sets, known as iterators. The main difference between the two is that an iterator has a specific number of columns of specific datatypes. You must define your iterator types beforehand, as in this example: 
 
#sql iterator MyIter (String ename, int empno, float sal);
 
This declaration results in SQLJ creating an iterator class MyIter. Iterators of type MyIter can store results whose first column maps to a Java String, whose second column maps to a Java int, and whose third column maps to a Java float. This definition also names the three columns--ename,empno, and sal, respectively--to match the table column names in the database. (MyIter is a named iterator. See Chapter 3 of the Oracle8i SQLJ Developer's Guide and Reference to learn about positional iterators, which do not require column names.) 

The first statement of the main method is:  

 
  Oracle.connect
("jdbc:oracle:thin:@oow11:5521:sol2","scott", "tiger");
 
Oracle SQLJ furnishes the Oracle class, and its connect() method accomplishes three important things: 1) it registers the Oracle JDBC drivers SQLJ uses to access the database; 2) it opens a database connection for the specified schema (user scott, password tiger) at the specified URL (host oow11, port 5521, SID so12, "thin" JDBC driver); 3) it establishes this connection as the default connection for your SQLJ statements. Although each JDBC statement must explicitly specify a connection object, a SQLJ statement can either implicitly use a default connection or optionally specify a different connection. 

The first SQLJ executable statement is the following, which inserts a row into the emp table:  

 
#sql {insert into emp (ename, empno, sal) values ('SALMAN', 32, 20000)};
 
The next SQLJ statement, following the declaration of an iterator variable, executes a query. This instantiates and populates the iterator: 
 
MyIter iter;
#sql iter={select ename, empno, sal from emp};
 
And once the iterator is populated, you access it as follows: 
 
while (iter.next()){
  System.out.println(iter.ename()+" "+iter.empno()+" "+iter.sal());
}
 
The next() method is common to all iterators and plays the same role as the next() method of a JDBC result set, returning true and moving to the next row of data if any rows remain. You access the data in each row by calling iterator accessor methods whose names match the column names (this is a characteristic of all named iterators). In this example, you access the data using the methods ename(), empno(), and sal().

SQLJ Strong Typing Paradigm

The preceding example and discussion of iterators points out a key advantage of SQLJ. Its use of strong typing, such as iterators instead of result sets, allows your SQL instructions to be checked against the database during translation. For example, SQLJ can optionally connect to a database and check your iterators against the database tables that will be queried. The translator will verify that they match, allowing you to catch SQL errors during translation that would otherwise not be caught until a user runs your application. Furthermore, if changes are subsequently made to the schema, you can determine if this affects the application simply by re-running the translator. 

Translating a SQLJ Program

Integrated development environments such as Oracle JDeveloper, a Windows-based visual development environment for Java programming, can translate, compile, and customize your SQLJ program for you as you build it. If you are not using an IDE, then you can use the front-end SQLJ utility, sqlj. You can specify many options when you run the sqlj utility, but in the simplest case you can run it as follows: 

%sqlj MyExample.sqlj

While translating a .sqlj file, the SQLJ translator checks the syntax and semantics of your SQL operations. Additionally, you can enable online checking to check your operations against the database. In doing this, you must specify an example database schema in your translator option settings. It is not necessary for the schema to have identical data to the one the program will eventually run against, but the tables in it should have columns with corresponding names and datatypes. Use the translator user option to enable online checking, and specify the username, password, and URL of your exemplar schema, as in the following example: 

%sqlj -user=scott/tiger@jdbc:oracle:thin:@oow11:5521:sol2
MyExample.sqlj

Running a SQLJ Program in the Server vs. Running on a Client

Many SQLJ applications run on a client; however, SQLJ, with its simple syntax, offers particular advantage in programming stored procedures (which are usually SQL-intensive) to run in the server.

There is almost no difference between coding for a client-side SQLJ program and a server-side SQLJ program. The SQLJ runtime packages are automatically available on the server, and there are just the following few coding considerations (which Chapter 11 of the Oracle8i SQLJ Developer's Guide and Reference further discusses):

To run a SQLJ program in the server, presuming you develop the code on a client (as is usually the case), you have two options:

In either case, you can use the Oracle loadjava utility to load the file or files to the server. Chapter 11 of the Oracle8i SQLJ Developer's Guide and Reference also discusses this.

 

 

Converting a Client Application to Run in the Server

The steps in converting an existing SQLJ client-side application to run in the server are fairly simple and straightforward. Presume this is an application that has already been translated on the client:

  1. Create a .jar file for your application components.
  2. Use the loadjava utility to load the .jar file to the server.
  3. Create a SQL wrapper in the server for your application. For example, to run the preceding MyExample application in the server:
    4.       create or replace procedure SQLJ_MYEXAMPLE as language java
      name `MyExample.main(java.lang.String[])';

You can then execute SQLJ_MYEXAMPLE as with any other stored procedure.

Interacting with PL/SQL

All of the Oracle JDBC drivers communicate seamlessly with Oracle SQL and PL/SQL, and it is important to note that SQLJ interoperates with PL/SQL. You can start using SQLJ without having to rewrite any PL/SQL stored procedures. Oracle SQLJ includes syntax for calling PL/SQL stored procedures and also allows PL/SQL anonymous blocks to be embedded in SQLJ executable statements, just as with SQL operations.

 

Distributed Objects Using CORBA and EJB

Java makes a terrific, simple but general purpose, language for writing stored procedures. JDBC and SQLJ supply the glue that allows Java to access SQL data. The glue comes in the form of Java language support for SQL operations and concepts, variable bindings between Java and SQL types, and supporting classes that map Java classes to SQL types. You can write portable Java code that can execute on a client or a server without change. With JDBC and SQLJ, the dividing line between client and server is usually obvious--SQL operations happen in the server, and application program logic resides in the client.

Normally, in a program whose logic is distributed, the architecture of choice has three tiers--the client, the middle tier, and the database server. The client tier is typically limited to display of information provided by the middle tier. The middle tier performs the business or application logic, accessing data that resides on the third tier, the database server. Oracle8i removes the need for a physical middle tier for many applications--those that require fast access to the database. Oracle8i still maintains a three-tier logical architecture, but by combining the middle tier and the database server, the physical architecture is two-tier. The flexibility inherent in this architecture is ideally suited to Internet applications where the client presents information in a Web browser, interacting with servers across the network. Those servers, in turn, can be federated and cooperate in their own client-server interactions to provide information to Web-based clients in an intranet or Internet application.

As you write more complex Java programs, you will discover opportunities to gain performance and scalability by controlling the location at which program logic executes. You will want to minimize network traffic and maximize locality of reference to SQL data. JDBC and SQLJ, particularly with the new JServer support for Java in Oracle8i, furnish ways to accomplish these goals. However, as you tend to leverage the object model in your Java application, a more significant portion of time is spent in Java execution, as opposed to SQL data access and manipulation. It becomes more important to understand and specify where Java objects reside and execute in an Internet application. Now you have become a candidate for moving into the world of CORBA and Enterprise JavaBeans.

A key feature of CORBA and EJB is the use of standards to specify components. CORBA uses Interface Definition Language (IDL) to specify, in a language-independent manner, how to access and use a group of objects known as a component. Enterprise JavaBeans extends this concept by relying on Java class definitions to specify the interface to a component and an RMI-style declarative deployment descriptor to define how the component is treated in a transactional, secure application. CORBA and EJB are complementary; the JServer implementation of the Enterprise JavaBeans 1.0 specification builds on the underlying support and services of CORBA.

You can access components through a name service, which forms a tree, similar to a file system, where you can store objects by name. The Java Naming and Directory Interface (JNDI) package provides a unified interface to name services. Part of JNDI provides a platform-independent abstraction for accessing a file system--something that is very platform dependent. When you put a CORBA object into the namespace, you are publishing it. Although this may seem analogous to publishing entry points (or Java static methods) through call specifications, as discussed with Java stored procedures, it is actually much more powerful. With EJB, you are publishing a component whose interface is completely specified, including the manner in which a transaction treats it and the security restrictions on its use. With Java stored procedures and call specifications, you are limited to SQL data types as arguments and as return values (or the limited set of Java classes that represent these SQL data types, to be more exact). With CORBA and EJB, you operate purely in an object-oriented, message-based world. Methods take objects as arguments and return objects. These objects maintain object identity during your session. This means that the communication protocol between a client and server is object-oriented and seamlessly integrated into the Java language.

Oracle 8i interacts with each client as if it had its own virtual machine running in the server. Because of this architecture, there is no single ORB in the JServer servicing multiple client requests. Instead, JServer leverages off of Oracle8i's Multithreaded Server (MTS) architecture, providing an ORB per session. Unlike a session in which the client communicates through SQL*Net, you access CORBA and EJB sessions through IIOP, and they are capable of servicing multiple client connections. Although scalable applications will generally provide one session per client-server interaction, the ability to serve multiple clients greatly extends the flexibility of the session in comparison to SQL*Net, allowing callbacks and loopbacks in your distributed communications. Such capabilities are an important requirement of CORBA and general Java programs.

If you are a CORBA programmer, then you are familiar with bootstrapping your application. In effect, your client Java code has to obtain handles to objects that actually reside on the server to operate. Those objects are reachable through the name service discussed in the preceding section. The ORB typically supplies the name service, which presupposes that the ORB is running when you attempt to locate the server objects necessary to bootstrap your client application. In the JServer, there is no ORB process per se. Instead, JServer provides an activation service based on CORBA's CosNaming. We provide a JNDI interface to CosNaming so that you use URL-based naming to refer to and activate CORBA objects in a session. This namespace incorporates the idea of a session directly in the URL, allowing the client to easily manipulate multiple sessions. In effect, all bootstrapping is performed by establishing a session with the JServer and using objects always reachable from the Oracle8i database that the standard Java JNDI and CORBA CosNaming make visible to you. You need not mess with flat-file-based Inter-ORB References (IORs) as you might be used to with most CORBA programming.

EJB makes component-based programming even easier than CORBA. An EJB programmer simply writes business logic and the interfaces to the component, and a deployment tool, deployejb, takes care of the rest. No knowledge of IDL, how transactions are implemented, nor how to implement security is necessary. This portable Java-based server framework provides a fast, scalable and easy solution to Java-based, three-tier applications.