Simplify with Java Stored Procedures （转）

Simplify with Java Stored Procedures

By Kuassi Mensah

Use Java stored procedures to bridge SQL, XML, Java, and J2EE and Web Services.

Stored procedures allow a clean separation of persistence logic that runs in the database tier from business logic that runs in the middle tier. This separation reduces overall application complexity and increases reuse, security, performance, and scalability.

A major obstacle, however, for widespread adoption of stored procedures is the set of various proprietary, database-dependent implementation languages that different database vendors use. The use of Java-based stored procedures fixes this concern. Oracle has implemented ANSI standards that specify the ability to invoke static Java methods from SQL as procedures or functions. This implementation is called simply "Java stored procedures."

In this article, you will learn how Java stored procedures help simplify and increase the performance of your business logic and extend database functionality. I'll show how Oracle enables the use of Java stored procedures within the database. I'll also look at how Java stored procedures access data, and show how to create a basic Java stored procedure.

PL/SQL or Java

When you think of Oracle stored procedures, you probably think of PL/SQL. Oracle, however, has provided Java support in the database since Oracle8i, to offer an open and portable alternative to PL/SQL for stored procedures. I can hear the $64,000 question: "How do I choose between PL/SQL and Java? Should I forget all the things I've been told about PL/SQL and move on to the greener Java pastures?"

Both languages are suitable for database programming, and each has its strengths and weaknesses. In deciding which language to use, here's a general rule of thumb:

 

• Use PL/SQL for database-centric logic that requires seamless integration with SQL and therefore complete access to database objects, types, and features.

   

   

• Use Java as an open alternative to PL/SQL for database independence, but also for integrating and bridging the worlds of SQL, XML, J2EE, and Web services.

 

OracleJVM Lets You Run Java within the Database

Since Oracle8i, Release 1 (Oracle 8.1.5), Oracle has offered a tightly integrated Java virtual machine (JVM) that supports Oracle's database session architecture. Any database session may activate a virtually dedicated JVM during the first Java code invocation; subsequent users then benefit from this already Java-enabled session. In reality, all sessions share the same JVM code and statics—only private states are kept and garbage collected in an individual session space, to provide Java sessions the same session isolation and data integrity capabilities as SQL operations. There is no need for a separate Java-enabled process for data integrity. This session-based architecture provides a small memory footprint and gives OracleJVM the same linear SMP scalability as the Oracle database.

Creating Java Stored Procedures

There are a few steps involved in turning a Java method into a Java stored procedure. These include loading the Java class into the database using the loadjava utility, and publishing the Java methods using a call specification (Call Spec) to map Java methods, parameter types, and return types to their SQL counterparts. The following section shows how to do this.

I'll use a simple Hello class, with one method, Hello.world(), that returns the string "Hello world":

 

public class Hello
{
   public static String world ()
   {

      return "Hello world";
   }
}

 

The Loadjava Utility

Loadjava is a utility for loading Java source files, Java class files, and Java resource files; verifying bytecodes; and deploying Java classes and JAR files into the database. It is invoked either from the command line or through the loadjava() method contained within the DBMS_JAVA class. To load our Hello.class example, type:

 

loadjava -user scott/tiger Hello.class

 

As of Oracle9i Release 2, loadjava allows you to automatically publish Java classes as stored procedures by creating the corresponding Call Specs for methods contained in the processed classes. Oracle provides Oracle9i JDeveloper for developing, testing, debugging, and deploying Java stored procedures.

The Resolver Spec

The JDK-based JVM looks for and resolves class references within the directories listed in the CLASSPATH. Because Oracle database classes live in the database schema, the OracleJVM uses a database resolver to look for and resolve class references through the schemas listed in the Resolver Spec. Unlike the CLASSPATH, which applies to all classes, the Resolver Spec is applied on a per-class basis. The default resolver looks for classes first in the schema in which the class is loaded and then for classes with public synonyms.

 

 
loadjava -resolve <myclass>

You may need to specify different resolvers, and you can force resolution to occur when you use loadjava, to determine at deployment time any problems that may occur later at runtime.

 

 

