Now that you have learned how to build and publish PL/SQL procedures as web services, we will discuss how to consume an external web service within the Oracle database. There are two ways to do this in Oracle Database 10g:
Generate Java web service clients using Oracle JDeveloper 10g and create Java stored procedures. In this approach, you need to load the Java web service clients and the Oracle SOAP support libraries into the Oracle JVM.
Send out SOAP messages to the web service providers using the UTL_HTTP or UTL_SMTP PL/SQL packages.
Because there are many examples on Oracle Technology Network that illustrate the first approach, we discuss only the second approach, which does not require Oracle JVM. In this example, we will use UTL_HTTP to consume the bookstore web service that you just built.
By applying the XML processing techniques discussed in Chapter 10 and Chapter 25, you have several SOAP message construction options in Oracle Database 10g. To simplify the example, you can use the PL/SQL string operation to construct a SOAP message as follows (all the customized fields are bold):
<?xml version = '1.0' encoding = 'UTF-8'?> <SOAP-ENV:Envelope xmslns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <SOAP-ENV:Body> <ns1:query xmlns:ns1="BookSaleService" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> <pPartnerId xsi:type="xsd:decimal">1</pPartnerId> </ns1:query> </SOAP-ENV:Body> </SOAP-ENV:Envelope>
The message is assigned to a PL/SQL variable to be used in your PL/SQL procedure as follows:
soap_request := '<?xml version = "1.0" encoding = "UTF-8"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<ns1:query xmlns:ns1="BookSaleService"
SOAP-ENV:encodingStyle=
"http://schemas.xmlsoap.org/soap/encoding/">
<pPartnerId xsi:type="xsd:decimal">'|| p_partner_id||'
</pPartnerId>
</ns1:query>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
In this example, you can update the partner ID in the SOAP messages by using the p_partner_id parameter.
After you create the SOAP message, you need to send it to the web service provider by using the UTL_HTTP package as follows:
CREATE OR REPLACE FUNCTION query_request(p_partner_id IN number)
RETURN XMLType
as
soap_request VARCHAR2(32767);
soap_respond VARCHAR2(32767);
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
resp XMLType;
i NUMBER;
len NUMBER;
BEGIN
soap_request := '<?xml version = "1.0" encoding = "UTF-8"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<ns1:query xmlns:ns1="BookSaleService"
SOAP-ENV:encodingStyle=
"http://schemas.xmlsoap.org/soap/encoding/">
<pPartnerId xsi:type="xsd:decimal">'|| p_partner_id||'
</pPartnerId>
</ns1:query>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
-- Invoke the SOAP message using UTL_HTTP package
http_req := utl_http.begin_request(
'http://127.0.0.1:8988/Chapter27/BookSaleService',
'POST','HTTP/1.0');
UTL_HTTP.set_header(http_req, 'Content-Type', 'text/xml');
UTL_HTTP.set_header(http_req, 'Content-Length',
length(soap_request));
UTL_HTTP.set_header(http_req, 'SOAPAction', 'query');
UTL_HTTP.write_text(http_req, soap_request);
http_resp := UTL_HTTP.get_response(http_req);
UTL_HTTP.read_text(http_resp, soap_respond);
UTL_HTTP.end_response(http_resp);
resp := XMLType.createxml(soap_respond);
resp := resp.extract('/soap:Envelope/soap:Body/child::node()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');
-- Print out the result
i := 1;
len := LENGTH(soap_respond);
WHILE (i <= len) LOOP
DBMS_OUTPUT.PUT_LINE(substr(soap_respond, i, 60));
i := i + 60;
END LOOP;
RETURN resp;
END;
/
show errors;
The UTL_HTTP package is provided within the Oracle database, allowing you to send out the constructed SOAP messages via HTTP. You need to set the HTTP URL for the web service provider, the data transmission method, and the HTTP version. In this example, the target URL for the web service is http://127.0.0.1:8988/Chapter26/BookSaleService, the data transmission method is POST, and the HTTP version is HTTP 1.0:
http_req := UTL_HTTP.begin_request( 'http://127.0.0.1:8988/Chapter26/BookSaleService', 'POST','HTTP/1.0');
Because SOAP messages are XML messages, the HTTP Content-Type is set to be text/xml:
UTL_HTTP.set_header(http_req, 'Content-Type', 'text/xml');
Using the PL/SQL LENGTH() function, you can get the length of the SOAP message:
UTL_HTTP.set_header(http_req, 'Content-Length', length(soap_request));
The SOAPAction HTTP request parameter is needed in the HTTP header. In this example, it is the query() function we want to call. Therefore, the SOAPAction HTTP request parameter is set to be query:
UTL_HTTP.set_header(http_req, 'SOAPAction', 'query');
After the HTTP header is set up, the SOAP message is sent out by calling the UTL_HTTP .WRITE_TEXT() function and the response is received from the web service by using UTL_HTTP.GET_RESPONSE() and UTL_HTTP.READ_TEXT():
UTL_HTTP.write_text(http_req, soap_request); http_resp := utl_http.get_response(http_req); UTL_HTTP.read_text(http_resp, soap_respond); UTL_HTTP.end_response(http_resp);
After you run the PL/SQL procedure, you can create an XMLType from the SOAP message received in the text:
resp := xmltype.createxml(soap_respond);
resp := resp.extract('/soap:Envelope/soap:Body/child::node()',
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');
This allows for further XML processing on the data. In this example, the XMLType.extract() function is used to extract the response content. To test the procedure, you can run the following command:
SQL> DECLARE 2 res XMLType; 3 BEGIN 4 res := query_request(1); 5 :out := res.getClobVal(); 6 END; 7 /
The following is the result:
<ns1:queryResponse xmlns:ns1="BookSaleService" xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"> <return xmlns:ns2="http://xmlns.oracle.com/xml-soap" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns2:DocumentFragment"> <ROWSET> <ROW partner_id="1"> <ISBN>0072225211</ISBN> <AUTHOR>Kevin Loney, George Koch, Tusc</AUTHOR> <TITLE>Oracle9i: The Complete Reference</TITLE> <PRICE>74.99</PRICE> <SALE_PRICE>44.994</SALE_PRICE> </ROW> <ROW partner_id="1"> <ISBN>0072229527</ISBN> <AUTHOR>Mark Scardina, Ben Chang, Jinyu Wang</AUTHOR> <TITLE>Oracle Database 10g XML and SQL</TITLE> <PRICE>49.99</PRICE> <SALE_PRICE>29.994</SALE_PRICE> </ROW> </ROWSET> </return> </ns1:queryResponse>
Because of the native XML support in Oracle Database 10g, we can integrate the data from web services into the client database environment. For example, in the client database, you can create a MY_BOOK view as follows:
CREATE OR REPLACE VIEW MY_BOOK AS SELECT EXTRACT(value(a),'/ROW/ISBN/text()').getNumberVal() AS ISBN, EXTRACT(value(a),'/ROW/AUTHOR/text()').getStringVal() AS AUTHOR, EXTRACT(value(a),'/ROW/PRICE/text()').getNumberVal() AS PRICE, EXTRACT(value(a),'/ROW/TITLE/text()').getStringVal() AS TITLE, EXTRACT(value(a),'/ROW/SALE_PRICE/text()').getNumberVal() AS SALE_PRICE FROM TABLE(XMLSEQUENCE( query_request(1).extract('ns1:queryResponse/return/ROWSET/ROW', 'xmlns:ns1="BookSaleService"'))) a;
In this example, the SOAP request is made by calling the query_request() procedure. The returned XMLType is then extracted to create an XMLSEQUENCE, which is used to build a temporary table. Looking at the definition of the view, you can see that it can be used just as any other views in the database:
SQL> desc my_book; Name Null? Type ----------------------------------- -------- ----------------- ISBN NUMBER AUTHOR VARCHAR2(4000) PRICE NUMBER TITLE VARCHAR2(4000) SALE_PRICE NUMBER
The data from the web service is seamlessly integrated with other data that is locally stored in the Oracle database.