5
Developing and Deploying Stored Procedure Web Services

This chapter describes the procedures you use to write and deploy Oracle9iAS Web Services implemented as stateless PL/SQL Stored Procedures or Functions (Stored Procedure Web Services). Stored Procedure Web Services enable you to export, as services running under Oracle9iAS Web Services, PL/SQL procedures and functions that run on an Oracle database server.

This chapter covers the following topics:

• Using Oracle9iAS Web Services with Stored Procedures

• Writing Stored Procedure Web Services

• Preparing Stored Procedure Web Services

• Setting Up Datasources in Oracle9iAS Web Services (OC4J)

• Limitations for Stored Procedures Running as Web Services

Using Oracle9iAS Web Services with Stored Procedures

Oracle9iAS Web Services can be implemented as any of the following:

• Java Classes

• Stateless Session EJBs

• Stateless PL/SQL Stored Procedures or Functions

This chapter shows sample code for writing Web Services implemented with stateless PL/SQL Stored Procedures or Functions. The sample is based on a PL/SQL package representing a company that manages employees.

Oracle9iAS Web Services supplies a Servlet to access Java classes that support PL/SQL Stored Procedure Web Services. The Servlet handles requests generated by a Web Service client, runs the Java method that accesses the stored procedure that implements the Web Service, and returns results back to the Web Service client.

The Oracle database server supports procedures implemented in languages other than PL/SQL, including Java, C/C++. These stored procedures can be exposed as Web Services using PL/SQL interfaces.

See Also: Chapter 3, "Developing and Deploying Java Class Web Services" Chapter 4, "Developing and Deploying EJB Web Services"

Writing Stored Procedure Web Services

Writing PL/SQL Stored Procedure based Web Services involves creating and installing a PL/SQL package on an Oracle database server that is available as a datasource to Oracle9iAS and generating a Java class that includes one or more methods to access the Stored Procedure.

The code for the sample Stored Procedure Web Service is supplied with Oracle9iAS Web Services in the directory $ORACLE_HOME/j2ee/home/demo/web_services/stored_procedure on UNIX or in %ORACLE_HOME%\j2ee\home\demo\web_services\stored_procedure on Windows.

Developing a Stored Procedure Web Service consists of the following steps:

• Creating a PL/SQL Stored Procedure for a Web Service

• Generating a Stored Procedure Web Service Jar File

• Creating and Compiling Stored Procedure Web Service Interfaces

• Writing a WSDL File or Client Stubs for Stored Procedure Web Services (Optional)

Creating a PL/SQL Stored Procedure for a Web Service

Create a Stored Procedure Web Service by writing and installing a PL/SQL Stored Procedure. To write and install a PL/SQL Stored Procedure, you need to use facilities independent of Oracle9iAS Web Services.

For example, to use the sample COMPANY package, first create and load the supplied package using the create.sql script. This script, along with several other required .sql scripts are in the stored_procedure directory. These scripts create several database tables and the sample COMPANY package.

When the Oracle database server is running on the local system, use the following command to create the PL/SQL package:

sqlplus scott/tiger @create

When the Oracle database server is not the local system, use the following command and include a connect identifier to create the PL/SQL package:

sqlplus scott/tiger@db_service_name @create

where db_service_name is the net service name for the Oracle database server.

Stored Procedure Web Services do not support OUT or IN/OUT parameters.

See Also: "Limitations for Stored Procedures Running as Web Services" PL/SQL User's Guide and Reference in the Oracle Database Documentation Library Oracle Net Services Administrator's Guide in the Oracle Documentation Library

Generating a Stored Procedure Web Service Jar File

After a PL/SQL stored procedure or function is available to support Stored Procedure Web Services, you need to generate the Java class to access the PL/SQL package. In this step you run Apache ant using the buildfile supplied in the directory $ORACLE_HOME/j2ee/home/bin/spbuild.xml, and setting Java properties to control the generation process. The Apache ant command calls the Oracle database server JPublisher tool and generates a Java class and methods to access the stored procedure (this step prepares a Jar file for use with Stored Procedure Web Services).

JPublisher generates a Java class that provides a mapping between a PL/SQL package name, including all PL/SQL types that the stored procedures in the package use, and Java classes with methods corresponding to the PL/SQL package and procedures or functions (and mapped Java types for each PL/SQL type).

To generate Stored Procedure Web Services using a PL/SQL package as the source, use the ant command as follows:

