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

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Dennis.

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

Last updated: November 21, 2017 - 1:29 pm UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

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 Connor 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.

Rating

  (2 ratings)

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

Comments

Very helpful

Dennis Ching, July 08, 2016 - 1:31 pm UTC

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

Steve Cosner, November 21, 2017 - 10:40 am UTC

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
November 21, 2017 - 1:29 pm UTC

Thanks for the input

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here