SQL & PL/SQL

Hello everyone,

I have an issue on a 10g database (10.2.0.4). I generate a PDF with Reports 10g, it works fine and the PDF can ben opened correctly.

The report is then sent be email.

But sometimes, around 1/200 PDF is not sent correctly by email.

Example (PDF opened with PSPad) : .!UM<nHXnnD]S/9"TJI>cNib3TK4HU4>EYr&@RSV*tGqe6lJD2$a[ESGr=M%#(?jCJ+-#?pc@$c

The dot disappears on the email received.

So there is an internal issue when the PDF is opened on Acrobat reader, and the file cannot be printed.

Here is the code used for the attached file :

FUNCTION Read_clob_file (conn IN OUT utl_smtp.connection, N_Fic IN Varchar2) return boolean IS

  Rep     Varchar2 (255);

  N_Fic2  Varchar2 (255);

  Fic     UTL_FILE.FILE_TYPE;

  Lig     clob;

  b_file bfile;

  file_content clob;

  dst_offset  number := 1 ;

    src_offset  number := 1 ;

    lang_ctx    number := DBMS_LOB.DEFAULT_LANG_CTX;

  war integer;

  l_step        PLS_INTEGER  := 12000; -- make sure you set a multiple of 3 not higher than 24573

BEGIN

  Get_Rep_Fic (N_Fic, Rep, N_Fic2);

  IF Rep IS NOT NULL AND N_Fic2 IS NOT NULL THEN

--    utl_smtp.write_data (conn,

    utl_smtp.write_Raw_data (conn, UTL_RAW.Cast_To_Raw (

           UTL_TCP.CRLF || '--DMW.Boundary.605592468' || UTL_TCP.CRLF ||

           'Content-Type: application/octet-stream; name="' || N_Fic2 || '"' || UTL_TCP.CRLF ||

           'Content-Disposition: attachment; filename="' || N_Fic2 || '"' || UTL_TCP.CRLF || UTL_TCP.CRLF));

    b_file := bfilename('PDF_MAIL', n_fic2);      

    dbms_lob.fileopen(b_file);

    DBMS_LOB.CREATETEMPORARY(file_content,true);

    DBMS_LOB.LOADCLOBFROMFILE (DEST_LOB => file_content, SRC_BFILE => b_file, AMOUNT => dbms_lob.getlength(b_file),

                                DEST_OFFSET => dst_offset, SRC_OFFSET => src_offset, BFILE_CSID => DBMS_LOB.DEFAULT_CSID, LANG_CONTEXT => lang_ctx,

                                WARNING => war);

    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(file_content) - 1 )/l_step) LOOP

      UTL_SMTP.write_raw_data(conn, UTL_RAW.Cast_To_Raw (DBMS_LOB.substr(file_content, l_step, i * l_step + 1)));

    END LOOP;

    dbms_lob.fileclose(b_file);

    return true;

  END IF;

EXCEPTION

  WHEN OTHERS THEN

     IF dbms_lob.fileisopen(b_file) = 1 THEN

        dbms_lob.fileclose(b_file);

     END IF;

     return false;

END;

Same issue if I use write_data instead of write_raw_data on the clob.

Do you have any idea ?

Thanks for the future answers.