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

20 comments:

  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.

    ReplyDelete
    Replies
    1. The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Projects for CSE.

      Software management seeks for decision support to identify technologies like JavaScript that meet best the goals and characteristics of a software project or improvement programme. JavaScript Training in Chennai Accessible experiences and repositories that effectively guide that technology selection are still lacking.

      Aim of technology domain analysis is to describe the class of context situations (e.g., kinds of JavaScript software projects) in which a software engineering technology JavaScript Training in Chennai can be applied successfully

      The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete
  2. Sure. What programming language are you using?

    ReplyDelete
  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"

    ReplyDelete
  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!

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

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

    oracle training center in chennai

    oracle training chennai

    ReplyDelete
  9. 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.
    Anexas
    Six Sigma Training in Abu Dhabi
    Six Sigma Training in Dammam
    Six Sigma Training in Riyadh

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

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

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

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

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

    ReplyDelete