This discussion is archived
12 Replies Latest reply: Sep 20, 2013 10:15 AM by thomaso RSS

Sending PDF attachment thru PL-SQL (sends garbage)

Panamanian Newbie
Currently Being Moderated

Hi guys.

 

I copy a procedure I found in the web (to attach external files), but when I send it and the user opens the email all I see is things like:

 

-------------------

This is email body

MIME-Version: 1.0

Content-Type: multipart/mixed; boundary="-----7D81B75CCC90D2974F7A1CBD"

 

-------7D81B75CCC90D2974F7A1CBD

Content-Type: application/pdf

Content-Disposition: attachment; filename="adm022.pdf"

Content-Transfer-Encoding: base64

 

JVBERi0xLjQKJcfsj6IKNSAwIG9iago8PC9MZW5ndGggNiAwIFIvRmlsdGVyIC9G

bGF0ZURlY29k

ZT4+CnN0cmVhbQp4nM2de3Mcx3XFCwAJ4lUgIYMwJcXikpQsrGKs+j09/k+2bEcu

ppLYdFIVw5VS

SX4WRVuyXfm8+Sbpvud29xA7O4td3tihitSpnTu7v73dM91zT8/s1zO10Gam8n9F

fPHV0dez4Om1

mXEm/e0WfR9mwSja+vHPutmnfzz6tyM1+0n6+9ujr4/iwuY/tMtQf/HV7Acv0g5x

FhbpHV785kil

t4rK46P0zPVx0ZlZZ/Uivfbiq6NfXv7r/Cqk10wXLv9nbhe6D7G//G1Vv59fmYXv

THf5qr72eVWz

uV4EZUK4/P78yuuwCC5e6vmV7qNdOHv5ZY389dx7n2F+9eKnR2bhlAv9TKuFTRhf

Jowfz8PCdqE3

KbLs88X8KvF3MYVe/u72n142N3U1d4uuc8ZfqvpaP7LV1NfUyLvY4Ve48gunlXcz

oxZdwHf42fzK

LrzXuht8iT9V9ceqvqmf+Jf6FX49/bV+2N77J3O1CMal4Mv/qjsNPrux+5HvE4bf

-----------------

and so on...


Is it something wrong I'm doing?

by the way the code is:


Blue color is the code I'm using.

====================

declare

    p_recipient VARCHAR2(500) := 'sender@domain.com';

    p_subject VARCHAR2(500) := 'Test attachement';

    p_message VARCHAR2(500) := 'This is email body';

    p_filename VARCHAR2(500) := 'adm022.pdf';

    v_msg VARCHAR2(32000);

    src_file bfile;

    i integer := 1;

    v_raw raw(57);

    v_length integer := 0;

    v_buffer_size integer := 57;

    v_mailconn utl_smtp.connection;

    gc_crlf VARCHAR2(4) := chr(13) || chr(10);

    gc_lf VARCHAR2(4) := chr(10);

    gc_mailhost varchar2(30) := '10.20.1.139';

    BOUNDARY CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

    FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;

    LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||

    utl_tcp.CRLF;

    -- A MIME type that denotes multi-part email (MIME) messages.

    MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||

    BOUNDARY || '"';

