Skip Headers

Oracle® Application Server Web Services Developer's Guide 10g Release 3 (10.1.3) B14434-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# 9 Developing Database Web Services

This chapter describes how you can develop Oracle Application Server Web Services that interact with an Oracle database. There are two ways in which OracleAS Web Services can interact with a database: call-in and call-out. Web service call-in refers to providing a Web service that provides access to a database resource. The Web service runs in an OC4J instance and accesses the database resource through JDBC.

Web services call-out refers to invoking an external Web service from inside the database. The invocation can be performed by PL/SQL, SQL, or Java code running in the database.

This chapter contains these sections.

- Understanding Database Web Services

- Developing Web Services that Expose Database Resources

- Developing a Web Service Client in the Database

- Tool Support for Web Services that Expose Database Resources

## Understanding Database Web Services

Web service technology enables application-to-application interaction over the Web—regardless of platform, language, or data formats. The key ingredients, including XML, SOAP, WSDL, UDDI, WS-Security, and WS-Reliability have been adopted across the entire software industry. Web service technology usually refers to services implemented and deployed in middle-tier application servers. However, in heterogeneous and disconnected environments, there is an increasing need to access stored procedures, data and metadata, through Web service interfaces. Database Web service technology is a database approach to Web services. It works in two directions:

- accessing database resources as a Web service (database call-in)

- consuming external Web services from the database itself (database call-out)

Database Call-In

Turning the Oracle database into a Web service provider leverages your investment in Java stored procedures, PL/SQL packages, Advanced Queues, pre-defined SQL queries and DML. Client applications can query and retrieve data from Oracle databases and invoke stored procedures using standard Web service protocols. There is no dependency on Oracle-specific database connectivity protocols. Applications can employ any cached OC4J connection. This approach is very beneficial in heterogeneous, distributed, and non-connected environments.

Since database Web services are a part of OracleAS Web Services, they can participate in a consistent and uniform development and deployment environment. Messages exchanged between the database and the Web service can take advantage of all of the management features provided by OracleAS Web Services, such as security, reliability, auditing and logging.

Figure 9-1 illustrates Web service call-in. The following steps describe the process.

1. A request for a type of database service arrives at the application server. The service endpoint implicitly specifies the type of service requested.

2. The OC4J JAX-RPC processing servlet references the SOAP libraries and XML parser to decode the request.

3. The servlet passes the request to the WebServicesAssembler-generated classes that correspond to the exposed database operations. WebServicesAssembler generates these classes by silently calling Oracle JPublisher. The generated classes can represent PL/SQL packages, queries, DML, AQ Streams, or Java classes in the database.

4. The database passes the response to the OC4J JAX-RPC processing servlet, which references the SOAP libraries and XML parser to encode it.

5. A SOAP response formed in accordance with the WSDL is returned to the client.

See "Developing Web Services that Expose Database Resources" for more information on exposing PL/SQL packages, SQL queries, DML statements, Oracle AQ Streams, or server-side Java classes database operations as a Web service.

Figure 9-1 Web Service Calling-In to the Database

Description of "Figure 9-1 Web Service Calling-In to the Database"

Database Call Out

You can extend a relational database's storage, indexing, and searching capabilities to include Web Services. By calling a Web service, the database can track, aggregate, refresh, and query dynamic data produced on-demand, such as stock prices, currency exchange rates, and weather information.An example of using the database as a service consumer would be to call an external Web service from a predefined database job to obtain inventory information from multiple suppliers, then update your local inventory database. Another example is that of a Web Crawler: a database job can be scheduled to collate product and price information from a number of sources.

Figure 9-2 illustrates database call out.

- SQL and PL/SQL call specs—Invoke a Web service through a user-defined function call (generated through Oracle JPublisher) either directly within a SQL statement or view or through a variable.

- Dynamic Web service invocation using the `UTL_DBWS` PL/SQL package. A `Call` object can be dynamically created based on a WSDL and subsequently, Web services operations can be invoked. This is described in the Oracle Database PL/SQL Packages and Types Reference.

- Pure Java static proxy class—Use Oracle JPublisher to pre-generate a client proxy class. which uses JAX-RPC. This method simplifies the Web service invocation as the location of the service is already known without needing to look up the service in the UDDI registry. The client proxy class does all of the work to construct the SOAP request, including marshalling and unmarshalling parameters.

- Pure Java using DII (dynamic invocation interface) over JAX-RPC—Dynamic invocation provides the ability to construct the SOAP request and access the service without the client proxy.

Which method to use depends on whether you want to execute from SQL or PL/SQL, from Java classes, or whether the service is known ahead of time (static invocation) or only at runtime (DII). See "Developing a Web Service Client in the Database" for more information about the support OracleAS Web Services offers for PL/SQL and Java call-outs from the database.

Figure 9-2 Calling Web Services From Within the Database

Description of "Figure 9-2 Calling Web Services From Within the Database"

## Type Mapping Between SQL and XML

The following sections describe the type mappings between SQL and XML for call-ins and call-outs when the Web service is known ahead of time (static invocation).

- SQL to XML Type Mappings for Web Service Call-Ins

- XML to SQL Type Mapping for Web Service Call-Outs

When the Web service is known at runtime you can use only the Dynamic Invocation Interface (DII) or the `UTL_DBWS` PL/SQL package. For more information on using the JAX-RPC DII, see the API at the following Web address.

`http://java.sun.com/j2ee/1.4/docs/#api`

For more information on using the `UTL_DBWS` package, see the Oracle Database PL/SQL Packages and Types Reference.

### SQL to XML Type Mappings for Web Service Call-Ins

In a database Web service call-in, a SQL operation, such as a PL/SQL stored procedure or a SQL statement, is mapped into one or more Web service operations. The parameters to the SQL operation are mapped from SQL types into XML types.

Note: The reason there may be more than one operation is because OracleAS Web Services may be providing additional data representation choices for the SQL values in XML, such as different representations of SQL result sets.

Table 9-1 illustrates the SQL to XML mappings for Web service call-ins. The first column lists the SQL types. The second column of the table, XML Type (Literal), shows SQL-to-XML type mappings for the default `literal` value of the `use` attribute. The third column, XML Type (Encoded), shows the mappings for the `encoded` value of the `use` attribute. The `literal` and `encoded` values refer to the rules for encoding the body of a SOAP message. See "Literal and Encoded Uses" for more information on these rules.

Table 9-1 SQL-to-XML Type Mappings for Web Services Call-Ins

SQL Type | XML Type (Literal) | XML Type (Encoded)
--- | --- | ---
INT | int | int
INTEGER | int | int
FLOAT | double | double
NUMBER | decimal | decimal
VARCHAR2 | string | string
DATE | dateTime | dateTime
TIMESTAMP | dateTime | dateTime
BLOB | byte[] | byte[]
CLOB | String | String
LONG | String | String
RAW | byte[] | byte[]
SQL object | complexType | complexType
PL/SQL record | complexType | complexType
Primitive PL/SQL indexby table | Array | Array
SQL table | complexType | complexType
PL/SQL indexby table | complexType | complexType
PL/SQL Boolean | boolean | boolean
REF CURSOR (nameBeans) | Array | Array
REF CURSOR (nameXML) | any | text_xml
REF CURSOR (nameXMLRowSet) | swaRef | text_xml
SYS.XMLTYPE | any | text_xml

A query or a PL/SQL function returning `REF` `CURSOR` will be mapped into three methods, name `Beans`, name `XMLRowSet`, and name `XML`, where name is the name of the query or the PL/SQL function.

- name `Beans`—this method returns an array, where each element is an instance of an XSD complex type that represents one row in the cursor. A complex type subelement corresponds to a column in that row.

- name `XMLRowSet`—this method returns a `swaRef` or `text_xml` response that contains an `OracleWebRowSet` instance in XML format. "Working with MIME Attachments" in the Oracle Application Server Advanced Web Services Developer's Guide provides more information on the `swaRef` MIME format

- name `XML`—this method returns an XML `any` or `text_xml` response that contains an Oracle `XDB` row set.

"Exposing a SQL Query or DML Statement as a Web Service" shows examples of returning a result set which is similar to `REF CURSOR`.

Both `OUT` and `IN` `OUT` PL/SQL parameters are mapped to `IN` `OUT` parameters in the WSDL file. See "Mapping PL/SQL IN and IN OUT Parameters to XML IN OUT Parameters" for an example of how parameters are generated into the WSDL and then accessed from client code.

Note that Table 9-1 provides two different mappings: one for literal and another for encoded `use`. The default mapping is literal. From a database Web service's perspective, there is no special reason why encoded should be used. The mapping for encoded is provided in case you encounter scenarios which call for the encoded `use` setting. All of the descriptions in this chapter assume that you will be using the literal `use` setting unless otherwise specified.

#### Changing the SQL to XML Mapping for Numeric Types

Table 9-1 defines SQL to XML type mappings used for call-ins. The mappings for the numeric types is determined by how Oracle JPublisher maps SQL types to Java types. By default, the WebServicesAssembler tool uses the Oracle JPublisher option `-numbertypes=objectjdbc`. As a result, the XML types corresponding to the SQL numeric types are all declared nillable in the generated WSDL file. To change the Oracle JPublisher mappings, and hence change the XML types, you can use the WebServicesAssembler `jpubProp` argument. For example, if you specify the following argument in the database WebServicesAssembler Ant task, then the XML types generated for SQL numeric types will not be declared nillable.

    jpubProp="numbertypes=jdbc"