%ant -buildfile spbuild.xml Java_properties

Table 5-1 lists the Java properties that control the generation command. You can set the properties shown in Table 5-1 as system properties by using the -D flag at the Java command line.

For example, the following command generates the company.jar file using the COMPANY package as the PL/SQL stored procedure:

cd $ORACLE_HOME/j2ee/demo/web_services/stored_procedure
ant -buildfile spbuild.xml 
-Dschema=scott/tiger 
-Durl="jdbc:oracle:thin:@system1.us.oracle.com:1521:db817"
-Dpackage=Company 
-DserviceJar=company.jar 
-Dprefix=sp.company 
-DOracleHome=/private2/oracleDBClient817/

This generates the Jar file company.jar that contains the Java source and class files to access the PL/SQL procedure (and supports Stored Procedure Web Services).

Table 5-1  Java Properties for Use with Ant and spbuild.xml

Option	Description
-DoracleHome=directory	Where directory is the directory path for the Oracle installation. Setting this property is required.
-Dpackage=pkg_name	Where pkg_name is the name of the PL/SQL package to export. Setting this property is required.
-Dprefix=prefix	Where prefix is the Java package prefix for generated classes. By default, the PL/SQL package is generated into a Java class in the default Java package. Setting this property is optional.
-Dschema=user_name/pword	Where user_name is the database user name and pword is the database password for the specified user name. Setting this property is required.
-DserviceJar=jarfile	Where jarfile is the name of the generated jar file for use with a Stored Procedure Web Service. Setting this property is required.
-Durl=url_path	Where url_path is the database connect string for the Oracle database server with the specified package to export. The user, password and url are the combination of database login username/password and a JDBC-style URL used to connect to the backend database which contains the stored procedures to be exported. For example: -Durl=jdbc:oracle:thin:@system1.us.oracle.com:1521:tv1 Setting this property is required.

See Also: Oracle9i JPublisher User's Guide in the Oracle Database Documentation Library http://jakarta.apache.org/ant/ for information on the Apache build tool

Creating and Compiling Stored Procedure Web Service Interfaces

The Java classes that are placed in the Jar file output from ant contain both support methods for accessing the PL/SQL stored procedure and JPublisher generated support methods that cannot be exposed as Oracle9iAS Web Services without generating errors. To limit the exposed methods to those that you want to expose from the PL/SQL package, you need to supply a public interface. The public interface limits the methods exposed as Stored Procedure Web Services.

To create the public interface expand the generated Jar file and examine the public methods in the generated Java source file (Company.java). Add the method signatures for the methods that you want to expose to the public interface that you create. You do not need to implement any of the interfaces that are added to the public interface, since the public interface just identifies the method names and signatures for Stored Procedure Web Services.

For example, if you are implementing a Stored Procedure Web Service using the COMPANY package and the generated Jar file from the supplied company sample, then create a public interface using the methods in the generated file Company.java as a guide for identifying method signatures. For the sample, the Company.java file is in the Jar file under sp/company/Company.java. The public interface CompanyInterface.java is shown in Example 5-1.

Example 5-1 Using a Public Interface to Expose Stored Procedure Web Services

import java.sql.*;
public interface CompanyInterface
{
public void addemp (
    java.math.BigDecimal id,
    String firstname,
    String lastname,
    String addr,
    double salary)
throws SQLException;

public void removeemp (
    java.math.BigDecimal id)
throws SQLException;

public void changesalary (
    java.math.BigDecimal id,
    double newsalary[])
      throws SQLException;

public double getempinfo (
    java.math.BigDecimal id,
    String firstname[],
    String lastname[])
    throws SQLException;
}

You need to compile the interface file using the Oracle9iAS Web Services standard Java compiler, as shown:

javac CompanyInterface.java

This generates CompanyInterface.class. This class that needs to be added to the J2EE .war file to support the deployment of the new Stored Procedure Web Service.

See Also: "Preparing Stored Procedure Web Services"

Writing a WSDL File or Client Stubs for Stored Procedure Web Services (Optional)

This step is optional when writing a Stored Procedure based Web Service. If you do not perform this step, the Oracle9iAS Web Services runtime generates a WSDL file and the client-side proxies for deployed Web Services. These files allow Oracle9iAS Web Services to supply a Web Service client with the WSDL or the client-side proxies that a client-side developer can use to build an application that uses a Web Service.

