10 Using Database Web Services

The following sections describe how to use database Web services:

• Overview of Database Web Services

• Type Mapping Between SQL and XML

• Developing Database Web Services Using Oracle JDeveloper

Overview of Database Web Services

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 enables Web services for databases. It works in two directions:

• Database Call-in—Access database resources as a Web service

• Database Call-out—Consuming external Web services from the database itself

Database Call-in

Turning the Oracle database into a Web service provider takes advantage of your investment in Java stored procedures, PL/SQL packages, Advanced Queues, pre-defined SQL queries and DML.

Note:

Creating Web services out of Query, Java, DML, and Advanced Queues is not supported in this release.

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 WebLogic Server connection. This approach is very beneficial in heterogeneous, distributed, and non-connected environments.

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

The following figure illustrates Web service call-in.

Figure 10-1 Web Service Calling in to the Database

Description of "Figure 10-1 Web Service Calling in to the Database"

The following steps describe the process shown in the previous figure:

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 JAX-RPC processing servlet references the SOAP libraries and XML parser to decode the request.

3. The servlet passes the request to the classes that correspond to the exposed database operations. 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 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.

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, or 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.

The following figure illustrates database call-out.

Figure 10-2 Calling Web Services from Within the Database

Description of "Figure 10-2 Calling Web Services from Within the Database"

The following steps describe the process shown in the previous figure:

• SQL and PL/SQL call specs—Invoke a Web service through a user-defined function call 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.

  Oracle Database PL/SQL Packages and Types Reference provides more information on using the UTL_DBWS PL/SQL package.

• Pure Java static proxy class—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).

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

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.

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.

The following table 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.

Table 10-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[]
Primitive PL/SQL indexby table	Array	Array
PL/SQL Boolean	boolean	boolean
PL/SQL indexby table	complexType	complexType
PL/SQL record	complexType	complexType
REF CURSOR (nameBeans)	Array	Array
REF CURSOR nameXML)	any	test_xml
REF CURSOR nameMLRowSet	swaRef	test_xml
SQL object	complexType	complexType
SQL table	complexType	complexType
SYS.XMLTYPE	any	test_xml

Note:

If National Language Support (also known as "NLS" or "Globalization Support") characters are used in a SQL SYS.XMLTYPE value, they may not be properly handled.

A query or a PL/SQL function returning REF CURSOR will be mapped into the three methods listed below, where name is the name of the query or the PL/SQL function.

• nameBeans—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 sub-element corresponds to a column in that row.

• nameXMLRowSet—This method returns a swaRef or text_xml response that contains an OracleWebRowSet instance in XML format.

• nameXML—this method returns an XML any or text_xml response that contains an Oracle XDB row set.

Both OUT and IN OUT PL/SQL parameters are mapped to IN OUT parameters in the WSDL file.

Note that Table 10-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.

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

In database Web services call-outs, XML types are mapped into SQL types. The following table lists the XML-to-SQL type mappings used in call-outs.

Table 10-2 XML-to-SQL Type Mappings for Web Service 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
test_xml	XML Type

Developing Database Web Services Using Oracle JDeveloper

Using Oracle JDeveloper 11g you can generate and deploy a PL/SQL Web service. JDeveloper's Create PL/SQL Web service wizard makes it easy to generate a web service from a PL/SQL package or a Java stored procedure that uses object types. A Java stored procedure is defined by a SQL specification that invokes it, and the PL/SQL Web service wizard treats these in the same way as packages.You can create Web services in a bottom-up fashion; that is, you cannot create a database Web service from a WSDL.

For more information, see "Creating PL/SQL Web Services" in the "Developing with Web Services" section of the Oracle JDeveloper online help.

Note:

Creating Web services out of Query, Java, DML, and Advanced Queues is not supported in this release.