<a name="994"></a><a name="wbp32ch27P1"></a>Chapter 27: Extending PL/SQL XML Functionality with Java - Perl Cd Bookshelf [Electronic resources] نسخه متنی

Chapter 27: Extending PL/SQL XML Functionality with Java

Starting with Oracle8i 8.1.5, Oracle Java Virtual Machine (Oracle JVM) has been provided as an execution environment for Java code running inside the Oracle databases. This functionality enables you to leverage Java functionality in PL/SQL by writing Java stored procedures.

In this chapter, we will help you understand how to build Java stored procedures to process XML data inside the database, including how to resolve URL references when parsing the XML data, and how to implement PL/SQL procedures to process XML in SAX. We will further provide suggestions to help you decide when to use Java stored procedures, and present some debugging and performance tips in the “Best Practices” section.

Creating the Java Stored Procedure toProcessXML

To create a Java stored procedure, you need to follow these general steps, which are described in detail in the following sections:

Implement the Java code, and include it within a static method (or methods) that can be deployed as a Java stored procedure.

Deploy the Java code to Oracle JVM inside the database.

Create the PL/SQL specification for the Java stored procedure.

After the Java stored procedure is created, you can call it in PL/SQL procedures, SQL commands, or database triggers, just like any other PL/SQL objects. In fact, the caller of such a stored procedure can ignore that it has been written and implemented using Java. In this section, we follow these steps to create a Java stored procedure that parses and validates XML Schema documents.

Implementing the Java Code

Before you implement the Java code that can be deployed as Java stored procedures, you need to understand the datatype conversions between Java and PL/SQL arguments. A complete list of the available data types and their mapping is provided in the Oracle Database 10g online manuals. With regard to XML processing, XMLType, VARCHAR2, and CLOB are the three most frequently used data types for storing XML data and they have the following mappings:

XMLType maps to oracle.xdb.XMLType

VARCHAR2 maps to oracle.sql.CHAR

CLOB maps to oracle.sql.CLOB

For example, if you want to read XML Schema documents from VARCHAR2, you can create a Java stored procedure as follows:

import java.io.ByteArrayInputStream;
import java.io.StringWriter;
import java.lang.Exception;
import oracle.sql.CHAR;
import oracle.xml.parser.schema.XMLSchema;
import oracle.xml.parser.schema.XSDBuilder;
import oracle.xml.parser.schema.XSDException;
public class XSDDBBuilder {
public static String build(CHAR xsd) {
StringWriter sw = new StringWriter();
try {
XSDBuilder builder = new XSDBuilder();
byte [] docbytes = xsd.getBytes();
ByteArrayInputStream in = new ByteArrayInputStream(docbytes);
XMLSchema schemadoc = (XMLSchema)builder.build(in,null);
sw.write("The input XSD parsed without errors.\n");
} catch(Exception ex) {
sw.write("Exception:"+ex.getMessage());
}
return sw.toString();
}
}

In this example, the input parameter is oracle.sql.CHAR, which maps to the VARCHAR2 data type in PL/SQL. You can then get a ByteArrayInputStream and parse it using oracle.xml.parser .schema.XSDBuilder provided by Oracle XDK. The XSDBuilder will not only parse the XML Schema document but also validate the syntax and resolve all the URL references in the XML schema.

In order to be exposed as a PL/SQL procedure, the build() method in the XSDDBBuilder class should be a static method in Java.

Deploying the Java Code to the Oracle JVM

Oracle JVM is both a JRE and a JDK; therefore, it not only can run your programs but also compile your Java code. You can deploy and compile the Java code to Oracle JVM by using the following syntax:

CREATE OR REPLACE AND COMPILE
JAVA SOURCE NAMED "XSDDBBuilder" AS
{…Java Code…}

After running in SQL*Plus successfully, you will get a Java Created message, which means the Java code is compiled to byte code and stored in Oracle JVM ready for use. If there are any errors, you can use the SHOW ERROR command to show details of the errors.

Creating the PL/SQL Specification

After the Java code is deployed to Oracle JVM, you need to create a PL/SQL call specification. For example, you can create an xsd_build() specification for XSDDBBuilder.build() as follows:

CREATE OR REPLACE FUNCTION xsd_build(xsd IN VARCHAR2) RETURN VARCHAR2
AUTHID CURRENT_USER
AS LANGUAGE JAVA
NAME 'XSDDBBuilder.build(oracle.sql.CHAR) return java.lang.String';

The AS LANGUAGE JAVA syntax indicates that the PL/SQL procedure is created based on a loaded Java class. The AUTHID CURRENT_USER syntax limits the running privileges of the procedure to the current user who is creating the specification. In this step, you need to properly specify the mapping between the PL/SQL and Java arguments. Otherwise, when executing the procedure, the Oracle database will give you a No Method Found error.

To simplify the deployment procedure and avoid typographical errors, you can use Oracle JDeveloper 10g to deploy and create Java stored procedures, which we will discuss in later sections.