When you do not want to use the Oracle9iAS Web Services generated WSDL file or client-side proxies, perform the following steps:

1. Manually create either the WSDL file or the client-side proxy Jar file, or both files for your service.

2. Name the supplied WSDL file or client-side proxy Jar file and place it in the appropriate location. The WSDL file must have a .wsdl extension. The client-side proxy Jar must have an _proxy.jar extension.The extension is placed after the service name.

   For example,

   simpleservice.wsdl
   simpleservice_proxy.jar
   

3. Add the manually created WSDL file or client-side Jar file to the J2EE .war file that contains the service implementation. There are several choices for adding the files to the .war file:
   • Place the files in the correct package directory under WEB-INF/classes. For example, if simpleservice.wsdl describes a service that is part of the demo package, place the file in the following directory:

     WEB-INF/classes/demo/simpleservice.wsdl
     
     

   • Place the Jar file in a Jar file under the WEB-INF/lib component of the .war file that is added to the .ear file and deployed as a Web Service.

     For example, the file simpeservice_proxy.jar could be added to simpleservice.jar and placed in WEB-INF/lib, or added to namex.jar in WEB-INF/lib (where namex is a file name).

     Note: The Jar file containing the proxy stubs, and optionally the proxy source must be a Jar component of a Jar file in WEB-INF/lib, it cannot simply be added to the WEB-INF/lib directory.

     See Also: "Preparing Stored Procedure Web Services" "Getting WSDL Descriptions and Client-Side Proxy Jar for Web Services"

Preparing Stored Procedure Web Services

This section describes the procedures you use to prepare a PL/SQL procedure as a Stored Procedure Web Service. The Jar file generated using ant must be packaged with an appropriate interface class and web.xml file into a .war file.

After the PL/SQL package, the JPublisher generated Jar file and the interface file are available, you need to create a J2EE .war file that includes the configuration file, web.xml, and the generated Jar file. The Stored Procedure .war file is then assembled into an application .ear file to be deployed into Oracle9iAS Containers for J2EE (OC4J).

This section contains the following topics:

• Modifying web.xml To Support Stored Procedure Web Services

• Preparing a WAR File for Stored Procedure Web Services

• Preparing an EAR File for Stored Procedure Web Services

• Setting Up Datasources in Oracle9iAS Web Services (OC4J)

The Oracle9iAS Web Services assembly tool, WebServicesAssembler, assists in assembling Oracle9iAS Web Services. The Web Services assembly tool takes a configuration file which describes the location of the Java classes, Jar files, and interface files and produces a J2EE .ear file that can be deployed under Oracle9iAS Web Services. This section describes how to assemble Oracle9iAS Web Services implemented as PL/SQL Stored Procedures manually, without using the assembly tool, WebServicesAssembler.

See Also: "Running the Web Services Assembly Tool"

Modifying web.xml To Support Stored Procedure Web Services

To use Stored Procedure Web Services you need to add a <servlet> entry and a corresponding <servlet-mapping> entry in the web.xml file for each PL/SQL package that is deployed as a Web Service. The resulting web.xml file is assembled as part of a .war file that is included in the .ear file that defines the Web Service.

To modify web.xml to support Web Services implemented as Stored Procedures, perform the following steps:

• Configure the servlet Tag for Stored Procedure Web Services

• Configure the servlet-mapping Tag for Stored Procedure Web Services

• Configure the resource-ref Tag for Stored Procedure Web Services

Configure the servlet Tag for Stored Procedure Web Services

To add Web Services based on PL/SQL Stored Procedures you need to modify the <servlet> tag in the web.xml file. This tag supports using the Oracle9iAS Web Services Servlet to access the PL/SQL Stored Procedure implementation for the Web Service. Table 5-2 describes the <servlet> tag and the values to include in the tag to add a Web Service based on a PL/SQL Stored Procedure.

Example 5-2 shows the <servlet> tag for the sample Stored Procedure Web Service extracted from the web.xml file (to view this file, expand spexample.ear).

Table 5-2  Servlet Tags Supporting PL/SQL Stored Procedure Deployment

