HTTP POST commands within Oracle's PL/SQL

Sometimes for system integration it's pretty interesting to perform POST (or GET) HTTP request within Oracle's PL/SQL. It's pretty straightforward, but I have a hard time looking for a specific example (at least for POST) in Oracle's documentation.

OK. But why on earth you would like to use PL/SQL to perform a HTTP POST request!? Turns out, there still are a lot of software shops or businesses with a software department with extremely highly skilled Oracle developers, that need to focus on solving complex business issues with a database instead of learning the new cool programming language that you can use to do this exact same task with two lines of code. For them, a PL/SQL wrapper around a RESTful API is pretty convenient.

So, here's what I did to perform a POST passing two parameters along:

create or replace
package wsaccess_pkg as

  /* 
     suppose your RESTful API requires a POST with two parameters: id, name  
     and it returns a string containing an xml
  */
  
  -- id number
  -- name string
  
  function executews
  (
      p_id number,
      p_name varchar2
  )
  return varchar2;
  
end wsaccess_pkg;

create or replace
package body wsaccess_pkg as

  function executews
    (
      p_id number,
      p_name varchar2
    )
  return varchar2 AS
    postData clob;
    xmlData clob;
    v_url varchar2(500);
    v_msg varchar(512);
    req utl_http.req;
    resp utl_http.resp;
  begin
  
    utl_http.set_response_error_check(enable => TRUE);

    -- this is your RESTful API's URL where you would POST data
    v_url := 'http://www.example.com/api/scoreCard';

    postData := '';
    postData := postData || 'id=' || p_id || '&';
    postData := postData || 'name=' || p_name || '&';
    
    -- begin request stating that method will be POST
    req := utl_http.begin_request(url => v_url, method => 'POST');
    
    -- setup request's headers. you need both: Content-Type and Content-Length
    utl_http.set_header(req, 'Content-Type', 'application/x-www-form-urlencoded');
    utl_http.set_header(req, 'Content-Length', length(postData));
    
    -- optionally, you might want to set User Agent header
    utl_http.set_header(req, 'User-Agent', 'MyCoolHttpClientFromWSAccessPkg/1.5');
    
    -- send data: this function handles URL encoding for us!
    utl_http.write_text(req, postData);
    
    -- read the response
    resp := utl_http.get_response(r => req);
    xmlData := '';
    begin
      loop
        utl_http.read_text(r => resp, data => v_msg);
        xmlData := xmlData || v_msg;
      end loop;
    exception
      when utl_http.end_of_body then
        null;
    end;
    
    -- end response
    utl_http.end_response(r => resp);
    
    return xmlData;
  end executews;

end wsaccess_pkg;

You could also parse the XML response and return a pure PL/SQL object. That's even better. But I would probably talk about in a separate blog entry.