loadjava -resolve -resolver "((* SCOTT) (foo/bar/* OTHERS) 
(* PUBLIC))"

 

Call Spec and Stored Procedures Invocation

To invoke a Java method from SQL (as well as from PL/SQL and JDBC), you must first publish the public static method through a Call Spec, which defines for SQL the arguments the method takes and the SQL types it returns.

In our example, we'll use SQL*Plus to connect to the database and define a top-level Call Spec for Hello.world():

 

SQL> connect scott/tiger
SQL> create or replace function helloworld return
VARCHAR2 as language java name 'Hello.world () return

java.lang.String';
 /
Function created.

 

You can then invoke the Java stored procedure as shown below:

 

SQL> variable myString varchar2[20];
SQL> call helloworld() into :myString;
Call completed.
SQL> print myString;

MYSTRING

---------------------
Hello world

 

Java stored procedures are callable, through their Call Spec, from SQL DML statements (INSERT, UPDATE, DELETE, SELECT, CALL, EXPLAIN PLAN, LOCK TABLE, and MERGE), PL/SQL blocks, subprograms, and packages, as well as database triggers. The beauty of Call Spec is that stored procedure implementations can change over time from PL/SQL to Java or vice versa, transparently to the requesters.

Call Spec abstracts the call interface from the implementation language (PL/SQL or Java) and therefore enables sharing business logic between legacy applications and newer Java/J2EE-based applications. At times, however, when invoking a database-resident Java class from a Java client, you may not want to go through the PL/SQL wrapper. In a future release, Oracle plans to provide a mechanism that will allow developers to bypass the Call Spec.

Advanced Data-Access Control

Java stored procedures can be used to control and restrict access to Oracle data by allowing users to manipulate the data only through stored procedures that execute under their invoker's privileges while denying access to the table itself. For example, you can disable updates during certain hours or give managers the ability to query salary data but not update it, or log all access and notify a security service.

Sharing Data Logic Between Legacy and J2EE Applications

Because legacy applications and J2EE applications both invoke stored procedures through the Call Spec, the same data logic can be shared between J2EE and non-J2EE worlds. Thanks to Call Spec, this data logic can be shared regardless of the implementation language used (whether PL/SQL or Java).

Autogeneration of Primary Keys for BMP Entity Beans

When using BMP for EJB entity beans, a bean instance can be uniquely identified by the auto-generated primary key associated with the newly inserted data as a return value for ejbCreate(). You can retrieve this value within ejbCreate() in one database operation by using a stored procedure that inserts the corresponding data and retrieves or computes the primary key. Alternatively, you could insert the data and retrieve the corresponding key (or ROWID) in one SQL statement, using the RETURN_GENERATED_KEYS feature in JDBC 3.0. However, the stored procedure approach is more portable across JDBC driver versions and databases.

You can implement this pattern with these three steps:

 

1. Create the Java stored procedure, defining a public static Java method insertAccount() within a public GenPK class. This method will insert data, compute a unique key (by passing out a sequence number), and return the computed key as primary key.

    

    

2. Define the Call Spec.

    

    

   CREATE OR REPLACE PROCEDURE insertAccount(owner IN
   varchar, bal IN number, newid OUT number)
   AS LANGUAGE JAVA NAME 'GenPK.insertAccount(
   java.lang.String [])';
   /
   

    

    

3. Invoke the stored procedure within ejbCreate().

    

    

   Public AccountPK ejbCreate(String ownerName, int balance) throws CreateException
   
   {
      try {
        CallableStatement call = conn.prepareCall{ 
        "{call insertAccount(?, ?, ?)}"};		
              return new AccountPK(accountID);
      }
   }
   

 

Custom Primary Key Finders for CMP Entity Beans

