Skip to Main Content
  • Questions
  • utl_http results to ORA-12541: TNS:no listener

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 07, 2016 - 7:17 pm UTC

Answered by: Connor McDonald - Last updated: July 08, 2016 - 6:25 am UTC

Category: Database - Version: 11.2.0.3

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: How to Create an Execution Plan

You Asked

Hi,
I have 11g DB and I have url below that I can open in the browser displaying pdf. This is pdf report generated from Jasper server.

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

below is the code that results to an error.

Also please note that the user has ACL granted, can connect to the DB and this URL is in the same server where Oracle is installed.

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

-- this is resulting to the URL above
dbms_output.put_line('url '||v_URL);

-- I tried to specify the http/1.0 and http/1.1 but same result
v_request := utl_http.begin_request(v_url);
utl_http.set_header(v_request, 'User-Agent', g_user_agent);

-- This is the line resulting to an error.
v_response := utl_http.get_response(v_request);

dbms_lob.createtemporary(v_file, TRUE, dbms_lob.session);
LOOP
BEGIN
utl_http.read_raw(v_response, v_download);
dbms_lob.writeappend(v_file, utl_raw.length(v_download), v_download);
EXCEPTION WHEN utl_http.end_of_body THEN
EXIT;
END;
END LOOP;
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;

and we said...

Typically ORA-12541 on a http request means that we did not find anything listening on the server/port will attempted to connect to, or we could not get to the port (eg firewall)

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