On the other hand, if you specify either `oracle` or `bigdecimal` as the target of the `numbertypes` option, then the XML types generated for SQL numeric types will all be `decimal` and nillable.

### XML to SQL Type Mapping for Web Service Call-Outs

In database Web services call-outs, XML types are mapped into SQL types. Table 9-2 lists the XML-to-SQL type mappings used in call-outs.

Table 9-2 XML-to-SQL Type Mappings for Web Services Call-Outs

XML Type | SQL Type
--- | ---
int | NUMBER
float | NUMBER
double | NUMBER
decimal | NUMBER
dateTime | DATE
String | VARCHAR2
byte[] | RAW
complexType | SQL OBJECT
Array | SQL TABLE
text_xml | XMLType

## Developing Web Services that Expose Database Resources

This section describes how to develop Web services implemented as PL/SQL stored procedures, SQL statements, Oracle Streams AQ queues, and server-side Java classes.

- How to Use Life Cycle for Web Service Call-in

- WebServicesAssembler Support for Web Service Call-in

- Exposing PL/SQL Packages as Web Services

- Exposing a SQL Query or DML Statement as a Web Service

- Exposing an Oracle Streams AQ as a Web Service

- Exposing a Server-Side Java Class as a Web Service

### How to Use Life Cycle for Web Service Call-in

Creating a database Web service call-in application is a bottom up process. In many cases, you will want to reuse existing database applications (such as PL/SQL packages or Java applications) or operational scripts (such as SQL query, DML, or AQ). You can also populate the database with the resources to be exposed as a Web service. Web service call-ins typically follow these steps:

1. Determine which database resources to expose and make them available.

   For example, you can provide the resources in any of the following ways.

   - load the PL/SQL package into the database

   - create the schema used by SQL query or DML statement Web service

   - load the Java class into the database, for a database server-side Java Web service

2. Run the WebServicesAssembler tool to assemble the Web service, based on the specified resources.

   Note that you could also use JDeveloper to assemble the Web service.

3. If the Web service assembly generates a PL/SQL wrapper, load it into the database.

   PL/SQL Web service assembly may generate a wrapper if a PL/SQL record or `INDEX BY` table type is included in the PL/SQL package. You must load the wrapper into the database. Server-side Java call-in assembly always generates a PL/SQL wrapper. WebServicesAssembler will load the wrapper automatically if the `sysUser` argument is set. For more information, see sysUser.

   Note that you could also use JDeveloper to load the PL/SQL wrapper into the database.

4. Configure the OC4J data sources to ensure that the Oracle JPublisher-generated Java classes that constitute the Web service implementation can connect to the database and the resource it is exposing.

   Add a data source entry in the J2EE data source file, so that the Web service application can connect to the database.

5. Deploy the Web service application into a running instance of OC4J.

6. (Optional) Check that deployment succeeded. OracleAS Web Services provides a Web Service Home Page for each deployed Web service. See "Using the Web Services Home Page" for information on accessing and using the Web Service Home Page.

7. Use the WebServicesAssembler tool to generate the Web services client proxy and incorporate it into your client application.

8. Invoke the Web service using the client proxy.

### WebServicesAssembler Support for Web Service Call-in

For all Web service call-in types, the WebServicesAssembler tool requires the following arguments.

- appName—the application name

- dataSource—the data source JNDI name; used at runtime

- dbConnection—the database connection URL; used at code generation time

- dbUser—the database user and password; used at code generation time

The `appName` argument specifies the Web service application name. The `dataSource` argument defines the data source's JNDI location for the database being accessed. At runtime, the Web service code accesses the database through that data source.

The WebServicesAssembler tool uses the `dbConnection` and `dbUser` arguments to define the connection to the database during Web service creation. The values of these arguments are used at code generation time, not at runtime. The `dbConnection` argument is used to access the information on the resource that is to be exposed. For example, it accesses information about the PL/SQL package, the schema, or the query.

The database used at Web service creation time and the one used at runtime do not have to be the same database. However, both should include the schema that contains the database resources being exposed.

At runtime, database Web services obtain the database connection from the data source. The database Web service deals with connection loss by reconnecting to the database. When the connection is in an invalid state, the Web service will attempt to reestablish the connection. If the Web service fails to re-connect, it will return a fault. The next time the client invokes the Web service, the Web service will attempt to connect to the database. Therefore, it is possible for the Web service to return a fault at one moment, but succeed later, due to connection failure handling.

The following arguments are optional and can be used in all Web service call-in scenarios.

- context—root context for the web application

- debug—displays detailed diagnostic messages

- ear—name and location of the generated EAR

- jpubProp—specifies Oracle JPublisher options to fine-tune Oracle JPublisher code generation

- output—location for storing generated files

- portName—the name of the port in the generated WSDL

- serviceName—local part of the service name in the generated WSDL

- style—the `style` part of the message format used in the generated WSDL

- uri—URI to use for the Web service in the deployment descriptors

- use—the `use` part of the message format used in the generated WSDL

The common prerequisite for all call-in types is that the database is populated with the resource to be exposed. The WebServicesAssembler employs Oracle JPublisher to generate Java code to access database resources. The `jpubProp` argument, which can appear more than once on the command line or in an Ant task, lets you pass options to Oracle JPublisher. Refer to the Oracle Database JPublisher User's Guide for the list of options and for information on how Oracle JPublisher maps PL/SQL, SQL types, SQL statements, and server-side Java into client-side Java wrappers.

### Exposing PL/SQL Packages as Web Services

Use the `plsqlAssemble` command to assemble Web services from a PL/SQL stored procedure. In the generated Web service, each Web service operation corresponds to a PL/SQL stored procedure.

#### Prerequisites

Before you begin, provide the following files and information.

- The PL/SQL package that you want to expose as a Web service

  See "Sample PL/SQL Package" for the stored procedure that is used in this example.

- A name for the Web service application

- A JNDI location for the JDBC data source

- The JDBC database connection URL, and the username and password

#### How to Assemble a Web Service from a PL/SQL Package

The following steps describe how to assemble a Web service for the PL/SQL package `echo_plsql`.

1. Provide the PL/SQL package and the information described in the Prerequisites section as input to the WebServicesAssembler `plsqlAssemble` command.

       java -jar wsa.jar
           -plsqlAssemble
           -appName Echo
           -sql echo_plsql
           -dataSource jdbc/OracleManagedDS
           -dbConnection jdbc:oracle:thin:@stacd15:1521:lsqlj
           -dbUser scott/tiger
           -style rpc
           -use encoded

   The arguments that can be used with the `plsqlAssemble` command are described in "plsqlAssemble".

   By default, WebServicesAssembler generates services using document-wrapped style. However, JAX-RPC clients that use document-wrapped style do not support `IN` `OUT` parameters directly. Instead, WebServicesAssembler packages `IN` and `IN OUT` parameters separately. Since the PL/SQL package used in this example contains `IN` `OUT` parameters, the `plsqlAssemble` command includes the `-style rpc` argument. For more information on parameters and different document styles, see "Mapping PL/SQL IN and IN OUT Parameters to XML IN OUT Parameters".

   The command generates a Web service application, `EchoPlsql.ear`, and optionally, the following PL/SQL scripts.

   - `Echo_plsql_wrapper.sql`—the PL/SQL wrapper generated to support PL/SQL `record` and `INDEX BY` table.

   - `Echo_plsql_dropper.sql`—the PL/SQL script to tear down the types and packages created by the wrapper script.

2. Install any PL/SQL wrappers created during Web service generation into the database.

   Not all PL/SQL Web services assembly generates PL/SQL wrappers. If it does, you must load them into the appropriate user schema in the database before running the Web service.

   The wrappers can be loaded automatically or manually. To load the wrappers automatically, add the following line to the `plsqlAssemble` command:

   `-jpubProp plsqload` (for the command line), or

   `jpubprop="plsqlload"` (for an Ant task)

   To manually load the wrapper package after Web service assembly, use SQL*PLUS. The following command line provides a sample SQL*PLUS command to load a wrapper package.

       SQL>@Echo_plsql_wrapper.sql

3. Deploy the service into a running instance of OC4J and bind the application.

   The data source referenced by the `-dataSource` argument in Step 1 must be set up in this OC4J instance.

   The following command lines provide sample deployment and bind commands.

       % java –jar <J2EE_HOME>/admin_client.jar deployer:oc4j:localhost:port admin welcome –deploy -file dist/echo.ear –deploymentName echo

       % java –jar <J2EE_HOME>/admin_client.jar deployer:oc4j:localhost:port admin welcome –bindWebApp plsql plsql-web default-web-site /plsql

   In this example, <`J2EE_HOME`> is the directory where J2EE is installed.

   For more information on deploying EAR files, see the Oracle Containers for J2EE Deployment Guide.

4. (Optional) Check that deployment succeeded. OracleAS Web Services provides a Web Service Home Page for each deployed Web service. See "Using the Web Services Home Page" for information on accessing and using the Web Service Home Page.

