Your browser was unable to load all of the resources. They may have been blocked by your firewall, proxy or browser configuration.
Press Ctrl+F5 or Ctrl+Shift+R to have your browser try again.

Report the is blank #63

Hello dear members of the forum.

I have created a PL/SQL process that generates a report in PDF format based on a Jasper Studio template (generating correctly showing the relevant data), however when the attachment is opened from the client's mail inbox , the document is blank.

My code is:

declare
 l_body       clob; 
 l_body_html  clob; 
 l_email varchar2(60);
 l_proc varchar2(100) := 'show report';
 l_additional_parameters varchar2(32767);
 l_blob blob;
 l_mime_type varchar2(30):='application/pdf';
 l_mensaje_id number;
begin
 l_body:= 'Reciba un cordial saludo. Enviamos la siguiente orden de pedido.<br>Cordiales';
 BEGIN

   xlib_jasperreports.set_report_url('http://xyz:8090/JasperReportsIntegration/report');
   -- construct addional parameter list
  l_additional_parameters := 'P_PRESUPUESTO_ID=' || apex_util.url_encode(:P34_PRESUPUESTO);
 

   xlib_jasperreports.get_report (p_rep_name => 'xyz/FMGPresupuesto',
     p_rep_format => 'pdf',
     p_data_source => 'xyz',
     p_rep_locale => 'en_US',
     p_rep_encoding => 'UTF-8',
     p_additional_params => l_additional_parameters,
     p_out_blob => l_blob,
     p_out_mime_type => l_mime_type
   );
   --apex_application.stop_apex_engine;
 END;
 l_mensaje_id:=apex_mail.send(
       p_to        => 'robert_antoni792012@yahoo.com',
       p_from      => 'report@xyzemail.xyz',
       p_body      => l_body,
       p_body_html => NULL,
       p_subj      => 'Presupuesto del Cliente');
 APEX_MAIL.ADD_ATTACHMENT(
       p_mail_id    => l_mensaje_id,
       p_attachment => l_blob,
       p_filename   => 'Presupuesto_Nro_'||:P34_PRESUPUESTO||'.pdf   ',
       p_mime_type  => l_mime_type);
 
 commit;

 l_body_html:=null;
  
 dbms_lob.freetemporary (l_blob);
 
end;

Thanks for your help.

  • replies 1
  • views 947
  • likes 0
#2

hi @robertopz,

hhhhmmm. Looks alright so far.

Are you certain, that the blob which is returned, contains the correct PDF?

Can you please try to display it in the browser to make sure the PDF is correct?

Please use the following code instead of sending the email:

------------------------------------------------------------------------
   -- set mime header
   ------------------------------------------------------------------------
   htp.flush;
   htp.init;
   OWA_UTIL.mime_header (ccontent_type      => l_mime_type,
                         bclose_header      => FALSE);
   ------------------------------------------------------------------------
   -- set content length
   ------------------------------------------------------------------------
   HTP.p ('Content-length: ' || DBMS_LOB.getlength (l_blob));
   OWA_UTIL.http_header_close;
   ------------------------------------------------------------------------
   -- download the file and display in browser
   ------------------------------------------------------------------------
   WPG_DOCLOAD.download_file (l_blob);
   ------------------------------------------------------------------------
   -- release resources
   ------------------------------------------------------------------------
   DBMS_LOB.freetemporary (l_blob);
   ------------------------------------------------------------------------
   -- stop rendering of APEX page
   ------------------------------------------------------------------------
  -- this was used before apex 4.1
  --apex_application.g_unrecoverable_error := true;
  apex_application.stop_apex_engine;

Then we can move on with the troubleshooting and check the apex_mail code ... perhaps the mime type. get_report() will use the output parameter l_mime_type and overwrite that. Perhaps you can check the value that is returned as well.

Best,
~Dietmar.