5
Developing and Deploying Stored Procedure Web Services

This chapter describes how 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

• Deploying Stored Procedure Web Services

• Limitations for Stored Procedures Running as Web Services

Using Oracle9iAS Web Services with Stored Procedures

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 and C/C++. These stored procedures can be exposed as Web Services using PL/SQL interfaces.

See Also: Chapter 2, "Oracle9iAS Web Services" Chapter 3, "Developing and Deploying Java Class Web Services" Chapter 6, "Developing and Deploying Document Style Web Services"

Writing Stored Procedure Web Services

Writing Stored Procedure 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 in the directory $ORACLE_HOME/webservices/demo/basic/stored_procedure on UNIX or in %ORACLE_HOME%\webservices\demo\basic\stored_procedure on Windows.

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 on the database server 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 sample 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 sample PL/SQL package:

sqlplus scott/tiger@db_service_name @create

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

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

Preparing Stored Procedure Web Services

This section describes how to use the Oracle9iAS Web Services tool WebServicesAssembler to prepare a J2EE .ear file that supports using a PL/SQL procedure or function as a Stored Procedure Web Service.

This section contains the following topics:

• Creating a Configuration File to Assemble Stored Procedure Web Services

• Running WebServicesAssembler With Stored Procedure Web Services

• Setting Up Datasources in Oracle9iAS Web Services (OC4J)

Creating a Configuration File to Assemble Stored Procedure Web Services

The Oracle9iAS Web Services assembly tool, WebServicesAssembler, assists in assembling Oracle9iAS Web Services. This section describes how to create a configuration file to use to assemble a Stored Procedure Web Service. The Web Services assembly tool uses an XML configuration file that describes the Stored Procedure Web Service and produces a J2EE .ear file that can be deployed under Oracle9iAS Web Services.

Create WebServicesAssembler configuration file by adding the following:

• Adding Web Service Top Level Tags

• Adding Stateless Stored Procedure Java Service Tags

• Adding WSDL and Client-Side Proxy Generation Tags

Adding Web Service Top Level Tags

Table 5-1 describes the top level WebServicesAssembler configuration file tags. Add these tags to provide top level information describing the PL/SQL Stored Procedure Web Service.

Example 5-1 shows a complete config.xml file, including the top level tags.

Table 5-1  Top Level WebServicesAssembler Configuration Tags

Tag	Description
<context> context </context>	Specifies the context root of the Web Service. This tag is required.
<datasource-JNDI-name> </datasource-JNDI-name>	Specifies the datasource associated with the Web Service.
<description> description </description>	Provides a simple description of the Web Service. This tag is optional.
<destination-path> dest_path </destination-path>	Specifies the name of the generated J2EE .ear file output. The dest_path specifies the complete path for the output file. This tag is required.
<display-name> disp_name </display-name>	Specifies the Web Service display name. This tag is optional.
<option name="source-path"> path <option>	Includes a specified file in the output .ear file. Use this option to include Java resources. The path specifies the path to the file to include.
<stateless-stored-procedure-java-service> sub-tags </stateless-stored-procedure-java-service>	Use this tag to add stateless stored procedure Web Services. See Table 5-2 and Table 5-4 for a description of valid sub-tags.
<temporary-directory> temp_dir </temporary-directory>	Specifies a directory where the assembler can store temporary files. This tag is optional.

Adding Stateless Stored Procedure Java Service Tags

There are two ways to develop Stored Procedure Web Services using the WebServicesAssembler:

• Adding Stateless Stored Procedure Java Service Using Jar Generation

• Adding Stateless Stored Procedure Java Services Using a Pre-generated Jar

  Note: Most Stored Procedure Web Service developers use the Jar generation technique for assembling the Web Service J2EE .ear file. Only use the pre-generated Jar technique for creating a J2EE .ear when you have a pre-generated Jar file containing JPublisher generated classes.

Adding Stateless Stored Procedure Java Service Using Jar Generation

Using a configuration file that includes the <jar-generation> tag specifies Oracle Database Server connection information that allows the WebServicesAssembler to run JPublisher to generate the classes to support the Stored Procedure Web Service. The JPublisher generated classes support accessing the PL/SQL procedure or function and also includes classes for mapping Java types to PL/SQL types. The WebServicesAssembler packages the generated classes into a Jar file that is assembled with the Stored Procedure Web Service.

Table 5-2 describes the <stateless-stored-procedure-java-service> WebServicesAssembler configuration file tags used when creating a configuration file that uses Jar generation to create a Stored Procedure Web Service. The <stateless-stored-procedure-java-service> tag is included within a <web-service> tag in the configuration file. Add this tag to provide information required for generating the Stored Procedure Web Service J2EE .ear file.

