Consuming WCF service from Oracle

Question

TLDR; look at last paragrap.

A developer from our partner software company needs to call our WCF (basic http binding) service, and he asked us to turn it to asmx for themselves, cause he has trouble with calling it from Oracle. WCF service is being used on different platforms (.net, java, php) with no error.

His code gives him Status code: 500 - Internal Server Error. I assume its about sending wrong soap format or content.

So i learned you should use utl_dbws instead of utl_http as that developer did.

Ok, this seemed an easy task to me first. Find a working code sample from internet and send a e-mail like "Hi fellow developer friend you should use utl_dbws package not utl_http and the sample code at this link".

I'm not the only person in the world that needs to do this, right ?

Weird but i couldn't find any sample approved working piece of code that accomplishes calling a WCF service from Oracle.

Here is some of link i found about it;

https://forums.oracle.com/forums/thread.jspa?threadID=2354357 https://forums.oracle.com/forums/thread.jspa?threadID=1071996 http://steveracanovic.blogspot.com/2008/10/using-utldbws-package-to-call-web.html https://forums.oracle.com/forums/thread.jspa?messageID=4205205&tstart=0#4205205
http://www.oracle-base.com/articles/10g/utl_dbws-10g.php

Noone writes any working code example or noone tells that this is not possible.

I would appreciate if anyone had a working code example that calling a WCF service from Oracle.

Answer 1

When you get a Http 500 error it is normally an internal error. For example that the developer is calling your service without setting all the input values, your code could then generate a divide by zero error, which when not caught is returned to the client as a http 500 error.

You can configure the soap version of a WCF service to be the same as an asmx service.

Answer 2

If you are getting response 500 (internal error) from WCF service, try set in web.config of WCF service includeexceptiondetailinfaults=true . (http://msdn.microsoft.com/cs-cz/library/system.servicemodel.description.servicedebugbehavior.includeexceptiondetailinfaults(v=vs.110).aspx)

Then you will get detailed exception (wrong soap action, wrong format...)

Call WCF service from PL/SQL.

utl_http but it works.

/*   
  declare
  p_request VARCHAR(32767);
  p_plainResult VARCHAR2(32767);
  begin
    p_request := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">
   <soapenv:Header/>
   <soapenv:Body>
      <tem:Sum>
         <tem:a>1</tem:a>
         <tem:b>2</tem:b>
      </tem:Sum>
   </soapenv:Body>
</soapenv:Envelope>';

select callSOAPService(p_request,'http://tempuri.org/IMathService/Sum','http://localhost:51106/MathService.svc') into p_plainResult from dual;    
    end;

  */
create or replace function callSOAPService 
(  
 p_plainRequest IN varchar2(20000),
 p_actionName IN varchar2(1024), --SOAP Action ( in WCF, attribute [OperationContract(Action="ActionName")
 p_url IN varchar2(1024),
   p_userName varchar2(1024) := null,
  p_password varchar2(1024) := null,
  p_isAsynchronous boolean:= FALSE,
  p_proxy varchar2(1024):=null,
  p_transferTimeout number :=null,
)
RETURN VARCHAR2(32767)
IS
  p_charset varchar2(1024) :='AL32UTF8'; --by default utf-8  
  p_request utl_http.req;
  p_response utl_http.resp;
  p_plainResponse varchar2(32767);
BEGIN

   p_url := utl_url.escape(url => p_url); --escape url

   if p_TransferTimeout > 0 THEN --set oracle timeout ( by defualt is 60 sec )
     utl_http.set_transfer_timeout(timeout => p_transferTimeout);
   END IF;

   if p_proxy IS NOT NULL THEN --if proxy is provided, then set it 
    utl_http.set_proxy(proxy => p_proxy);
   end if;
    utl_http.set_response_error_check(enable => TRUE); --http status errorCheck ( 404 not found, 500 internal error...)
    utl_http.set_detailed_excp_support(enable => TRUE); --detailed error stack

    p_request := UTL_HTTP.begin_request(url => p_url,method => 'POST' /*u SOAP bude vzdy POST meotda*/ ,http_version => 'HTTP/1.1');

    --pripravim si obalku
    UTL_HTTP.set_header (r => p_request,name =>  'Content-Type', value => 'text/xml'); 
    UTL_HTTP.set_header (r => p_request,name =>  'Content-Length',value =>  LENGTH (p_plainRequest));
    UTL_HTTP.set_header (r => p_request, name => 'SOAPAction',value => p_actionName); --if status is 500 check SOAP action
    UTL_HTTP.write_text(r => p_request,data => p_plainRequest);

    p_response := UTL_HTTP.get_response (p_request);

    if p_isAsynchronous THEN --one-way service
      UTL_HTTP.end_response (p_response); --proto ukoncim request a vratim prazdno
      RETURN ''; 
    end if;

   utl_http.read_text (p_response, p_plainResponse); --read response
   utl_http.end_response (p_response); --close resposne

   dbms_output.put_line ('Response from: ' || p_url || ' is ' || p_plainResponse); --vypisu odpoved pro kontrolu   
   return p_plainResponse;   
EXCEPTION
    when others then      
        dbms_output.put_line('Chyba ' || UTL_HTTP.get_detailed_sqlerrm()); --get error stack
        utl_http.end_response (p_response);
END;