title: Processing JSON object via Webservice in PL/SQL
body: <p>I am developing a test Oracle Procedure to receive a JSON object from a locally XAMPP hosted web-service. Followed below steps.</p>
  
  <p>(01) <strong>Created the ACL</strong></p>
  
  <pre><code>set define off;
  BEGIN
  SYS.DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         =&gt; 'www.xml',
                                      description =&gt; 'TEST JSON',
                                      principal   =&gt; 'TEST_USER',
                                      is_grant    =&gt; true,
                                      privilege   =&gt; 'connect',
                                      start_date   =&gt; SYSTIMESTAMP,
                                      end_date     =&gt; NULL);
  
  SYS.DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       =&gt; 'www.xml',
                                         principal =&gt; 'TEST_USER',
                                         is_grant  =&gt; true,
                                         privilege =&gt; 'resolve');
  
  SYS.DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  =&gt; 'www.xml',
                                      host        =&gt; 'localhost',
                                      lower_port  =&gt; 8081,
                                      upper_port  =&gt; NULL);
  END;
  </code></pre>
  
  <p>(02) <strong>Following is the test proc</strong></p>
  
  <pre><code>create or replace PROCEDURE JSON11 IS
    l_param_list     VARCHAR2(512);
    l_http_request   UTL_HTTP.req;
    l_http_response  UTL_HTTP.resp;
    l_response_text  VARCHAR2(32767);
    l_list system.json_list;
    acl            VARCHAR2(100);
    url            VARCHAR2(1000) := 
    'http://localhost:8081/PhpProjectFirst/displayValuesSent.php?
    name=name11&amp;email=name11@gmail.com&amp;gender=male&amp;course=OCA2&amp;class=2008&amp;s
    ubject=PL/SQL';
  
  BEGIN
      DBMS_OUTPUT.put_line('start');
      l_http_request := utl_http.begin_request(url, 'POST','HTTP/1.1');
      DBMS_OUTPUT.put_line('aa');
      --utl_http.set_header(l_http_request, 'user-agent', 'mozilla/4.0'); 
      utl_http.set_header(l_http_request, 'Content-Type', 'application/json'); 
      --utl_http.set_header(l_http_request, 'Content-Length', 
      length(content));    
      --utl_http.write_text(l_http_request, content);
      DBMS_OUTPUT.put_line('bb');
      l_http_response := utl_http.get_response(l_http_request);
      DBMS_OUTPUT.put_line('cc');
  
      -- process the response from the HTTP call
      begin
          loop
              utl_http.read_line(l_http_response, l_response_text);
              dbms_output.put_line(l_response_text);
          end loop;
          utl_http.end_response(l_http_response);
  
      end;   
  exception
      when utl_http.end_of_body then
          utl_http.end_response(l_http_response);
      when others then
          DBMS_OUTPUT.put_line('Error &gt;&gt; '||sqlerrm);
  END JSON11;
  </code></pre>
  
  <p>(03) <strong>Called the proc</strong></p>
  
  <pre><code>set serveroutput on size 30000;
  BEGIN
    JSON11();
  END;
  </code></pre>
  
  <p><strong>But end with below error.</strong></p>
  
  <pre><code>Error &gt;&gt; ORA-29273: HTTP request failed
  ORA-06512: at "SYS.UTL_HTTP", line 1130
  ORA-29270: too many open HTTP requests
  </code></pre>
  
  <p>DB is Oracle XE and using Oracle SQL Developer.
  Webservice is very simple and run via XAMPP and returns a simple JSON object.</p>
  
  <p>Any tips could be shared?</p>
  
  <p>Regards,
  LM</p>