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

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dennis.

Asked: July 07, 2016 - 7:17 pm UTC

Last updated: July 08, 2016 - 6:25 am UTC

Version: 11.2.0.3

Viewed 1000+ times

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

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here