BEGIN

    v_mailconn := utl_smtp.open_connection(gc_mailhost,25);

    utl_smtp.helo(v_mailconn,gc_mailhost);

    utl_smtp.mail(v_mailconn,'sender@domain.com');

    utl_smtp.rcpt(v_mailconn,p_recipient);

    utl_smtp.open_data (v_mailconn);

    utl_smtp.write_data (v_mailconn, 'From: ' || p_recipient || utl_tcp.crlf );

    utl_smtp.write_data (v_mailconn, 'To: ' || p_recipient || utl_tcp.crlf );

    utl_smtp.write_data (v_mailconn, 'Subject: ' || p_subject || utl_tcp.crlf );

    utl_smtp.write_data (v_mailconn, 'This is email body' || utl_tcp.CRLF);

    utl_smtp.write_data (v_mailconn, 'MIME-Version: 1.0' || utl_tcp.CRLF);

    utl_smtp.write_data (v_mailconn, 'Content-Type: multipart/mixed; boundary="'|| BOUNDARY || '"' || utl_tcp.CRLF);

    utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

    utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);

    utl_smtp.write_data (v_mailconn, 'Content-Type' || ': ' || 'application/pdf' || utl_tcp.CRLF);

    utl_smtp.write_data (v_mailconn, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf);

    --utl_smtp.write_data (v_mailconn, 'Content-Disposition: attachment; filename="ADM022.pdf"' || utl_tcp.crlf);

    utl_smtp.write_data (v_mailconn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );

    utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

    src_file := bfilename('EXAMPLEDIR', p_filename);

    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

    v_length := dbms_lob.getlength(src_file);

    while i < v_length loop

       dbms_lob.read( src_file, v_buffer_size, i, v_raw );

       utl_smtp.write_raw_data( v_mailconn, utl_encode.base64_encode(v_raw) );

       utl_smtp.write_data( v_mailconn, utl_tcp.crlf );

       i := i + v_buffer_size;

    end loop while_loop;

    utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);

    utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

    dbms_lob.fileclose(src_file);

    utl_smtp.close_data (v_mailconn);

    utl_smtp.quit(v_mailconn);

EXCEPTION

   WHEN OTHERS THEN

      utl_smtp.quit(v_mailconn);

      dbms_output.put_line(sqlcode||sqlerrm);

END;

=================

 

The file to attach "adm022.pdf" is in the server and I created an alias directory named: EXAMPLEDIR

 

I really appreciate your help.

 