Servlet Tag	Description
<init-param>	The <init-param> tag contains a name value pair within <param-name> and <param-value> tags. class-name: The Stored Procedure Web Services Servlet definition requires at least one <param-name> with the value class-name and a corresponding <param-value>. The <param-value> specifies the fully qualified name of the Java class that accesses the PL/SQL Web Service implementation. You need to supply the class name for this parameter; you can find the class name in the Jar file generated using the ant command. See Also: "Generating a Stored Procedure Web Service Jar File" interface-name: A <param-name> with the value interface-name, and a corresponding <param-value> set to the fully qualified name of the Java interface specifies the methods to include in the stored procedure Web Service. This init parameter tells the Web Service Servlet generation code which methods should be exposed as Web Services. datasource-JNDI-name: The Stored Procedure Web Services Servlet definition requires at least one <param-name> with the value datasource-JNDI-name, and a corresponding <param-value> set to the JNDI name of the backend database. The data-sources.xml OC4J config file describes the database server source.
<servlet-class>	This is always oracle.j2ee.ws.StatelessStoredProcRpcWebService for all stateless stored procedure Web Services.
<servlet-name>	This tag specifies the name for the Servlet that runs the Web Service.

Example 5-2 Sample Stored Procedure <servlet> Entry for a Web Service

<servlet>
  <servlet-name>stateless Stored Procedure web service example</servlet-name>
  <servlet-class>oracle.j2ee.ws.StatelessStoredProcRpcWebService</servlet-class>
  <init-param>
    <param-name>class-name</param-name>
    <param-value>sp.company.Company</param-value>
  </init-param>
  <init-param>
    <param-name>datasource-JNDI-name</param-name>
    <param-value>jdbc/OracleCoreDS</param-value>
  </init-param>
  <init-param>
    <param-name>interface-name</param-name>
    <param-value>CompanyInterface</param-value>
  </init-param>
</servlet>

Configure the servlet-mapping Tag for Stored Procedure Web Services

To add Web Services based on PL/SQL Stored Procedures you need to modify the <servlet-mapping> tag in the web.xml file. This tag specifies the URL for the Servlet that implements a Web Service.

Example 5-3 shows a sample <servlet-mapping> entry corresponding to the servlet entry shown in Example 5-2.

Example 5-3 Sample <servlet-mapping> Entry for Web Services

<servlet-mapping>
  <servlet-name>stateless Stored Procedure web service example</servlet-name>
  <url-pattern>/statelessSP</url-pattern>
</servlet-mapping>

Configure the resource-ref Tag for Stored Procedure Web Services

To add Web Services based on PL/SQL Stored Procedures you need to modify the <resource-ref> tag in the web.xml file. This tag specifies the data source for running the PL/SQL Stored Procedure.

Example 5-4 shows a sample <resource-ref> entry corresponding to the <servlet> tag's datasource-JNDI-name parameter shown in Example 5-2.

Example 5-4 Sample <resource-ref> Entry for Web Services

<resource-ref>
   <res-ref-name>jdbc/OracleCoreDS</res-ref-name>
   <res-type>javax.sql.DataSource</res-type>
   <res-auth>Application</res-auth>
</resource-ref>

Preparing a WAR File for Stored Procedure Web Services

Stored Procedure Web Services use a standard .war file to define J2EE Servlet configuration and deployment information. After modifying the web.xml file in the WEB-INF directory to support the Stored Procedure Web Service, add the implementation classes and any required support classes or Jar files either under WEB-INF/classes, or under WEB-INF/lib (or in a classpath location available to OC4J). Be sure to add the compiled interface classes and the generated Jar file to support the Stored Procedure Web Service.

Note: All the classes the are required for a Stored Procedure Web Service implementation must conform to the standard J2EE class loading norms. Thus, the implementation classes and support classes must either be in the .war or .ear file, or they must be available in the OC4J classpath.

See Also: "Generating a Stored Procedure Web Service Jar File" "Creating and Compiling Stored Procedure Web Service Interfaces"

Preparing an EAR File for Stored Procedure Web Services

To add Web Services based on PL/SQL Stored Procedures you need to include an application.xml file and package the application.xml and .war file containing the interface class and generated Jar file into a J2EE .ear file.

The Oracle9iAS Web Services assembly tool, WebServicesAssembler, assists in assembling Oracle9iAS Web Services.

See Also: "Running the Web Services Assembly Tool"

Setting Up Datasources in Oracle9iAS Web Services (OC4J)

