Processing JSON object via Webservice in PL/SQL

Question

I am developing a test Oracle Procedure to receive a JSON object from a locally XAMPP hosted web-service. Followed below steps.

(01) Created the ACL

set define off;
BEGIN
SYS.DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
                                    description => 'TEST JSON',
                                    principal   => 'TEST_USER',
                                    is_grant    => true,
                                    privilege   => 'connect',
                                    start_date   => SYSTIMESTAMP,
                                    end_date     => NULL);

SYS.DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
                                       principal => 'TEST_USER',
                                       is_grant  => true,
                                       privilege => 'resolve');

SYS.DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host        => 'localhost',
                                    lower_port  => 8081,
                                    upper_port  => NULL);
END;

(02) Following is the test proc

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&[email protected]&gender=male&course=OCA2&class=2008&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 >> '||sqlerrm);
END JSON11;

(03) Called the proc

set serveroutput on size 30000;
BEGIN
  JSON11();
END;

But end with below error.

Error >> ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29270: too many open HTTP requests

DB is Oracle XE and using Oracle SQL Developer. Webservice is very simple and run via XAMPP and returns a simple JSON object.

Any tips could be shared?

Regards, LM

Answer 1

You can have a maximum of 5 HTTP requests per session. With each run of the procedure you are creating one new connection but you do not always close it. You are closing your connections only in when utl_http.end_of_body then, but you should also close it in when others then.