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. 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

  5. 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

  6. Great blog! Really awesome I got more information from this blog. Thanks for sharing with us.

    oracle training center in chennai

    oracle training chennai

  7. Very nice post here and thanks for it .I always like and such a super contents of these post.Excellent and very cool idea and great content of different kinds of the valuable information's.
    Good discussion. Thank you.
    Six Sigma Training in Abu Dhabi
    Six Sigma Training in Dammam
    Six Sigma Training in Riyadh

  8. Great blog thanks for sharing Looking for the best creative agency to fuel new brand ideas? Adhuntt Media is not just a digital marketing company in chennai. We specialize in revamping your brand identity to drive in best traffic that converts. Buckle up for a ride that is going to be filled with SEO, Social media marketing, unique Graphic & Logo design and efficient ads strike the perfect chord! Join the Adhuntt Media adventure right at Adhuntt Media.
    digital marketing company in chennai

  9. Nice blog thanks for sharing Is this a special day for you? Beautiful and fragrant flowers are sure to make it even more amazing of a day no doubt. This is why Karuna Nursery Gardens offers you the best rental plants in Chennai that too at drop dead prices.
    plant nursery in chennai

  10. Excellent blog thanks for sharing Run your salon business successfully by tying up with the best beauty shop in Chennai - The Pixies Beauty Shop. With tons of prestigious brands to choose from, and amazing offers we’ll have you amazed.
    beauty Shop in Chennai

  11. Very useful blog thanks for sharing IndPac India the German technology Packaging and sealing machines in India is the leading manufacturer and exporter of Packing Machines in India.

  12. Excellent Blog! I would Thanks for sharing this wonderful content.its very useful to us.There is lots of Post about Python But your way of Writing is so Good & Knowledgeable. I gained many unknown information, the way you have clearly explained is really fantastic.keep posting such useful information.
    hadoop training in chennai

    hadoop training in tambaram

    salesforce training in chennai

    salesforce training in tambaram

    c and c plus plus course in chennai

    c and c plus plus course in tambaram

    machine learning training in chennai

    machine learning training in tambaram

  13. Worth reading! Our experts also have given detailed inputs about these trainings & courses! Presenting here for your reference. Do checkout Oracle Training In Chennai , Oracle PLSQL Training In Chennai , Oracle DBA Training In Chennai , AWS Training in Chennai & enjoy learning more about it.

  14. Study Amazon Web Services for making your career as a shining sun with Infycle Technologies. Infycle Technologies is the best AWS training institute in Chennai, providing complete hands-on practical training of professional specialists in the field. In addition to that, it also offers numerous programming language tutors in the software industry such as Oracle, Python, Big Dat, Hadoop, etc. Once after the training, interviews will be arranged for the candidates, so that, they can set their career without any struggle. Of all that, 200% placement assurance will be given here. To have the best career, call 7502633633 to Infycle Technologies and grab a free demo to know more.No.1 AWS Training Institute in Chennai | Infycle Technologies

  15. Infycle Technologies, the No.1 software training institute in Chennai offers the No.1 Big Data Hadoop Training in Chennai | Infycle Technologies for students, freshers, and tech professionals. Infycle also offers other professional courses such as DevOps, Artificial Intelligence, Cyber Security, Python, Oracle, Java, Power BI, Selenium Testing, Digital Marketing, Data Science, etc., which will be trained with 200% practical classes. After the completion of training, the trainees will be sent for placement interviews in the top MNC's. Call 7502633633 to get more info and a free demo.

  16. Nice thanks for sharing informative post like this keep posting if like more details visit my website link