5. Generate the client-side code.

   - For the J2SE environment, generate stubs (client proxies) for a J2SE Web service client by running the WebServicesAssembler tool with the `genProxy` command. See Chapter 14, "Assembling a J2SE Web Service Client" for more information on generating and assembling client-side code for the J2SE environment.

   - For the J2EE environment, generate a service endpoint interface and a JAX-RPC mapping file for a J2EE Web service client by running the WebServicesAssembler tool with the `genInterface` command. See Chapter 13, "Assembling a J2EE Web Service Client" for more information on generating and assembling client-side code.

   For example, the following command uses the `genProxy` command to generate a J2SE client in the `build/classes/client` directory.

       % java –jar wsa.jar –genProxy
                           –wsdl http://locahost:8888/plsql/echo?WSDL
                           -output build/src/client
                           –mappingFileName ./mapping.xml
                           –packageName oracle.demo.db.plsql.stub
                           -unwrapParameters true

6. Compile and run the client.

   List the appropriate JARs on the classpath before compiling the client. Table A-2, "Classpath Components for a Client Using a Client-Side Proxy" lists all of the JAR files that can possibly be used on the client classpath. As an alternative to listing individual JARs, you can include the client-side JAR, `wsclient_extended.jar` on the client classpath. This JAR file contains all the classes necessary to compile and run a Web service client. The classes are from the individual JAR files listed in Table A-2. See "Setting the Web Service Proxy Client Classpath" for more information on `wsclient_extended.jar` and the client classpath.

   The following command line provides sample compile and run commands.

        % javac –classpath path:
                <ORACLE_HOME>/webservices/lib/wsclient_extended.jar:
                :<ORACLE_HOME>/webservices/lib/jaxrpc-api.jar EchoClient.java

       % java -classpath path:
              <ORACLE_HOME>/webservices/lib/wsclient_extended.jar:
              <ORACLE_HOME>/webservices/lib/jaxrpc-api.jar:
              <J2EE_HOME>/lib/jax-qname-namespace.jar:
              <J2EE_HOME>/lib/activation.jar:
              <J2EE_HOME>/lib/mail.jar:
              <J2EE_HOME>/lib/http_client.jar:
              <ORACLE_HOME>/lib/xmlparserv2.jar EchoClient

   In this example, <`J2EE_HOME`> is the directory where J2EE is installed, <`ORACLE_HOME`> is the directory where OracleAS Web Services is installed.

#### Ant Tasks for Generating a Web Service

This release provides Ant tasks for Web services development. The following example shows how the WebServicesAssembler `plsqlAssemble` command can be rewritten as an Ant task.

    <oracle:plsqlAssemble
        dbUser="scott/tiger"
        sql="echo_plsql"
        dbConnection="jdbc:oracle:thin:@stacd15:1521:1sqlj"
        dataSource="jdbc/OracleManagedDS"
        appName="EchoPlsql"
        style="rpc"
        use="encoded"
    />

#### Sample PL/SQL Package

Example 9-1 illustrates a PL/SQL package in the database that can be exposed as a Web service. The package contains procedures and functions that exercise various SQL and PL/SQL data types.

Example 9-1 Sample PL/SQL Package

    create or replace type address as object(
         street varchar2(30), city varchar2(30), state varchar2(2), zip varchar2(5));
    create or replace type employee as object (eid int, efirstname varchar(30),
         elastname varchar(30), addr address, salary float);
    create table employees (eid int, emp employee);
    create table employee_espp (eid int, status int);
    create table employee_accounts (eid int, accounts sys.xmltype);
    create table employee_biodata (eid int, biodata CLOB);
    create table employee_image (eid int, image BLOB);
    create table employee_hiredate(eid int, hiredate TIMESTAMP);

    create or replace package echo_plsql as
    procedure set_object (emp IN employee);
    function get_object1(id IN int) return employee;
    function get_object2(id IN int) return address;
    function hold_varchar(id IN int, firstname OUT varchar2, lastname OUT varchar2) return float;
    procedure set_boolean(id IN int , status IN boolean);
    function get_boolean(id IN int) return boolean;
    procedure hold_float_inout(id IN int, newsalary IN OUT float);
    procedure clear_object (id IN int);
    procedure set_clob (id int, biodata IN CLOB);
    function get_clob(id IN int) return CLOB;
    procedure set_blob(id int, image IN BLOB);
    function get_blob(id IN int) return BLOB;
    procedure set_xmltype(id IN number, accounts sys.xmltype);
    function get_xmltype(id IN number) return sys.xmltype;
    procedure set_date(id IN int, hiredate IN TIMESTAMP);
    function get_date(id IN int) return TIMESTAMP;
    TYPE rec is RECORD (emp_id int, manager_id int);
    TYPE index_tbl is TABLE OF rec INDEX BY BINARY_INTEGER;
    function echo_rec(mrec rec) return rec;
    function echo_index_tbl(mtbl index_tbl) return index_tbl;
    end echo_plsql;

#### Mapping Between PL/SQL Functions and Web Service Operations

PL/SQL functions or procedures are mapped into Web service operations, often with adjusted names. Typically, the underscore in a PL/SQL name is removed and the letter following the underscore is capitalized. For example, notice the PL/SQL function `echo_index_tbl` in Example 9-1. This function is mapped into the Web service operation `echoIndexTbl`. The WSDL fragment in Example 9-2 shows how the PL/SQL function `echo_index_tbl` is expressed as the `echoIndexTbl` Web service operation.

Example 9-2 WSDL Fragment, Illustrating the Mapping of a PL/SQL Function

    <operation name="echoIndexTbl" parameterOrder="EchobaseIndexTblBase_1">
    <input message="tns:Echo_echoIndexTbl"/>
    <output message="tns:Echo_echoIndexTblResponse"/>
    </operation>

#### Mapping PL/SQL IN and IN OUT Parameters to XML IN OUT Parameters

The PL/SQL parameters `OUT` and `IN` `OUT` in Example 9-1 are represented as XML `IN` `OUT` parameters, as shown by the holder parameters of `holdVarchar`. The entries in the WSDL fragment in Example 9-3 illustrate the `holdVarchar` operation. The second and third parameters appear in both the input and output messages, which indicates that both parameters are IN OUT parameters.

Example 9-3 WSDL Fragment, Illustrating IN OUT Parameters

    <operation name="holdVarchar"
       parameterOrder="Integer_1 String_2 String_3">
      <input message="tns:Echo_holdVarchar"/>
      <output message="tns:Echo_holdVarcharResponse"/>
    </operation>
    <message name="Echo_holdVarchar">
    <part name="Integer_1" type="xsd:int"/>
    <part name="String_2" type="xsd:string"/>
    <part name="String_3" type="xsd:string"/>
    </message>
    <message name="Echo_holdVarcharResponse">
    <part name="result" type="ns1:double"/>
    <part name="String_2" type="xsd:string"/>
    <part name="String_3" type="xsd:string"/>
    </message>

To access the `IN` `OUT` parameters in JAX-RPC client code, you must use JAX-RPC holders. For example, the code in Example 9-4 retrieves the returned values as `firstName.value` and `lastName.value`, where `firstName` and `lastName` are both String holders. The actual values in the holders are accessed by the member `value`, as shown in the `println` statement.

Example 9-4 Accessing IN OUT Parameters in Client Code by Using JAX-RPC Holders

    System.out.println("holdVarchar");
    StringHolder firstName = new StringHolder("Tom");
    StringHolder lastName = new StringHolder("Gordon");
    System.out.println("Holder returned:  empid="
    + ci.holdVarchar(id, firstName, lastName)
    + ", name="
    + firstName.value
    + "."
    + lastName.value);

Note that the `plsqlAssemble` command line specified `-style rpc`. The RPC style supports holders. The default document-wrapped style does not support holders.

If the Web service had been created with the default document-wrapped style, then a different `holdVarchar` signature would have been generated. The `OUT` arguments would be captured as attributes on the return value.

The following WSDL segment shows the `holdVarchar` operation in the document-wrapped style. In the return type, `EchoUser_holdVarchar_Out`, the attributes `lastnameOut` and `firstnameOut` record the `OUT` value of the PL/SQL parameters `firstname` and `lastname`.

Example 9-5 WSDL Fragment, Illustrating IN OUT Parameters Handled in Document-Wrapped Style

    <operation name="holdVarchar" parameterOrder="Integer_1">
                <input message="tns:Echo_holdVarchar"/>
                <output message="tns:Echo_holdVarcharResponse"/>
     </operation>

    <message name="Echo_holdVarchar">
      <part name="Integer_1" type="xsd:int"/>
    </message>
    <message name="Echo_holdVarcharResponse">
      <part name="result" type="tns:Echo_holdVarchar_Out"/>
    </message>

    <complexType name="Echo_holdVarchar_Out">
         <sequence>
             <element name="return" type="double" nillable="true"/>
            <element name="lastnameOut" type="string" nillable="true"/>
             <element name="firstnameOut" type="string" nillable="true"/>
         </sequence>
    </complexType>

#### Mapping SQL XMLType to XML any

The SQL `XMLType` in Example 9-1 is mapped into the XML `any` type. The `getXmltype` operation in the WSDL fragment in Example 9-6 illustrates this mapping.

Example 9-6 WSDL Fragment, Illustrating the Mapping of SQL XMLType into text_xml

    <message name="Echo_getXmltypeResponse">
    <part name="result" type="ns2:any"/>
    </message>
    <operation name="getXmltype" parameterOrder="BigDecimal_1">
    <input message="tns:Echo_getXmltype"/>
    <output message="tns:Echo_getXmltypeResponse"/>
    </operation>

