Calling .Net Web Services from Oracle PL/SQL

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…

Related Articles:

    Post Footer automatically generated by Add Post Footer Plugin for wordpress.

    About Joshua Lockwood

    I code stuff and people pay me for it.
    This entry was posted in Oracle, SOAP, SQL, Web Services. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

    29 Responses to Calling .Net Web Services from Oracle PL/SQL

    1. Joe Ocampo says:

      You are mad man! Very Clever though.

    2. eric givler says:

      Do you have an example that includes BASIC authentication, and then parsing an XML response, rather than just showing it, maybe using Oracle’s XMLType or the PLSQL DOM APIs? That would be nice.

    3. eric givler says:

      Do you have an example that includes BASIC authentication, and then parsing an XML response, rather than just showing it, maybe using Oracle’s XMLType or the PLSQL DOM APIs? That would be nice.

      I do appreciate this example though – thanks!

    4. Paul says:

      Great solution man

    5. Marco says:

      Hi, your example helped me a lot to consume some basic information from a Web Service, but the thing is that it got a little more complex and now I need to get just selected information from a service XML response such us the one I list below, do you know how can I scan inside the XML and get only certain values that I need?

      < ?xml version="1.0" encoding="UTF-8"?>

      PRINCIPAL AAA MERCANTIL ACTIVIDAD 1: TRUE 90 ACTIVIDAD 2: TRUE 123 BIN LADEN ACTIVIDAD 1: 550000

      Thanks in advance!! =0)

    6. jlockwood says:

      @Marco

      You should be able to use Oracle’s DOM parser in PL/SQL for that:
      http://download-east.oracle.com/docs/cd/B10464_05/web.904/b12099/adx28ppl.htm

      If I remember correctly, the XML package was not installed by default on our servers. I had to get the DB admin to add the package. Not sure…

    7. Mo says:

      Hi all, anybody have more advanced cases that involve encrypting the SOAP body using the so-called username token? It’s so easy with .NET – infact everything is done for you using a RequestContext. But it seems I have to do it all manually in PL/SQL

    8. jlockwood says:

      @Mo,

      Have you tried using utl_http.set_authentication()?

      Params are:
      http_request,
      username,
      password,
      scheme (default ‘Basic’,
      for_proxy (default false)

      You should be able to use the method to set the authentication info in the request header.

    9. MO says:

      Hi jlockwood,
      Thanks for the reply. I seem to have missed seeing the response all this time – sorry.

      Anyway, the kind of security am interested in right now is message-level security. So my SOAP request itself contains a security header with a so-called UsernameToken.

      Authentication is actually not the problem right now. Am already able to call the web service with unencrypted data.

      However, am trying to use my Username Token to encrypt the SOAP body. Seems there’s too much work to be done for this in PL/SQL. Alot of this functionality is encapsulated in .NET, etc.

      Just thought someone had done this before using PL/SQL. Otherwise might have to consider another technology :-(

      Mo

    10. Subramanian Kaushik says:

      Really thanks for ur example

    11. Subramanian Kaushik says:

      Really thanks for ur example

    12. Indradipta Biswas says:

      Is it possible to use the same things through Stored proc/Function. I have tried it but it is not working.

    13. Nithya says:

      Hi,
      Im trying to create a DB Trigger to invoke a web service, that is as and when I insert records in a particular table, it has call the web service and the record attributes should be sent as parameters to the Web Service. Is this possible? Can u give me an example for the same, it would be very helpful.
      My Email Id:Nithya6.r@tcs.com
      Nithyaprincess@gmail.com

    14. jlockwood says:

      Nithya,

      I’m sure this is possible. I’d write a function first to make the web service call and test it independently. I’d then use the function in the trigger. If you do really need to do this,

      That said, I’d be carful that you add robust error handling in the trigger. This seems like a risky approach. If the service is down or the contract is altered you may no longer be able to insert records. If the service is unavailable, you’d want a mechanism to handle that case gracefully.

      Coupling the web service with inserts is something I’d be reluctant to do, but I could image cases where it might be needed. I would prefer, however, to have the service call made before the insert was attempted.

    15. Nithya says:

      Hi,
      Thanks a lot Jlockwood. I was on a long leave. So sorry for the late reply. Can u tell me how to pass the arguements to the web service , in the following code…. The parameters to be passed are the attribute values of the new record inserted.. In usual cases we use :new.attribute_name etc,.. hw can I do it here…. Pls help me..

      declare
      http_req utl_http.req;
      http_resp utl_http.resp;
      request_env varchar2(32767);
      response_env varchar2(32767);
      begin
      request_env:=’
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">


      This is my message



      ‘;
      dbms_output.put_line(‘Length of Request:’ || length(request_env));
      dbms_output.put_line (‘Request: ‘ || request_env);

      http_req := utl_http.begin_request(‘http://172.20.211.115:8080/IcomsApi/services/ICOMSOrderService?wsdl’, ‘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;

    16. Nithya says:

      Hi,
      I tried invoking a web service with the help of your code. I get an error in the Response, stating ‘Internal Error’.. I have copied the code and the error below..Im finding it difficult to fix the issue as Im new to web services. Can u tell me why it occurs?
      Code:
      _____

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

      begin
      request_env:=’
      xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


      4856
      Paul
      Tuticorin





      ‘;
      dbms_output.put_line(‘Length of Request:’ || length(request_env));
      dbms_output.put_line (‘Request: ‘ || request_env);

      http_req := utl_http.begin_request(‘http://172.20.212.158:10001/EvdServices/services/addUserData’, ‘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’, ‘”API.EVD.TCS.COM”‘);
      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;

      Output:
      _______
      Length of Request:486
      Request:
      xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">


      4856
      Paul
      Tuticorin




      Response Received
      ————————–
      Status code: 500
      Reason phrase: Internal Server Error
      Response:
      < ?xml version='1.0' encoding='utf-8'?>soapenv:ClientThe service cannot be found for the endpoint reference (EPR) http://172.20.212.158:10001/EvdServices/services/addUserData

    17. Nithya says:

      Hi,
      Thanks Jlockwood..I resolved the issue.Wrote a trigger to invoke a web service on update and passed the new value as the parameter to the web service.Your code was helpful.. would have ended up in a No man’s land, if not for yoyur code posted here!

    18. jlockwood says:

      @Nithya,
      Np, sorry I didn’t get back to your last post…I’m a bit overtaxed at the moment. Glad to hear you got everything working!

    19. Nithya says:

      Hi Jlockwood,
      Is there any way of looking into the system log tables in Oracle 10g? My task isto look the activities that are done by a specific user , so that if someone creates an issue, we should be able to look back and see who did what. I have no clues..Can u help me in this regard? I tried to look into the syslog..bt there is no such system tables in Oracle 10g..it was available only in the previous versions.

    20. jlockwood says:

      @Nathiya,
      Sorry, I couldn’t answer that this time…I am not working with Oracle these days. I am currently using Postgres.

    21. Nithya says:

      Hi,
      Thanks and no issues :) !

    22. Kata says:

      Hi everybody,

      I ‘m trying to use the XML Parser for PL/SQL like this:
      at declare section :

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

      p xmlparser.parser;
      doc xmldom.DOMDocument;


      then in the statement, after i get the response:


      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);

      p := xmlparser.newParser;
      xmlparser.parseBuffer(p,response_env);
      doc := xmlparser.getDocument(p);

      I think is not a correct syntax..can someone help me with this?

    23. sagar says:

      sagar wrote re: Calling .Net Web Services from Oracle PL/SQL
      on 06-09-2009 3:20 PM
      Hi Jlockwood, can you please help me out to solve this

      declare

      l_service SYS.UTL_DBWS.service;

      l_call SYS.UTL_DBWS.call;

      l_result ANYDATA;

      l_wsdl_url VARCHAR2(32767);

      l_namespace VARCHAR2(32767);

      l_service_qname SYS.UTL_DBWS.qname;

      l_port_qname SYS.UTL_DBWS.qname;

      l_operation_qname SYS.UTL_DBWS.qname;

      l_input_params SYS.UTL_DBWS.anydata_list;

      l_return_string VARCHAR2 (100);

      XML XMLTYPE;

      string_type_qname sys.utl_dbws.QNAME;

      retx sys.xmltype;

      xml_string sys.xmltype;

      l_str long;

      BEGIN

      v_err_loc :=1;

      — Set the Target NameSpace

      l_wsdl_url := url watever rquired ;

      l_namespace := namespace wat ever required’;

      — Set the Service

      l_service_qname := SYS.UTL_DBWS.to_qname(l_namespace, ‘DataviewService’);

      — Set the Port

      l_port_qname := SYS.UTL_DBWS.to_qname(l_namespace, ‘DataviewServiceSoap’);

      — Set the Operation

      l_operation_qname := SYS.UTL_DBWS.to_qname(l_namespace, ‘Application name’);

      — Create a Service. The Service created here will be used to create a call

      — to the Web Service.

      l_service := SYS.UTL_DBWS.create_service (wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),

      service_name => l_service_qname);

      — Create a Call to the Web Service. The call created here will be used to

      — invoke the Web Services, using the End Point URL.

      l_call := SYS.UTL_DBWS.create_call (

      service_handle => l_service,

      port_name => NULL,

      operation_name => l_operation_qname);

      — Set the SOAP Action Properties to use.

      sys.utl_dbws.set_property (l_call,’SOAPACTION_USE’,'TRUE’);

      sys.utl_dbws.set_property (l_call,’SOAPACTION_URI’, l_namespace /application name);

      sys.utl_dbws.set_property (l_call,’OPERATION_STYLE’,'document’);

      sys.utl_dbws.set_property(l_call, ‘ENCODINGSTYLE_URI’,'schemas.xmlsoap.org/…/&);

      string_type_qname := sys.utl_dbws.to_qname(‘www.w3.org/…/XMLSchema&, ‘‘);

      sys.utl_dbws.add_parameter(l_call, ‘Request’, string_type_qname, ‘ParameterMode.IN’);

      sys.utl_dbws.set_return_type(l_call, string_type_qname);

      xml_string := xmltype(‘< ?xml version="1.0" encoding="utf-8"?>

      To be, or not to be

      ‘);

      retx := sys.utl_dbws.invoke(call_Handle => l_call ,request => xml_string);

      l_str := retx.extract(‘/*’).getstringval();

      loop

      exit when l_str is null;

      dbms_output.put_line (substr (l_str, 1, instr (l_str, chr(10)) – 1));

      l_str := substr (l_str, instr (l_str, chr(10)) + 1);

      end loop;

      dbms_output.put_line (‘Message returned is: ‘ || nvl(l_return_string, ‘No success message returned’));

      — Release the call to the Web Service. A Call to a Web Service is a

      — Resource that needs to be released after the work is done.

      SYS.UTL_DBWS.release_call (call_handle => l_call);

      — Release the Service. A Service to a Web Service is a

      — Resource that needs to be released after the work is done.

      SYS.UTL_DBWS.release_service (service_handle => l_service);

      dbms_output.put_line(ANYDATA.AccessVarchar2(l_result));

      – RETURN ANYDATA.AccessVarchar2(l_result);

      EXCEPTION WHEN OTHERS THEN

      SHOW_MESSAGE(‘==ErrorCode==’||SQLCODE);

      SHOW_MESSAGE(‘==ErrorText==’||SQLERRM);

      SHOW_MESSAGE(DBMS_UTILITY.format_error_backtrace);

      end;

      when i run this from sql developer it’s throwing this error:
      =ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist.

      please tell me how to solve this and c an you tell me how to pass parameters to webservices to invoke a third party engine from oracle.

    24. Niels says:

      Sagar, considering that you use UTL_DBWS did you install the Java libraries into your database as per instructions on the Oracle example site http://www.oracle.com/technology/sample_code/tech/java/jsp/loadjars.html ?

      Cheers,
      Niels

    25. Ananth (calling .net webservice from oracle) says:

      Should i give a proxy url. I tried using the real url, in httpreq := UTL_HTTP.begin_request (in_web_svc_url, ‘POST’, ‘HTTP/1.1′);

      ora-06512 sys.utl_http line 1029

      ora-12545 connect failed because target host or object does not exist

      It throws an error. kindly help me.

    26. Taj says:

      Thanks Alot. This really helped

    27. Miguel says:

      DUDE you are my hero !!! thank god for people like you that are willing to share.

    28. Suresh says:

      Great reaseach ,very usefull, it works in 10g also.

      Thanks again.

    29. SJari says:

      Hi, nice post, I have made similar thing :-)

      but I need to know something

      the string “Logged Message: [This is my message]”

      how I can in PL/SQL extract it to a variable in PL/SQL code so I can process the results with other function?

      cheers

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>