How To Call a Web Service from Oracle PL/SQL

I would like to say that calling a web service has been made easy with Sys.UTL_DBWS package but it took me a bit of time to get it right.  This biggest issue that I ran into is that the samples that I found online and in the forums simply don’t work.  The sample below was provided by Oracle support and helped a lot in getting started so I thought that I would pass it along.  The sample uses a public web service so if the service is still running this code should just work for you.  My ultimate goal was to call a Microsoft WCF service which I have been able to do and plan to post the code soon.

function get_joke
return varchar2
is
  service_           sys.utl_dbws.SERVICE;
  call_              sys.utl_dbws.CALL;
  service_qname      sys.utl_dbws.QNAME;
  port_qname         sys.utl_dbws.QNAME;
  xoperation_qname   sys.utl_dbws.QNAME;
  xstring_type_qname sys.utl_dbws.QNAME;
  response           sys.xmltype;
  request            sys.xmltype;
begin
  service_qname := sys.utl_dbws.to_qname(null, 'getJoke');
  service_ := sys.utl_dbws.create_service(service_qname);
  call_ := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_, 'http://interpressfact.net/webservices/getjoke.asmx');
  sys.utl_dbws.set_property( call_, 'SOAPACTION_USE', 'TRUE');
  sys.utl_dbws.set_property( call_, 'SOAPACTION_URI', 'http://interpressfact.net/webservices/getJoke');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');
  request := sys.xmltype(
       '<getJoke xmlns="http://interpressfact.net/webservices/">'
    || '<Category>Excuses-10</Category>'
    || '</getJoke>');
  response :=sys. utl_dbws.invoke(call_, request);
  return response.extract('//getJokeResult/child::text()', 
    'xmlns="http://interpressfact.net/webservices/"').getstringval(); 
end;

Resources

About these ads
Explore posts in the same categories: Oracle PL/SQL

Tags: ,

You can comment below, or link to this permanent URL from your own site.