Table 5-3 describes the sub-tags for <jar-generation> within the <stateless-stored-procedure-java-service> tag. The <jar-generation> tags provide information to the WebServicesAssembler so that it can run JPublisher to generate the Java classes for the Stored Procedure Web Service. The WebServicesAssembler then uses these classes to generate the Jar file that provides Java mappings for the stored procedure or function.

Example 5-1 shows a complete config.xml file, including the Stored Procedure Web Service tags shown in Table 5-2 and Table 5-3.

Table 5-2  Stateless Stored Procedure Sub-Tags (Using Jar Generation)

Tag	Description
<database-JNDI-name> source_JNDI_name </database-JNDI-name>	This tag specifies the JNDI name of the backend database. The data-sources.xml OC4J configuration file describes the database server source associated with the specified source_JNDI_name.
<jar-generation> sub-tags </jar-generation>	Table 5-3 describes the supported sub-tags for <jar-generation>. Example: <jar-generation> <schema>scott/tiger</schema> <db-url>jdbc:oracle:thin:@system1:1521:orcl</db-url> <prefix>sp.company</prefix> <db-pkg-name>Company</db-pkg-name> </jar-generation>
<uri> URI </uri>	This tag specifies servlet mapping pattern for the Servlet that implements the Web Service. The path specified as the URI is appended to the <context> to specify the Web Service location.

Table 5-3  Stateless Stored Procedure <jar-generation> Sub-Tags

Tag	Description
<db-pkg-name> pkg_name </db-pkg-name>	Where pkg_name is the name of the PL/SQL package to export. This is required when <jar-generation> is included.
<db-url> url_path </db-url>	Where url_path is the database connect string for the Oracle database server with the specified package to export. The <schema> and <db-url> are combined to connect to the database which contains the stored procedures to be exported. This is required when <jar-generation> is included. Example: <db-url>jdbc:oracle:thin:@system1.us.oracle.com:1521:tv1</db-url>
<method-name> method </method-name>	Where method is the name of the PL/SQL method to export. This tag is optional. Including multiple <method> tags is valid. In this case the specified methods are exported. Without this tag, all methods within the package are exported. If the specified method is overloaded, then all variations of the method are exported.
<prefix> prefix </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. This tag is optional. Example: <prefix>sp.company</prefix>
<schema> user_name/password </schema>	This tag includes the Database Server user_name/password: where: user_name is the database user name. password is the database password for the specified user name. This tag is required when <jar-generation> is included. Example: <schema>scott/tiger</schema>

Example 5-1 Sample WebServicesAssembler Configuration File For Stored Procedure Using <jar-generation> Tag

<web-service>
    <display-name>Web Services Example</display-name>
    <description>Java Web Service Example</description>
    <!-- Specifies the resulting web service archive will be stored in ./spexample.ear -->
    <destination-path>./spexample.ear</destination-path>
    <!-- Specifies the temporary directory that web service assembly tool can create temporary files. -->
    <temporary-directory>/tmp</temporary-directory>
    <!-- Specifies the web service will be accessed in the servlet context named "/webservices". -->
    <context>/webservices</context>
    <!-- Specifies the web service will be stateless -->

    <stateless-stored-procedure-java-service>
       <jar-generation>
         <schema>scott/tiger</schema>
         <db-url>jdbc:oracle:thin:@system1:1521:orcl</db-url>
         <prefix>sp.company</prefix>
         <db-pkg-name>Company</db-pkg-name>
       </jar-generation>
      <!-- Specifies the web service will be accessed in the uri named
             "statelessSP" within the servlet context. -->
       <uri>/statelessSP</uri>
       <database-JNDI-name>/jdbc/OracleDataSource</database-JNDI-name>
    </stateless-stored-procedure-java-service>
  <wsdl-gen>
    <wsdl-dir>wsdl</wsdl-dir>
    <!--force 'true'  will write over existing wsdl -->
    <option name="force">true</option>
    <!-- change this to point to your soap servers http listener -->
    <option name="httpServerURL">http://localhost:8888</option>
  </wsdl-gen>
  <proxy-gen>
    <proxy-dir>proxy</proxy-dir>
    <!-- include-source 'true'  will create an additional jar with only the proxy source-->
    <option name="include-source">true</option>
  </proxy-gen>
</web-service>

Adding Stateless Stored Procedure Java Services Using a Pre-generated Jar

Using a configuration file that specifies the stored procedure <class-name> and <interface-name> assembly options when a pre-generated Jar file that includes the required classes to support the Web Service is available. The <class-name> and <interface-name> tags specified in a configuration file support using a previously generated Jar file that contains the Java classes that provide a mapping between the PL/SQL procedure or function and the Web Service.

