Akdora’s Blog

Programming, Oracle, Life, Fun

Calling a web service by PL/SQL (utl_http) August 3, 2007

Filed under: Oracle — Akdora @ 1:49 pm
Tags: , , , , , , , , , ,

Last day, a mission is given to me about calling a web services by pl/sql. Firstly, i started to search ; “What is a web service?” The term Web services describes a standardized way of integrating Web-based applications using the XML, SOAP, WSDL and UDDI open standards over an Internet protocol backbone. Actually we can summarize it;

Web Services can convert your applications into Web-applications.
By using Web services, your application can publish its function or message to the rest of the world.

Web Services can be used by other applications.
With Web services your accounting department’s Win 2k servers can connect with your IT supplier’s UNIX server.

The basic Web Services platform is XML + HTTP.
Web services uses XML to code and decode your data and SOAP to transport it.

We have some input parameters and make a request. Then, we get a response from it with output parameters. All this syntax is in XML format. So, we will use XMLtype in our pl/sql package.

 

Let’s start to write a soap access package or we can use this package🙂 (www.oracle-base.com/dba/miscellaneous/soap_api.sql)

🙂 Let’s assume we have a function “myFunction” in this page http://www.mywebservice.com/web🙂 Then,start to write our code

 

 

 CREATE OR REPLACE PACKAGE pkg_my_webservice IS
     FUNCTION call_myfuntion
     (
          vp_parameter1 VARCHAR2,
          vp_parameter2 VARCHAR2
     ) RETURN VARCHAR2;
END pkg_my_webservice;
/
CREATE OR REPLACE PACKAGE BODY pkg_my_webservice IS
     vg_funciton_fnc VARCHAR2(256) := 'myFunction';
     vg_ws_address   VARCHAR2(255) := 'http://www.mywebservice.com/web';
     FUNCTION call_myfuntion(
                             --lets assume that it inputs two parameters called string1, string2
                             vp_parameter1 VARCHAR2,
                             vp_parameter2 VARCHAR2)
     
      RETURN VARCHAR2 AS
          ol_req  soap_api.t_request;
          ol_resp soap_api.t_response;
     BEGIN
          -- we initilize a new request
          ol_req := soap_api.new_request(vg_funciton_fnc,
                                         'xmlns="' || vg_ws_address || '"');
          -- we started to add parameters
          soap_api.add_parameter(ol_req,
                                 'string1',
                                 'partns:string',
                                 vp_parameter1);
                                 
          soap_api.add_parameter(ol_req,
                                 'string2',
                                 'partns:string',
                                 vp_parameter1);
                                 
          -- we call the web service
          ol_resp := soap_api.invoke(ol_req, vg_ws_address, vg_funciton_fnc);
          -- we get back the results
          RETURN soap_api.get_return_value(ol_resp,
                                           'result', -- result tag name
                                           'xmlns:m="' || --can be change as "xmlns:n1"
                                           vg_ws_address || '"');
     
     END call_myfuntion;
END pkg_my_webservice;
/

 

pkg_my_webservice_blog.txt

You may need to change some tag attributes. The main basic function syntax is like this. If you have more than one return xml tags, then you need to call the get_return_value function as much as result attributes. You may also store the results in a pl/sql type and return it back.

There are also other ways to do this. You may also use oracle’s “utl_dbws” package, however “utl_http” is more faster than it. You can test it.

In my next post, i am gonna explain soap_api package in more details.

 

