How to run http web service request from pl/sql pa

Question

I have developed a new procedure to call web service but its showing Oracle adaptor error when I run it. Could you please guide me how can I resolve this error? Is teher something wrong below code?

/* Formatted on 17/07/2014 16:49:02 (QP5 v5.185.11230.41888) */
CREATE OR REPLACE PROCEDURE APPS.xx_web_id
IS
l_http_request    UTL_HTTP.req;
l_http_response   UTL_HTTP.resp;
l_response_text   VARCHAR2 (10000);
BEGIN
-- preparing request
l_http_request :=
  UTL_HTTP.begin_request ('https://freegeoip.net/xml/82.39.109.147',
                          'POST',
                          'HTTP/1.1');

l_http_response := UTL_HTTP.get_response (l_http_request);

UTL_HTTP.read_text (l_http_response, l_response_text);

DBMS_OUTPUT.put_line (l_response_text);

UTL_HTTP.end_response (l_http_response);
EXCEPTION
WHEN UTL_HTTP.end_of_body
THEN
  UTL_HTTP.end_response (l_http_response);
END;

Answer 1

First you should configure ACL ;

BEGIN
dbms_network_acl_admin.create_acl(acl => 'pkg.xml',
                                description => 'Normal Access',
                                principal => 'CONNECT',
                                is_grant => TRUE,
                                privilege => 'connect',
                                start_date => NULL,
                                end_date => NULL);
    END;
    /

    BEGIN
    dbms_network_acl_admin.add_privilege(acl => 'utlpkg.xml',
                                   principal => 'XXXX', --- user name
                                   is_grant => TRUE,
                                   privilege => 'connect',
                                   start_date => NULL,
                                   end_date => NULL);
      END;
      /


   BEGIN
      dbms_network_acl_admin.assign_acl(acl => 'pkg.xml',
                                host => 'HOST ADDRESS',--  Web Server URL
                                lower_port => 7222, -- Web  Server PORTs
                                upper_port => 7222);
    END;
   /

Then you should create procedure which will be called web service. Sample that i used you may find in below;

CREATE OR REPLACE PROCEDURE web_service_call()
AS

l_http_request     UTL_HTTP.req;
l_http_response    UTL_HTTP.resp;
l_buffer_size      NUMBER (10)      := 512;
l_line_size        NUMBER (10)      := 50;
l_lines_count      NUMBER (10)      := 20;
l_clob_request   CLOB;
l_line             VARCHAR2 (32767);
l_substring_msg    VARCHAR2 (32767);
l_raw_data         RAW (512);
l_clob_response    CLOB;
l_host_name        VARCHAR2 (128)   := 'get.bla.bla';
l_port             VARCHAR2 (128)   := '7222'; 
req_length binary_integer;
bufferY   varchar(2000);
amount  pls_integer:=2000;
offset  pls_integer:=1;

BEGIN

  l_clob_request := 'xml'; 
  l_http_request :=
     UTL_HTTP.begin_request (url               =>    'http://'
                                                  || l_host_name
                                                  || ':'
                                                  || l_port
                                                  || '/Something/GetSomething',
                             method            => 'POST',
                             http_version      => 'HTTP/1.1'
                            );
  UTL_HTTP.set_header (l_http_request, 'User-Agent', 'Mozilla/4.0');
  UTL_HTTP.set_header (l_http_request,
                       'Host',
                       l_host_name || ':' || l_port
                      );
  UTL_HTTP.set_header (l_http_request, 'Connection', 'close');
  utl_http.set_header(l_http_request, 'Content-Type', 'text/xml;charset=ISO-8859-9'); 
  UTL_HTTP.set_header (l_http_request, 'SOAPAction', '"sayHello"');
  UTL_HTTP.set_header (l_http_request,
                       'Content-Length',
                       LENGTH (l_clob_request)
                      );   

        req_length := DBMS_LOB.getlength (l_clob_request);
        offset :=1;
        bufferY :=null;
        amount:=2000;

  WHILE (offset < req_length)
   LOOP
      DBMS_LOB.read (l_clob_request,
                     amount,
                     offset,
                     bufferY);
      UTL_HTTP.write_text (l_http_request, bufferY);
      offset := offset + amount;

   END LOOP;  
      l_http_response := UTL_HTTP.get_response (l_http_request); 
      l_clob_response :=null;
      BEGIN

         <<response_loop>>
         LOOP
            UTL_HTTP.read_raw (l_http_response, l_raw_data, l_buffer_size);
            l_clob_response :=
                     l_clob_response || UTL_RAW.cast_to_varchar2 (l_raw_data);
         END LOOP response_loop;
      EXCEPTION
         WHEN UTL_HTTP.end_of_body
         THEN
            UTL_HTTP.end_response (l_http_response);
      END; 

  IF l_http_request.private_hndl IS NOT NULL
  THEN
     UTL_HTTP.end_request (l_http_request);
  END IF;

  IF l_http_response.private_hndl IS NOT NULL
  THEN
     UTL_HTTP.end_response (l_http_response);
  END IF; 
END;
 /