Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, John.

Asked: January 24, 2001 - 3:01 am UTC

Last updated: January 14, 2013 - 12:56 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom,

Is it possible to construct a PL/SQL stored procedure that makes a URL call. Then have the resulting HTML displayed.

Something like...
procedure helloworld is

begin
htp.htmlOpen;
htp.BodyOpen;

htp.p('This would be cool, if it worked...');

owa_util.redirect_url('helloworld.html');

htp.BodyClose;
htp.htmlClose;
end;

Thanks,

John Perkins

and Tom said...

Yes, there is a builtin UTL_HTTP package for doing this.

Lets say the text of the page you are calling is going to be "small". You can:


...
begin
htp.htmlOpen;
htp.BodyOpen;

htp.p('This would be cool, if it worked...');

htp.p(
utl_http.request('http;//host:port/path/helloworld.html')
);

htp.BodyClose;
htp.htmlClose;
end;


If the page is large, you'll need to use utl_http.request_pieces. See

</code> http://docs.oracle.com/cd/A81042_01/DOC/server.816/a76936/utl_http.htm#998100 <code>

for details on utl_http.



Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Same bugs in utl_http.request_pieces

alper tandogan, July 27, 2004 - 7:44 am UTC

I write url in my browser return correct page
But i use utl_http.request_pieces it return the same page but some undefined string in it.

ret utl_http.html_pieces;
BEGIN
ret := utl_http.request_pieces('</code> http://mypage.html', <code>32767);
FOR I IN 1..ret.COUNT LOOP
HTP.P(ret(I));
END LOOP;
end ;




Tom Kyte
July 27, 2004 - 7:50 am UTC

er?

"some undefined string" -- sorry, but I've no idea what issue you might be encountering given the sparsness of detail here.

can this method be used to call reports?

hash, December 07, 2008 - 10:34 am UTC

I m trying to call a developer 6i report by using this method.

htp.p(utl_http.request(' http://host:port//dev60cgi/rwcgi60.exe?report=emp.rep&destype=cache&desformat=HTML¶mform=HTML' ));

I get

ORA-29273: HTTP request failed

but if I

htp.p('<a href=" http://host:port/dev60cgi/rwcgi60.exe?report=emp.rep&destype=cache&desformat=HTML¶mform=HTML" >click the link to run report</a>');

It runs fine

is there any method to do this inside PL/SQL?

thanks
Tom Kyte
December 09, 2008 - 12:18 pm UTC

do a little debugging???

$ oerr ora 29273
29273, 00000, "HTTP request failed"
// *Cause:  The UTL_HTTP package failed to execute the HTTP request.
// *Action: Use get_detailed_sqlerrm to check the detailed error message.
//          Fix the error and retry the HTTP request.


isolate the problem - do it right in sqlplus or something interactively so you can see what is going on. Just do the utl_http bit to get it working.


Probably - what you really want to do is return a redirect_url call - and have the web browser run the url automagically.

Confused...

hash, December 11, 2008 - 9:47 am UTC

OK I m confused. I m generating a web page through differnet htp calls. the page has a usual header, a usual footer and an unusual body. the header and footer is generated through htp calls. but the body consists of a developer 6i report. now if i use redirect_url, it won't let me use htp calls for the header section. then i can I embed that report inside the header and footer?
Tom Kyte
December 11, 2008 - 7:10 pm UTC

developer is returning a FULL HTML page - header and all.

sounds like you might want an iframe or frame if you want to surround the report with your own data.

As always, you are the best

hash, December 12, 2008 - 11:53 am UTC

Sir thank you so much. you always come up with the right solution

Problem

Hala Assaad, June 29, 2012 - 7:56 am UTC

I use the same code but I get an
ora-29273: HTTP request failed
ORA-12535:TNS operation time out

parallel url calls

Goki Raj, January 09, 2013 - 10:51 am UTC

Sir,
I need to hit a url for 2 million of records approx. The response given will be less than of 100 char. I have used utl_http package for implementing this. But the process runs for more than 8 hours. All the urls are hit inside a cursor. i.e. the url is invoked sequentially. Is there any possible way to hit url's parallely so url could be get faster or is there any other way of improving the performance.

Thanks.
Tom Kyte
January 14, 2013 - 12:56 pm UTC

insufficient data, I have no idea why you would hit a URL for millions of records.


it sounds like someone went crazy with "web services" and now you are trying to do a batch operation using slow by slow processing with a REALLY SLOW thing in the slow by slow processing.


I'm surprised it takes 8 hours, I would have guessed longer - it is taking you about 15-20ms to do the HTTP call, get a document back, parse it, apply it - that is pretty darn good.


search this site for

do it yourself parallelism

or

dbms_parallel_execute (11g)


you might be able to do this in a 1/4th of the time or something. But if you want it to go orders of magnitude faster - you will blow off that web service call, do things natively and in bulk. You should be able to do this in seconds using a CREATE TABLE AS SELECT and embedding the processing in the database.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library