Java Stored Procedures

You can build an application in Java by using three methods:

•    Write triggers and Java stored procedures.
•    In Java implement CORBA serves in the database.
•    Execute Enterprise JavaBeans.

Oracle8i lets you write stored procedures by using Java and PL/SQL. While PL/SQL is seamless with SQL, using Java gives an open, general-purpose platform to develop and deploy applications. It stores procedures can be written efficiently by using SQLJ that permits SQL to be embedded in Java code. It stored procedures can run in different runtime contexts:

•    You can write top-level SQL procedures and functions which allow you to implement business logic in a manner same to PL/SQL.
•    All types of triggers supported through Oracle can be implemented using Java
•    Methods  related  with  objects  can  be  implemented  by  using  Java,  also  to PL/SQL or C/C++. The body of an object type also can be implemented as a Java class.

Java should stored procedures implemented in Oracle8I are standards complaints that consist of two major parts:

Part 0    The specification of SQLJ which lets you develops efficient applications through embedding SQL statements into Java programs.
Part 1    The specification of Java stored procedures which provides standard DDL extensions to "publish" or register Java programs with SQL thus that the Java stored procedure is callable from SQL. It also gives standard DML extensions to call Java from SQL.

The use of Java stored procedures gives various advantages to OLTP applications:

- Network traffic can be reduced since result sets can be processed on the    database server and only the results are sent   across the network. This permits efficient usage of server resources since transfer of huge quantities of data across the network is not necessary.

- Business rules can be enforced centrally and through allowing Java stored procedures to be replicated among, say, the headquarters and the branch offices.

- Several   configurations   can   be   supported,   like   as   client/server   and   multi-tier configuration.

- The  use  of  Java  in  the  development  of  stored  procedures  gives  an  open  and ANSI/ISO - complaint standard environment. This permits the stored procedures to be portable across several platforms.

- PL/SQL and Java can inter-operate very well to permit application reuse.

You develop Java stored procedures via following these common steps:

1.    By using a standard Java tool, develop a Java program which you want to make a stored procedure. An Oracle provides Jdeveloper and SQLJ that you can use to develop efficient Java programs quickly.

2.    After the Java program is written, load it into Oracle8I's JVM. It source text can be loaded as standard Java.class files or as Java.jar files. For load Java class files in Oracle8I, issue the CREATE JAVA DDL command or use the LOADJAVA utility to automate the loading process.

3.    Register the Java program with SQL through exposing the top-level entry point that SQL can use to call Java. Those exposed entry points are the only calls which SQL can make to this Java program. Following Java-to-Java calls can be made within the JVM as required. You should also make sure in which the data types are appropriately mapped among Java and SQL.

4.    Give appropriate rights to the users so in which they can run and call this procedure.

5.    Call the Java stored procedure from SQL DML, triggers or PL/SQL.