WebServicesAssembler generates a proxy that maps XML `any` to the Java type `org.w3c.org.dom.Element`. Therefore, a Java client accesses a SQL `XMLType` instance as an `Element` instance.

### Exposing a SQL Query or DML Statement as a Web Service

Use the `sqlAssemble` command to generate Web services from a SQL statement. The statement can include SQL queries and DML (Data Manipulation Language) statements.

Unlike PL/SQL Web services generation, SQL statement assembly does not generate PL/SQL wrappers. PL/SQL wrappers are generated only to handle PL/SQL `record` or `INDEX BY` table types. These types cannot be used in a SQL statement.

#### Prerequisites

Before you begin, provide the following files and information.

- The SQL statements or queries. Example 9-7, "Sample SQL Statements" illustrates the SQL statements used in the following example.

  Multiple `sqlstatement` arguments can be specified on the command line or Ant task. For information on the format of the `sqlstatement` argument, see "sqlstatement".

- A name for the Web service application.

- The JNDI location of the JDBC data source. This information is used at runtime.

- The JDBC database connection URL. This information is used at compile time.

- The name and password of the schema which the query or statement is based on. This information is used at compile time.

#### How to Assemble a Web Service from a SQL Statement or Query

The following steps use the `sqlAssemble` command to assemble a Web service for the queries and statements on the SCOTT schema.

1. Provide the SQL statements or query, the name and password for the database that they are based on, and the other information described in the Prerequisites section as input to the WebServicesAssembler `sqlAssemble` command.

   For example, the following command generates the Web service application `query.ear`.

       java -jar wsa.jar
          -sqlAssemble
          -appName query
          -dataSource jdbc/OracleManagedDS
          -sqlstatement "getEmpCount=select ename, sal from emp where
                             sal>:{mysal NUMBER}"
          -sqlstatement "getEmpBySal=select ename, sal from emp where
                             sal>:{mysal NUMBER}"
          -sqlstatement "updateEmp=update emp SET sal=sal+500 where
                             ename=:{myname VARCHAR}"
          -dbConnection jdbc:oracle:thin:@stacd15:1521:lsqlj
          -dbUser scott/tiger

2. Deploy the service into a running instance of OC4J and bind the application.

   The data source referenced in the `-dataSource` argument must have been set up for this OC4J instance.

   The following command lines provide sample deployment and bind commands.

       % java –jar <J2EE_HOME>/admin_client.jar deployer:oc4j:localhost:port admin welcome –deploy -file dist/query.ear –deploymentName query

       % java –jar <J2EE_HOME>/admin_client.jar deployer:oc4j:localhost:port admin welcome –bindWebApp plsql plsql-web default-web-site /query

   In these sample command lines, <`J2EE_HOME`> is the directory where J2EE is installed.

   For more information on deploying EAR files, see the Oracle Containers for J2EE Deployment Guide.

3. (Optional) Check that deployment succeeded. OracleAS Web Services provides a Web Service Home Page for each deployed Web service. See "Using the Web Services Home Page" for information on accessing and using the Web Service Home Page.

4. Generate the client-side code.

   - For the J2SE environment, generate stubs (client proxies) for a J2SE Web service client by running the WebServicesAssembler tool with the `genProxy` command. For more information on generating and assembling client-side code for the J2SE environment, see Chapter 14, "Assembling a J2SE Web Service Client".

   - For the J2EE environment, generate a service endpoint interface and a JAX-RPC mapping file for a J2EE Web service client by running the WebServicesAssembler tool with the `genInterface` command. For more information on generating and assembling client-side code, see Chapter 13, "Assembling a J2EE Web Service Client".

   For example, the following command uses `genProxy` to generate code for a J2SE client.

       % java –jar wsa.jar –genProxy
                           –wsdl http://locahost:8888/query/query?WSDL
                           -output build/src/client
                           –mappingFileName ./mapping.xml
                           –packageName oracle.demo.db.query.stub
                           -unwrapParameters true

   The command generates the client in the `build/src/client` directory.

5. Compile and run the client.

   List the appropriate JARs on the classpath before compiling the client. Table A-2, "Classpath Components for a Client Using a Client-Side Proxy" lists all of the JAR files that can possibly be used on the client classpath. As an alternative to listing individual JARs, you can include the client-side JAR, `wsclient_extended.jar` on the client classpath. This JAR file contains all the classes necessary to compile and run a Web service client. The classes are from the individual JAR files listed in Table A-2. See "Setting the Web Service Proxy Client Classpath" for more information on `wsclient_extended.jar` and the client classpath.

   The following command lines provide sample compile and run commands.

       % javac –classpath path:
            <ORACLE_HOME>/webservices/lib/wsclient_extended.jar:
            :<ORACLE_HOME>/webservices/lib/jaxrpc-api.jar QueryClient.java

       % java -classpath path
            <ORACLE_HOME>/webservices/lib/wsclient_extended.jar:
            <ORACLE_HOME>/webservices/lib/jaxrpc-api.jar:
            <J2EE_HOME>/lib/jax-qname-namespace.jar:
            <J2EE_HOME>/lib/activation.jar:
            <J2EE_HOME>/lib/mail.jar:
            <J2EE_HOME>/lib/http_client.jar:
            <ORACLE_HOME>/webservices/lib/commons-logging.jar:
            <ORACLE_HOME>/lib/xmlparserv2.jar QueryClient

In this example, <`J2EE_HOME`> is the directory where J2EE is installed; <`ORACLE_HOME`> is the directory where the OC4J is installed.

#### Ant Tasks for Assembling a Web Service from SQL Queries or DML Statements

The current release provides Ant tasks for Web services development. The following example shows how the WebServicesAssembler `sqlAssemble` command can be rewritten as an Ant task.

    <oracle:sqlAssemble
            appName="query"
            dataSource="jdbc/OracleManagedDS"
            dbConnection="jdbc:oracle:thin:@dsunrde22:1521:sqlj"
            dbUser="scott/tiger">
            <sqlstatement="getEmpCount=select ename, sal from emp where sal>:{mysal NUMBER}"/>
            <sqlstatement="getEmpBySal=select ename, sal from emp where sal>:{mysal NUMBER}"/>
            <sqlstatement="updateEmp=update emp SET sal=sal+500 where  ename=:{myname VARCHAR}"/>
    />

#### Sample SQL Statements

Example 9-7 illustrates the SQL statements that are exposed as a Web service.

Example 9-7 Sample SQL Statements

    getEmpCount=select ename, sal from emp where sal>:{mysal NUMBER}
    getEmpBySal=select ename, sal from emp where sal>:{mysal NUMBER}
    updateEmp=update emp SET sal=sal+500 where  ename=:{myname VARCHAR}

#### Mapping SQL Queries to Service Operations

A SQL query, when exposed as a Web service, is mapped to three service operations. For example, the `getEmpBySal` query in Example 9-7 generates these service operations.

- `getEmpBySal` `Beans`—returns an array. The array element is an object type with attributes corresponding to the columns in the row of the query result.

- `getEmpBySal` XML `RowSet`—returns an XML document with the query result in the `WebRowSet` format.

- `getEmpBySal` `XML`—returns an XML document with the query result in Oracle `XDB` rowset format.

Providing three operations from one query is a convenience. The return values differ only in format. Note the naming convention is to attach `Beans`, `XMLRowSet`, and `XML` to the original query name.

The WSDL fragment in Example 9-8 describes the return types of the three operations in the WSDL file.

Example 9-8 WSDL Fragment, Illustrating Service Operations for a SQL Query

    <complexType name="getEmpBySalBeansResponse">
    <sequence>
    <element name="result" type="tns:Query_getEmpBySalRowUser" nillable="true"
         minOccurs="0" maxOccurs="unbounded"/>
    </sequence>
    </complexType>
    <complexType name="Query_getEmpBySalRowUser">
    <sequence>
    <element name="ename" type="string" nillable="true"/>
    <element name="sal" type="decimal" nillable="true"/>
    </sequence>
    </complexType>

    <complexType name="getEmpBySalXMLRowSetResponse">
    <sequence>
    <element name="result" type="ns1:swaRef" nillable="true"/>
    </sequence>
    </complexType>

    <complexType name="getEmpBySalXMLResponse">
    <sequence>
    <element name="result" type="xsd:any" nillable="true"/>
    </sequence>
    </complexType>

Two of the methods, `getEmpBySalXMLRowSetResponse` and `getEmpBySalXMLResponse`, have parameters of `swaRef` type. For this type, the assembler generates `javax.xml.soap.AttachmentPart` in the client proxy. Example 9-9 illustrates the client code to access the returned query result.