Finder methods are used for retrieving existing EJB entity bean instances. Primary key finders allow you to retrieve a uniquely identified EJB instance. For CMP entity beans, the EJB container automatically generates the primary key finder findByPrimaryKey() method, based on declarative description. In some situations, however, you might need more control; for example, you may need a specialized finder such as findByStoredProcKey(). In these situations, you can use Java stored procedures in conjunction with an object relational framework (such as Oracle9i Application Server [Oracle9iAS] TopLink) to implement a custom primary key finder method. After you define the EJB finder as a REDIRECT or NAMED finder, TopLink will generate the SQL query for retrieving the bean instance.

Data-Driven EJB Invocation

In a data-driven architecture, business logic invocation can be triggered as a result of database operations (such as inserts, updates, or deletes). A Java stored procedure implementing the data logic can be declared as a database trigger to invoke EJBs running in a middle-tier J2EE application server. You can make EJB calls by using either standard remote method invocation (RMI) over Interoperable Inter-ORB Protocol (IIOP), using a J2EE 1.3 compatible server, or RMI over a vendor-specific transport protocol (such as ORMI with Oracle9iAS/OC4J or RMI over T3 with BEA WebLogic). Each application server vendor has its own optimized protocol while providing RMI over IIOP for interoperability. Oracle9iAS supports both RMI calls over IIOP and ORMI protocols.

Data-Driven Messaging

Oracle9i Database embeds Advanced Queuing (AQ), which is an integrated, persistent, reliable, secure, scalable, and transactional message-queuing framework. Oracle exposes AQ features to Java developers through the standard Java Messaging System (JMS) API. Java stored procedures can invoke AQ operations through the JMS interface to allow fast, intra-session, scalable, data-driven messaging.

Java stored procedures can use JMS to invoke AQ operations. You can implement this pattern in four steps:

 

