SOAP Calls from Oracle

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