This chapter describes how to use database Web services.
This chapter includes the following topics:
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
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
The following steps describe the process shown in the previous figure:
A request for a type of database service arrives at the application server. The service endpoint implicitly specifies the type of service requested.
The JAX-RPC processing servlet references the SOAP libraries and XML parser to decode the request.
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.
The database passes the response to the JAX-RPC processing servlet, which references the SOAP libraries and XML parser to encode it.
A SOAP response formed in accordance with the WSDL is returned to the client.
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
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).
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.
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.
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.