msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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


Filed in ColdFusion, Oracle on 30 Nov 05 | Tags: , , ,


Reader's Comments

  1. |

    Hi,

    Do you know why I’m getting Null values for all the parameters, I’m sending as part of POST ?

    My code looks like :

    l_request := UTL_HTTP.begin_request(l_servlet_uri, ‘POST’,UTL_HTTP.HTTP_VERSION_1_0);

    UTL_HTTP.SET_HEADER(l_request,’Content-Type’,'text/xml’); UTL_HTTP.SET_HEADER(l_request,’content-length’,length(l_context_params));

    Utl_Http.Write_Text(l_request,l_context_params);

    l_context_params contains all the parameters separated by ‘&’.

    In the servlet when I try to say getParameter for individual parameters, I get Null values for everything.

    Please advice.

  2. |

    Meg, in your line:

    UTL_HTTP.SET_HEADER(l_request,’content-length’,length(l_context_params));

    I believe that l_context_params should be the parameter containing the actual data (clob maybe?) that needs to be posted.

    Then again, I have no idea about the structure of your programs, so I’m just guessing here.

  3. |

    Meg,

    Did you ever get this solved? I’m running into the same problem.

    Thanks!

  4. |

    Hi

    I am using the following procdedure passing in these parameters

    i_urltext := http://www.clicksms.co.uk:9090/ i_messagetext := sendsmstesttesttest message1000000000

    CREATE OR REPLACE PROCEDURE POST_Test (o_replytext out VARCHAR2 ,o_return_code out INTEGER ,i_messagetext in VARCHAR2 ,i_urltext in VARCHAR2 ,i_timeout in INTEGER DEFAULT 60) IS sslreq UTL_HTTP.req; /* Secure connection request / rsp UTL_HTTP.resp; / Secure response / replymessage VARCHAR2(32767); / reply message (local) / replyline VARCHAR2(32767); / piece of reply message read from secure site. / BEGIN / Set up wallet information for secure connection capability / / remove this line if you don’t have wallet installed and are not using https / – UTL_HTTP.SET_WALLET(‘file:DirectoryPath’,'put password here’); / Initiate request to secure site, set parameters / sslreq := UTL_HTTP.BEGIN_REQUEST(i_urltext,’POST’,'HTTP/1.0′); UTL_HTTP.SET_HEADER(sslreq,’Content-Type’,'text/xml’); UTL_HTTP.SET_HEADER(sslreq,’Content-Length’,to_char(length(i_messagetext))); UTL_HTTP.SET_TRANSFER_TIMEOUT(i_timeout); / Write information over secure connection / UTL_HTTP.WRITE_TEXT(sslreq,i_messagetext); / Initialize Response / rsp:=UTL_HTTP.GET_RESPONSE(sslreq); replymessage := ”; / Retrieve response / BEGIN LOOP UTL_HTTP.READ_LINE(rsp,replyline,TRUE); replymessage := replymessage || replyline; END LOOP; UTL_HTTP.END_RESPONSE(rsp); EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(rsp); END; / Set output information */ o_replytext := replymessage; o_return_code := 0; EXCEPTION WHEN OTHERS THEN o_return_code := SQLCODE; o_replytext := SQLERRM; END POST_Test;

    I am getting a TNS operation timed out error, please can you help

    Thanks Rashad

  5. |

    Great sample! It saved me lot of time today. Can I use little changed code in my app?

  6. |

    PaweÅ‚, it’s always nice to save time. I do not know what you exactly mean by Can I use little changed code in my app?, but I’ll guess yes anyway.

  7. |

    Hi this is very interesting. Do you know how a pl/sql procedure running under mod_plsql can be coded to be the RECIPIENT of a POST like this (instead of the .cfm in yoiur example)? I am struggling to understand how I could implement this as it seems that you have to send into a pl/sql a procedure name=value pairs (whether using flexible paramater passing or not).

    I’d be interested to know if you’ve come across this and a a solution?

    Regards Jeremy

  8. |

    Jeremy, I’m afraid I have not come across this or done this before (read an http post body from PL/SQL).

    You may want to ask your question at http://forums.oracle.com/forums/

    Cheers!

  9. |

    Hi, this example was very useful to me. Thanks, Peter.

  10. |

    We are using similar PL/SQL code to post the XML to a WebService.

    Erroring Scenario (Generally this code works perfectly in production,But the below case had happened once ):

    1) XML has posted succesfully 2) When reading the response ( l_http_resp := utl_http.get_response (l_http_req);) an error occured and control goes to main Exception. and throwing a “Bad argument” error.

    How do Oracle manages a case where XML has posted successfully and then fail to provide success or failure response . ?

    Anybody experience a similar error?

    Oracle version: DB version 9.2.04 generates the XML and posts on an Oracle Apps Server (HTTPServer).

    Sample Code Summarized:

    -- Begin the post request
    
    -- Set the HTTP request headers
    
    
    -- 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);
    

    if the response is 200 Then Insert into a post message table which logs the content of succesful message. if not 200 then I have a parent process which will retry posting after certain time.

    Exception WHEN OTHERS THEN UTL_HTTP.END_RESPONSE(l_http_resp); Log Error.

    Exact Error shows below.

    ORA-29273: HTTP request failed ORA-06512: at “SYS.UTL_HTTP”, line 1380 ORA-29261: bad argument ORA-06512: at “Prodcode.Package_Name”, line 120 (here line 120 is the PLSQL exception) ORA-29273: HTTP request failed ORA-06512: at “SYS.UTL_HTTP

  11. |

    Thanks for the info. This article is good and it might work for me. I was wondering if at the receiving end i need to open a URL from ORACLE Forms, with the following code for hte post, how can I do that? Can I create this procedure at the database level and do the post there and then just invoke the URL from the when-button-pressed trigger using web.show_document()?

    The code for hte post is something like this.

    /* Code for the POST */

    Click this button to view the books related to these courses.

  12. |

    DB, I suggest you ask your question on the Forms OTN Forum.

  13. |

    Thanks! Worked well.

  14. |

    Hi Eddie

    The procedure works, as in it posts to the URL with the parameters, but it also throws an error:

    SQL execution error, ORA-29273: HTTP request failed ORA-06512: at “SYS.UTL_HTTP”, line 1029 ORA-29261: bad argument ORA-06512: at “HH.UTIL_PKG”, line 101

    This is how im using the code (its in a trigger)

    DECLARE v_url varchar2(200); l_data clob := ’1′; ….

    BEGIN … select REPLACE(REPLACE(:new.input_value,’ ‘,NULL),’-',NULL) into v_phone_num from dual; — select REPLACE(REPLACE(1234567,’ ‘,NULL),’-',NULL) into v_phone_num from dual; v_url := ‘http://www.******.com/test.asp?pnum=12345′ || ‘&’ || ‘phone=’ || v_phone_num;

    util_pkg.http_post(p_url_in => v_url, p_data_in => l_data);

    Im only passing 2 arguments, p_url and p_data_in, what is the bad argument error about? Any help would be greatly appreciated.

  15. |

    This is really interesting. How would this work on a page that encrypts the names/ID’s of form elements? For instance, when a user creates a job post on our site, I then take them to a page that loads Craigslist into an IFRAME. I want to be able to populate the fields with the information they entered on our site (so they don’t have to re-type everything). I’ve been having problems with it though.

  16. |

    taneal, it seems that you need to decrypt the data on the other end.

  17. |

    Hmmmm. I was hoping that wouldn’t be the answer :)

    Oh well, guess I have to figure something else out then for doing this.

  18. |

    Very nice, saved me hours of work I am sure!

    It would be nice to know how to encode form elements into “x-www-form-Urlencoded” format and post. The examples I found using Oracle did it in Java, which I would rather avoid. XML will work however.

  19. |

    For those of you who want to HTTP post some parameters to another site, use the following code:

    set serveroutput on; exec dbms_output.enable(1000000000);

    set escape ‘\’

    DECLARE req UTL_HTTP.REQ; resp UTL_HTTP.RESP; value VARCHAR2(1024);

    – URL to post to v_url VARCHAR2(200) := ‘http://T97040476TA9000/core_dmt/withdraw_job.php’;

    – Post Parameters v_param VARCHAR2(500) := ‘pwd=coredmt8567\&core_id=12223\&type=PK\&reason=This is the test reason’; v_param_length NUMBER := length(v_param); BEGIN — Set up proxy servers if required — UTL_HTTP.SET_PROXY(‘proxy.my-company.com’, ‘corp.my-company.com’); req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => ‘POST’); — UTL_HTTP.SET_HEADER(req, ‘User-Agent’, ‘Mozilla/4.0′); UTL_HTTP.SET_HEADER (r => req, name => ‘Content-Type’,
    value => ‘application/x-www-form-urlencoded’); UTL_HTTP.SET_HEADER (r => req, name => ‘Content-Length’, value => v_param_length); UTL_HTTP.WRITE_TEXT (r => req, data => v_param);

    resp := UTL_HTTP.GET_RESPONSE(req); LOOP UTL_HTTP.READ_LINE(resp, value, TRUE); DBMS_OUTPUT.PUT_LINE(value); END LOOP; UTL_HTTP.END_RESPONSE(resp); EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(resp); END; /

  20. |

    Great code example. I ran into a problem however when I used this in a loop. I got “ORA-29270: too many open HTTP requests” errors. I finally found a fix that seemed to work… Exception When UTL_HTTP.END_OF_BODY Then UTL_HTTP.END_RESPONSE (l_http_resp);

  21. |

    This information is great but I’m having problems calling a url with several parameters. SELECT UTL_HTTP.REQUEST( https://realtime.gpcatalogue.com/servlet/GPCRealTime?Query=Logon&Alias=GXSRETL&Userid=GXSREAL&Password=REALTIME) FROM DUAL

    The query returns session timed out but pasting it in the browser it says login successfully…

  22. |

    @Ed: I tested your query and got the error: Certificate validation failure.

    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> set define off
    SQL> SELECT UTL_HTTP.REQUEST('https://realtime.gpcatalogue.com/servlet/GPCRealTi
    me?Query=Logon&Alias=GXSRETL&Userid=GXSREAL&Password=REALTIME') FROM DUAL;
    SELECT UTL_HTTP.REQUEST('https://realtime.gpcatalogue.com/servlet/GPCRealTime?Qu
    ery=Logon&Alias=GXSRETL&Userid=GXSREAL&Password=REALTIME') FROM DUAL
           *
    ERROR at line 1:
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1577
    ORA-29024: Certificate validation failure
    ORA-06512: at line 1
    
  23. |

    Thanks very much for sharing that Eddie, it saved me a lot of time!

    Best regards,

    -Adam vonNieda