The APEX_WEB_SERVICE API enables you to integrate other systems with Application Express by allowing you to interact with Web services anywhere you can use PL/SQL in your application. The API contains procedures and functions to call both SOAP and RESTful style Web services. It contains functions to parse the responses from Web services and to encode/decode into SOAP friendly base64 encoding.This API also contains package globals for managing cookies and HTTP headers when calling Web services whether from the API or by using standard processes of type Web service. Cookies and HTTP headers can be set before invoking a call to a Web service by populating the globals and the cookies and HTTP headers returned from the Web service response can be read from other globals.
Use the APEX_WEB_SERVICE API to invoke a Web service and examine the response anywhere you can use PL/SQL in Application Express.
The following are examples of when you might use the APEX_WEB_SERVICE API:
When you want to invoke a Web service by using an On Demand Process using AJAX.
When you want to invoke a Web service as part of an Authentication Scheme.
When you need to pass a large binary parameter to a Web service that is base64 encoded.
When you want to invoke a Web service as part of a validation.
There is a procedure and a function to invoke a SOAP style Web service. The procedure stores the response in the collection specified by the parameter p_collection_name. The function returns the results as an XMLTYPE. To retrieve a specific value from the response, you use either the PARSE_RESPONSE function if the result is stored in a collection or the PARSE_XML function if the response is returned as an XMLTYPE. To pass a binary parameter to the Web service as base64 encoded character data, use the function BLOB2CLOBBASE64. Conversely, to transform a response that contains a binary parameter that is base64 encoded use the function CLOBBASE642BLOB.The following is an example of using the BLOB2CLOBBASE64 function to encode a parameter, MAKE_REQUEST procedure to call a Web service, and the PARSE_RESPONSE function to extract a specific value from the response.
declare
l_filename varchar2(255);
l_BLOB BLOB;
l_CLOB CLOB;
l_envelope CLOB;
l_response_msg varchar2(32767);
BEGIN
IF :P1_FILE IS NOT NULL THEN
SELECT filename, BLOB_CONTENT
INTO l_filename, l_BLOB
FROM APEX_APPLICATION_FILES
WHERE name = :P1_FILE;
l_CLOB := apex_web_service.blob2clobbase64(l_BLOB);
l_envelope := q'!<?xml version='1.0' encoding='UTF-8'?>!';
l_envelope := l_envelope '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:chec="http://www.stellent.com/CheckIn/">
<soapenv:Header/>
<soapenv:Body>
<chec:CheckInUniversal>
<chec:dDocName>'||l_filename||'</chec:dDocName>
<chec:dDocTitle>'||l_filename||'</chec:dDocTitle>
<chec:dDocType>Document</chec:dDocType>
<chec:dDocAuthor>GM</chec:dDocAuthor>
<chec:dSecurityGroup>Public</chec:dSecurityGroup>
<chec:dDocAccount></chec:dDocAccount>
<chec:CustomDocMetaData>
<chec:property>
<chec:name></chec:name>
<chec:value></chec:value>
</chec:property>
</chec:CustomDocMetaData>
<chec:primaryFile>
<chec:fileName>'||l_filename'||</chec:fileName>
<chec:fileContent>'||l_CLOB||'</chec:fileContent>
</chec:primaryFile>
<chec:alternateFile>
<chec:fileName></chec:fileName>
<chec:fileContent></chec:fileContent>
</chec:alternateFile>
<chec:extraProps>
<chec:property>
<chec:name></chec:name>
<chec:value></chec:value>
</chec:property>
</chec:extraProps>
</chec:CheckInUniversal>
</soapenv:Body>
</soapenv:Envelope>';
apex_web_service.make_request(
p_url => 'http://127.0.0.1/idc/idcplg',
p_action => 'http://www.stellent.com/CheckIn/',
p_collection_name => 'STELLENT_CHECKIN',
p_envelope => l_envelope,
p_username => 'sysadmin',
p_password => 'welcome1' );
l_response_msg := apex_web_service.parse_response(
p_collection_name=>'STELLENT_CHECKIN',
p_xpath=>'//idc:CheckInUniversalResponse/idc:CheckInUniversalResult/idc:StatusInfo/idc:statusMessage/text()',
p_ns=>'xmlns:idc="http://www.stellent.com/CheckIn/"');
:P1_RES_MSG := l_response_msg;
END IF;
END;
RESTful style Web services use a simpler architecture than SOAP. Typically the input to a RESTful style Web service is a collection of name/value pairs. The response can be an XML document or simply text such as a comma separated response or JSON. The following is an example of MAKE_REST_REQUEST being used in an application process that is callable by AJAX.
declare
l_clob clob;
l_buffer varchar2(32767);
l_amount number;
l_offset number;
begin
l_clob := apex_web_service.make_rest_request(
p_url => 'http://us.music.yahooapis.com/ video/v1/list/published/popular',
p_http_method => 'GET',
p_parm_name => apex_util.string_to_table('appid:format'),
p_parm_value => apex_util.string_to_table(apex_application.g_x01||':'||apex_application.g_x02));
l_amount := 32000;
l_offset := 1;
begin
loop
dbms_lob.read( l_clob, l_amount, l_offset, l_buffer );
htp.p(l_buffer);
l_offset := l_offset + l_amount;
l_amount := 32000;
end loop;
exception
when no_data_found then
null;
end;
end;
When you invoke a Web service using any of the supported methods in Application Express, the g_response_cookies and g_headers globals are populated if the Web service response included any cookies or HTTP headers. You can interrogate these globals and store the information in collections.The following are examples of interrogating the APEX_WEB_SERVICE globals to store cookie and HTTP header responses in collections.
declare
i number;
secure varchar2(1);
begin
apex_collection.create_or_truncate_collection('P31_RESP_COOKIES');
for i in 1.. apex_web_service.g_response_cookies.count loop
IF (apex_web_service.g_response_cookies(i).secure) THEN
secure := 'Y';
ELSE
secure := 'N';
END IF;
apex_collection.add_member(p_collection_name => 'P31_RESP_COOKIES',
p_c001 => apex_web_service.g_response_cookies(i).name,
p_c002 => apex_web_service.g_response_cookies(i).value,
p_c003 => apex_web_service.g_response_cookies(i).domain,
p_c004 => apex_web_service.g_response_cookies(i).expire,
p_c005 => apex_web_service.g_response_cookies(i).path,
p_c006 => secure,
p_c007 => apex_web_service.g_response_cookies(i).version );
end loop;
end;
declare
i number;
begin
apex_collection.create_or_truncate_collection('P31_RESP_HEADERS');
for i in 1.. apex_web_service.g_headers.count loop
apex_collection.add_member(p_collection_name => 'P31_RESP_HEADERS',
p_c001 => apex_web_service.g_headers(i).name,
p_c002 => apex_web_service.g_headers(i).value,
p_c003 => apex_web_service.g_status_code);
end loop;
end;
You set cookies and HTTP headers that should be sent along with a Web service request by populating the globals g_request_cookies and g_request_headers before the process that invokes the Web service.The following examples show populating the globals to send cookies and HTTP headers with a request.
for c1 in (select seq_id, c001, c002, c003, c004, c005, c006, c007
from apex_collections
where collection_name = 'P31_RESP_COOKIES' ) loop
apex_web_service.g_request_cookies(c1.seq_id).name := c1.c001;
apex_web_service.g_request_cookies(c1.seq_id).value := c1.c002;
apex_web_service.g_request_cookies(c1.seq_id).domain := c1.c003;
apex_web_service.g_request_cookies(c1.seq_id).expire := c1.c004;
apex_web_service.g_request_cookies(c1.seq_id).path := c1.c005;
if c1.c006 = 'Y' then
apex_web_service.g_request_cookies(c1.seq_id).secure := true;
else
apex_web_service.g_request_cookies(c1.seq_id).secure := false;
end if;
apex_web_service.g_request_cookies(c1.seq_id).version := c1.c007;
end loop;
for c1 in (select seq_id, c001, c002
from apex_collections
where collection_name = 'P31_RESP_HEADERS' ) loop
apex_web_service.g_request_headers(c1.seq_id).name := c1.c001;
apex_web_service.g_request_headers(c1.seq_id).value := c1.c002;
end loop;
Use this function to convert a BLOB datatype into a CLOB that is base64 encoded. This is often used when sending a binary as an input to a Web service.
APEX_WEB_SERVICE.BLOB2CLOBBASE64 (
p_blob IN BLOB)
RETURN CLOB;
Table 22-1 describes the parameters available in the BLOB2CLOBBASE64 function.
Table 22-1 BLOB2CLOBBASE64 Parameters
| Parameter | Description |
|---|---|
|
|
The BLOB to convert into base64 encoded CLOB. |
The following example gets a file that was uploaded from the apex_application_files view and converts the BLOB into a CLOB that is base64 encoded.
declare
l_clob CLOB;
l_blob BLOB;
begin
SELECT BLOB_CONTENT
INTO l_BLOB
FROM APEX_APPLICATION_FILES
WHERE name = :P1_FILE;
l_CLOB := apex_web_service.blob2clobbase64(l_BLOB);
end;
Use this function to convert a CLOB datatype that is base64 encoded into a BLOB. This is often used when receiving output from a Web service that contains a binary parameter.
APEX_WEB_SERVICE.CLOBBASE642BLOB (
p_clob IN CLOB)
RETURN BLOB;
Table 22-2 describes the parameters available in the CLOBBASE642BLOB function.
Table 22-2 CLOBBASE642BLOB Parameters
| Parameter | Description |
|---|---|
|
|
The base64 encoded CLOB to convert into a BLOB. |
The following example retrieves a base64 encoded node from an XML document as a CLOB and converts it into a BLOB.
declare
l_base64 CLOB;
l_blob BLOB;
l_xml XMLTYPE;
begin
l_base64 := apex_web_service.parse_xml_clob(l_xml, ' //runReportReturn/reportBytes/text()');
l_blob := apex_web_service.clobbase642blob(l_base64);
end;
Use this procedure to invoke a SOAP style Web service with the supplied SOAP envelope and store the results in a collection.
APEX_WEB_SERVICE.MAKE_REQUEST (
p_url IN VARCHAR2,
p_action IN VARCHAR2 default null,
p_version IN VARCHAR2 default '1.1',
p_collection_name IN VARCHAR2 default null,
p_envelope IN CLOB,
p_username IN VARCHAR2 default null,
p_password IN VARCHAR2 default null,
p_proxy_override IN VARCHAR2 default null,
p_transfer_timeout IN NUMBER default 180,
p_wallet_path IN VARCHAR2 default null,
p_wallet_pwd IN VARCHAR2 default null );
Table 22-3 describes the parameters available in the MAKE_REQUEST procedure.
Table 22-3 MAKE_REQUEST Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The URL endpoint of the Web service. |
|
|
The SOAP Action corresponding to the operation to be invoked. |
|
|
The SOAP version, 1.1 or 1.2. The default is 1.1. |
|
|
The name of the collection to store the response. |
|
|
The SOAP envelope to post to the service. |
|
|
The username if basic authentication is required for this service. |
|
|
The password if basic authentication is required for this service |
|
|
The proxy to use for the request. The proxy supplied overrides the proxy defined in the application attributes. |
|
|
The amount of time in seconds to wait for a response. |
|
|
The file system path to a wallet if the URL endpoint is https. For example, file:/usr/home/oracle/WALLETS. The wallet path provided overrides the wallet defined in the instance settings. |
|
|
The password to access the wallet. |
The following example uses the make_request procedure to retrieve a list of movies from a SOAP style Web service. The response is stored in an Application Express collection named MOVIE_LISTINGS.
declare
l_envelope CLOB;
BEGIN
l_envelope := '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:tns="http://www.ignyte.com/whatsshowing"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<tns:GetTheatersAndMovies>
<tns:zipCode>43221</tns:zipCode>
<tns:radius>5</tns:radius>
</tns:GetTheatersAndMovies>
</soap:Body>
</soap:Envelope>';
apex_web_service.make_request(
p_url => ' http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx',
p_action => ' http://www.ignyte.com/whatsshowing/GetTheatersAndMovies',
p_collection_name => 'MOVIE_LISTINGS',
p_envelope => l_envelope
);
END;
Use this function to invoke a SOAP style Web service with the supplied SOAP envelope returning the results in an XMLTYPE.
APEX_WEB_SERVICE.MAKE_REQUEST (
p_url IN VARCHAR2,
p_action IN VARCHAR2 default null,
p_version IN VARCHAR2 default '1.1',
p_envelope IN CLOB,
p_username IN VARCHAR2 default null,
p_password IN VARCHAR2 default null,
p_proxy_override IN VARCHAR2 default null,
p_transfer_timeout IN NUMBER default 180,
p_wallet_path IN VARCHAR2 default null,
p_wallet_pwd IN VARCHAR2 default null )
RETURN XMLTYPE;
Table 22-4 describes the parameters available in the MAKE_REQUEST function.
Table 22-4 MAKE_REQUEST Function Parameters
| Parameter | Description |
|---|---|
|
|
The URL endpoint of the Web service. |
|
|
The SOAP Action corresponding to the operation to be invoked. |
|
|
The SOAP version, 1.1 or 1.2. The default is 1.1. |
|
|
The SOAP envelope to post to the service. |
|
|
The username if basic authentication is required for this service. |
|
|
The password if basic authentication is required for this service |
|
|
The proxy to use for the request. The proxy supplied overrides the proxy defined in the application attributes. |
|
|
The amount of time in seconds to wait for a response. |
|
|
The file system path to a wallet if the URL endpoint is https. For example, file:/usr/home/oracle/WALLETS. The wallet path provided overrides the wallet defined in the instance settings. |
|
|
The password to access the wallet. |
The following example uses the make_request function to invoke a SOAP style Web service that returns movie listings. The result is stored in an XMLTYPE.
declare
l_envelope CLOB;
l_xml XMLTYPE;
BEGIN
l_envelope := ' <?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:tns="http://www.ignyte.com/whatsshowing"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<tns:GetTheatersAndMovies>
<tns:zipCode>43221</tns:zipCode>
<tns:radius>5</tns:radius>
</tns:GetTheatersAndMovies>
</soap:Body>
</soap:Envelope>';
l_xml := apex_web_service.make_request(
p_url => ' http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx',
p_action => ' http://www.ignyte.com/whatsshowing/GetTheatersAndMovies',
p_envelope => l_envelope
);
END
Use this function to invoke a RESTful style Web service supplying either name value pairs, a character based payload or a binary payload and returning the response in a CLOB.
APEX_WEB_SERVICE.MAKE_REST_REQUEST(
p_url IN VARCHAR2,
p_http_method IN VARCHAR2,
p_username IN VARCHAR2 default null,
p_password IN VARCHAR2 default null,
p_proxy_override IN VARCHAR2 default null,
p_transfer_timeout IN NUMBER default 180,
p_body IN CLOB default empty_clob(),
p_body_blob IN BLOB default empty_blob(),
p_parm_name IN apex_application_global.VC_ARR2 default empty_vc_arr,
p_parm_value IN apex_application_global.VC_ARR2 default empty_vc_arr,
p_wallet_path IN VARCHAR2 default null,
p_wallet_pwd IN VARCHAR2 default null )
RETURN CLOB;
Table 22-5 describes the parameters available in the MAKE_REST_REQUEST function.
Table 22-5 MAKE_REST_REQUEST Function Parameters
| Parameter | Description |
|---|---|
|
|
The URL endpoint of the Web service. |
|
|
The HTTP method to use, PUT, POST, GET, HEAD, or DELETE. |
|
|
The username if basic authentication is required for this service. |
|
|
The password if basic authentication is required for this service |
|
|
The proxy to use for the request. The proxy supplied overrides the proxy defined in the application attributes. |
|
|
The amount of time in seconds to wait for a response. |
|
|
The HTTP payload to be sent as CLOB. |
|
|
The HTTP payload to be sent as binary BLOB. For example, posting a file. |
|
|
The name of the parameters to be used in name/value pairs. |
|
|
The value of the parameters to be used in name/value pairs. |
|
|
The file system path to a wallet if the URL endpoint is https. For example, file:/usr/home/oracle/WALLETS. The wallet path provided overrides the wallet defined in the instance settings. |
|
|
The password to access the wallet. |
The following example calls a RESTful style Web service using the make_rest_request function passing the parameters to the service as name/value pairs. The response from the service is stored in a locally declared CLOB.
declare
l_clob CLOB;
BEGIN
l_clob := apex_web_service.make_rest_request(
p_url => 'http://us.music.yahooapis.com/ video/v1/list/published/popular',
p_http_method => 'GET',
p_parm_name => apex_util.string_to_table('appid:format'),
p_parm_value => apex_util.string_to_table('xyz:xml'));
END
Use this function to parse the response from a Web service that is stored in a collection and return the result as a VARCHAR2 type.
APEX_WEB_SERVICE.PARSE_RESPONSE (
p_collection_name IN VARCHAR2,
p_xpath IN VARCHAR2,
p_ns IN VARCHAR2 default null )
RETURN VARCHAR2;
Table 22-6 describes the parameters available in the PARSE_RESPONSE function.
Table 22-6 PARSE_RESPONSE Function Parameters
| Parameter | Description |
|---|---|
|
|
The name of the collection where the Web service response is stored. |
|
|
The XPath expression to the desired node. |
|
|
The namespace to the desired node. |
The following example parses a response stored in a collection called STELLENT_CHECKIN and stores the value in a locally declared VARCHAR2 variable.
declare
l_response_msg VARCHAR2(4000);
BEGIN
l_response_msg := apex_web_service.parse_response(
p_collection_name=>'STELLENT_CHECKIN',
p_xpath =>
'//idc:CheckInUniversalResponse/idc:CheckInUniversalResult/idc:StatusInfo/idc:statusMessage/text()',
p_ns=>'xmlns:idc="http://www.stellent.com/CheckIn/"');
END;
Use this function to parse the response from a Web service that is stored in a collection and return the result as a CLOB type.
APEX_WEB_SERVICE.PARSE_RESPONSE_CLOB (
p_collection_name IN VARCHAR2,
p_xpath IN VARCHAR2,
p_ns IN VARCHAR2 default null )
RETURN CLOB;
Table 22-7 describes the parameters available in the PARSE_RESPONSE_CLOB function.
Table 22-7 PARSE_RESPONSE _CLOB Function Parameters
| Parameter | Description |
|---|---|
|
|
The name of the collection where the Web service response is stored. |
|
|
The XPath expression to the desired node. |
|
|
The namespace to the desired node. |
The following example parses a response stored in a collection called STELLENT_CHECKIN and stores the value in a locally declared CLOB variable.
declare
l_response_msg CLOB;
BEGIN
l_response_msg := apex_web_service.parse_response_clob(
p_collection_name=>'STELLENT_CHECKIN',
p_xpath=>
'//idc:CheckInUniversalResponse/idc:CheckInUniversalResult/idc:StatusInfo/idc:statusMessage/text()',
p_ns=>'xmlns:idc="http://www.stellent.com/CheckIn/"');
END;
Use this function to parse the response from a Web service returned as an XMLTYPE and return the value requested as a VARCHAR2.
APEX_WEB_SERVICE.PARSE_XML (
p_xml IN XMLTYPE,
p_xpath IN VARCHAR2,
p_ns IN VARCHAR2 default null )
RETURN VARCHAR2;
Table 22-8 describes the parameters available in the PARSE_XML function.
Table 22-8 PARSE_XML Function Parameters
| Parameter | Description |
|---|---|
|
|
The XML document as an XMLTYPE to parse. |
|
|
The XPath expression to the desired node. |
|
|
The namespace to the desired node. |
The following example uses the make_request function to call a Web service and store the results in a local XMLTYPE variable. The parse_xml function is then used to pull out a specific node of the XML document stored in the XMLTYPE and stores it in a locally declared VARCHAR2 variable.
declare
l_envelope CLOB;
l_xml XMLTYPE;
l_movie VARCHAR2(4000);
BEGIN
l_envelope := ' <?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:tns="http://www.ignyte.com/whatsshowing"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<tns:GetTheatersAndMovies>
<tns:zipCode>43221</tns:zipCode>
<tns:radius>5</tns:radius>
</tns:GetTheatersAndMovies>
</soap:Body>
</soap:Envelope>';
l_xml := apex_web_service.make_request(
p_url => ' http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx',
p_action => ' http://www.ignyte.com/whatsshowing/GetTheatersAndMovies',
p_envelope => l_envelope );
l_movie := apex_web_service.parse_xml(
p_xml => l_xml,
p_xpath => ' //GetTheatersAndMoviesResponse/GetTheatersAndMoviesResult/Theater/Movies/Movie/Name[1]',
p_ns => ' xmlns="http://www.ignyte.com/whatsshowing"' );
END;
Use this function to parse the response from a Web service returned as an XMLTYPE and return the value requested as a CLOB.
APEX_WEB_SERVICE.PARSE_XML_CLOB (
p_xml IN XMLTYPE,
p_xpath IN VARCHAR2,
p_ns IN VARCHAR2 default null )
RETURN VARCHAR2;
Table 22-9 describes the parameters available in the PARSE_XML_CLOB function.
Table 22-9 PARSE_XML_CLOB Function Parameters
| Parameter | Description |
|---|---|
|
|
The XML document as an XMLTYPE to parse. |
|
|
The XPath expression to the desired node. |
|
|
The namespace to the desired node. |
The following example uses the make_request function to call a Web service and store the results in a local XMLTYPE variable. The parse_xml function is then used to pull out a specific node of the XML document stored in the XMLTYPE and stores it in a locally declared VARCHAR2 variable
declare
l_envelope CLOB;
l_xml XMLTYPE;
l_movie CLOB;
BEGIN
l_envelope := ' <?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:tns="http://www.ignyte.com/whatsshowing"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<tns:GetTheatersAndMovies>
<tns:zipCode>43221</tns:zipCode>
<tns:radius>5</tns:radius>
</tns:GetTheatersAndMovies>
</soap:Body>
</soap:Envelope>';
l_xml := apex_web_service.make_request(
p_url => ' http://www.ignyte.com/webservices/ignyte.whatsshowing.webservice/moviefunctions.asmx',
p_action => ' http://www.ignyte.com/whatsshowing/GetTheatersAndMovies',
p_envelope => l_envelope );
l_movie := apex_web_service.parse_xml_clob(
p_xml => l_xml,
p_xpath => ' //GetTheatersAndMoviesResponse/GetTheatersAndMoviesResult/Theater/Movies/Movie/Name[1]',
p_ns => ' xmlns="http://www.ignyte.com/whatsshowing"' );
END;