5 Developing and Deploying Stored Procedure Web Services

This chapter describes how to write and deploy Oracle Application Server 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 Oracle Application Server Web Services, PL/SQL procedures and functions that run on an Oracle database server.

This chapter covers the following topics:

Using Oracle Application Server 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

5.1 Using Oracle Application Server 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.

Oracle Application Server 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:

5.2 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 Oracle Application Server and generating a Java class that includes one or more methods to access the Stored Procedure.

The sample code is supplied on the Oracle Technology Network Web site,

http://otn.oracle.com/tech/java/oc4j/demos/1012/index.html

After expanding the Web Services demo.zip file, the sample Stored Procedure Web Service is supplied in the directory under webservices/demo/basic/stored_procedure on UNIX or in 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 Oracle Application Server 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 Database Documentation Library

5.3 Preparing Stored Procedure Web Services

This section describes how to use the Oracle Application Server 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 Oracle Application Server Web Services (OC4J)

5.3.1 Creating a Configuration File to Assemble Stored Procedure Web Services

The Oracle Application Server Web Services assembly tool, WebServicesAssembler, assists in assembling Oracle Application Server 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 Oracle Application Server 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

5.3.1.1 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`</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.

5.3.1.2 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 Oracle JPublisher generated classes.

5.3.1.2.1 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 Oracle JPublisher to generate the classes to support the Stored Procedure Web Service. The Oracle 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 Oracle 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

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-ge`neration>` `<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.

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

5.3.1.2.2 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.xm`l 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 backward 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

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

5.3.2 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 Oracle JPublisher generated classes (the WebServicesAssembler collects the Oracle JPublisher generated classes into a single Jar file that it includes in the generated J2EE .ear).

Run the Oracle Application Server 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"

5.3.3 Setting Up Datasources in Oracle Application Server 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 Oracle Application Server 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 Oracle Application Server Web Services does not provide explicit support or tools to do this).

When using an emulated data source with CLOB or BLOB types in the stored procedure, the emulated data source must use the location attribute to specify the JNDI name. The name cannot be specified using the ejb-location.

See Also:

Oracle Application Server Containers for J2EE User's Guide in the Oracle Application Server 10g Documentation Library

5.4 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:

Oracle Application Server Containers for J2EE User's Guide in the Oracle Application Server 10g Documentation Library

5.5 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 PL/SQL Web Services
TIMESTAMP and DATE Granularity Limitation
LOB (CLOB/BLOB) Emulated Data Source Limitation

5.5.1 Supported Stored Procedure Features for Web Services

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

PL/SQL stored procedures, including both procedures and functions.
IN, OUT, IN, INOUT parameter modes. When a stored procedure contains OUT or INOUT parameters, the INOUT and OUT data are passed back to the client as attributes of the returned objects. The declared stored procedure return value, if the stored procedure is a function, will also be included as an attribute of the returned objects INOUT parameter modes.
Packaged procedures only (top-level procedures must be wrapped in a package before they can be exported as a Web Service).
Overloaded procedures. Oracle 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. In this case the Java method names will be renamed to avoid compilation errors for the generated code. However, at runtime, the PL/SQL engine may report PLS-00307 error (too many declarations of <method name> match this call). The error is due to PL/SQL limitation on overloading resolution.
Simple PL/SQL types

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

The Oracle 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
TIMESTAMP is supported, along with variations TIMESTAMP WITH LOCAL TIME ZONE and TIMESTAMP WITH TIME ZONE.
DATE is supported.
User-defined Object Types.
Oracle JPublisher and Oracle Application Server Web Services provide support for the following LOB types: BLOB, CLOB, and BFILE.

If your PL/SQL procedures use LOB types as input/output types, then the WebServices Assembler will not publish those stored procedures that will cause runtime errors. For instance, the WebServices Assembler will not publish a method containing BFILE as an IN parameter.
SYS.XMLTYPE is supported. SYS.XMLTYPE is mapped into the type, org.w3c.dom.DocumentFragment in Web Services.

See Also:

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

5.5.2 Unsupported Stored Procedure Features for Web Services

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

Only procedures and functions within a PL/SQL package are exported as Web Services. Top-level stored procedures must be wrapped inside a package. Methods must be wrapped into package-level methods with a default "this" reference.
NCHAR and related types are not supported.
Oracle JPublisher translates almost all PL/SQL types to Java types. The deployment tools for Stored Procedure Web Services generate "jdbc" style for builtin and number types and "oracle" style for user types and lob types. The lob types are converted to java types that can be serialized/deserialized by Web Services. The user types that conform to java beans are also serialized/deserialized by Web Services. Check the Oracle JPublisher documentation for full details of these styles, and for the caveats associated with them.
Fractional seconds in a TIMESTAMP value are not preserved when using Stored Procedure Web Services.
TIMESTAMP as a field in a user defined ADT is not supported. However, DATE as a field in a user defined ADT is supported.

See Also:

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

5.5.3 Database Server Release Limitation for Boolean Use in Oracle PL/SQL 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 PL/SQL 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

5.5.4 TIMESTAMP and DATE Granularity Limitation

Fractional seconds in a TIMESTAMP value are not preserved when using Stored Procedure Web Services.

5.5.5 LOB (CLOB/BLOB) Emulated Data Source Limitation

When using an emulated data source with CLOB or BLOB types, the emulated data source must use the location attribute to specify the JNDI name. The name cannot be specified using the ejb-location.