Downtime Announcement: Please note the SAP Community Wiki will be unavailable due to a system upgrade on Thursday, September 24th between 6 and 7 AM CEST
Skip to end of metadata
Go to start of metadata
 Scenario

Triggering webservice from Oracle Database with UTL_HTTP supplied package.(This package is available from Oracle 9i onwards)

Introduction

This document is applicable to the Oracle DB version which supports the web service. This document deals with how to use the UTL_HTTP supplied package to communicate directly with the Currency conversion WebService (http://www.webservicex.com/CurrencyConvertor.asmx ) and send and retrieve entire SOAP messages.

The webservice will return a response for the current available conversion rate from 'USD' to 'INR' .

You can have this approach if you want to have a trigger from the Oracle DB and doesn't want to use the JDBC adapters which will be working on the polling the database day in day out.

PL/SQL procedure Code
 
--To Keep server output parameter on
set serveroutput on;--declaration
  declare
 
  soap_request varchar2(30000);
  soap_respond varchar2(30000);
  http_req utl_http.req;
  http_resp utl_http.resp;
 
--PL/SQL procedure
  begin--Webservice request schema
  soap_request:= '<?xml version="1.0" encoding="utf-8"?>
  <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"   xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://www.webserviceX.NET/">
  <soap:Body>
    <tns:ConversionRate>
      <tns:FromCurrency>USD</tns:FromCurrency>
      <tns:ToCurrency>INR</tns:ToCurrency>
    </tns:ConversionRate>
   </soap:Body>
  </soap:Envelope>';-- Set proxy details if no direct net connection.
  --HTTP request
  http_req:= utl_http.begin_request( 'http://209.162.186.60:80/CurrencyConvertor.asmx' , 'POST' , 'HTTP/1.1');--set authentication
    utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
  utl_http.set_header(http_req, 'Content-Length', length(soap_request));
  utl_http.set_header(http_req, 'SOAPAction', 'http://www.webserviceX.NET/ConversionRate');
  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);
  dbms_output.put_line(soap_respond);  end;
/

If the communication is through a proxy server

1-add the additional code below line
--Set proxy details if no direct net connection.
--set authentication

-- Set proxy details if no direct net connection.
 utl_http.set_proxy('proxyserver:port');
  -- set authentication
  utl_http.set_authentication(http_req, 'username',  'password',  'Basic', TRUE); 

 Reaponse from Webservice (http://www.webservicex.com/CurrencyConvertor.asmx)

The SAP XI exposed webservice can be triggered using this utl_http pakage.
Hope this will be useful for the cummunity.

Cheers

Sunil John.

  • No labels