Hello,
Webservices are becoming a staple in inter-business data exchange.
If you’re running Oracle, the whole ETL process can be integrated in a few short steps.
First, you need to setup the ACL for the service requested.
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => ‘companyX.xml’,
description => ‘Network permissions for CompanyX server’,
principal => ‘LocalOracleAccount’,
is_grant => TRUE,
privilege => ‘connect’);
EXEC DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => ‘companyX.xml’,
host => ‘companyX.dataservices.com’,
lower_port => 80);
GRANT XDB_WEBSERVICES to LocalOracleAccount;
GRANT XDB_WEBSERVICES_OVER_HTTP to LocalOracleAccount;
Then you’re ready to fetch the data from the service: Note: You need to know the EndPoint for initial WSDL/WADL. See the v_target_url variable.
DECLARE
extract_clob CLOB;
v_soap_action varchar2(255);
v_soap_request varchar2(32767);
v_soap_response varchar2(32767);
v_http_req utl_http.req;
v_http_resp utl_http.resp;
v_target_url in varchar2 default ‘http://companyx.dataservices.com/DATA/oltpservices.asmx?WSDL’;
v_soap_envelope varchar2(5000):=’<soap:Envelope xmlns:soap=”http://www.w3.org/2003/05/soap-envelope“
xmlns:oltp=”dataservices/DATA/oltpservices/”>
<soap:Header/> <soap:Body>
<oltp:DailySalesaggregation>
</soap:Body>
</soap:Envelope>’;
BEGIN
DBMS_LOB.createtemporary(extract_clob, FALSE);
v_soap_request := v_soap_envelope;
http_req:= utl_http.begin_request( v_target_url, ‘POST’, ‘HTTP/1.1′);
utl_http.set_body_charset(v_http_req, ‘UTF-8′);
utl_http.set_header(v_http_req, ‘Content-Type’, ‘text/xml;charset=”UTF-8″‘);
utl_http.set_header(v_http_req, ‘Content-Length’, length(v_soap_request));
utl_http.set_header(v_http_req, ‘SOAPAction’, v_soap_action);
utl_http.write_text(v_http_req, v_soap_request);
v_http_resp:= utl_http.get_response(v_http_req);
LOOP
UTL_HTTP.read_text(v_http_resp, v_soap_response, 32767);
DBMS_LOB.writeappend (extract_clob, LENGTH(v_soap_response), v_soap_response);
END LOOP;
EXCEPTION WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(v_http_resp);
…
At this point you can manipulate the lob to transform and load the extracted data in the database.
Pierre