21 Responses to “Calling a web service by PL/SQL (utl_http)”

  1. anjali Says:

    wat if an authenticated web service has to be called using pl/sql procedure where user name password has 2 b passed as parameters???

  2. Bala Says:

    Hi,
    Thanks for your example. I am getting the following error.

    ERROR at line 1:
    ORA-31011: XML parsing failed
    ORA-19202: Error occurred in XML processing
    LPX-00007: unexpected end-of-file encountered
    ORA-06512: at “SYS.XMLTYPE”, line 0
    ORA-06512: at “SOAP_API”, line 143
    ORA-06512: at “PKG_MY_WEBSERVICE”, line 29

    URL which I have given is http://10.10.217.134:5568/ProcessDefinitions/Testing/TestServer/GSCP/GSCP-service.serviceagent?wsdl

    I can able to access the above URL in IE. Do I need to add something in this URL when I am giving in PL/SQL. Please help me to solve the issue.

    With Regards,
    Bala

  3. Sundar Says:

    Hi…
    Thanks for info…
    I wish to know whether oracle 8i supports webservices.

  4. vj Says:

    Hi,
    I am getting following error when I use your api
    8:06:10 AM Execution failed: ORA-31011: XML parsing failed
    8:06:10 AM ORA-19202: Error occurred in XML processing
    8:06:10 AM LPX-00260: invalid xml:space attribute declaration

    Just wondering if anyone else got this error

  5. Prakash Reddy Says:

    anyone who can suggest about calling WCF service from Oracle 9i.
    I am able to call webservice from 9i but while trying to call WCF its givig status_code – 415 format not supported

  6. Zack Says:

    Thanks for the post.. Exactly what I was looking for. I can’t wait to try it out myself.

  7. Sudha Says:

    Hi thanks for the code. My requirement is slightly different. I have a servlet in java that gets the doc id and queries the db for the blob and writes it on the response.getOutputStream.
    Now for some reasons, I have to call this servlet from my db procedure, via UTL_HTTP. I am able to call the servlet but it always fails at response.getOutputStream().write( data );
    and the error I get in sqlplus is
    ORA-06502: PL/SQL: numeric or value error: hex to raw conversion error
    ORA-06512: at “ADMSDBA.UTIL_PKG”, line 130
    No idea. but i guess the blob i am trying to write onto response is not taking it.

  8. getting error while compiling the fuction in oracle. I have soap_api packages also in oracle. could you provide where I am mistaking ?

    ol_req soap_api.t_request;
    ol_resp soap_api.t_response;

    PACKAGE TEST.PKG_MY_WEBSERVICE
    On line: 10
    PLS-00103: Encountered the symbol “OL_REQ” when expecting one of the following:

    language

  9. k Says:

    Hi,

    I have tried your code but I am getting the NULL SELF error.

    I am calling this webservice http://www.webservicex.net/CurrencyConvertor.asmx?WSDL

    Appreciate if you could help me with this

  10. […] to handle XML complex types is non-existent. I was using the SOAP_API for simple types (examples here and here) but as soon as we get into more complex types are we really left with no simple option? I […]

  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. Ahmad Abo Aljamal Says:

    please help
    am call web service from PL/SQL , but there error
    ORA-30625: method dispatch on NULL SELF argument is disallowed
    ORA-06512: at “sys.SOAP_API”, line 117
    ORA-06512: at “sys.SOAP_API”, line 157
    ORA-06512: at “sys.PKG_MY_WEBSERVICE”, line 55

    • Akdora Says:

      This means; PL/SQL cannot parse the response xml. It happens when PL/SQL cannot access the ws endpoint (url).
      Probably you are getting a 404 response.

  13. “Calling a web service by PL/SQL (utl_http) Akdoras Blog” in
    fact got myself hooked with ur website! I personallywill
    certainly be back more frequently. Thanks ,Valarie

  14. Charlie Says:

    Exactly how much time did it require u to compose
    “Calling a web service by PL/SQL (utl_http) | Akdora’s Blog” traduke ? It features a good deal of good material. Many thanks -Swen

  15. […] Last day, a mission is given to me about calling a web services by pl/sql. Firstly, i started to search ; “What is a web service?” The term Web … more… […]

  16. A little bit of help over here, plase:

    ORA-29273: HTTP request failed
    ORA-06512: at “SYS.UTL_HTTP”, line 1029
    ORA-12535: TNS:operation timed out
    ORA-06512: at “SOAP_API”, line 150
    ORA-06512: at “ADD_NUMBERS”, line 34


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