Skip to Main Content
  • Questions
  • utl_htp is begin_request is resulting to ORA-29263: HTTP protocol error

Breadcrumb

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.

Question and Answer

Connor McDonald

Thanks for the question, Dennis.

Asked: July 06, 2016 - 8:50 pm UTC

Answered by: Connor McDonald - Last updated: November 21, 2017 - 1:29 pm UTC

Category: Database - Version: 11.2.0.3

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Brand new year ? Brand new to installing Oracle ?

You Asked

Hi,
I have URL that I can access with a browser and this displays the PDF

http://serv-ora11g:8081/jasperserver/flow.html?_flowId=viewReportFlow&j_username=jasperadmin&j_password=jasperadmin&reportUnit=/KVS/Collection/col1190&output=pdf

I create an ACL for my user and did verify.

When I run the code below I get this error, thanks

PROCEDURE call_report(p_report_name VARCHAR2, p_parameter type_parameter)
IS
v_url VARCHAR2(4000);
v_request sys.utl_http.req;
v_response sys.utl_http.resp;
v_file BLOB;
v_download RAW(32767);
BEGIN
v_url := build_url(p_report_name, p_parameter);

-- below is the line causing an error
v_request := sys.utl_http.begin_request(v_url);

sys.utl_http.set_header(v_request, 'User-Agent', g_user_agent);
v_response := sys.utl_http.get_response(v_request);
dbms_lob.createtemporary(v_file, TRUE, dbms_lob.session);

LOOP
BEGIN
sys.utl_http.read_raw(v_response, v_download);
dbms_lob.writeappend(v_file, utl_raw.length(v_download), v_download);
EXCEPTION WHEN sys.utl_http.end_of_body THEN
EXIT;
END;
END LOOP;
sys.utl_http.end_response(v_response);

owa_util.mime_header('application/' || output_format, false);
htp.p('Content-length: ' || dbms_lob.getlength(v_file));
htp.p('Content-Disposition: attachment; filename="' || p_report_name || '.'|| output_format ||'"');
owa_util.http_header_close;

wpg_docload.download_file(v_file);

dbms_lob.freetemporary(v_file);
END call_report;

and we said...

Your blob fetching code appears fine. I just did this:

SQL> set serverout on
SQL> declare
  2  v_url VARCHAR2(4000);
  3  v_request sys.utl_http.req;
  4  v_response sys.utl_http.resp;
  5  v_file BLOB;
  6  v_download RAW(32767);
  7  BEGIN
  8  v_url := 'http://www.ausoug.org.au/2020/documents/Connor_McDonald_profile2.pdf';
  9  v_request := sys.utl_http.begin_request(v_url);
 10  --sys.utl_http.set_header(v_request, 'User-Agent', g_user_agent);
 11  v_response := sys.utl_http.get_response(v_request);
 12  dbms_lob.createtemporary(v_file, TRUE, dbms_lob.session);
 13  LOOP
 14  BEGIN
 15  sys.utl_http.read_raw(v_response, v_download);
 16  dbms_lob.writeappend(v_file, utl_raw.length(v_download), v_download);
 17  EXCEPTION WHEN sys.utl_http.end_of_body THEN
 18  EXIT;
 19  END;
 20  END LOOP;
 21  sys.utl_http.end_response(v_response);
 22  --owa_util.mime_header('application/' || output_format, false);
 23  --htp.p('Content-length: ' || dbms_lob.getlength(v_file));
 24  --htp.p('Content-Disposition: attachment; filename="' || p_report_name || '.'|| output_format ||'"');
 25  --owa_util.http_header_close;
 26  --wpg_docload.download_file(v_file);
 27  dbms_output.put_line(dbms_lob.getlength(v_file));
 28  dbms_lob.freetemporary(v_file);
 29  END;
 30  /
5814

PL/SQL procedure successfully completed.

C:\temp>dir Connor_McDonald_profile2.pdf
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

18/04/2014  05:49 PM             5,814 Connor_McDonald_profile2.pdf




ORA-29263 isnt an ACL error - its a more "basic" error, eg, you asked for http and the site only supports https, or you got a network timeout etc etc... Your code is fine - but there is something not correct in your "navigation" to that web server.

Hope this helps.

and you rated our response

  (2 ratings)

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

Reviews

Very helpful

July 08, 2016 - 1:31 pm UTC

Reviewer: Dennis Ching from LV USA

I read a separate article that the issue might be that Oracle EPG only supports http 1.0 whereas the site (jasper report server) uses http 1.1.

Due to the encoding / chunking I would always get this error.

I tried the code like you did but I still get the same issue.

Thanks!

Retry after ORA-29263: HTTP protocol error

November 21, 2017 - 10:40 am UTC

Reviewer: Steve Cosner from California USA

I have a process that calls the same site thousands of times -- it is updating data on the remote site. Sometimes it runs without problems, other times it gets the "ORA-29263: HTTP protocol error" a dozen times.

I have tweaked my process, so that it issues "utl_http.end_request(req)", then immediately starts over with the "utl_http.begin_request" ...utl_http.get_response(req);

This recovery process has successfully recovered every failed call.

I actually tried repeating the utl_http.get_response first after the exception, but that consistently returns ORA-29261: bad argument. So the full restart method appears to be the best solution.
Connor McDonald

Followup  

November 21, 2017 - 1:29 pm UTC

Thanks for the input

More to Explore

DBMS_LOB

More on PL/SQL routine DBMS_LOB here