Table 5-4 describes the <stateless-stored-procedure-java-service> WebServicesAssembler configuration file tags used when creating a configuration file that uses a pre-generated Jar file to create a Stored Procedure Web Service. The <stateless-stored-procedure-java-service> tag is included within a <web-service> tag in the configuration file. Add this tag to provide information required for generating the Stored Procedure Web Service J2EE .ear file.

The <class> and <interface> tags that are added to the <stateless-stored-procedure-java-service> only when using a pre-generated Jar file.

Table 5-4  Stateless Stored Procedure Sub-Tags (Using Pre-generated Jar File)

Tag	Description
<class-name> class </class-name>	The Stored Procedure Web Services Servlet definition requires a <param-name> with the value class-name and a corresponding <param-value> set to the fully qualified name of the Java class that accesses the PL/SQL Web Service implementation. You need to use the configuration file <class-name> tag to supply the class name for this parameter; you can find the class name in the Jar file you provide that is specified in the top level <option name="source-path"> tag.
<database-JNDI-name> source_JNDI_name </database-JNDI-name>	This tag specifies the JNDI name of the backend database. The data-sources.xml OC4J configuration file describes the database server source associated with the specified source_JNDI_name.
<interface-name> interface </interface-name>	A Stored Procedure Web Services Servlet definition requires a <param-name> with the value interface-name, and a corresponding <param-value> set to the fully qualified name of the Java interface that specifies the methods to include in the stored procedure Web Service. The <interface-name> tag provides the name of the interface that tells the Web Service Servlet generation code which methods should be exposed as Web Services. You can find the interface name in the Jar file you provide that is specified in the top level <option name="source-path"> tag.
<java-resource> resource </java-resource>	This is a backwards compatibility tag. See Also: the top level <option name="source-path"> tag in Table 5-1. This tag is optional. The Stored Procedure pre-generated Jar file should be specified using the <java-resource> tag. The class specified with the <class-name> tag and the interface specified with the <interface-name> tag must exist in the resource specified in the <java-resource> tag(s).
<uri> URI </uri>	This tag specifies servlet mapping pattern for the Servlet that implements the Web Service. The path specified as the URI is appended to the <context> to specify the Web Service location.

See Also: "Adding Stateless Stored Procedure Java Service Using Jar Generation" Oracle9i JPublisher User's Guide in the Oracle Database Documentation Library

Adding WSDL and Client-Side Proxy Generation Tags

The WebServicesAssembler configuration file supports the <wsdl-gen> and <proxy-gen> tags to allow a Web Service developer to generate Web Service description WSDL files and client-side proxy files. You can add these tags to control whether the WSDL file and the client-side proxy are generated. You can also specify that the WSDL file be assembled with the Stored Procedure Style Web Service J2EE .ear. A client-side developer can then use the WSDL file that is obtained from the deployed Web Service to build an application that uses the Web Service.

See Also: "Generating WSDL Files and Client Side Proxies"

Running WebServicesAssembler With Stored Procedure Web Services

After you create the WebServicesAssembler configuration file, you can generate a J2EE .ear file for the Stored Procedure Web Service. The J2EE .ear file includes Stored Procedure Web Service servlet configuration information, including the file web.xml, and JPublisher generated classes (the WebServicesAssembler collects the JPublisher generated classes into a single Jar file that it includes in the generated J2EE .ear).

Run the Oracle9iAS Web Services assembly tool, WebServicesAssembler as follows:

java -jar WebServicesAssembler.jar -config my_pl_service_config

Where: my_pl_service_config is the configuration file that contains the <stateless-stored-procedure-java-service> tag.

See Also: "Creating a Configuration File to Assemble Stored Procedure Web Services" "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 J2EE .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 a J2EE .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

• Database Server Release Limitation for Boolean Use in Oracle PLSQL 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), BOOLEAN

6. User-defined Object Types.

   See Also: Oracle9i JPublisher User's Guide in the Oracle Database Documentation Library

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. NCHAR and related types are not supported.

4. 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.

5. 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.

6. 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.

See Also: Oracle9i JPublisher User's Guide in the Oracle Database Documentation Library

Database Server Release Limitation for Boolean Use in Oracle PLSQL Web Services

Using a Oracle Database Server of Release 9.2.0.1 or earlier, or with a Database Server that is not Java-enabled, then you must install the SYS.SQLJUTIL package into the SYS schema to support PL/SQL BOOLEAN arguments.

The PLSQL script that defines this package is located at the following location on Unix:

${ORACLE_HOME}/sqlj/lib/sqljutil.sql

On Windows systems, this script is located at the following location:

%ORACLE_HOME%\sqlj\lib\sqljutil.sql