10 Using Database Web Services

This chapter describes how to use database Web services.

This chapter includes the following topics:

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

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

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.