Example 9-9 Accessing Returned Query Results from a swaRef Type in Client Code

    import oracle.jdbc.pool.OracleDataSource;
    import oracle.jdbc.rowset.OracleWebRowSet;
    import javax.xml.soap.AttachmentPart;
    import org.w3c.org.Element;
    import javax.xml.transform.dom.*;
    import java.io.*;
       ...

    /* Access the query result as Oracle XDB RowSet */
    Element element = eme.getEmpBySalXML(new BigDecimal(500));
    DOMSource doms = new javax.xml.transform.doc.DOMSource(element);
    buf = new jav.io.ByteArrayOutputStream();
    StreamResult streamr = new StreamResult(buf);
    trnas.transform(doms, streamr);
    System.out.println(buf, toString());

    /* Access the query result as Oracle WebRowSet */
    ap = eme.getEmpBySalXMLRowSet(new BigDecimal(500));
    source = (Source) ap.getContent();
    trans = TransformerFactory.newInstance().newTransformer();
    buf = new ByteArrayOutputStream();
    streamr = new StreamResult(buf);
    trans.transform(source, streamr);
    InputStream istream = new ByteArrayInputStream(buf.toString().getBytes());
    OracleWebRowSet rowset = new OracleWebRowSet();
    System.setProperty("http.proxyHost", "www-proxy.us.oracle.com");
    System.setProperty("http.proxyPort", "80");
             System.setProperty("javax.xml.parsers.DocumentBuilderFactory", "oracle.xml.jaxp.JXDocumentBuilderFactory");
    rowset.readXml(new InputStreamReader(istream));
    rowset.writeXml(new PrintWriter(System.out));

The code in Example 9-9 emits the query result in two formats: Oracle XDB row set (`ROWSET`) and Oracle Web row set (`OracleWebRowSet`). Example 9-10 shows the query result as an Oracle XDB row set. Example 9-11 prints the result in `WebRowSet` format. In practice, you can access the variable rowset `OracleWebRowSet` instance in Example 9-9 using `oracle.jdbc.rowset.OracleWebRowSet` APIs. For more information on these data types, see the Oracle Database JDBC Developer's Guide and Reference.

Example 9-10 Query Results as an Oracle XDB Row Set

    <ROWSET>
    <ROW num="1">
    <ENAME>SMITH</ENAME><SAL>800</SAL>
    </ROW>
    <ROW num="2">
    <ENAME>ALLEN</ENAME><SAL>1600</SAL>
    </ROW>
    <ROW num="3">
    <ENAME>WARD</ENAME><SAL>1250</SAL>
    </ROW>
    </ROWSET>

Example 9-11 Query Results as a JDBC Web Row Set

    <?xml version="1.0" encoding="UTF-8"?>
     <webRowSet xmlns="http://java.sun.com/xml/ns/jdbc"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/jdbc http://java.sun.com/xml/ns/jdbc/webrowset.xsd">
       <properties>
    <command></command>
    <concurrency>1007</concurrency>
    <datasource></datasource>
    <escape-processing>true</escape-processing>
    <fetch-direction>1002</fetch-direction>
    <fetch-size>10</fetch-size>
    <isolation-level>2</isolation-level>
    <key-columns>
    </key-columns>
    <map>
    </map>
    <max-field-size>0</max-field-size>
    <max-rows>0</max-rows>
    <query-timeout>0</query-timeout>
    <read-only>false</read-only><rowset-type>1005</rowset-type>
    <show-deleted>false</show-deleted>
    <table-name></table-name>
    <url>jdbc:oracle:thin:@stacd15:1521:lsqlj1</url>
    <sync-provider>
      <sync-provider-name>com.sun.rowset.providers.RIOptimisticProvider</sync-provider-name>
      <sync-provider-vendor>Sun Microsystems Inc.</sync-provider-vendor>
      <sync-provider-version>1.0</sync-provider-version>
      <sync-provider-grade>2</sync-provider-grade>
      <data-source-lock>1</data-source-lock>
    </sync-provider>
       </properties>
       <metadata>
    <column-count>2</column-count>
    <column-definition>
      <column-index>1</column-index>
      <auto-increment>false</auto-increment>
      <case-sensitive>true</case-sensitive>
      <currency>false</currency>
      <nullable>1</nullable>
      <signed>true</signed>
      <searchable>true</searchable>
      <column-display-size>10</column-display-size>
      <column-label>ENAME</column-label>
      <column-name>ENAME</column-name>
      <schema-name></schema-name>
      <column-precision>0</column-precision>
      <column-scale>0</column-scale>
      <table-name></table-name>
      <catalog-name></catalog-name>
      <column-type>12</column-type>
      <column-type-name>VARCHAR2</column-type-name>
                   </column-definition>

    <column-definition>
      <column-index>2</column-index>
      <auto-increment>false</auto-increment>
      <case-sensitive>true</case-sensitive>
      <currency>false</currency>
      <nullable>1</nullable>
      <signed>true</signed>
      <searchable>true</searchable>
      <column-display-size>10</column-display-size>
      <column-label>SAL</column-label>
      <column-name>SAL</column-name>
      <schema-name></schema-name>
      <column-precision>0</column-precision>
      <column-scale>0</column-scale>
      <table-name></table-name>
      <catalog-name></catalog-name>
      <column-type>2</column-type>
      <column-type-name>NUMBER</column-type-name>
    </column-definition>
       </metadata>
       <data>
    <currentRow>
      <columnValue>SMITH</columnValue>
                  <columnValue>800</columnValue>
    </currentRow>
    <currentRow>
      <columnValue>ALLEN</columnValue>
                 <columnValue>1600</columnValue>
         </currentRow>
         <currentRow>
           <columnValue>WARD</columnValue>
                 <columnValue>1250</columnValue>
         </currentRow>
       </data>
     </webRowSet>

#### Mapping DML Operations to Web Service Operations

A DML statement is an `UPDATE`, `DELETE`, or `INSERT` SQL statement. The `sqlAssemble` command can expose DML statements into operations in a Web Service. DML operations are automatically committed when successful and automatically rolled back when they are not.

Example 9-12 illustrates a WSDL fragment that exposes the DML statement in Example 9-7:

`updateEmp=update emp SET sal=sal+500 where ename=:{myname VARCHAR}`

The DML statement is exposed as two operations. In this example, `updateEmp` executes the statement; `updateEmpiS` executes it in batch mode. The batched operation takes an array for each original DML argument. Each element in the array is used for one execution in the batch. The result of a batched operation reflects the total number of rows updated by the batch.

Example 9-12 WSDL Fragment, Illustrating the Response Type of a DML Operation

        <message name="SqlStmts_updateEmp">
          <part name="salary" type="xsd:string"/>
      </message>
      <message name="SqlStmts_updateSchemaResponse">
          <part name="result" type="xsd:int"/>
      </message>
      <message name="SqlStmts_updateEmpiS">
          <part name="salary" type="tns:ArrayOfstring"/>
      </message>
      <message name="SqlStmts_updateEmpiSResponse">
          <part name="result" type="xsd:int"/>
      </message>

### Exposing an Oracle Streams AQ as a Web Service

Oracle Streams Advanced Queuing is an asynchronous messaging system provided by Oracle databases. By exposing an Advanced Queue (AQ) as a Web service, the client can send a message to a receiver inside the database, or eventually, to another client of the same Web service.

The WebServicesAssembler tool can generate a Web service from an AQ existing in a database. An AQ can have a single consumer or multiple consumers. A single consumer is often referred to as a queue. A multiple consumer AQ is often referred to as a topic. Each Oracle Streams AQ belongs to a queue table, which defines the payload type of all its AQs, and whether the AQs support only queues or topics. The generated Java code employs the Oracle Streams AQ JMS APIs.

Example 9-15 and Example 9-16 illustrate the Web service operations that the WebServicesAssembler exposes for a queue and a topic, respectively. The assembler creates the operations based on Oracle Streams AQ and AQ JMS APIs. For information regarding Oracle Streams AQ and AQ JMS APIs, refer to the Oracle Streams Advanced Queuing Java API Reference.

#### Prerequisites

Before you begin, provide the following files and information.

- A database connection URL to the database where the AQ resides. As an alternative to JDBC, WebServicesAssembler gives you the flexibility of using a JMS queue instance to access an Oracle AQ. For more information, see "Accessing an Oracle AQ Queue with JMS".

- The name of the schema where the AQ resides and the user name and password to access it. This is used at compile time.

- The name of the queue or topic that you want to expose as a Web service. You can publish only a single queue or topic to be exposed by a Web service. See "Sample AQ Queue and Topic Declaration" for a sample queue and topic.

- A name for the Web service application.

- The data source JNDI name. This information is used at runtime.

#### How to Assemble a Web Service from an Oracle AQ

The following steps describe how to use WebServicesAssembler to assemble a Web service from an Oracle AQ queue.

1. Provide the files and other information described in the Prerequisites section as input to WebServicesAssembler `-aqAssemble` command.

   For example, the following command creates a Web service application with the `queue.ear` file generated in the current directory. The WebServicesAssembler tool generates Java files to access the queue at runtime. "Sample AQ Queue and Topic Declaration" illustrates the AQ `sample_queue` declaration.

       java -jar $ORACLE_HOME/webservices/lib/wsa.jar
         -aqAssemble
         -appName queue
         -dataSource jdbc/OracleManagedDS
         -portName assembleQueuePort
         -sql sample_queue
         -dbConnection jdbc:oracle:thin:@stacd15:1521:lsqlj
         -dbUser scott/tiger

   You can publish the sample topic in the declaration, `sample_topic`, in the same way as `sample_queue` (but in a different WebServicesAssembler invocation). The only difference would be the values for `sql` and `appName` arguments in the `aqAssemble` command.