1. Create and start the JMS Queue (to do so, embed the following operations within a SQL script):

    

    

   execute dbms_aqadm.create_queue_table(queue_table =>
   'queue1', queue_payload_type =>
   'SYS.AQ
   Simplify with Java Stored Procedures 
   By Kuassi Mensah 
   Use Java stored procedures to bridge SQL, XML, Java, and J2EE and Web Services. 
   Stored procedures allow a clean separation of persistence logic that runs in the database tier from business logic that runs in the middle tier. This separation reduces overall application complexity and increases reuse, security, performance, and scalability. 
   A major obstacle, however, for widespread adoption of stored procedures is the set of various proprietary, database-dependent implementation languages that different database vendors use. The use of Java-based stored procedures fixes this concern. Oracle has implemented ANSI standards that specify the ability to invoke static Java methods from SQL as procedures or functions. This implementation is called simply "Java stored procedures." 
   In this article, you will learn how Java stored procedures help simplify and increase the performance of your business logic and extend database functionality. I'll show how Oracle enables the use of Java stored procedures within the database. I'll also look at how Java stored procedures access data, and show how to create a basic Java stored procedure. 
   PL/SQL or Java 
   When you think of Oracle stored procedures, you probably think of PL/SQL. Oracle, however, has provided Java support in the database since Oracle8i, to offer an open and portable alternative to PL/SQL for stored procedures. I can hear the $64,000 question: "How do I choose between PL/SQL and Java? Should I forget all the things I've been told about PL/SQL and move on to the greener Java pastures?" 
   Both languages are suitable for database programming, and each has its strengths and weaknesses. In deciding which language to use, here's a general rule of thumb: 
    
   
      
      
   Use PL/SQL for database-centric logic that requires seamless integration with SQL and therefore complete access to database objects, types, and features. 
       
    
       
    
       
   Use Java as an open alternative to PL/SQL for database independence, but also for integrating and bridging the worlds of SQL, XML, J2EE, and Web services. 
    
   OracleJVM Lets You Run Java within the Database 
   Since Oracle8i, Release 1 (Oracle 8.1.5), Oracle has offered a tightly integrated Java virtual machine (JVM) that supports Oracle's database session architecture. Any database session may activate a virtually dedicated JVM during the first Java code invocation; subsequent users then benefit from this already Java-enabled session. In reality, all sessions share the same JVM code and statics—only private states are kept and garbage collected in an individual session space, to provide Java sessions the same session isolation and data integrity capabilities as SQL operations. There is no need for a separate Java-enabled process for data integrity. This session-based architecture provides a small memory footprint and gives OracleJVM the same linear SMP scalability as the Oracle database. 
   Creating Java Stored Procedures 
   There are a few steps involved in turning a Java method into a Java stored procedure. These include loading the Java class into the database using the loadjava utility, and publishing the Java methods using a call specification (Call Spec) to map Java methods, parameter types, and return types to their SQL counterparts. The following section shows how to do this. 
   I'll use a simple Hello class, with one method, Hello.world(), that returns the string "Hello world": 
    
   public class Hello
   {
      public static String world ()
      {
   
         return "Hello world";
      }
   }
   
    
   The Loadjava Utility 
   Loadjava is a utility for loading Java source files, Java class files, and Java resource files; verifying bytecodes; and deploying Java classes and JAR files into the database. It is invoked either from the command line or through the loadjava() method contained within the DBMS_JAVA class. To load our Hello.class example, type: 
    
   loadjava -user scott/tiger Hello.class
   
    
   As of Oracle9i Release 2, loadjava allows you to automatically publish Java classes as stored procedures by creating the corresponding Call Specs for methods contained in the processed classes. Oracle provides Oracle9i JDeveloper for developing, testing, debugging, and deploying Java stored procedures. 
   The Resolver Spec 
   The JDK-based JVM looks for and resolves class references within the directories listed in the CLASSPATH. Because Oracle database classes live in the database schema, the OracleJVM uses a database resolver to look for and resolve class references through the schemas listed in the Resolver Spec. Unlike the CLASSPATH, which applies to all classes, the Resolver Spec is applied on a per-class basis. The default resolver looks for classes first in the schema in which the class is loaded and then for classes with public synonyms. 
    
    
   loadjava -resolve <myclass>
   
   You may need to specify different resolvers, and you can force resolution to occur when you use loadjava, to determine at deployment time any problems that may occur later at runtime.
    
    
   loadjava -resolve -resolver "((* SCOTT) (foo/bar/* OTHERS) 
   (* PUBLIC))"
   
    
   Call Spec and Stored Procedures Invocation 
   To invoke a Java method from SQL (as well as from PL/SQL and JDBC), you must first publish the public static method through a Call Spec, which defines for SQL the arguments the method takes and the SQL types it returns. 
   In our example, we'll use SQL*Plus to connect to the database and define a top-level Call Spec for Hello.world(): 
    
   SQL> connect scott/tiger
   SQL> create or replace function helloworld return
   VARCHAR2 as language java name 'Hello.world () return
   
   java.lang.String';
    /
   Function created.
   
    
   You can then invoke the Java stored procedure as shown below: 
    
   SQL> variable myString varchar2[20];
   SQL> call helloworld() into :myString;
   Call completed.
   SQL> print myString;
   
   MYSTRING
   
   ---------------------
   Hello world
   
    
   Java stored procedures are callable, through their Call Spec, from SQL DML statements (INSERT, UPDATE, DELETE, SELECT, CALL, EXPLAIN PLAN, LOCK TABLE, and MERGE), PL/SQL blocks, subprograms, and packages, as well as database triggers. The beauty of Call Spec is that stored procedure implementations can change over time from PL/SQL to Java or vice versa, transparently to the requesters. 
   Call Spec abstracts the call interface from the implementation language (PL/SQL or Java) and therefore enables sharing business logic between legacy applications and newer Java/J2EE-based applications. At times, however, when invoking a database-resident Java class from a Java client, you may not want to go through the PL/SQL wrapper. In a future release, Oracle plans to provide a mechanism that will allow developers to bypass the Call Spec. 
   Advanced Data-Access Control 
   Java stored procedures can be used to control and restrict access to Oracle data by allowing users to manipulate the data only through stored procedures that execute under their invoker's privileges while denying access to the table itself. For example, you can disable updates during certain hours or give managers the ability to query salary data but not update it, or log all access and notify a security service. 
   Sharing Data Logic Between Legacy and J2EE Applications 
   Because legacy applications and J2EE applications both invoke stored procedures through the Call Spec, the same data logic can be shared between J2EE and non-J2EE worlds. Thanks to Call Spec, this data logic can be shared regardless of the implementation language used (whether PL/SQL or Java). 
   Autogeneration of Primary Keys for BMP Entity Beans 
   When using BMP for EJB entity beans, a bean instance can be uniquely identified by the auto-generated primary key associated with the newly inserted data as a return value for ejbCreate(). You can retrieve this value within ejbCreate() in one database operation by using a stored procedure that inserts the corresponding data and retrieves or computes the primary key. Alternatively, you could insert the data and retrieve the corresponding key (or ROWID) in one SQL statement, using the RETURN_GENERATED_KEYS feature in JDBC 3.0. However, the stored procedure approach is more portable across JDBC driver versions and databases. 
   You can implement this pattern with these three steps: 
    
   
      
      
   Create the Java stored procedure, defining a public static Java method insertAccount() within a public GenPK class. This method will insert data, compute a unique key (by passing out a sequence number), and return the computed key as primary key. 
       
    
       
    
       
   Define the Call Spec. 
       
    
       
    
       
   CREATE OR REPLACE PROCEDURE insertAccount(owner IN
   varchar, bal IN number, newid OUT number)
   AS LANGUAGE JAVA NAME 'GenPK.insertAccount(
   java.lang.String [])';
   /
   
       
    
       
    
       
   Invoke the stored procedure within ejbCreate(). 
       
    
       
    
       
   Public AccountPK ejbCreate(String ownerName, int balance) throws CreateException
   
   {
      try {
        CallableStatement call = conn.prepareCall{ 
        "{call insertAccount(?, ?, ?)}"};		
              return new AccountPK(accountID);
      }
   }
   
       
    
   Custom Primary Key Finders for CMP Entity Beans 
   Finder methods are used for retrieving existing EJB entity bean instances. Primary key finders allow you to retrieve a uniquely identified EJB instance. For CMP entity beans, the EJB container automatically generates the primary key finder findByPrimaryKey() method, based on declarative description. In some situations, however, you might need more control; for example, you may need a specialized finder such as findByStoredProcKey(). In these situations, you can use Java stored procedures in conjunction with an object relational framework (such as Oracle9i Application Server [Oracle9iAS] TopLink) to implement a custom primary key finder method. After you define the EJB finder as a REDIRECT or NAMED finder, TopLink will generate the SQL query for retrieving the bean instance. 
   Data-Driven EJB Invocation 
   In a data-driven architecture, business logic invocation can be triggered as a result of database operations (such as inserts, updates, or deletes). A Java stored procedure implementing the data logic can be declared as a database trigger to invoke EJBs running in a middle-tier J2EE application server. You can make EJB calls by using either standard remote method invocation (RMI) over Interoperable Inter-ORB Protocol (IIOP), using a J2EE 1.3 compatible server, or RMI over a vendor-specific transport protocol (such as ORMI with Oracle9iAS/OC4J or RMI over T3 with BEA WebLogic). Each application server vendor has its own optimized protocol while providing RMI over IIOP for interoperability. Oracle9iAS supports both RMI calls over IIOP and ORMI protocols. 
   Data-Driven Messaging 
   Oracle9i Database embeds Advanced Queuing (AQ), which is an integrated, persistent, reliable, secure, scalable, and transactional message-queuing framework. Oracle exposes AQ features to Java developers through the standard Java Messaging System (JMS) API. Java stored procedures can invoke AQ operations through the JMS interface to allow fast, intra-session, scalable, data-driven messaging. 
   Java stored procedures can use JMS to invoke AQ operations. You can implement this pattern in four steps: 
    
   
      
      
   Create and start the JMS Queue (to do so, embed the following operations within a SQL script): 
       
    
       
    
       
   ___FCKpd___8
       
    
       
    
       
   Create the Java stored procedure (a code snippet is shown):
       
    
       
    
       
   public static void runTest(String msgBody)
   {
    try
       {
   
     // get database connection
        ora_drv = new OracleDriver();
        db_conn = ora_drv.defaultConnection();
   
     // setup sender (cf online code sample)
       ..
     // create message
        s_msg = s_session.createTextMessage(msgBody);
   
     // send message
        sender.send(s_msg);
        s_session.commit();
   
     // receive message
        r_msg = (TextMessage) receiver.receive();
        r_session.commit();
   
     // output message text 
        String body = r_msg.getText();
        System.out.println("message was '"+body+"'");
     ..}
   } 
   
       
    
       
    
       
   Create the Call Spec:
       
    
       
    
       
   create or replace procedure jmsproc (t1 IN VARCHAR) 
   as language java name 'jmsSample.main (java.lang.String[])';
   /
   
       
    
       
    
       
   Invoke the stored procedure:
       
    
       
    
       
   call jmsproc('hello');
   
       
    
   Database-Assisted Web Publishing (Cache Invalidation) 
   One of the common issues application architects must face is how to cache database information reliably to increase overall system performance. JCACHE is an upcoming standard specification (JSR 107) that addresses this problem. It specifies an approach for temporary, in-memory caching of Java objects, including object creation, shared access, spooling, invalidation, and consistency across JVMs. It can be used to cache read-mostly data such as product catalogs and price lists within JSP. Using JCACHE, most queries will have response times an order of magnitude faster because of cached data (in-house testing showed response times about 15 times faster). 
   In order to track all the changes to the origin data and refresh the cached data, a Java stored procedure is attached to a table as a trigger. Any change to this table will result in the automatic invocation of this stored procedure, which in turn will call out a defined JSP to invalidate the JCACHE object that maps its state to the database table. Upon invalidation, the very next query will force the cache to be refreshed from the database. 
   
      
      
   
          
          
           
           Next Steps 
          
          
               
   READ MORE about Java Stored Procedures 
   This article is adapted from the white paper "Unleash the Power of Java Stored Procedures." You can find the white paper at:
   /tech/java/java_db/pdf/
   OW_30820_JAVA_STORED_PROC_paper.PDF 
               
   New PL/SQL features in Oracle9i Database, Release 2
   /tech/pl_sql/pdf/
   Paper_30720_Doc.pdf 
               
   Resolver Spec
   /docs/products/oracle9i/
   doc_library/release2/java.920/a96659.pdf 
               
   OracleJVM and Java 2 Security
   /docs/products/oracle9i/
   doc_library/release2/java.920/a96656.pdf 
               
   DOWNLOAD Code
   Exercise code examples from this article: 
   /sample_code/tech/
   java/jsp/oracle9ijsp.html 
               
   LEARN about stored procedures as Web services
   /tech/webservices 
               
   
   Extending Database Functionality 
   One of the great things about running Java code directly in the database is the ability to implement new functionality by simply loading the code or library and using the Call Spec to make the entry points (public static methods) available to SQL, PL/SQL, Java, J2EE, and non-Java APIs. Oracle9i Database customers can easily extend database functionality. Oracle itself leverages this capability for new utilities and packages such as the XML Developer Kits (XDKs). 
   Bridging SQL, PL/SQL, Java, J2EE, .NET, and XML 
   The Oracle XDK is written in Java and exposes its public methods as Java stored procedures, extending the database's XML programmability. SQL, PL/SQL, Java, J2EE, and non-Java (.NET) business logic all have access to the XML parser, the XSLT processor, the XPath engine, and XML SQL Utility (XSU). 
   The XML parser is accessible through the xmlparser and xmldom packages. XSU is a Java utility that generates an XML document from SQL queries or a JDBC ResultSet, and writes data from an XML document into a database table or view. Using XSU, XML output can be produced as Text, DOM trees, or DTDs. XSU is exposed to PL/SQL through the dbms_xmlquery and dbms_xmlsave packages. 
   Conclusion 
   The integration of the Oracle database with a Java VM enables the creation of portable, powerful, database-independent data logic and persistence logic. The loose coupling of business logic that runs in the middle tier with data logic that runs in the database tier improves application scalability, performance, flexibility, and maintenance. 
   Kuassi Mensah (kuassi.mensah@oracle.com) is a product manager in the Server Technologies division at Oracle.
   JMS_TEXT_MESSAGE', comment => 'a test queue', 
   multiple_consumers => false, compatible => '8.1.0');
   
   execute dbms_aqadm.create_queue( queue_name  => 'queue1',
    queue_table => 'queue1' );
   
   execute dbms_aqadm.start_queue(queue_name => 'queue1');
   

    

    

