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
Paweł Barut
· 3 years ago
Great sample! It saved me lot of time today. Can I use little changed code in my app?
Eddie Awad
· 3 years ago
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.
Jeremy Ovenden
· 2 years ago
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
Eddie Awad
· 2 years ago
Jeremy, I'm afraid I have not come across this or done this before (read an http post body from PL/SQL).
Hi, this example was very useful to me. Thanks, Peter.
ask4bv
· 2 years ago
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).
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
DB
· 1 year ago
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.
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
Im only passing 2 arguments, p_url and p_data_in, what is the bad argument error about? Any help would be greatly appreciated.
taneal
· 1 year ago
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.
Eddie Awad
· 1 year ago
taneal, it seems that you need to decrypt the data on the other end.
taneal
· 1 year ago
Hmmmm. I was hoping that wouldn't be the answer :)
Oh well, guess I have to figure something else out then for doing this.
Ethan
· 1 year ago
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.
Ariel
· 1 year ago
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; /
Jim
· 1 year ago
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);
The query returns session timed out but pasting it in the browser it says login successfully...
Eddie Awad
· 1 year ago
@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
Adam vonNieda
· 1 year ago
Thanks very much for sharing that Eddie, it saved me a lot of time!
All Comments
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/</p>
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:
<pre>-- 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);
</pre>
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
The code for hte post is something like this.
/* Code for the POST */
Click this button to view the books related to these courses.
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.
Oh well, guess I have to figure something else out then for doing this.
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.
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;
/
Exception
When UTL_HTTP.END_OF_BODY Then
UTL_HTTP.END_RESPONSE (l_http_resp);
SELECT UTL_HTTP.REQUEST(
https://realtime.gpcatalogue.com/servlet/GPCRea...) FROM DUAL
The query returns session timed out but pasting it in the browser it says login successfully...
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
Best regards,
-Adam vonNieda