2. Deploy the service into a running instance of OC4J and bind the application.

   This step assumes that the AQ has been set up as a data source in the OC4J instance.

   The following command lines provide sample deployment and bind commands.

       % java –jar <J2EE_HOME>/admin_client.jar deployer:oc4j:localhost:port admin welcome –deploy -file dist/queue.ear –deploymentName queue

       % java –jar <J2EE_HOME>/admin_client.jar deployer:oc4j:localhost:port admin welcome –bindWebApp queue queue-web default-web-site /queue

   In this example, <`J2EE_HOME`> is the directory where J2EE is installed.

   For more information on deploying EAR files, see the Oracle Containers for J2EE Deployment Guide.

3. (Optional) Check that deployment succeeded. OracleAS Web Services provides a Web Service Home Page for each deployed Web service. See "Using the Web Services Home Page" for information on accessing and using the Web Service Home Page.

4. Generate the client-side code.

   - For the J2SE environment, generate stubs (client proxies) for a J2SE Web service client by running the WebServicesAssembler tool with the `genProxy` command. For more information on generating and assembling client-side code for the J2SE environment, see Chapter 14, "Assembling a J2SE Web Service Client".

   - For the J2EE environment, generate a service endpoint interface and a JAX-RPC mapping file for a J2EE Web service client by running the WebServicesAssembler tool with the `genInterface` command. For more information on generating and assembling client-side code, see Chapter 13, "Assembling a J2EE Web Service Client".

   For example, the following command uses `genProxy` to generate code for a J2SE client.

       % java –jar wsa.jar –genProxy
                           –wsdl http://locahost:8888/queue/queue?WSDL
                           -output build/src/client
                           –mappingFileName ./mapping.xml
                           –packageName oracle.demo.db.queue.stub
                           -unwrapParameters true

5. Compile and run the client.

   List the appropriate JARs on the classpath before compiling the client. Table A-2, "Classpath Components for a Client Using a Client-Side Proxy" lists all of the JAR files that can possibly be used on the client classpath. As an alternative to listing individual JARs, you can include the client-side JAR, `wsclient_extended.jar` on the client classpath. This JAR file contains all the classes necessary to compile and run a Web service client. The classes are from the individual JAR files listed in Table A-2. See "Setting the Web Service Proxy Client Classpath" for more information on `wsclient_extended.jar` and the client classpath.

   The following command lines provide sample compile and run commands.

       % javac –classpath path
       <ORACLE_HOME>/webservices/lib/wsclient_extended.jar:
       :<ORACLE_HOME>/webservices/lib/jaxrpc-api.jar QueueClient.java

       % java -classpath path
       <ORACLE_HOME>/webservices/lib/wsclient_extended.jar:
       <ORACLE_HOME>/webservices/lib/jaxrpc-api.jar:
       <J2EE_HOME>/lib/jax-qname-namespace.jar:
       <J2EE_HOME>/lib/activation.jar:<J2EE_HOME>/lib/mail.jar:
       <J2EE_HOME>/lib/http_client.jar:
       <ORACLE_HOME>/webservices/lib/commons-logging.jar:
       <ORACLE_HOME>/lib/xmlparserv2.jar QueueClient

In this example, <`J2EE_HOME`> is the directory where J2EE is installed; <`ORACLE_HOME`> is the directory where OracleAS Web Services is installed.

#### Ant Tasks for Generating a Web Service

This release provides Ant tasks for Web services development. The following example shows how the WebServicesAssembler `aqAssemble` command can be rewritten as an Ant task.

    <aqAssemble
       appName="queue"
       dataSource="jdbc/OracleManagedDS"
       sql="sample_queue"
       portName="assembleQueuePort"
       dbConnection="jdbc:oracle:thin:@stacd15:1521:lsqlj"
       dbUser="scott/tiger"
    />

#### Developing Client Code to Access an AQ Queue Exposed as a Web Service

Example 9-13 illustrates a sample client that accesses the published Web service. "Sample Web Service for a Topic Generated by WebServicesAssembler" illustrates the operations exposed by the queues and topics.

Example 9-13 Client Code to Access an AQ Queue Exposed as a Web Service

    SampleQueuePortClient queue = new SampleQueuePortClient();
         QueueMessageUser m;
         queue.send(new QueueMessageUser( "star chopper", "sample 1"));
         queue.send(new QueueMessageUser("easy blocks", "sample 2"));
         queue.send(new QueueMessageUser("back to future", "sample 3"));
         m = queue.receive();
         while (m != null) {
             System.out.println("Message received from SampleQueue: " + m.getSubject() + ": " + m.getText());
             m = queue.receiveNoWait();
              }

This client returns the following responses.

          Message received from SampleQueue: sample 1: star chopper
          Message received from SampleQueue: sample 2: easy blocks
          Message received from SampleQueue: sample 3: back to future

#### Accessing an Oracle AQ Queue with JMS

By default, the Web service interface code uses the data source to get a JDBC connection. It then uses the connection to access the queue inside the database.

As an alternative to JDBC, you can use JMS to access the queue. The WebServicesAssembler tool provides these specialized arguments to the `aqAssemble` command that let you access the exposed Oracle AQ with a JMS queue instance.

- aqConnectionLocation—the JDNI location of the Oracle Streams AQ JMS queue connection connecting to the exposed AQ.

- aqConnectionFactoryLocation—the JNDI location of the Oracle Streams AQ JMS queue connection factory for the exposed AQ.

Instead of specifying the `dataSource` argument in the `aqAssemble` command, you can specify either of the parameters above. The Web service will use a JMS queue at runtime instead of a JDBC-based queue.

#### Sample AQ Queue and Topic Declaration

The PL/SQL script in Example 9-14 defines a queue, `sample_queue`, and a topic, `sample_topic`. The queue payload type is `queue_message`, a SQL object type. The topic payload type is `topic_message`, also a SQL object type.

Example 9-14 Sample Queue and Topic Declaration

    create type scott.queue_message as object (
       Subject                VARCHAR2(30),
       Text                   VARCHAR2(80));
    create type scott.topic_message as object (
       Subject                VARCHAR2(30),
       Text                   VARCHAR2(80));
    BEGIN
      dbms_aqadm.create_queue_table (
               Queue_table            => 'scott.queue_queue_table',
               Queue_payload_type     => 'scott.queue_message');
      dbms_aqadm.create_queue(
        queue_name  => 'scott.sample_queue',
        queue_table => 'scott.queue_queue_table' );
      dbms_aqadm.start_queue(queue_name => 'scott.sample_queue');

      dbms_aqadm.create_queue_table (
        Queue_table            => 'scott.topic_queue_table',
        Multiple_consumers => TRUE,
        Queue_payload_type     => 'scott.topic_message');
      dbms_aqadm.create_queue(
        queue_name  => 'scott.sample_topic',
        queue_table => 'scott.topic_queue_table' );
      dbms_aqadm.start_queue(queue_name => 'scott.sample_topic');
    END;
    /

#### Sample Web Service for a Queue Generated by WebServicesAssembler

For the queue described in "Sample AQ Queue and Topic Declaration", Example 9-15 lists the Web service operations exposed by WebServicesAssembler.

In this example, the `send` operation enqueues a payload to the queue. The payload type is the complex type `tns:QueueMessageUser`, which corresponds to the SQL type `QUEUE_MESSAGE`, as shown by `<send/>` element.The `receive` operation returns a payload from the queue. The `<receiveResponse/>` element shows that the type of the returned payload is `tns:QueueMessage`. The operation blocks until a message becomes available.The `receiveNoWait` operation returns a payload from the queue. If no message is available in the queue, the operation returns null without waiting. The `receive2` operation has two arguments.

- `selector` of type `xsd:string`

- `noWait` of type `xsd:boolean`

The `selector` is a filter condition specified in the AQ convention. It allows the `receive` operation to return only messages that satisfies that condition. For example, the `JMSPriority < 3 AND PRICE < 300` selector exposes only messages with priority 3 or higher, and the attribute `PRICE` is lower than 300. If the parameter `noWait` is `true`, the operation does not block.

Example 9-15 Web Service Operations Exposed for a Queue

    <operation name="receive">
      <input message="tns:SampleQueue_receive" />
      <output message="tns:SampleQueue_receiveResponse" />
      </operation>
    <operation name="receive2">
      <input message="tns:SampleQueue_receive2" />
      <output message="tns:SampleQueue_receive2Response" />
      </operation>
    <operation name="receiveNoWait">
      <input message="tns:SampleQueue_receiveNoWait" />
      <output message="tns:SampleQueue_receiveNoWaitResponse" />
      </operation>
    <operation name="send">
      <input message="tns:SampleQueue_send" />
      <output message="tns:SampleQueue_sendResponse" />
      </operation>

    <complexType name="receive">
      <sequence />
      </complexType>
    <complexType name="receiveResponse">
    <sequence>
      <element name="result" type="tns:QueueMessageUser" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="QueueMessageUser">
    <sequence>
      <element name="text" type="string" nillable="true" />
      <element name="subject" type="string" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="receive2">
    <sequence>
      <element name="String_1" type="string" nillable="true" />
      <element name="boolean_2" type="boolean" />
      </sequence>
      </complexType>
    <complexType name="receive2Response">
    <sequence>
      <element name="result" type="tns:QueueMessageUser" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="receiveNoWait">
      <sequence />
      </complexType>
    <complexType name="receiveNoWaitResponse">
    <sequence>
      <element name="result" type="tns:QueueMessageUser" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="send">
    <sequence>
      <element name="QueueMessageUser_1" type="tns:QueueMessageUser" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="sendResponse">
      <sequence />
      </complexType>

