Brothers In Code

...a serious misallocation of .net resources

HTTP Requests With Oracle - Part 1

Making a web request from oracle is as easy as:


select utl_http.request('www.google.com')
from dual;

But if you're making a request from Oracle, chances are you need to do a little work and not just google something so here's a little pl/sql code...


declare
  l_url varchar2(4000) := 'www.google.com';
 
  l_httpRequest   utl_http.req;
  l_httpResponse  utl_http.resp;
  l_tempResponse clob;
  l_buffer varchar2(32767);
begin  
  utl_http.set_detailed_excp_support (true);

  --setup the http connection
  l_httpRequest := UTL_HTTP.begin_request(l_url, 'GET','HTTP/1.1');

  l_httpResponse := utl_http.get_response(l_httpRequest);
  
  --write the response (in blocks) to a temporary clob
  dbms_lob.createtemporary(l_tempResponse, FALSE);
  BEGIN
    LOOP
       UTL_HTTP.read_text(l_httpResponse, l_buffer, 32767);
       dbms_lob.writeappend(l_tempResponse, length(l_buffer), l_buffer);
    END LOOP;
  EXCEPTION
    WHEN utl_http.end_of_body THEN NULL;
  END;
  UTL_HTTP.end_response(l_httpResponse);

  DBMS_OUTPUT.PUT_LINE('Response: ' || l_tempResponse);

end;
/

It would be a simple job to add a couple of query string parameters on to the URL.  But it's very likely you'll be sending data to some sort of a web service and then expecting a response.

Here's an example of a POST request.


  ....
  l_httpRequest := UTL_HTTP.begin_request(l_serviceUrl, 'POST','HTTP/1.1');
  utl_http.set_header(l_httpRequest, 'Content-Type', 'application/x-www-form-urlencoded');
 
  --these are likely constant for the application
  l_postData :=
    'x_login='|| 'xxxxxx' 
    || '&x_tran_key='|| 'yyyyyyy'
    || '&x_delim_data='|| 'TRUE'

   utl_http.set_header(l_httpRequest, 'Content-Length', length(l_postData));


  utl_http.write_text(l_httpRequest, l_postData);
  --get a reference to the response
  l_httpResponse := utl_http.get_response(l_httpRequest);
  ....

That's it.  In Part 2 I'll go over some of the finer points of SSL requests and the Oracle Wallet.