27 Comments on “How To Call a Web Service from Oracle PL/SQL”

  1. Alex Says:

    Hi, Tom.
    i got tired of looking so many useless examples about oracle and soap, and finally i found yours…. which works……

    many thaks, you’ve saved my life…..

    may God bless you….

  2. Arnold Says:

    Thanks for your clear example. Would it be possible to post the code to call a
    Microsoft WCF service from PLSQL? Just a working example of the code
    is probably sufficient. You could ofcourse also send me the code by mail.
    This would definitely save me quite some time because I am trying to do exactly the same,

  3. Gabe Saporito Says:

    I would love to see your example that consumes a WCF. I’m still struggling through that. Thanks for this example. If it weren’t for it, I’d still be stumbling around.

    Regards,

    Gabe Saporito

  4. Burhan Says:

    Hi Tom,
    I stumbled upon your blog searching for Oracle code that consumes a WCF based service.
    Did you manage to achieve your objective?
    I am eagerly looking for any such sample. I would really appreciate if you can help me out by pointing in the right direction.
    Thanks for the great article..

    Burhan.

  5. Emma Says:

    I have tried this example but am getting this error: class oracle/jpub/runtime/dbws/DbwsProxy does not exist

    • Tom Krueger Says:

      Hi Emma,

      I believe DBWS is a separate install that you may also need to grant the appropriate security to. I’m not an Oracle Admin, so I don’t know how this is done, however, I’m certain that your Oracle Admin will know how.

      If you still cannot get it to work, I can find out the specifics for you.

  6. Dylan Says:

    Hi Tom,

    Great example, thank you for that. In your post you say “My ultimate goal was to call a Microsoft WCF service which I have been able to do and plan to post the code soon”. Would you be so kind as to post or e-mail me a sample of how you accomplished calling a WCF service using UTL_DBWS? Even if it’s a partial solution, a start would be really appreciated. I promise not to badger you for support on any hints you can provide!!

    Thanks again,
    Dylan.

  7. shilpa Says:

    Hi tom, you are using xmltype to passinput.

    How can i pass strings ?I need to pass address,sity,state,zip_code as input parameters.
    Do i need to change it to XML document and send it or can i send it as strings instead?

  8. raghu Says:

    Hi Tom,

    It would be great if you can answer my as soon as you can . I am in deadly need of that .
    I have to pass input parameters to a web service , in WSDL , i have like 20 parameters but only some are required.
    How can i pass 5-6 input parameters to a webservice from pl/sql.

    Please reply me soon.

    • Tom Krueger Says:

      Hi Raghu,

      I have just added a new post at http://tomkrueger.wordpress.com/2010/02/02/how-to-call-a-web-service-from-oracle-plsql-part-ii/. Refer to the part of the code under “// Create service request xml”. You will notice that I have only a single parameter being passed in. I believe you should just need to modify it so that you pass in more parameters in the xml.

      For instance here I have added two more parameters.

      lx_serviceRequest := sys.xmltype(
      ‘<‘ || as_wsMethodName || ‘ xmlns=”‘ || ls_targetNamespace || ‘”>’
      || ‘<‘ || as_parmName1 || ‘>’
      || as_parmValue1
      || ‘</’ || as_parmName1 || ‘>’
      || ‘<‘ || as_parmName2 || ‘>’
      || as_parmValue2
      || ‘</’ || as_parmName2 || ‘>’
      || ‘<‘ || as_parmName3 || ‘>’
      || as_parmValue3
      || ‘</’ || as_parmName3 || ‘>’
      || ‘</’ || as_wsMethodName || ‘>’);

      Something like this should solve your problem.

      Good luck,
      Tom

  9. Atif Suleman Says:

    Hi Tom,

    i am again in search of ‘WCF service consumption in oracle’ as many of us looking for the same code.

    it would be great help if you share code at your earliest.

    for direct communication you can use: atifsul@hotmail.com

    Thanks in advance. Looking for your valuable input

    Atif Suleman

    • Tom Krueger Says:

      Sorry for being absent. As much as I would love to blog more, I continue to get caught up in the next thing and forget to blog about what I have learned. To somewhat help out, I have posted a better example of how to call a web service here: http://tomkrueger.wordpress.com/2010/02/02/how-to-call-a-web-service-from-oracle-plsql-part-ii/. This should be able to be used to call a WCF web service. I don’t have oracle available to me at current time so I cannot properly write the post. What you will want to do is navigate to the WSDL for your WCF web service to pull out the proper service name, method name, parameter name, etc. There are two points to doing this. First, to make sure that you have enabled the wsdl. Look up “mex” and WCF configuration and you should find details. You probably already have this though. Second, you want to look at the WSDL so you know that you have the proper names. Sometimes you may think you know the right name, but it ends up being different in the WSDL.

      Good luck.

      btw – feel free to continue to ask questions. I really do want to help.

      • Rathi Says:

        Hi Tom,

        I tried your example with modifications to call my WCF web service. I get the following error:
        ORA-29532: Java call terminated by uncaught Java exception: javax.xml.rpc.soap.SOAPFaultException: The message with Action ‘http://seattleschools.org/Transportation/AddStudentRequest2′ cannot be processed at the receiver, due to a ContractFilter mismatch at the EndpointDispatcher. This may be because of either a contract mismatch (mismatched Actions between sender and receiver) or a binding/security mismatch between the sender and the receiver. Check that sender and receiver have the same contract and the same binding (including security requirements, e.g. Message, Transport, None).
        I was able to call an ASP .Net web service (.asmx extension) with your PL/SQL but I’m not able to call a WCF web service (the one with a .svc extension). If you have a specific example of PL/SQL calling a .svc web service, could you please send it to me?

        Thanks in advance,
        Rathi

        • Tom Krueger Says:

          I wish I did. I ended up moving on from the position that I had created that code.

          Apologies,
          Tom.

    • Tom Krueger Says:

      Thanks for the feedback. I assume it did not work for you. Could you explain the problem or why it is useless.

  10. nitin Says:

    Hi Tom,

    We are creating interface between oracle 11i with SAP. The data will be trasferred between these 2 system using web services (Common integration cloud).
    My requirement is, value will be passed from oracle to SAP using web services and based on this value, return the results from SAP to oracle again. Can you please help with the code how can i achieve this?
    I am in great need of this code and quick reply from you will be very helpful.

    Regards,
    Nitin.

    • Tom Krueger Says:

      Hi Nitin,

      Sorry for the late response. Hopefully you were able to get it working. If now I can try to answer a few questions for you, but I currently don’t have an Oracle environment to code in.

      Tom

  11. Ravi de Silva Says:

    Hello, we are calling a web-service from our Oracle 10g form using a Javabean. Problem is, if the WS is not up or server is down the JavaBean tries to connect to it continuously. It does not give an error and exit. So we our Form gets stuck. What is the solution to this? How do we know if the WS is up or Server is up before we try to connect. Our JavaBean has no documentation. Is there any other way we can do this??

  12. JV Says:

    Thanks a lot ………..This comes as boon for me .I have an application calling ws from oracle implemnted by UTL_HTTP and need to inplement the same through UTL_DBWS.

  13. Anonymous Says:

    Hi All i tried the same code is working but i can’t log the soap messages on diagnostic log file, any idea?


  14. […] while back I posted on how to call a web service from Oracle PL/SQL here, but it was more of a basic starting point.  I had further enhanced the version to be more generic […]

  15. Nasiha Nigam Says:

    I’m truly enjoying the design and layout of your site. It’s a very easy on the eyes which
    makes it much more pleasant for me to come here and visit more often.

    Did you hire out a developer to create your theme? Excellent work!

    • Tom Krueger Says:

      Hi Nasiha,

      Thanks for the feedback. This is just a standard theme out of the box from WordPress.

      Take care,
      Tom


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: