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
HTTP Communication from Within the Oracle Database
Related articles:
- Read XML from an HTTP POST
- Here’s a Quick Way to Get the Line Number in PL/SQL
- Random string generator
Filed in ColdFusion, Oracle on 30 Nov 05 | Tags: dbms_xmlgen, http, utl_http, xml
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.
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.
Meg,
Did you ever get this solved? I’m running into the same problem.
Thanks!
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
Great sample! It saved me lot of time today. Can I use little changed code in my app?
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.
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
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!
Hi, this example was very useful to me. Thanks, Peter.
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
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.
DB, I suggest you ask your question on the Forms OTN Forum.
Thanks! Worked well.
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.
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.
taneal, it seems that you need to decrypt the data on the other end.
Hmmmm. I was hoping that wouldn’t be the answer
Oh well, guess I have to figure something else out then for doing this.
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.
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; /
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);
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…
@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 1Thanks very much for sharing that Eddie, it saved me a lot of time!
Best regards,
-Adam vonNieda