#### Sample Web Service for a Topic Generated by WebServicesAssembler

For the topic described in "Sample AQ Queue and Topic Declaration", Example 9-16 lists the Web service operations exposed by WebServicesAssembler.

In this example, the `publish` operation enters a payload to the topic. The argument is a payload type, for instance, `tns:TopicMessageUser`, as shown in Example 9-16. The message will be received by all topic subscribers.

The `publish2` operation sends the payload to all the subscribers in the recipients list. This operation takes the following arguments.

- `payload` of type `tns:TopicMessageUser`

- `recipients` of `String` array type

The `publish3` operation broadcasts the payload to the topic. This operation takes the following arguments.

- `payload`, the message to be sent

- `deliveryMode`, of type `xsd:int`—can be either `javax.jms.DeliveryMode.PERSISTENT` or `javax.jms.DeliveryMode.NON_PERSISTENT`. However, only `DeliveryMode.PERSISTENT` is supported in this release. The interface `javax.jms.DeliveryMode` is from the JMS APIs

- `priority`, of type `xsd:int`—specifies the priority of the message. Values can be from 0 to 9, with 0 as lowest priority and 9 as highest.

- `timeToLive`, of type `xsd:long`—indicates the life span of the message in milliseconds. Zero means no limit.

The `receive` operation returns a message sent to the receiver. This operation takes one argument: `receiver`.

The `receiveNoWait` operation returns a message sent to the specified recipient without waiting.

The `receive2` operation returns a filtered message sent to the specified recipient. This operation takes the following arguments.

- `receiver`, of type `xsd:string`—recipient of the filtered message.

- `selector`, of type `xsd:string`—a filter condition specified in the AQ convention.

The `receive3` operation returns filtered payload for the specified recipient. This operation takes the following arguments.

- `receiver`, of type `xsd:string`—recipient of the filtered message.

- `selector`, of type `xsd:string`—a filter condition specified in the AQ convention.

- `timeout`, of type `xsd:long`—specifies the timeout for the operation in milliseconds. Zero means no timeout.

The `subscribe` operation subscribes a user to the topic. The underlying connection supporting the Web service must have appropriate privileges to subscribe a consumer. Otherwise, this operation has no effect.

The `unsubscribe` operation unsubscribes a user from the topic. Again, the underlying connection supporting the Web service must have appropriate privileges to unsubscribe a consumer. Otherwise, this operation has no effect.

For more information on the privileges needed for subscribing and unsubscribing consumers, see the Oracle Streams Advanced Queuing Java API Reference.

Example 9-16 Web Service Operations Exposed for a Topic

    <operation name="publish">
      <input message="tns:SampleTopic_publish" />
      <output message="tns:SampleTopic_publishResponse" />
      </operation>
    <operation name="publish2">
      <input message="tns:SampleTopic_publish2" />
      <output message="tns:SampleTopic_publish2Response" />
      </operation>
    <operation name="publish3">
      <input message="tns:SampleTopic_publish3" />
      <output message="tns:SampleTopic_publish3Response" />
      </operation>
    <operation name="receive">
      <input message="tns:SampleTopic_receive" />
      <output message="tns:SampleTopic_receiveResponse" />
      </operation>
    <operation name="receive2">
      <input message="tns:SampleTopic_receive2" />
      <output message="tns:SampleTopic_receive2Response" />
      </operation>
    <operation name="receive3">
      <input message="tns:SampleTopic_receive3" />
      <output message="tns:SampleTopic_receive3Response" />
      </operation>
    <operation name="receiveNoWait">
      <input message="tns:SampleTopic_receiveNoWait" />
      <output message="tns:SampleTopic_receiveNoWaitResponse" />
      </operation>
    <complexType name="publish">
    <sequence>
      <element name="TopicMessageUser_1" type="tns:TopicMessageUser" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="TopicMessageUser">
    <sequence>
      <element name="text" type="string" nillable="true" />
      <element name="subject" type="string" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="publishResponse">
      <sequence />
      </complexType>
    <complexType name="publish2">
    <sequence>
      <element name="TopicMessageUser_1" type="tns:TopicMessageUser" nillable="true" />
      <element name="arrayOfString_2" type="string" nillable="true" minOccurs="0" maxOccurs="unbounded" />
      </sequence>
      </complexType>
    <complexType name="publish2Response">
      <sequence />
      </complexType>
    <complexType name="publish3">
    <sequence>
      <element name="TopicMessageUser_1" type="tns:TopicMessageUser" nillable="true" />
      <element name="int_2" type="int" />
      <element name="int_3" type="int" />
      <element name="long_4" type="long" />
      </sequence>
      </complexType>
    <complexType name="publish3Response">
      <sequence />
      </complexType>
    <complexType name="receive">
    <sequence>
      <element name="String_1" type="string" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="receiveResponse">
    <sequence>
      <element name="result" type="tns:TopicMessageUser" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="receive2">
    <sequence>
      <element name="String_1" type="string" nillable="true" />
      <element name="String_2" type="string" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="receive2Response">
    <sequence>
      <element name="result" type="tns:TopicMessageUser" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="receive3">
    <sequence>
      <element name="String_1" type="string" nillable="true" />
      <element name="String_2" type="string" nillable="true" />
      <element name="long_3" type="long" />
      </sequence>
      </complexType>
    <complexType name="receive3Response">
    <sequence>
      <element name="result" type="tns:TopicMessageUser" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="receiveNoWait">
    <sequence>
      <element name="String_1" type="string" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="receiveNoWaitResponse">
    <sequence>
      <element name="result" type="tns:TopicMessageUser" nillable="true" />
      </sequence>
      </complexType>

### Exposing a Server-Side Java Class as a Web Service

Use the `dbJavaAssemble` command to generate Web services that invoke a Java class inside the Java VM in an Oracle database. You can expose either static or instance methods as Web service operations. An instance method can be invoked through either a default or singleton instance in the session.

The Java class that you want to expose can contain any of the following parameters and return types.

- primitive types (except `char`)

- serializable types

- Java Beans whose attributes are supported types

- JDBC types; that is, `oracle.sql.*` types

- arrays of supported types

#### Prerequisites

Before you begin, supply the following information.

- The fully-qualified class name of the server-side Java class

- A database connection URL; used at code generation time

- The name and password of the schema which contains the Java class; used at code generation time

- A name for the Web service application

- The data source JNDI name; used at runtime

#### How to Assemble a Web Service from a Server-Side Java Class

The following steps describe how to use WebServicesAssembler to assemble a Web service from a server-side Java class.

1. Supply the information described in the Prerequisites section as input to WebServicesAssembler `dbJavaAssemble` command.

   For example, in the following `dbAssemble` command, the server-side class, `oracle.sqlj.checker.JdbcVersion`, is part of the SQLJ server-side translator. This command assembles a Web service application for the class, `javacallin.ear`. It also generates a PL/SQL wrapper and a Java stored procedure wrapper. The purpose of the Java stored procedure wrapper is to convert signature types in the server-side Java class into types can be exposed to PL/SQL stored procedures. Since the `sysUser` argument is declared, the WebServicesAssembler automatically loads the generated wrappers into the database.

       java -jar wsa.jar
         -dbJavaAssemble
         -appName javacallin
         -dbJavaClassName oracle.sqlj.checker.JdbcVersion
         -dbConnection jdbc:oracle:thin:@stacd15:1521:lsqlj
         -dataSource jdbc/OracleManagedDS
         -dbUser scott/tiger
         -sysUser sys/knl_test7

   At run time, the Web service code uses JDBC to invoke the PL/SQL wrapper, which in turns calls the Java stored procedure wrapper, which eventually calls the server-side class. Example 9-18 illustrates some Web service operations generated by this command.

2. Deploy the service into a running instance of OC4J and bind the application.

   This step assumes that the data source specified in Step 1 has been installed in this instance of OC4J.

   The following command lines provide sample deployment and bind commands.

       % java –jar <J2EE_HOME>/admin_client.jar deployer:oc4j:localhost:port admin welcome  –deploy -file dist/javacallin.ear –deploymentName javacallin

       % java –jar <J2EE_HOME>/admin_client.jar deployer:oc4j:localhost:port admin welcome  –bindWebApp javacallin javacallin-web default-web-site /javacallin

   In this example, <`J2EE_HOME`> is the directory where J2EE is installed.

   For more information on deploying EAR files, see the Oracle Containers for J2EE Deployment Guide.

3. (Optional) Check that deployment succeeded. OracleAS Web Services provides a Web Service Home Page for each deployed Web service. See "Using the Web Services Home Page" for information on accessing and using the Web Service Home Page.

4. Generate the client-side code.

   - For the J2SE environment, generate stubs (client proxies) for a J2SE Web service client by running the WebServicesAssembler tool with the `genProxy` command. See Chapter 14, "Assembling a J2SE Web Service Client" for more information on generating and assembling client-side code for the J2SE environment.

   - For the J2EE environment, generate a service endpoint interface and a JAX-RPC mapping file for a J2EE Web service client by running the WebServicesAssembler tool with the `genInterface` command. See Chapter 13, "Assembling a J2EE Web Service Client" for more information on generating and assembling client-side code.

   For example, the following command uses `genProxy` to generate code for a J2SE client.

       % java –jar wsa.jar –genProxy
                           –wsdl http://locahost:8888/javacallin/javacallin?WSDL
                           -output build/src/client
                           –mappingFileName ./mapping.xml
                           –packageName oracle.demo.db.queue.stub
                           -unwrapParameters true

