6 Replies Latest reply: Mar 5, 2013 1:06 PM by 989009 RSS

    How to access web service from Oracle ?

    989009
      Database version: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

      I am trying to make a call to a web service through a procedure/function...

      I tried to use
      CREATE OR REPLACE PROCEDURE Call_Rest_Webservice
      
       AS
      
        t_Http_Req Utl_Http.Req;
      
        t_Http_Resp Utl_Http.Resp;
      
        t_Request_Body VARCHAR2(30000);
      
        t_Respond VARCHAR2(30000);
      
        t_Start_Pos INTEGER := 1;
      
        t_Output VARCHAR2(2000);
      
      BEGIN
      
        /*Construct the information you want to send to the webservice.
        
        Normally this would be in a xml structure. But for a REST-
        
        webservice this is not mandatory. The webservice i needed to
        
        call excepts plain test.*/
      
        t_Request_Body := 'the data you want to send to the webservice';
      
        /*Telling Oracle where the webservice can be found, what kind of request is made
        
        and the version of the HTTP*/
      
        t_Http_Req := Utl_Http.Begin_Request('**webservice address**',
                                             'GET',
                                             'HTTP/1.1');
      
        /*In my case the webservice used authentication with a username an password
        
        that was provided to me. You can skip this line if it's a public webservice.*/
      
        --Utl_Http.Set_Authentication(t_Http_Req, 'username', 'password');
      
        /*Describe in the request-header what kind of data is send*/
      
        Utl_Http.Set_Header(t_Http_Req, 'Content-Type', 'text/xml charset=UTF-8');
      
        /*Describe in the request-header the lengt of the data*/
      
        Utl_Http.Set_Header(t_Http_Req, 'Content-Length', Length(t_Request_Body));
      
        /*Put the data in de body of the request*/
      
        Utl_Http.Write_Text(t_Http_Req, t_Request_Body);
      
        /*make the actual request to the webservice en catch the responce in a
        
        variable*/
      
        t_Http_Resp := Utl_Http.Get_Response(t_Http_Req);
      
        /*Read the body of the response, so you can find out if the information was
        
          received ok by the webservice.
        
          Go to the documentation of the webservice for what kind of responce you
        
          should expect. In my case it was:
        
          <responce>
        
            <status>ok</status>
        
          </responce>
        
        */
      
        Utl_Http.Read_Text(t_Http_Resp, t_Respond);
      
        /*Some closing?1 Releasing some memory, i think....*/
      
        Utl_Http.End_Response(t_Http_Resp);
      
      END;
      But it gave me ORA-29272: HTTP request failed
      ORA-06512: at "SYS.UTL_HTTP", line 1029
      ORA-12545: Connect failed because target host or object does not exist

      But I can link to the web server by going to **webservice address** through my browser.

      Is there any ACL should be open in order to have this capability? I asked my DBA but she asked me I will need to give her username/password/ip in order to open an ACL...
      however there is no username/password required while visiting the web service...

      Any help would be highly appreciated..

      Thanks,

      Edited by: 986006 on Mar 4, 2013 8:38 AM
        • 1. Re: How to access web service from Oracle ?
          989009
          Could anyone help please thanks!
          • 2. Re: How to access web service from Oracle ?
            989009
            any help is highly appreciated!!

            Edited by: 986006 on Mar 4, 2013 1:53 PM
            • 3. Re: How to access web service from Oracle ?
              Billy~Verreynne
              Y.L wrote:

              But it gave me ORA-29272: HTTP request failed
              ORA-06512: at "SYS.UTL_HTTP", line 1029
              ORA-12545: Connect failed because target host or object does not exist
              That is because the database was unable to connect to the specified server. Wrong hostname or IP specified. Failure to resolve the hostname into an IP. Firewall blocking. Etc.
              Is there any ACL should be open in order to have this capability?
              On 11g. Not on 10g.
              Any help would be highly appreciated..
              See examples {message:id=10448611} and {message:id=10158148}.
              • 4. Re: How to access web service from Oracle ?
                989009
                First, thank you Billy for your response... I have been waiting for a whole day for someone to respond my post... I do appreciate you would like to help...
                That is because the database was unable to connect to the specified server. Wrong hostname or IP specified. Failure to resolve the hostname into an IP. Firewall blocking. Etc.
                The hostname I put there can be visit through my browser. I think it should not be the host server's issue... So it could be a firewall on my database side that needs to be open in order to visit the hostserver?
                On 11g. Not on 10g.
                I saw the example code you post through the link you provided... will those only work on 11g?
                If it can work on 10g, what package or what steps I need to follow in order to have that working on me?

                Thanks,
                • 5. Re: How to access web service from Oracle ?
                  Billy~Verreynne
                  Y.L wrote:
                  That is because the database was unable to connect to the specified server. Wrong hostname or IP specified. Failure to resolve the hostname into an IP. Firewall blocking. Etc.
                  The hostname I put there can be visit through my browser. I think it should not be the host server's issue... So it could be a firewall on my database side that needs to be open in order to visit the hostserver?
                  The "web browser" code (your PL/SQL code using UTL_HTTP) runs on the Oracle database server. It needs the same type of network access that your browser on your PC enjoys. (firewalls opened, proxy authentication if required, etc).
                  On 11g. Not on 10g.
                  I saw the example code you post through the link you provided... will those only work on 11g?
                  If it can work on 10g, what package or what steps I need to follow in order to have that working on me?
                  The code I've posted will work on both versions. My comment was regards to ACLs. No ACLs existed on 10g. So you only need execute privs on the relevant packages (e.g. UTL_HTTP, etc).

                  ACLs were introduced with 11g - so on 11g you also need the DBA to create an ACL for you that will allow UTL_HTTP access to the network.
                  • 6. Re: How to access web service from Oracle ?
                    989009
                    Thanks... that makes sense to me... Thanks so much for your kindly help