To add Web Services based on PL/SQL Stored Procedures you need to set up data sources in OC4J by configuring data-sources.xml. Configuring the data-sources.xml file points OC4J to a database. The database should contain PL/SQL Stored Procedure packages that implement a Stored Procedure Web Service.

A single database connection is created when OC4J initializes a Web Services Servlet instance. The resulting database connection is destroyed when OC4J removes the Web Services Servlet instance. Each Stored Procedure Web Services Servlet implements a single threaded model. As a result, any Web Services Servlet instance can only service a single client's database connection requests at any given time. OC4J pools the Web Services Servlet instances and assigns instances to Oracle9iAS Web Services clients.

Every invocation of a PL/SQL Web Service is implicitly a separate database transaction. It is not possible to have multiple service method invocations run within a single database transaction. When such semantics are required, the user must write a PL/SQL procedure that internally invokes other procedures and functions, and then expose the new procedure as another method in a Stored Procedure Web Service (but Oracle9iAS Web Services does not provide explicit support or tools to do this).

See Also: Oracle9iAS Containers for J2EE User's Guide in the Oracle9iAS Documentation Library

Deploying Stored Procedure Web Services

After creating the .ear file containing the Stored Procedure Web Service configuration, class, Jar, and support files you can deploy the Web Service as you would any standard J2EE application stored in an .ear file (to run under OC4J).

See Also: Oracle9iAS Containers for J2EE User's Guide in the Oracle9iAS Documentation Library

Limitations for Stored Procedures Running as Web Services

This section covers the following topics:

• Supported Stored Procedure Features for Web Services

• Unsupported Stored Procedure Features for Web Services

Supported Stored Procedure Features for Web Services

Stored Procedure Web Services support the following PL/SQL features:

1. PL/SQL stored procedures, including both procedures and functions

2. IN parameter modes

3. Packaged procedures only (top-level procedures must be wrapped in a package before they can be exported as a Web Service)

4. Overloaded procedures. However, if two different PL/SQL types map to the same Java type during the Java class generation step, there may be errors reported when the PL/SQL package is exported; these errors may be fixed by avoiding the overloading in the PL/SQL parameters, or by writing a new dummy package which does not contain the offending overloaded procedures.

   JPublisher may map multiple PL/SQL types into the same Java type. For example, different PL/SQL number types may all map to Java int. This means that methods that were considered overloaded in PL/SQL are no longer overloaded in Java. If this is an issue, the user should wrap their PL/SQL code in a new PL/SQL package that does not contain these ambiguity problems.

5. Simple PL/SQL types

   The following simple types are supported. NULL values are supported for all of the simple types listed below, except NATURALN and POSITIVEN.

   The JPublisher documentation provides full details on the mappings for these simple types.

   VARCHAR2 (STRING, VARCHAR), LONG, CHAR (CHARACTER), NUMBER (DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT), PLS_INTEGER, BINARY_INTEGER (NATURAL, NATURALN, POSITIVE, POSITIVEN)

6. User-defined Object Types.

Unsupported Stored Procedure Features for Web Services

Stored Procedure Web Services impose the following limitations on PL/SQL functions and procedures:

1. Only procedures and functions within a PL/SQL package are exported as Web Services. Top-level stored procedures must be wrapped inside a package; ADT methods must be wrapped into package-level methods with a default "this" reference.

2. OUT and IN OUT parameter modes are not supported.

3. Due to a restriction in the OCI layer, the JDBC drivers do not support the passing of BOOLEAN parameters to PL/SQL stored procedures. Please refer to the JDBC Developer's Guide and Reference for a workaround.

4. NCHAR and related types are not supported.

5. JPublisher does not support internationalization.

6. JPublisher and Oracle9iAS Web Services does not provide comprehensive support for LOB types.

   If your PL/SQL procedures use LOB types as input/output types, then the generated Java translation may not work in all cases. If you see an error, the offending procedures will have to be rewritten before the PL/SQL package can be exported as Stored Procedure Web Services.

7. Due to a bug in JPublisher, many integer numeric types are translated into java.math.BigDecimal instead of the Java scalar types---the workaround for this bug is to temporarily use java.math.BigDecimal as the argument and return types.

8. JPublisher translates almost all PL/SQL types to Java types. The deployment tools for Stored Procedure Web Services generate "jdbc" style for builtin, number, and lob types, and the "oracle" style for user types (in the "customdatum" compatibility mode). Check the JPublisher documentation for full details of these styles, and for the caveats associated with them.