HTTP POST from inside Oracle

A while ago Robert and Pete blogged about Oracle’s UTL_HTTP package. Robert gave an example of how to use this package to request a web page from the Internet into Oracle and Pete mentioned that you could do the opposite, i.e. post data from Oracle to the Internet. Recently I had a requirement to do just that, HTTP post data from the Oracle database to another web server. So, I wrote the following generic procedure util_pkg.http_post. Here it is followed by an example of how to use it (tested on 9.2):

First, the package specification:

create or replace 
package util_pkg
    is
    procedure http_post (
        p_url_in in varchar2
        ,p_data_in in clob
        ,p_data_type in varchar2 default 'text/xml'
        ,p_proxy_in in varchar2 default null
        ,p_no_proxy_domains_in  in varchar2 default null
        ,p_username_in in varchar2 default null
        ,p_password_in in varchar2 default null
    );
end;
/

Now the package body:

create or replace 
package body util_pkg
is
    procedure http_post (
        p_url_in in varchar2
        ,p_data_in in clob
        ,p_data_type in varchar2 default 'text/xml'
        ,p_proxy_in in varchar2 default null
        ,p_no_proxy_domains_in  in varchar2 default null
        ,p_username_in in varchar2 default null
        ,p_password_in in varchar2 default null
    ) is
        l_http_req utl_http.req;
        l_http_resp utl_http.resp;
        l_my_scheme varchar2(256);
        l_my_realm  varchar2(256);
        l_my_proxy  boolean;
    begin
        -- When going through a firewall, pass requests through this host.
        -- Specify sites inside the firewall that don't need the proxy host.
        if (p_proxy_in is not null) and
            (p_no_proxy_domains_in is not null)
        then
            utl_http.set_proxy(p_proxy_in, p_no_proxy_domains_in);
        end if;

        -- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
        -- rather than just returning the text of the error page.
        utl_http.set_response_error_check(false);

        -- Begin the post request
        l_http_req := utl_http.begin_request (p_url_in, 'POST');

        -- Set the HTTP request headers
        utl_http.set_header(l_http_req, 'User-Agent', 'Mozilla/4.0');
        utl_http.set_header(l_http_req, 'content-type', p_data_type);
        utl_http.set_header(l_http_req, 'content-length', length(p_data_in));

        -- Specify a user ID and password for pages that require them.
        if p_username_in is not null then
            utl_http.set_authentication(
                l_http_req, p_username_in, p_password_in);
        end if;

        -- Write the data to the body of the HTTP request
        utl_http.write_text(l_http_req, p_data_in);

        -- Process the request and get the response.
        l_http_resp := utl_http.get_response (l_http_req);

        dbms_output.put_line ('status code: ' || l_http_resp.status_code);
        dbms_output.put_line ('reason phrase: ' || l_http_resp.reason_phrase);

        -- Look for client-side error and report it.
        if (l_http_resp.status_code >= 400) and
            (l_http_resp.status_code <= 499)
        then
            -- Detect whether the page is password protected,
            -- and we didn't supply the right authorization.
            -- Note the use of utl_http.HTTP_UNAUTHORIZED, a predefined
            -- utl_http package global variable
            if (l_http_resp.status_code = utl_http.HTTP_UNAUTHORIZED) then
                utl_http.get_authentication(
                    l_http_resp, l_my_scheme, l_my_realm, l_my_proxy);
                if (l_my_proxy) then
                    dbms_output.put_line('Web proxy server is protected.');
                    dbms_output.put(
                        'Please supply the required ' ||
                        l_my_scheme ||
                        ' authentication username/password for realm ' ||
                        l_my_realm ||
                        ' for the proxy server.');
                else
                    dbms_output.put_line(
                        'Web page ' || p_url_in || ' is protected.');
                    dbms_output.put(
                        'Please supplied the required ' ||
                        l_my_scheme ||
                        ' authentication username/password for realm ' ||
                        l_my_realm ||
                        ' for the Web page.');
                end if;
            else
                dbms_output.put_line('Check the URL.');
            end if;
            utl_http.end_response(l_http_resp);
            return;

        -- Look for server-side error and report it.
        elsif (l_http_resp.status_code >= 500) and
                (l_http_resp.status_code <= 599)
        then
            dbms_output.put_line('Check if the Web site is up.');
            utl_http.end_response(l_http_resp);
            return;
        end if;
        utl_http.end_response (l_http_resp);

    exception
        when others then
            dbms_output.put_line (sqlerrm);
            raise;
    end http_post;

end;
/

And here is an example of how to use it. Replace the URL and the query below with your stuff. Since I am posting XML, I used dbms_xmlgen.getxml to transform the result of the query to XML:

declare
    l_url varchar2 (200) := 'http://www.example.com/post.cfm';
    l_data clob;
    l_query varchar2(32767);    
begin
    l_query := '
    select object_name, created, status 
    from all_objects
    where rownum <=2
    ';      
    l_data := dbms_xmlgen.getxml(l_query);
    util_pkg.http_post(
        p_url_in => l_url, p_data_in => l_data);
end;
/

On the receiving end (http://www.example.com/post.cfm for example) I simply read the posted data and insert it into a table. This can be accomplished fairly easily in ColdFusion (You may also use .NET, JSP or whatever server technology you have):

<cfset x = GetHttpRequestData()>
<cfquery name="q" datasource="ora92">
    insert into test values ('#x.content#')
</cfquery>

And finally, here is what gets inserted into the table:

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <OBJECT_NAME>I_CDEF1</OBJECT_NAME>
  <CREATED>05/12/2000</CREATED>
  <STATUS>VALID</STATUS>
 </ROW>
 <ROW>
  <OBJECT_NAME>IND$</OBJECT_NAME>
  <CREATED>05/12/2000</CREATED>
  <STATUS>VALID</STATUS>
 </ROW>
</ROWSET>

Pretty neat!

Resources:

Oracle9i Supplied PL/SQL Packages

Retrieving the Contents of an HTTP URL from PL/SQL

UTL_HTTP on AskTom

HTTP Communication from Within the Oracle Database

Possibly related:

Tagged , , , | Post a Comment