I didn't excatly know which forum I should go but it's more APEX related (I think)...
We have a "public" APEX instance, that is splitted into AppServer and Database. The AppServer is protected by two realy strict firewalls that block everything that is coming from outside. so the BI Pub. instance is proteced and only accessible from intranet. I succesfully called the Reports from an internal APEX instance.
Here are the questions:
1) Is there a possibility to "receive" (with file download dialog) a generated report in APEX from BI Publisher without linking to the BI Publisher (Pre)view?
2) Is there a possible way to call a BI-Report and receive ie. the PDF Report as BLOB?
What I tried:
1) I only achieved this download as APEX Report Print link and with configured BI Pub settings in APEX Admin instance settings...
2) I tried this with using utl_http.begin_request, utl_http.get_response etc. in a background procedure in the public APEX instance database (that could access the intranet), that was succesfully but I ended up with a "Your session has expired. To sign in please click here." (See code snippet)
-----
create or replace procedure show_bipub_doc(
inp_v_dir in varchar2,
inp_v_report in varchar2,
inp_n_show_mode in number,
inp_v_params in varchar2,
inp_v_template in varchar2,
inp_v_format in varchar2
) as
l_escaped_url varchar2(4000);
l_http_request utl_http.req;
cookies utl_http.cookie_table;
l_http_response utl_http.resp;
l_blob blob;
l_timer date;
l_raw raw(32767);
begin
-- inp_v_dir = '003-Data Template/' (Report directory, like /Guest =>Somedir/<= where Guest is automatically concatenated)
-- inp_v_report = '003-Data Template.xdo' (Report Name)
-- inp_n_show_mode = 4 (Mode if only document or with header should be shown)
-- inp_v_params = 'p_job=PRESIDENT' (Parameterlist)
-- inp_v_template = 'Template1' (Name of Template, RTF)
-- inp_v_format = 'pdf' or 'html' or '...'
-- initialize the BLOB that get filled by returning
dbms_lob.createtemporary(l_blob, false);
-- build destination url
l_escaped_url := 'http://my.bipub.serverinstance:7777/xmlpserver/servlet/xdo?_xpf=&_xpt=0&_xdo=/Guest/'||inp_v_dir||
inp_v_report||'&'||inp_v_params||'&_xt='||inp_v_template||'&_xf='||inp_v_format||'&_xmode='||inp_n_show_mode;
l_escaped_url := utl_url.escape(l_escaped_url);
-- Make a HTTP request and get the response.
l_http_request := utl_http.begin_request(l_escaped_url);
utl_http.set_header(l_http_request, 'User-Agent', 'Mozilla/4.0');
l_http_response := utl_http.get_response(l_http_request);
-- Copy the response into the BLOB.
begin
loop
utl_http.read_raw(l_http_response, l_raw, 32767);
dbms_lob.writeappend(l_blob, utl_raw.length(l_raw), l_raw);
end loop;
exception
when utl_http.end_of_body then
utl_http.end_response(l_http_response);
end;
-- mit htp selber ausgeben
owa_util.mime_header('application/pdf',false);
htp.p('Content-length:'||dbms_lob.getlength(l_blob));
htp.p('Content-Disposition: attachment; filename=REPORT.PDF');
owa_util.http_header_close;
wpg_docload.download_file(l_blob);
-- Relase the resources associated with the temporary LOB.
dbms_lob.freetemporary(l_blob);
exception
when others then
utl_http.end_response(l_http_response);
dbms_lob.freetemporary(l_blob);
raise;
end show_bipub_doc;