5. Compile and run the client.

   List the appropriate JARs on the classpath before compiling the client. Table A-2, "Classpath Components for a Client Using a Client-Side Proxy" lists all of the JAR files that can possibly be used on the client classpath. As an alternative to listing individual JARs, you can include the client-side JAR, `wsclient_extended.jar` on the client classpath. This JAR file contains all the classes necessary to compile and run a Web service client. The classes are from the individual JAR files listed in Table A-2. See "Setting the Web Service Proxy Client Classpath" for more information on `wsclient_extended.jar` and the client classpath.

   The following command lines provide sample compile and run commands.

       % javac –classpath path
       <ORACLE_HOME>/webservices/lib/wsclient_extended.jar:
       :<ORACLE_HOME>/webservices/lib/jaxrpc-api.jar JavacallinClient.java

       % java -classpath path
       <ORACLE_HOME>/webservices/lib/wsclient_extended.jar:
       <ORACLE_HOME>/webservices/lib/jaxrpc-api.jar:
       <J2EE_HOME>/lib/jax-qname-namespace.jar:
       <J2EE_HOME>/lib/activation.jar:<J2EE_HOME>/lib/mail.jar:
       <J2EE_HOME>/lib/http_client.jar:
       <ORACLE_HOME>/webservices/lib/commons-logging.jar:
       <ORACLE_HOME>/lib/xmlparserv2.jar JavacallinClient

   In this example, <`J2EE_HOME`> is the directory where J2EE is installed; <`ORACLE_HOME`> is the directory where the OC4J is installed.

#### Ant Tasks for Generating a Web Service

The current release provides Ant tasks for Web services development. The following sample shows how the WebServicesAssembler `dbJavaAssemble` command can be rewritten as an Ant task.

    <oracle:dbJavaAssemble
       appName="javacallin"
       dbJavaClassName="oracle.sqlj.checker.JdbcVersion"
       dbConnection="jdbc:oracle:thin:@stacd15:1521:lsqlj"
       dataSource="jdbc/OracleManagedDS"
       dbUser="scott/tiger"
       sysUser="sys/knl_test7"
    />

#### Sample Server-Side Java Class

Example 9-17 illustrates two APIs in `oracle.sqlj.checker.JdbcVersion`, a server-side Java class distributed with Oracle9i and 10g databases. The `dbJavaAssemble` command exposes these APIs as a Web service.

Example 9-17 Sample Server-Side Java Class

    public class oracle.sqlj.checker.JdbcVersion extends java.lang.Object {
        public static int getDriverMajorVersion();
        public static int getDriverMinorVersion();
        ...
    }

#### Sample Web Service Operations Generated from a Server-Side Java Class

The WSDL fragment in Example 9-18 illustrates the Web service operations generated for the `JdbcVersion` APIs `getDriverMajorVersion` and `getDriverMinorVersion` in Example 9-17.

Example 9-18 WSDL Fragment, Illustrating Operations Generated for a Server-Side Java Class

    <complexType name="getDriverMajorVersion">
      <sequence />
      </complexType>
    <complexType name="getDriverMajorVersionResponse">
    <sequence>
      <element name="result" type="decimal" nillable="true" />
      </sequence>
      </complexType>
    <complexType name="getDriverMinorVersion">
      <sequence />
      </complexType>
    <complexType name="getDriverMinorVersionResponse">
    <sequence>
      <element name="result" type="decimal" nillable="true" />
      </sequence>
      </complexType>

    <portType name="JdbcVersion">
    <operation name="getDriverMajorVersion">
      <input message="tns:JdbcVersion_getDriverMajorVersion" />
      <output message="tns:JdbcVersion_getDriverMajorVersionResponse" />
      </operation>
    <operation name="getDriverMinorVersion">
      <input message="tns:JdbcVersion_getDriverMinorVersion" />
      <output message="tns:JdbcVersion_getDriverMinorVersionResponse" />
      </operation>
      </portType>

Note: A server-side Java class can also be invoked through JDBC (rather than through Web services). If this is the case, refer to the Oracle Database JPublisher User's Guide to find out how to generate a proxy class for invoking database server-side Java.

## Developing a Web Service Client in the Database

Oracle JPublisher supports PL/SQL and Java Web service call-outs by creating the appropriate stub code. This enables you to use PL/SQL or Java to invoke a Web service client from inside the database. For a Web service call-out, you supply a WSDL file or location to Oracle JPublisher. Oracle JPublisher generates a PL/SQL wrapper and the necessary database server-side Java classes that implement the PL/SQL wrapper. The generated PL/SQL wrapper contains a PL/SQL procedure or function for each Web service operation.

As an alternative, Oracle JPublisher has the ability to generate Java client proxies only. These Java client proxies can be used for Web service call-outs by Java code in the database.

Figure 9-3 illustrates the stub code that Oracle JPublisher can generate.

Figure 9-3 Creating Web Service Call Out Stubs

Description of "Figure 9-3 Creating Web Service Call Out Stubs"

Note: If you must dynamically construct invocations of external Web services based on a WSDL which is available only at runtime, use the JAX-RPC Dynamic Invocation Interface API for Java or the PL/SQL `UTL_DBWS` package.

The client proxy which Oracle JPublisher generates is based on the simplified client code generated for Java proxies in OracleAS Web Services 10.1.3. Therefore, the Java and PL/SQL client which Oracle JPublisher generates is fully supported by OracleAS Web Services 10.1.3. In addition, Oracle JPublisher can also generate OracleAS Web Services 9.0.4-style Web service clients.

Web service call-out requires these utilities and tools.

- Database Release 9.2 or later

- Database Web Service call-out Utilities

  Load the JAR and SQL files into the database as instructed. These utilities are available from the Oracle Database Web Services Web site.

  `http://www.oracle.com/technology/tech/webservices/database.html`

- Oracle JPublisher 10g

  If you do not have Oracle JPublisher installed, you can obtain it from the JDBC, SQLJ, and Oracle JPublisher download Web site.

  `http://www.oracle.com/technology/tech/java/java_db/index.html`

These are the required Oracle JPublisher options for Web service call-outs.

- `proxywsdl`—the URL of the WSDL file for the Web service to be invoked

- `user`—the database schema (and password) for which the PL/SQL wrapper is generated

These are the optional Oracle JPublisher parameters.

- `httpproxy`—the HTTP proxy host and port for accessing the WSDL file

- `sysuser`—the database user (and password) with SYSDBA privileges

- `proxyopts`—a list of options specific to `proxywsdl`

- `dir`—the directory storing all the generated files

The `sysUser` argument allows Oracle JPublisher to load the generated file into the database. If this argument is not declared, you must manually load the generated file into the database to invoke the Web service from PL/SQL.

See the Oracle Database JPublisher User's Guide for examples and options related to Web service call-out, such as `proxywsdl`, `proxyopts`, and `httpproxy`.

## Tool Support for Web Services that Expose Database Resources

With Oracle JDeveloper, you can create a Web service based on program units in a PL/SQL package that is stored in an Oracle database. You can use the wizards in JDeveloper to perform the following tasks.

- Create the PL/SQL Package in the Database

- Create the PL/SQL Web service

- Deploy the PL/SQL Web service

- Create a stub to use the Web service

For more information on using JDeveloper to create PL/SQL package units and expose them as a Web service, see the JDeveloper on-line help.

## Limitations

See "Developing Web Services From Database Resources".

## Additional Information

For more information on:

- using the Home Page to test Web service deployment, see Chapter 12, "Testing Web Service Deployment".

- building J2SE clients, see Chapter 14, "Assembling a J2SE Web Service Client".

- building J2EE clients, see Chapter 13, "Assembling a J2EE Web Service Client".

- JAX-RPC handlers, see Chapter 15, "Understanding JAX-RPC Handlers".

- using the WebServicesAssembler tool to assemble Web services, see Chapter 17, "Using WebServicesAssembler".

- packaging and deploying Web services, see Chapter 18, "Packaging and Deploying Web Services"

- JAR files that are needed to assemble a client, see Appendix A, "Web Service Client APIs and JARs".

- Web services interoperability, see "Ensuring Interoperable Web Services" in the Oracle Application Server Advanced Web Services Developer's Guide.

- using quality of service features in Web service clients, see "Managing Web Services" in the Oracle Application Server Advanced Web Services Developer's Guide.

- adding security to a Web service, see the Oracle Application Server Web Services Security Guide.

- adding reliability to a Web service, see "Ensuring Web Service Reliability" in the Oracle Application Server Advanced Web Services Developer's Guide.

- adding an auditing and logging configuration to a Web service, see "Auditing and Logging Messages" in the Oracle Application Server Advanced Web Services Developer's Guide.

- processing nonstandard data types, see "Custom Serialization of Java Value Types" in the Oracle Application Server Advanced Web Services Developer's Guide.

- the JAX-RPC mapping file, see "JAX-RPC Mapping File Descriptor" in the Oracle Application Server Advanced Web Services Developer's Guide.

- data types supported by OracleAS Web Services, see "Mapping Java Types to XML and WSDL Types" in the Oracle Application Server Advanced Web Services Developer's Guide.

- JDeveloper tool support for Web service development, see the JDeveloper on-line help.