Gracias.



  • 1. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    thomaso Journeyer
    Currently Being Moderated

    Try this small change:

         ...

           utl_smtp.write_raw_data( v_mailconn, utl_encode.base64_encode(v_raw) );

           -- remove crlf here: utl_smtp.write_data( v_mailconn, utl_tcp.crlf );

           i := i + v_buffer_size;

        end loop while_loop;

        -- add crlf here..

        utl_smtp.write_data( v_mailconn, utl_tcp.crlf );

        utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);

    ...

    Thomas

  • 2. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    Panamanian Newbie
    Currently Being Moderated

    I replace it, as you mentioned, with this "last part of the procedure":

     

        while i < v_length loop

           dbms_lob.read( src_file, v_buffer_size, i, v_raw );

           utl_smtp.write_raw_data( v_mailconn, utl_encode.base64_encode(v_raw) );

           --utl_smtp.write_data( v_mailconn, utl_tcp.crlf);

           i := i + v_buffer_size;

        end loop while_loop;

        utl_smtp.write_data( v_mailconn, utl_tcp.crlf);

        utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);

        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

        dbms_lob.fileclose(src_file);

        utl_smtp.close_data (v_mailconn);

        utl_smtp.quit(v_mailconn);

     

     

     

    Is that what you meant?

     

    Thanks

  • 3. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    thomaso Journeyer
    Currently Being Moderated

    Did it work?

    T.

  • 4. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    Panamanian Newbie
    Currently Being Moderated

    No, I'm afraid I got the same "garbage".

     

    Why is it happening? and what else could I try?

     

    Also, thanks a lot for your previous answer.


    Gracias

  • 5. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    thomaso Journeyer
    Currently Being Moderated

    I'm comparing your code with similar working procedure.

    I can see one more difference:

     

    Try adding this:

    ...

    utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);

    utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

    utl_smtp.write_data (v_mailconn, utl_tcp.crlf ||'.'|| utl_tcp.crlf||utl_tcp.crlf);

    dbms_lob.fileclose(src_file);

    utl_smtp.close_data (v_mailconn);

    utl_smtp.quit(v_mailconn);

    ...

     

    Thomas

  • 6. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    Panamanian Newbie
    Currently Being Moderated

    the same garbage.   

    it failed again.

     

    why is it happening?

  • 7. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    thomaso Journeyer
    Currently Being Moderated

    Ok, I've go it working.

    You have to separate email body from attachment.

    Change your code:

     

    ...
        v_mailconn := utl_smtp.open_connection(gc_mailhost,25);
        utl_smtp.helo(v_mailconn,gc_mailhost);
        utl_smtp.mail(v_mailconn,'sender@domain.com');
        utl_smtp.rcpt(v_mailconn,p_recipient);
        utl_smtp.open_data (v_mailconn);
        utl_smtp.write_data (v_mailconn, 'From: ' || p_recipient || utl_tcp.crlf );
        utl_smtp.write_data (v_mailconn, 'To: ' || p_recipient || utl_tcp.crlf );
        utl_smtp.write_data (v_mailconn, 'Subject: ' || p_subject || utl_tcp.crlf );


        utl_smtp.write_data (v_mailconn, 'MIME-Version: 1.0' || utl_tcp.CRLF);
        utl_smtp.write_data (v_mailconn, 'Content-Type: multipart/mixed; boundary="'|| BOUNDARY || '"' || utl_tcp.CRLF);

        --------------------------------------------------------------------------------------------------------------

        --

        -- New boundary block for text of the email

        --
        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

        utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);
        utl_smtp.write_data (v_mailconn, 'Content-Type: text/plain;'|| utl_tcp.CRLF);
        utl_smtp.write_data (v_mailconn, ' charset=US-ASCII'|| utl_tcp.CRLF);
        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );   
        utl_smtp.write_data (v_mailconn, 'This is email body' || utl_tcp.CRLF);   

        --------------------------------------------------------------------------------------------------------------

        --

        -- and now boundary block for attachment

        --

        utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);
        utl_smtp.write_data (v_mailconn, 'Content-Type' || ': ' || 'application/pdf' || utl_tcp.CRLF);
        utl_smtp.write_data (v_mailconn, 'Content-Disposition: attachment; ' || utl_tcp.crlf);
        utl_smtp.write_data (v_mailconn, ' filename="' || p_filename || '"' || utl_tcp.crlf);
        utl_smtp.write_data (v_mailconn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );
    ...

     

    It worked on my test.

    HTH

    Thomas

  • 8. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    Panamanian Newbie
    Currently Being Moderated

    Same "garbage".

     

    With some tweaking the most i did get was as follow:

     

    -------------------------------------------

    This is email body

     

    ADM022.pdfADM022.pdf
    0 K   View   Download 

    --------------------------------------------

     

    It seemed to worked but size is zero, therefore maybe something is incomplete, I'm like blind working.

     

    What did you do so it worked? Also, did your attachment had size?

    Please post your complete code (from the beginning).

     

    Gracias.

  • 9. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    thomaso Journeyer
    Currently Being Moderated

    I created this procedure as a part of my email testing package.

     

    PROCEDURE TestEmail AS

        p_recipient VARCHAR2(500) := '<add your email address>';
        p_subject VARCHAR2(500) := 'Test attachement';
        p_message VARCHAR2(500) := 'This is email body';
        p_filename VARCHAR2(500) := 'test01.pdf';
        --
        -- Make sure directory object is valid and your PDF file is in it.
        --
        p_directory VARCHAR2(100) := '<add your existing oracle directory name';
       
        v_msg VARCHAR2(32000);
        src_file bfile;
        i integer := 1;
        v_raw raw(57);
        v_length integer := 0;
        v_buffer_size integer := 57;
        v_mailconn utl_smtp.connection;
        gc_crlf VARCHAR2(4) := chr(13) || chr(10);
        gc_lf VARCHAR2(4) := chr(10);
        gc_mailhost varchar2(30) := '192.168.200.1' ;
        BOUNDARY CONSTANT VARCHAR2(256) := '7D81B75CCC90D2974F7A1CBD';
        FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
        LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
        utl_tcp.CRLF;
        MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||BOUNDARY ||'"';

    BEGIN

        v_mailconn := utl_smtp.open_connection(gc_mailhost,25);
        utl_smtp.helo(v_mailconn,gc_mailhost);
        utl_smtp.mail(v_mailconn,'sender@domain.com');
        utl_smtp.rcpt(v_mailconn,p_recipient);
        utl_smtp.open_data (v_mailconn);
        utl_smtp.write_data (v_mailconn, 'From: ' || p_recipient || utl_tcp.crlf );
        utl_smtp.write_data (v_mailconn, 'To: ' || p_recipient || utl_tcp.crlf );
        utl_smtp.write_data (v_mailconn, 'Subject: ' || p_subject || utl_tcp.crlf );


        utl_smtp.write_data (v_mailconn, 'MIME-Version: 1.0' || utl_tcp.CRLF);
        utl_smtp.write_data (v_mailconn, 'Content-Type: multipart/mixed; boundary="'|| BOUNDARY || '"' || utl_tcp.CRLF);
        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

        utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);
        utl_smtp.write_data (v_mailconn, 'Content-Type: text/plain;'|| utl_tcp.CRLF);
        utl_smtp.write_data (v_mailconn, ' charset=US-ASCII'|| utl_tcp.CRLF);
        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );   
        utl_smtp.write_data (v_mailconn, 'This is email body' || utl_tcp.CRLF);   


        utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);
        utl_smtp.write_data (v_mailconn, 'Content-Type' || ': ' || 'application/pdf' || utl_tcp.crlf);
        utl_smtp.write_data (v_mailconn, 'Content-Disposition: attachment; ' || utl_tcp.crlf);
        utl_smtp.write_data (v_mailconn, ' filename="' || p_filename || '"' || utl_tcp.crlf);


        utl_smtp.write_data (v_mailconn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

        src_file := bfilename(p_directory, p_filename);
        dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
        v_length := dbms_lob.getlength(src_file);

        while i < v_length loop

           dbms_lob.read( src_file, v_buffer_size, i, v_raw );
           utl_smtp.write_raw_data( v_mailconn, utl_encode.base64_encode(v_raw) );
           i := i + v_buffer_size;

        end loop;

        utl_smtp.write_data( v_mailconn, utl_tcp.crlf );
        utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);
        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );
        dbms_lob.fileclose(src_file);
        utl_smtp.close_data (v_mailconn);
        utl_smtp.quit(v_mailconn);

    EXCEPTION

       WHEN OTHERS THEN
          utl_smtp.quit(v_mailconn);
      RAISE;

    END;

     

     

    HTH

    T.

  • 10. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    Panamanian Newbie
    Currently Being Moderated

    Wow, Thomaso, you are a "monster" (a person with plenty of knowledge).

     

    Finaly it worked as expected. Thanks a lot my friend.

     

    Cheers.

  • 11. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    Panamanian Newbie
    Currently Being Moderated

    I have another question. (dont worry, it's working good now). However...

    If I run it from an implicit store procedure:

     

    declare

    ...

     

    ...

    end;

     

    It works perfect, but if I create a store procedure "create or replace procedure ......"

    it sends me an "

    ORA-24247: network access denied by access control list (ACL)"

     

    why or what's the difference? in both cases I'm running them with the same oracle user.

     

    Are store procedures ran by another user? "SYS" or "SYSTEM" or something internally?

     

    Please clarify me.

     

    And a lot of thanks again.

  • 12. Re: Sending PDF attachment thru PL-SQL (sends garbage)
    thomaso Journeyer
    Currently Being Moderated

    In Oracle 11g and later you have to set up ACLs to let pl/sql programs access external urls.

    That includes SMPT servers.

     

    ACLs are "granting" access

    to a specific ip address or url or a group of addresses or a group of urls

    for a specific user (or procedure owner).

     

    So between Your run of the anonymous block

    BEGIN

    ...

    END;

    and Your stored procedure something has changed,

    either the user or the address of the SMTP server.

     

    DBMS_NETWORK_ACL_ADMIN

     

    HTH

    T.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points