Calling .Net Web Services from Oracle PL/SQL

http://www.lostechies.com/blogs/joshua_lockwood/archive/2007/09/14/calling-net-web-services-from-oracle.aspx

 

I'm working relatively closely these days with an Oracle DBA on this super-cool mainframe integration project (anyone want to join me?  LOL).  The old batch ops were managed through cron'd jobs in Oracle and I was asked to take a different approach.  I haven't found anything like Control-M here and was toying with the idea of rolling my own scheduling services.  The management wanted the integration pieces written in VB.Net, not PL/SQL scripts as had been done in the past, fair enough.  In a later meeting, the DBA had said that he REALLY wanted to replace the cron jobs with Oracle Jobs...hmmm...what to do, what to do...

I'd already planned on publishing .Net web services to support calls to the actual service objects.  A scheduler would be configured to call the web services at set intervals and kick of the batch processing.  I did a little research and found the utl_http library in Oracle.  I ran a quick test if it was installed and working and was delighted.

With utl_http it's pretty easy to call an XML web service.  It supports using PL/SQL to utilize HTTP requests and responses.  To test it I threw together a very simple web service that allows anonymous callers to write to a custom event log.  Now the DBA can automate sending me nasty grams (none received yet, I'm a little disappointed).

Here's the PL/SQL script that calls the web service.  All you have to do is define the requesting soap envelope, set the appropriate HTTP header info, point to your target using the right protocol and fire!

declare
     http_req  utl_http.req;
     http_resp utl_http.resp;
     request_env varchar2(32767);
     response_env varchar2(32767); 
begin

request_env:='
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <LogMessage xmlns="http://tempuri.org/">
      <message>This is my message</message>
    </LogMessage>
  </soap:Body>
</soap:Envelope>
';
dbms_output.put_line('Length of Request:' || length(request_env));
dbms_output.put_line ('Request: ' || request_env);

http_req := utl_http.begin_request('http://wsXXXX/Test_WebService/Service.asmx', 'POST', utl_http.HTTP_VERSION_1_1);
utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
utl_http.set_header(http_req, 'Content-Length', length(request_env));
utl_http.set_header(http_req, 'SOAPAction', '"http://tempuri.org/LogMessage"');
utl_http.write_text(http_req, request_env);

dbms_output.put_line('');

http_resp := utl_http.get_response(http_req);
dbms_output.put_line('Response Received');
dbms_output.put_line('--------------------------');
dbms_output.put_line ( 'Status code: ' || http_resp.status_code );
dbms_output.put_line ( 'Reason phrase: ' || http_resp.reason_phrase );

utl_http.read_text(http_resp, response_env);
dbms_output.put_line('Response: ');
dbms_output.put_line(response_env);

utl_http.end_response(http_resp);

end;

 

See? Easy as PI!  It's practically self explanatory (and the web service itself gives the client pretty much the info they need to get wired in).  As you see, I used varchars to declare vars for the request and the response.  With larger SOAP messages you'd want to take a different approach (reading chunks into a buffer) because the varchar is so small. 

Here's the console output showing what was sent and received (reformatted a bit for readability):

Length of Request:324
Request:
<soap:Envelope 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <LogMessage xmlns="http://tempuri.org/">
      <message>This is my message</message>
    </LogMessage>
  </soap:Body>
</soap:Envelope>


Response Received
--------------------------
Status code: 200
Reason phrase: OK
Response:
<?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">
    <soap:Body>
        <LogMessageResponse xmlns="http://tempuri.org/">
            <LogMessageResult>Logged Message: [This is my message]</LogMessageResult>
        </LogMessageResponse>
    </soap:Body>
</soap:Envelope>

Now how cool is that?  This was really my first time consuming (or starting to anyway) .Net web services from a disparate technology and hadn't really spent much quality time with SOAP since around 2002.  Now kicking of the jobs through Oracle will be a breeze...after I iron out authentication, of course.

Now, to learn how to use the 'out of the box'  XML parser that comes with Oracle...