APEX

I have created a pdf report on jasperserver and I can call it from apex via a url and it displays fine. The url is this format {http://server:port/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/Apex/deptemp&output=pdf&deptNo=#DEPTNO#&j_username=un&j_password=pw}

However, I am trying to follow a stored procedure executed from Apex so it would not expose the username/pwd in the url like above.

If I am reading the apache error log correctly it is erroring in this piece of the code below where it executes {Dbms_Lob.writeAppend}
{
loop
begin
--------------------------------------------------------------------
-- read the next chunk of binary data
--------------------------------------------------------------------
Utl_Http.read_raw(vResponse, vData);
--------------------------------------------------------------------
-- append it to our blob for the pdf file
--------------------------------------------------------------------
Dbms_Lob.writeAppend
( lob_loc => vBlobRef
, amount => Utl_Raw.length(vData)
, buffer => vData
);
exception when utl_http.end_of_body then
exit; -- exit loop
end;
end loop;
Utl_Http.end_response(vResponse);}

The error log says this; HTTP-500 ORA-14453: attempt to use a LOB of a temporary table, whose data has alreadybeen purged\n

What is this error telling me and how can I correct it?

The stored procedure I am following is below but replaced the vReportURL with my url like above that works.

Thank you,
Mark




{CREATE OR REPLACE procedure runJasperReport(i_deptno in varchar2)
is
vReportURL varchar2(255);
vBlobRef blob;
vRequest Utl_Http.req;
vResponse Utl_Http.resp;
vData raw(32767);
begin
----------------------------------------------------------------------------
-- build URL to call the report
----------------------------------------------------------------------------
vReportURL := 'http://host:port/jasperserver/flow.html?_flowId=viewReportFlow'||
'&reportUnit=/reports/Apex/deptemp'||
'&output=pdf'||
'&j_username=un&j_password=pw'||
'&deptNo='||i_deptno;
----------------------------------------------------------------------------
-- get the blob reference
----------------------------------------------------------------------------
insert into demo_pdf (pdf_report)
values( empty_blob() )
returning pdf_report into vBlobRef;
----------------------------------------------------------------------------
-- Get the pdf file from JasperServer by simulating a report call from the browser
----------------------------------------------------------------------------
vRequest := Utl_Http.begin_request(vReportUrl);
Utl_Http.set_header(vRequest, 'User-Agent', 'Mozilla/4.0');
vResponse := Utl_Http.get_response(vRequest);
loop
begin
--------------------------------------------------------------------
-- read the next chunk of binary data
--------------------------------------------------------------------
Utl_Http.read_raw(vResponse, vData);
--------------------------------------------------------------------
-- append it to our blob for the pdf file
--------------------------------------------------------------------
Dbms_Lob.writeAppend
( lob_loc => vBlobRef
, amount => Utl_Raw.length(vData)
, buffer => vData
);
exception when utl_http.end_of_body then
exit; -- exit loop
end;
end loop;
Utl_Http.end_response(vResponse);

owa_util.mime_header('application/pdf',false);
htp.p('Content-length: ' || dbms_lob.getlength(vBlobRef));
owa_util.http_header_close;
wpg_docload.download_file(vBlobRef);

end runJasperReport;
/}