Saturday, July 7, 2012

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;
    utl_http.set_response_error_check(enable => TRUE);

    -- this is your RESTful API's URL where you would POST data
    v_url := '';

    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 := '';
        utl_http.read_text(r => resp, data => v_msg);
        xmlData := xmlData || v_msg;
      end loop;
      when utl_http.end_of_body then
    -- 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.


  1. Do you have an example of the RESTful application that consumes the parameters? I'm working on a similar project and would appreciate seeing an example.

  2. Sure. What programming language are you using?

  3. I simply wanted to write down a quick word to say thanks to you for those wonderful tips and hints you are showing on this site."Oracle Training in Bangalore"

  4. Hey, I just want to ask, are you using word press for this website? It's super smooth and easy to use! By the way, if you need to register a business, let me know, I know the best business incorporation, register a business now!

  5. I ‘d mention that most of us visitors are endowed to exist in a fabulous place with very many wonderful individuals with very helpful things.

    oracle training in bangalore

  6. wow great Article, the details you have provided are much clear, easy to understand, if you post some more Article, it will be very much useful for me.
    PL/SQL Training in Chennai

  7. Very useful information to everyone thanks for sharing, learn the latest updated Technology at Best Training institutions
    Salesforce Lightning is the latest updated technology
    Salesforce Online Training in Bangalore
    Salesforce Training online in India