2. Create the Java stored procedure (a code snippet is shown):

    

    

   ___FCKpd___9

    

    

3. Create the Call Spec:

    

    

   ___FCKpd___10

    

    

4. Invoke the stored procedure:

    

    

   ___FCKpd___11

 

Database-Assisted Web Publishing (Cache Invalidation)

One of the common issues application architects must face is how to cache database information reliably to increase overall system performance. JCACHE is an upcoming standard specification (JSR 107) that addresses this problem. It specifies an approach for temporary, in-memory caching of Java objects, including object creation, shared access, spooling, invalidation, and consistency across JVMs. It can be used to cache read-mostly data such as product catalogs and price lists within JSP. Using JCACHE, most queries will have response times an order of magnitude faster because of cached data (in-house testing showed response times about 15 times faster).

In order to track all the changes to the origin data and refresh the cached data, a Java stored procedure is attached to a table as a trigger. Any change to this table will result in the automatic invocation of this stored procedure, which in turn will call out a defined JSP to invalidate the JCACHE object that maps its state to the database table. Upon invalidation, the very next query will force the cache to be refreshed from the database.

Next Steps READ MORE about Java Stored Procedures This article is adapted from the white paper "Unleash the Power of Java Stored Procedures." You can find the white paper at: /tech/java/java_db/pdf/ OW_30820_JAVA_STORED_PROC_paper.PDF New PL/SQL features in Oracle9i Database, Release 2 /tech/pl_sql/pdf/ Paper_30720_Doc.pdf Resolver Spec /docs/products/oracle9i/ doc_library/release2/java.920/a96659.pdf OracleJVM and Java 2 Security /docs/products/oracle9i/ doc_library/release2/java.920/a96656.pdf DOWNLOAD Code Exercise code examples from this article: /sample_code/tech/ java/jsp/oracle9ijsp.html LEARN about stored procedures as Web services /tech/webservices

Extending Database Functionality

One of the great things about running Java code directly in the database is the ability to implement new functionality by simply loading the code or library and using the Call Spec to make the entry points (public static methods) available to SQL, PL/SQL, Java, J2EE, and non-Java APIs. Oracle9i Database customers can easily extend database functionality. Oracle itself leverages this capability for new utilities and packages such as the XML Developer Kits (XDKs).

Bridging SQL, PL/SQL, Java, J2EE, .NET, and XML

The Oracle XDK is written in Java and exposes its public methods as Java stored procedures, extending the database's XML programmability. SQL, PL/SQL, Java, J2EE, and non-Java (.NET) business logic all have access to the XML parser, the XSLT processor, the XPath engine, and XML SQL Utility (XSU).

The XML parser is accessible through the xmlparser and xmldom packages. XSU is a Java utility that generates an XML document from SQL queries or a JDBC ResultSet, and writes data from an XML document into a database table or view. Using XSU, XML output can be produced as Text, DOM trees, or DTDs. XSU is exposed to PL/SQL through the dbms_xmlquery and dbms_xmlsave packages.

Conclusion

The integration of the Oracle database with a Java VM enables the creation of portable, powerful, database-independent data logic and persistence logic. The loose coupling of business logic that runs in the middle tier with data logic that runs in the database tier improves application scalability, performance, flexibility, and maintenance.

Kuassi Mensah (kuassi.mensah@oracle.com) is a product manager in the Server Technologies division at Oracle.