Sending Mail with Multiple Attachment In PL/Sql

Oracle provides you two different packages for sending mail. Utl_Smtp package firstly introduced in Oracle 8i and Utl_Mail package firstly introduced in Oracle 10g. In my post, I will give example with Utl_Smtp package.

First of all you need to understand ‘MIME Type’ concept for my example. I will explain it briefly, but you can find more detail in here. We will use MIME standart for creating multiple part e-mail.

We will use common header MIME informations like To, Date, From etc. to standartize our email content. Boundry feature will be used for creating multi attachment mail. Every attachment will be divided by a boundry and every attachment will have its own content type and name. With content type parameter, you can specify text/plain, text/html etc. types. If your content type is set to text/html, you can use html tags like table, tr, br etc. and with this way you can create tables in your mail.

I will put my code directly here and going to explain it line by line. When you understand this code you can easily create your custom mail procedures with multiple attachments. (Generic package version of mine: click)

DECLARE
    v_from_name         VARCHAR2(100) := 'xyz@xyz.com';
    v_to_name           VARCHAR2(100) := 'zyx@xyz.com';

    v_subject           VARCHAR2(100) := 'This is an awesome mail';
    v_message_body      VARCHAR2(100) := 'Hey you!! Bla Bla Bluu';
    v_message_type      VARCHAR2(100) := 'text/plain';

    v_smtp_server       VARCHAR2(14)  := '10.200.xxx.xxx';
    n_smtp_server_port  NUMBER        := 25;
    conn                utl_smtp.connection;

    TYPE attach_info IS RECORD (
        attach_name     VARCHAR2(40),
        data_type       VARCHAR2(40) DEFAULT 'text/plain',
        attach_content  CLOB DEFAULT ''
    );
    TYPE array_attachments IS TABLE OF attach_info;
    attachments array_attachments := array_attachments();

    n_offset            NUMBER;
    n_amount            NUMBER        := 1900;
    v_crlf              VARCHAR2(5)   := CHR(13) || CHR(10);

BEGIN

  -- Fill data for example
    attachments.extend(5);
    FOR i IN 1..5
    LOOP
        SELECT 'test' || to_char(i) || '.txt','text/plain','test' || to_char(i)
        INTO attachments(i)
        FROM dual;
    END LOOP;

  -- Open the SMTP connection ...
    conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port);
    utl_smtp.helo(conn, v_smtp_server);
    utl_smtp.mail(conn, v_from_name);
    utl_smtp.rcpt(conn, v_to_name);

  -- Open data
    utl_smtp.open_data(conn);

  -- Message info
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('To: ' || v_to_name || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('From: ' || v_from_name || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Subject: ' || v_subject || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('MIME-Version: 1.0' || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: multipart/mixed; boundary="SECBOUND"' || v_crlf || v_crlf));

  -- Message body
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || v_message_type || v_crlf || v_crlf));
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || v_crlf));

  -- Attachment Part
    FOR i IN attachments.FIRST .. attachments.LAST
    LOOP
    -- Attach info
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND' || v_crlf));
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).data_type
                            || ' name="'|| attachments(i).attach_name || '"' || v_crlf));
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'
                            || attachments(i).attach_name || '"' || v_crlf || v_crlf));

    -- Attach body
        n_offset := 1;
        WHILE n_offset < dbms_lob.getlength(attachments(i).attach_content)
        LOOP
            utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(attachments(i).attach_content, n_amount, n_offset)));
            n_offset := n_offset + n_amount;
        END LOOP;
        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || v_crlf));
    END LOOP;

  -- Last boundry
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--SECBOUND--' || v_crlf));

  -- Close data
    utl_smtp.close_data(conn);
    utl_smtp.quit(conn);

END;

Line 2 – 11: There are simple declarations for my mail content. I use variables for the common things to provide changeability.

Line 13 – 19: I create a new type for attachments. Actually this isn’t necessary, but this way looks better for maintainability and simplicity. You can basicly keep the all information into the clob object and parse it to different parts. I prefer an OO design to keep algorithm simple. Line 13 – 17 contains a new type for attachment informations. An attach is simply constructed with three information: name, data type and content. In line 18, I create a new type for keep the multiple attach informations in an array. Finally, I create a variable from my array type and that’s all.

Line 21 – 22: Defines variable for CLOB operations. I will explain these with more details when the code is going on.

Line 23: In this line, there is a simple constant for the creating new lines for the MIME type.

Line 28 – 34: These lines create dummy attach informations and fill the attachments array. In the code, we will loop through this array and attach the necessary contents to the our mail.

Line 37 – 40: This is the second time that we use utl_smtp in this code. At the first time, we only create a variable for our connection. This connection variable has a type of utl_smtp.connection. In the current line set we initialize the this variable with necessary informations. We simply open the connection, say hello to server and then feed the connection with sender and recipient informations.

Line 42: We are opening the data block and after that we will start to fill content of the mail.

Line 46 – 51: In this part, we write necessary MIME informations to our mail header. After this, we say ‘SECBOUND’ is our boundry keyword. It means, when we put ‘–SECBOUND’ to our content, new boundry will be created and data will be written in this new boundry. Then we start to write our data to mail.

Line 54 – 56: At these lines we put our mail body to data part. We just create a new part with ‘SECBOUND’ and fill the message body.

Line 59 – 76: These lines manage the multiple attachment content for mail. Outer loop create new attachment in the mail and inner loop writes all attachment content to data part. We use CLOB for attachment content, so we write our content with looping the our CLOB object.

Line 79 – 83: Nothing tricky here. We only close our boundry and data part. After this line our mail must be sended..

You are free to change the content whatever you need and ask me more specification if you need:)

UPDATE: All UTL_SMTP.WRITE_DATA procedure calls replaced with UTL_SMTP.WRITE_RAW_DATA to support 8BITMIME characters. With this change you can send existing pdf, word or excel files using this code. A code sample is below and I use this package for sample:

DECLARE
    attachments pkg_send_mail.array_attachments := pkg_send_mail.array_attachments();
    b_input_file BFILE := BFILENAME('DIR_MYFOLDER', 'ExistingFile.pdf');
    c_output_file CLOB;
BEGIN
    dbms_lob.open(b_input_file, DBMS_LOB.LOB_READONLY);

    dbms_lob.createtemporary(lob_loc => c_output_file, cache => false);
    dbms_lob.open(c_output_file, DBMS_LOB.LOB_READWRITE);

    dbms_lob.loadfromfile(c_output_file, b_input_file, DBMS_LOB.LOBMAXSIZE);

    dbms_lob.close(b_input_file);

    attachments.extend(1);
    attachments(1).attach_name := 'ExistingFile.pdf';
    attachments(1).data_type := 'text/plain';
    attachments(1).attach_content := c_output_file;

    PKG_SEND_MAIL.SEND_MAIL('test@yourdomain.com','test@yourdomain.com','test','test',attachments => attachments);

    dbms_lob.close(c_output_file);
END;

Developers Rock!!!

Advertisements
This entry was posted in Oracle and tagged , , , , , , , . Bookmark the permalink.

70 Responses to Sending Mail with Multiple Attachment In PL/Sql

  1. Dinesh says:

    Your Logic looks good ..I have a doubt will this work for all type of files (csv) ??

  2. Aykut Akin says:

    If you put comma separated values in attachment_content and specify attach_name like ‘myfile.csv’, you simply send csv file.
    I created a generic package from this writing and put it in here: https://github.com/aykutakin/pkg_send_mail
    You can examine the samples and see how i send a excel(xls) file with a little html trick.

  3. Shubham says:

    Hi in which top need to keep the csv file for attachment. pl let me know.

    • Aykut Akin says:

      Hi,

      In my example, I just turn some dummy data to attachment. You can remove lines between 28-34 and open your csv file here, then you can add opened file data as clob attachment.
      The other way is, you can create a external file from your csv file and then you can iterate table with for loop. After concatenating columns and rows in variable you can add this variable as attachment. Probably this will have less performance than first method.

      If my explanations are not clear enough please let me know, I would be happy to help you with writing real code example

      • Shubham says:

        Hi,
        I have my external csv/excel file, on server in which location file to be kept for attachment?
        Or how could it be done through local machine i.e C:/attach?

        Thanks,
        Shubham

      • Aykut Akin says:

        Hi,

        – First you need to create Oracle directory to specify your file’s location:
        CREATE OR REPLACE DIRECTORY dir_attach AS 'C:\attach'
        I put a sample.csv file in C:\attach folder that contains below lines:

        a,b,c,d
        1,2,3,4
        x,y,z,t

        – Then you must read this file into a clob and send a mail with that variable. I’m giving example with UTL_FILE to read file, but you may find much better example if you search.
        I use the package version in this sample to make it short.


        DECLARE
        filehandler UTL_FILE.FILE_TYPE;
        clobsample CLOB;
        vLine VARCHAR2(100);
        attachments PKG_SEND_MAIL.ARRAY_ATTACHMENTS := PKG_SEND_MAIL.ARRAY_ATTACHMENTS();

        BEGIN
        filehandler := utl_file.fopen('DIR_ATTACH', 'sample.csv', 'R');

        IF utl_file.is_open(filehandler) THEN
        LOOP
        BEGIN
        utl_file.get_line(filehandler, vLine);
        clobsample := clobsample || vLine || UTL_TCP.CRLF;
        EXCEPTION WHEN no_data_found THEN
        EXIT;
        END;
        END LOOP;
        END IF;

        utl_file.fclose_all();

        attachments.extend(1);
        attachments(1).attach_name := 'sample.csv';
        attachments(1).data_type := 'text/plain';
        attachments(1).attach_content := clobsample;

        PKG_SEND_MAIL.SEND_MAIL('test@mailserver.com',
        'test@mailserver.com',
        'test','test',null,
        attachments);
        END;

        I hope this example help you. If any point is not clear please let me know.

      • yogesh says:

        can we send ZIp as attachment

      • Aykut Akin says:

        Hi,

        You can send any object using Oracle LOB types.

        Regards

  4. Shubham says:

    Hi,
    Thanks a lot for your support,
    1) I have created the directory and given read/write to public.
    2) Compiled PKG_SEND_MAIL(from your site) in my local database.
    3) Changed SMTP server of mine.
    4) ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at “SYS.UTL_TCP”, line 17
    ORA-06512: at “SYS.UTL_TCP”, line 267
    ORA-06512: at “SYS.UTL_SMTP”, line 161
    ORA-06512: at “SYS.UTL_SMTP”, line 197
    ORA-06512: at “SYSTEM.PKG_SEND_MAIL”, line 27
    ORA-06512: at line 2

    above error is occurring please let me know in this regards and send complete code.

    Thanks,

    • Aykut Akin says:

      I guess you are using Oracle 11g and you need to create an ACL to access network. Below code should solve your problem:

      begin

      --Create ACL
      dbms_network_acl_admin.create_acl (
      acl => 'networkacl.xml',
      description => 'Allow Network Connectivity',
      principal => 'YOUR DATABASE USER',
      is_grant => TRUE,
      privilege => 'connect'
      );

      --Add Privilege
      dbms_network_acl_admin.add_privilege (
      acl => 'networkacl.xml',
      principal => 'YOUR DATABASE USER',
      is_grant => TRUE,
      privilege => 'resolve'
      );

      --Assign ACL
      dbms_network_acl_admin.assign_acl(
      acl => 'networkacl.xml',
      host => 'YOUR SMTP SERVER ADDRESS OR HOST NAME'
      );
      commit;

      end;

      If you do not care your security so much, you may want to execute below code and give network access to all users:

      begin

      dbms_network_acl_admin.create_acl (
      acl => 'networkacl.xml',
      description => 'Allow Network Connectivity',
      principal => 'PUBLIC',
      is_grant => TRUE,
      privilege => 'connect'
      );

      dbms_network_acl_admin.assign_acl (
      acl => 'networkacl.xml',
      host => '*',
      lower_port => NULL,
      upper_port => NULL
      );
      commit;

      end;

      I believe that’ll do enough.
      Regards

  5. ufp1000 says:

    very nice….Its working for me…
    I have a requirement to send multiple attachments …
    these attachments come from BLOB objects and database server files from different location…
    BLOB is working for me…but
    How do I send files from server attached to the same mail…

    thanks..

    • Aykut Akin says:

      Hi,

      I’m not sure that I understand your question correctly, but I guess you want to send files which are not in your database server via this code, right?
      If this is the situation, you can get files via ftp protocol to your database server and then send them with this code. Oracle has necessary packages to do ftp job. If you don’t want to take files a place in your database server, you can delete them after sending the mail.

      If this answer doesn’t satisfy you, could you explain me what is your needs again?
      Regards

      • ufp1000 says:

        Hi,
        Thanks for replying.
        The idea of sending multiple attachments works for me as per your above code.
        But the files that I am getting in the mail are not opening well.
        Details:
        Requirement: MS Word files are uploaded as BLOB using a Form.
        mime type: application/msword
        Above code retrieves the files and puts in the mail with attachments as
        “abc.doc” “xyz.doc”.
        But when you open these files, they show some Hexadecimal characters written all over..

        (ERP Ver: 11i, DB ver: 11g.)

        Please help…

        Thanks

  6. ufp1000 says:

    @Aykut…any help…

    • Aykut Akin says:

      Hi,

      Sorry for late answer:( I just look around and try some solutions, but I’m not able to find to proper way of doing this. The best try I get is this one:


      DECLARE
      attachments PKG_SEND_MAIL.ARRAY_ATTACHMENTS := PKG_SEND_MAIL.ARRAY_ATTACHMENTS();
      b_input_file BFILE := BFILENAME('DIR_TEST', 'myfile.doc');
      b_output_file BLOB;
      c_output_file CLOB;
      i_warning INTEGER;
      i_dest_offset INTEGER := 1;
      i_src_offset INTEGER := 1;
      i_lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
      BEGIN
      dbms_lob.open(b_input_file, DBMS_LOB.LOB_READONLY);

      dbms_lob.createtemporary(lob_loc => b_output_file, cache => false);
      dbms_lob.open(b_output_file, DBMS_LOB.LOB_READWRITE);

      dbms_lob.loadfromfile(b_output_file, b_input_file, DBMS_LOB.LOBMAXSIZE);

      dbms_lob.close(b_input_file);

      dbms_lob.createtemporary(lob_loc => c_output_file, cache => false);

      dbms_lob.converttoclob(dest_lob => c_output_file,
      src_blob => b_output_file,
      amount => DBMS_LOB.LOBMAXSIZE,
      dest_offset => i_dest_offset,
      src_offset => i_src_offset,
      blob_csid => DBMS_LOB.DEFAULT_CSID,
      lang_context => i_lang_context,
      warning => i_warning);

      dbms_lob.close(b_output_file);

      attachments(1).attach_name := 'myfile.doc';
      attachments(1).data_type := 'application/msword';
      attachments(1).attach_content := c_output_file;
      PKG_SEND_MAIL.SEND_MAIL_WITH_ATTACH('test@test.com',
      'test@test.com',
      'test','test',
      NULL,attachments,
      'text/plain');

      END;

      This is useless too, but at least you can find some ‘real words’ inside the word document. I’m still searching and trying different things. If you find the solution before me, please let me know how you do it.

      Regards

    • Aykut Akin says:

      Hi,

      It has been a long time, but if you still need to send MS Word files, you can check updated part of the post.

      Regards

  7. Zafar Iqbal says:

    Hello Aykut Akin
    This post has really solved my lots of problems but when the data of CSV file is more than thousands then it will show me the error
    declare
    filehandler utl_file.file_type;
    clobsample clob;
    vline varchar2(100);
    filename1 varchar2(200) := ‘MoveToQuarantine.CSV’;
    attachments pkg_send_mail.array_attachments := pkg_send_mail.array_attachments();

    begin
    filehandler := utl_file.fopen(‘TEMP_DIR’, filename1, ‘R’);

    if utl_file.is_open(filehandler) then
    loop
    begin
    utl_file.get_line(filehandler, vline);
    clobsample := clobsample || vline || utl_tcp.crlf;
    exception when no_data_found then
    exit;
    end;
    end loop;
    end if;

    utl_file.fclose_all();

    attachments.extend(1);
    attachments(1).attach_name := filename1;
    attachments(1).data_type := ‘text/plain’;
    attachments(1).attach_content := clobsample;

    PKG_SEND_MAIL.SEND_MAIL(
    v_from_name => ‘test@company.com’,
    v_to_name => ‘receiver@company.com’,
    v_subject => ‘Move to Quarantine’,
    v_message_body => ‘Move to Quarantine’,
    attachments => attachments
    );

    end;

    when I run this it give me error at line 14
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

    Database 10g
    Version 10.2.0.1.0

    • Aykut Akin says:

      Hi Zafar,

      In line 14, I see this line: ‘utl_file.get_line(filehandler, vline);’. When I look the output parameter, which is vline variable for your example, is a Varchar2 type. It seems that your one line is larger than 4000 bytes, which is the maximum size for Varchar2. If you send file without editing it, you can try below code:

      DECLARE
      attachments pkg_send_mail.array_attachments := pkg_send_mail.array_attachments();
      b_input_file BFILE := BFILENAME('TEMP_DIR', 'MoveToQuarantine.CSV');
      c_output_file CLOB;
      BEGIN
      dbms_lob.open(b_input_file, DBMS_LOB.LOB_READONLY);

      dbms_lob.createtemporary(lob_loc => c_output_file, cache => false);
      dbms_lob.open(c_output_file, DBMS_LOB.LOB_READWRITE);

      dbms_lob.loadfromfile(c_output_file, b_input_file, DBMS_LOB.LOBMAXSIZE);

      dbms_lob.close(b_input_file);

      attachments.extend(1);
      attachments(1).attach_name := 'MoveToQuarantine.csv';
      attachments(1).data_type := 'text/plain';
      attachments(1).attach_content := c_output_file;

      PKG_SEND_MAIL.SEND_MAIL (
      v_from_name => 'test@company.com',
      v_to_name => 'receiver@company.com',
      v_subject => 'Move to Quarantine',
      v_message_body => 'Move to Quarantine',
      attachments => attachments
      );

      dbms_lob.close(c_output_file);
      END;

      This will directly load your file to clob and I believe this will solve your problems.

  8. Zafar Iqbal says:

    Dear Aykut Akin

    Bundles of thanks I have successfully mailed CSV file in attachment

    Regards,

  9. Zafar Iqbal says:

    Hi Aykut,

    Can we send email body data in HTML format???

    Regards

    • Aykut Akin says:

      Hi Zafar,

      Yes, you can. Here is a small sample for this:


      BEGIN
      PKG_SEND_MAIL.SEND_MAIL('send@test.com','receive@test.com','test',
      'html code here', NULL,'text/html');
      END;

      Regards

  10. Zafar Iqbal says:

    Hi Aykut,

    Bundles of thanks

    Regards,
    Zafar Iqbal

    • Zafar Iqbal says:

      Hi Aykut,

      I have a little bit question about sending email,

      How to can I enter multiple email IDs in CC??

      Best Regards,
      Zafar Iqbal

      • Aykut Akin says:

        Hi Zafar,

        You can use comma seperated values to enter email IDs in CC. For example you can give this string as a parameter to v_cc_name variable and see that every receiver is in the cc part of the mail : ‘abc@sample.com,xyz@sample.com,klm@sample.com’.

        Regards

  11. David Suazo says:

    hello my problem is that when you get the mail arrives with all the encoded file as it arrives to make full file and not what is inside the file. Thank you.

    • Aykut Akin says:

      Hi David,

      Can you inform me more about this situation? For example, what kind of file do you want to send (word,pdf,txt) ? What kind of encoding are you using?

      Thank you.

  12. Marina says:

    Hi, thanks for the informative post. Is there a reason you used n_amount=1900? I tried a different amount = 32000, and attachment is getting truncated, however works fine if I use 1900 as a step.
    Thanks,
    -Marina

    • Aykut Akin says:

      Hi Marina,

      There is no specific reason for 1900. I tried for 32000 and couldn’t find any problem. My test file has 23698 line and 3133905 character. My test database is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit. It seems that my whole file is sent without truncation. Can you give me more information about information and file?

      Regards.

  13. Stalin says:

    Hi Aykut ,

    I have created a html mail output using the following
    utl_smtp.write_rawdata(‘Sample Text’);
    but i need to send the above contents as pdf attachment.
    Tried with following
    Content-Type:application/octet-stream
    and also with
    Content-Type:application/pdf

    Can u Help me out !

    Thanks,
    Stalin.

    • Aykut Akin says:

      Hi,

      You can check updated part of the post if you are trying to send an existing file. If you are trying to create pdf file from scratch, I haven’t done anything like that and I will try that as soon as possible.

      Regards

  14. Hello.
    I already tried the code from your updated post, the mail is sending correctly, but when you receive the file and you try to open it is imposible, apparently the file have errors, have damage or something like that.
    I tried with a PDF and a PPTX file.
    Do you have an idea why this is happening?
    Thank you so much.
    Regards.

    • Aykut Akin says:

      Hi,

      Are you trying to send an existing pdf file or create a pdf file from scratch? This updated post will help you to send an already existing pdf document. If your need is to create a new pdf file, you must follow the pdf specifications document. If you want to create an entirely PDF document from the beginning, you must follow the recipe in the specification document and put some standard information to create proper pdf document. Otherwise, it will say pdf document has errors, like in your case. Below, I put a very basic example for pdf document that I found from the internet. Even numbers in the below code has some meanings to create a proper pdf file. You can search web or read the documentation for detail implementations.


      %PDF-1.4
      1 0 obj
      << /Type /Catalog /Outlines 2 0 R /Pages 3 0 R >>
      endobj
      2 0 obj
      << /Type Outlines /Count 0 >>
      endobj
      3 0 obj
      << /Type /Pages /Kids [4 0 R] /Count 1 >>
      endobj
      4 0 obj
      << /Type /Page /Parent 3 0 R /MediaBox [0 0 612 792] /Contents 5 0 R /Resources << /ProcSet 6 0 R >> >>
      endobj
      5 0 obj
      << /Length 35 >>
      stream
      … Page-marking operators …
      endstream
      endobj
      6 0 obj
      [/PDF]
      endobj
      xref
      0 7
      0000000000 65535 f
      0000000009 00000 n
      0000000074 00000 n
      0000000119 00000 n
      0000000176 00000 n
      0000000295 00000 n
      0000000376 00000 n
      trailer
      << /Size 7 /Root 1 0 R >>
      startxref
      394
      %%EOF

      Regards.

      • dnlopezm says:

        Thank you for your replay.
        Actually I was trying to send an existing file.
        I solved my problem.
        But I think your code have something wrong, because the files that you send arrives, but its can not be opening.
        Thanks again.
        Regards.

      • Aykut Akin says:

        Hi,

        I just tried my code and it worked for me properly (I tried with my package, not with the plsql block in the post). There was no problem for opening file. Can you explain, what was the problem and how did you solve your problem? If you specify the problematic point of the code, I would be happy to solve the wrong parts.

        Regards.

      • dnlopezm says:

        Well then If you say your code worked properly I don’t know where could be the problem. Did you try with a file from server?
        I will try to explain my original problem:
        I have sent several times files from server in an email, but only one file per email. What I needed this time is to send four files from server in one email. I tried with your code, for some reason that I don’t understand yet the files arrives to the destination, but It can not be opened because the files had damage.

        I found in another blog an example, so my code for attach more than one file in one email looks like:
        –The code needs a list separate by comas of the files you will attach, and a directory in which
        –the files exist.
        — Split up the attachment list

        loopcount := 0;
        Select count(*) into att_count from table(split(AttachList));
        if AttachList is not null and Directory is not null then
        for i in (Select ltrim(rtrim(column_value)) as attachment from table(split(AttachList))) loop
        loopcount := loopcount +1;
        v_log(‘Attaching: ‘||Directory||’/’||i.attachment);
        utl_file.fgetattr(Directory, i.attachment, file_exists, file_len, block_size);
        if file_exists then
        v_log(‘Getting mime_type for the attachment’);
        Select get_mime_type(i.attachment) into mime_type from dual;
        WriteLine( ‘Content-Type: ‘||mime_type );
        WriteLine( ‘Content-Transfer-Encoding: base64’);
        WriteLine( ‘Content-Disposition: attachment; filename=”‘||i.attachment||'”‘ );
        WriteLine;
        file_handle := bfilename(Directory,i.attachment);
        pos := 1;
        total := 0;
        file_len := dbms_lob.getlength(file_handle);
        dbms_lob.open(file_handle,dbms_lob.lob_readonly);

        loop
        if pos + 57 – 1 > file_len then
        read_bytes := file_len – pos + 1;
        –v_log(‘Last read – Start: ‘||pos);
        else
        –v_log(‘Reading – Start: ‘||pos);
        read_bytes := 57;
        end if;
        total := total + read_bytes;
        dbms_lob.read(file_handle,read_bytes,pos,data);
        utl_smtp.write_raw_data(smtp,utl_encode.base64_encode(data));
        –utl_smtp.write_raw_data(smtp,data);
        pos := pos + 57;
        if pos > file_len then
        exit;
        end if;
        end loop;
        v_log(‘Length was ‘||file_len);
        dbms_lob.close(file_handle);
        if (loopcount < att_count) then
        WriteLine;
        WriteLine( '–gc0p4Jq0M2Yt08jU534c0p' );
        else
        WriteLine;
        WriteLine( '–gc0p4Jq0M2Yt08jU534c0p–' );
        v_log('Writing end boundary');
        end if;
        else
        v_log('Skipping: '||Directory||'/'||i.attachment||'Does not exist.');
        end if;
        end loop;
        end if;

        reply := utl_smtp.close_data( smtp );
        reply := utl_smtp.quit( smtp );

      • Aykut Akin says:

        Hi,

        I’ve tested all of the updated post with a file from server before I updated. I tried it today again and everything is gone well. I just send pdf file to the server via ftp and then created a folder in Oracle. Then I just did what I wrote in the post. I will try to find the problem. If you find any clue please let me know.

        Regards.

  15. Girish Sharma says:

    Hi
    Can you please help me (even though your this page might have solution, but since I am not PL/SQL Developer) in below requirement:
    1.There is a table in a schema and some rows :
    create table student_data
    (
    roll_number number,
    physics number,
    chemistry number,
    IT number,
    maths number
    )
    /
    insert into student_data values (100,20,30,35,38);
    insert into student_data values (101,null,42,35,null);
    insert into student_data values (102,30,30,30,30);
    insert into student_data values (103,31,32,33,34);
    insert into student_data values (104,null,null,null,null);
    insert into student_data values (105,null,null,null,22);

    Now, I want to show the marks in pie chart image file, wish to attach with email and send the email with pie chart as an attached image i.e. something like this :
    execute pieandmail(‘select * from student_data where roll_number=100′,’abc@abc.com’);
    this should generate pie chart based upon marks of roll_number 100 in directory location and send it to the given mail id as an attached image. If there are null values then pie chart should be accordingly shown. Pie chart should show the marks with subject name and its values and subject of mail should have something like this : “Pie Chart for Roll Number “.

    Kindly help me, I am not getting which one is the best way to achieve this.

    Regards
    Girish Sharma

    • Aykut Akin says:

      Hi,

      I think, you have very tough requirements to do. You need different technologies to achieve this work. Maybe you can read this post to get an idea. This uses a javascript function to create pie chart. You can create a html file (the file does not have to be on the disk, it may be in variable like your select result) and send it with mail. With that way, you can use javascript easily. You may need some customization according to your needs. I will try to implement the solution similar to the link I send you in my free time, but unfortunately that can not be soon. If you need an image file directly, you can use language like python or java for creating those files. I am sure, you can find good libraries out there. Later you can send it with mail in pl/sql.

      Regards

      • Girish Sharma says:

        I am really thankful to you for your fast and helpful reply. I shall take a try of the solution which you posted in the link and wait to see your solution as and when you gets free time. Actually this problem have many folds :
        1.Suppose I am going to create pie chart from X technology, so in that tech I must have to known how to connect oracle database.
        2.Whatever written in the step 1, how to call that in PL/SQL
        3.PL/SQL procedure will read and attach the image file to the mail and mail sending.
        4.Problem becomes more tough, due to null values in the columns.
        So, all these are the points which I really don’t know anything, but shall take this as an opportunity to learn PL/SQL and/or similar tech.
        Once again thank you so much for your reply.

        Regards
        Girish Sharma

      • Aykut Akin says:

        At first, I think pl/sql is not a proper language for your goal. In my opinion, you shouldn’t send an email or call a web service from the database. Because, it is not why databases are designed for. But if pl/sql is your only option, you must use X technology one way or another for your case. Once upon a time I wrote a package that turns sql query to html table. I edit that package for your problem based on the link I sent you. You can get package from here. You can use it with below code:

        declare
        attachments pkg_send_mail.array_attachments := pkg_send_mail.array_attachments();
        begin
        attachments.extend(1);

        for rec in (select ROLL_NUMBER from STUDENT_DATA)
        loop
        attachments(1).attach_name := 'RollNumber' || rec.ROLL_NUMBER || '.html';
        attachments(1).data_type := 'text/html';
        attachments(1).attach_content := PKG_TABLE_TO_HTML.ROW_TO_PIE_CHART_HTML('select PHYSICS,CHEMISTRY,IT,MATHS from STUDENT_DATA where roll_number=' || rec.ROLL_NUMBER, 'DENEME');
        pkg_send_mail.send_mail_with_attach('test@company.com',
        'receiver@company.com',
        'Report for ' || rec.ROLL_NUMBER,
        'Check attachment',
        null,
        attachments,
        'text/plain'
        );
        end loop;
        end;

        It expects only one row. Otherwise there will be no error, but you won’t be able to see pie chart properly. I eliminated null values in javascript part. My code contains really bad practices and not properly organized. I hope it will help you somehow as a start point. Please ask me if there is missing part.

        Regards.

  16. Girish Sharma says:

    My bad, I forgot to mention :
    Oracle 11.2.0.1 Enterprise Edition 64 bit
    Windows 7 Home Edition 64 bit

    Regards
    Girish Sharma

  17. Girish Sharma says:

    SQL> declare
    2 attachments pkg_send_mail.array_attachments := pkg_send_mail.array_attachments();
    3 begin
    4 attachments.extend(1);
    5
    6 for rec in (select ROLL_NUMBER from STUDENT_DATA)
    7 loop
    8 attachments(1).attach_name := ‘RollNumber’ || rec.ROLL_NUMBER || ‘.html’;
    9 attachments(1).data_type := ‘text/html’;
    10 attachments(1).attach_content := PKG_TABLE_TO_HTML.ROW_TO_PIE_CHART_HTML(‘select PHYSICS,CHEMISTRY,IT,MATHS from STUDENT_DATA where roll_number=’ || rec.ROLL_NUMBER, ‘DENEME’);
    11 pkg_send_mail.send_mail_with_attach(‘mymailid@gmail.com’,
    12 ‘Report for ‘ || rec.ROLL_NUMBER,
    13 ‘Check attachment’,
    14 null,
    15 attachments,
    16 ‘text/plain’
    17 );
    18 end loop;
    19 end;
    20 /
    pkg_send_mail.send_mail_with_attach(‘mymailid@gmail.com’,
    *
    ERROR at line 11:
    ORA-06550: line 11, column 15:
    PLS-00302: component ‘SEND_MAIL_WITH_ATTACH’ must be declared
    ORA-06550: line 11, column 1:
    PL/SQL: Statement ignored

    SQL>
    As you suggested it may be good start point, so I tried to send the mail with pie chart as an attachment to my gmail id, but I am not getting above error please.
    I just copied your code from github link and all package and package body created successfully.
    As you said in the last reply :
    I eliminated null values in javascript part. My code contains really bad practices and not properly organized.
    So, I am not getting which javascript part you are talking about and why above code is comes in bad practices, not organized. I did not run any javascript part or file. I am just at SQLplus prompt.

    Regards
    Girish Sharma

    • Aykut Akin says:

      Sorry, it is my fault. I’ve tried with my own database but, procedure names are different. Can you change it to ‘PKG_SEND_MAIL.SEND_MAIL’ ?

      If you check the PL/SQL code, you will encounter with a function that called ‘JS_PIE_FUNC’. That includes javascript code that I’m talking about, but it is represented in clob variable. Basically, you create a variable that includes a web page content in your PL/SQL code. After that, you send it with mail like it was a html file. When client opens the attachment html file, Javascript code will run at page’s onload event, I mean before client see the web page’s full content. That way, it seems that you put a pie chart in your attachment. Like you say, you don’t run any javascript code. You just put it to attachment and than watch the magic happen:) That part happens in client side.

      When I look my own code, I don’t feel like the code is really smooth. Like I said, I try to change some old code and that is always really hard thing to do. I believe, it can be written more properly. If you check ‘ROW_TO_PIE_CHART_HTML’ function, you will see, it opens same cursor twice. It will be better to open it only once and use ‘out’ variables to get javascript data array and html table content at the same time. I just take the wrong but the fast way to deliver the some working code to you. I just try to use old functions that I wrote with minimal changes and try to keep old contents as much as unchanged.

      Regards

      • Girish Sharma says:

        I followed below link for ACL configuration:
        http://www.oracle-base.com/articles/11g/fine-grained-access-to-network-services-11gr1.php
        All went fine. Oracle 11.2.0.1 Enterprise Edition Windows XP Professional 32 bit
        set line 200;
        COLUMN acl FORMAT A30
        COLUMN principal FORMAT A30

        SELECT acl,
        principal,
        privilege,
        is_grant,
        TO_CHAR(start_date, ‘DD-MON-YYYY’) AS start_date,
        TO_CHAR(end_date, ‘DD-MON-YYYY’) AS end_date
        FROM dba_network_acl_privileges;
        ACL PRINCIPAL PRIVILE IS_GR START_DATE END_DATE
        —————————— —————————— ——- —– ———– ———–
        /sys/acls/test_acl_file.xml TEST1 connect true 09-AUG-2014
        /sys/acls/test_acl_file.xml SCOTT connect false

        SQL> show user;
        USER is “SCOTT”
        SQL> BEGIN
        2 PKG_SEND_MAIL.SEND_MAIL(‘mymailid@gmail.com’,’mymailid@gmail.com’,’test’,’test’);
        3 END;
        4 /
        BEGIN
        *
        ERROR at line 1:
        ORA-24247: network access denied by access control list (ACL)
        ORA-06512: at “SYS.UTL_TCP”, line 17
        ORA-06512: at “SYS.UTL_TCP”, line 246
        ORA-06512: at “SYS.UTL_SMTP”, line 127
        ORA-06512: at “SYS.UTL_SMTP”, line 150
        ORA-06512: at “SCOTT.PKG_SEND_MAIL”, line 25
        ORA-06512: at line 2

        But, now I am fighting with ORA-24247. Google is full of big big solution(s), but I am not getting where I am doing mistake. This is my test machine and database for learning purpose. I am sorry, topic is going bit diverted, but I think once if I am able to send a simple hello mail and message from your code, then it would be great to explore it further for pie-chart and similar issue(s) if any.

        Regards
        Girish Sharma

      • Aykut Akin says:

        Hi,

        Did you do the necessary configurations to use your gmail account as a smtp server? Some links about that: 1 2 3 4. One of the above comments, I posted a some codes about ACL. If security is not important for your test machine, you can give access control globally.

        Regards

  18. Mohammad Kaleem says:

    Hi dear its my code of sending an email but it is showing the following error
    sending failed : 29179 : smtp permanent error 502.5.5.0: unrecognized command k5sm153625 win 21-gsmtp

    CREATE OR REPLACE PROCEDURE sendMail (
    smtpHost in varchar2,
    smtpPort in pls_integer default 587,
    mailFrom in varchar2,
    rcptTo in varchar2,
    messageSubject in varchar2,
    messageBody in varchar2,
    username in varchar2,
    password in varchar2
    )

    IS
    l_conn UTL_SMTP.connection;
    L_ENCODED_USERNAME varchar2(2048);
    L_ENCODED_PASSWORD varchar2(2048);
    crlf varchar2(2) := chr(10) || chr(13);

    BEGIN
    l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));
    l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));

    l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);

    UTL_SMTP.ehlo(l_conn, smtpHost);

    utl_smtp.command(l_conn, ‘smtp.mail.STARTTLS:true’);

    UTL_SMTP.command(l_conn, ‘AUTH’, ‘LOGIN’);

    UTL_SMTP.command(l_conn, l_encoded_username);

    UTL_SMTP.command(l_conn, l_encoded_password);
    –prepare headers
    UTL_SMTP.mail(l_conn, mailFrom);
    UTL_SMTP.rcpt(l_conn, rcptTo);
    UTL_SMTP.open_data(l_conn);
    UTL_SMTP.write_data(l_conn, ‘To: ‘ || rcptTo || crlf);
    UTL_SMTP.write_data(l_conn, ‘From: ‘ || mailFrom || crlf);
    UTL_SMTP.write_data(l_conn, ‘Subject: ‘ || messageSubject || crlf);
    UTL_SMTP.write_data(l_conn, messageBody || crlf || crlf);
    UTL_SMTP.close_data(l_conn);
    UTL_SMTP.quit(l_conn);
    END;
    /

    —————–;;;;;;——————-Then call it……………;;;;;;;……………….

    begin
    sendMail (
    username => ‘ someone@gmail.com ‘ ,
    password => ‘ aaaaaaa ‘ ,
    smtpHost => ‘smtp.gmail.com’,
    smtpPort => ‘ 587 ‘ ,
    mailFrom => ‘ someone@gmail.com‘ ,
    rcptTo => ‘ someone@gmail.com‘ ,
    messageSubject => ‘Test subject’,
    messageBody => ‘Test body’) ;
    EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(‘Sending failed: ‘||SQLERRM);
    END;

  19. Johnny says:

    Hey, I have just downloaded your code and it works fine, emails come through with PDF attached, easy to use, you saved a lot of extra work for me, thanks!

  20. yogesh says:

    hi I am able to send mail with zip attachment but that zip is not opening.

  21. yogesh says:

    Hi,
    I am able to send mail with zip attachment but that zip is not opening. giving following error
    can not open file as archive

  22. nikita says:

    Hi

    This package help me alot .Thank you so much 🙂

  23. Sujit says:

    The server side PDF file is attached and received successfully but it is showing blank. The same PDF is opening once I ftp it to my computer. What could be the reason?

  24. Sajid says:

    Dear Aykut,

    I wrote a cursor which fetch multiple record(eg Name ,age,DOB) and now i wanted to send an email as an attachment in such a way that it should look like this

    Name Age DOB
    abc 20 20-11-1995
    ….. … …………..

    Could you please provide me the complete code?

    Many thanks in advance for assisting us

    Regards,
    Sajid

  25. Naresh says:

    Hi Aykut,

    We have a file in server (path-‘/oradata_gdpdv/TEMP’ size more than 32K). now i am using SYS.UTL_FILE.GET_RAW. but this is not supporting more than size 32K.
    I am getting below error
    error ORA-06525: Length Mismatch for CHAR or RAW data.

    so can you please help me how we can proceed .
    can we use smtp_utl??

    Please help me with sample code.

    • Aykut Akin says:

      Hi,

      I think, you can use dbms_lob package to read external file. There is an example with using this package at the end of the post. There shouldn’t be any problem with the mail sending code, because it writes raw data with chunks.

      Regards

  26. vkishv says:

    Hi

    I need to check the size of the attachments before sending the mail. If they are greater than an X, i don’t attach the file to the mail. Also i have several attachments. If the total size of the attachments is greater than Y, then a notification email should be sent.

    How can i do that? Can you help me?
    Thanks

    • Aykut Akin says:

      Hi,

      Since you are adding content in loop, you can use pl/sql’s lengthb function on varchar variable and get sum of all the returning values. After that you can decide using this total to decide according to your limitations.

      Regards.

  27. Jiddesh says:

    Hello,

    I am trying to send an attachment via an SMTP email that fetches data from a Database.
    I have created a database procedure that would do the complete job however I face one challenge as stated belows :

    – One of the columns in the table that the proc fetches data from is of type varchar that stores text. Here, for your ref. assume the text in that column is of the format just like this comment of mine with “multiple lines”. So, when a run a database cursor to fetch all the rows and then when I loop the rows to be printed in an excel with a variable, “the new lines” (from that column) are printed in new lines on the excel.

    Would like to know how can I print the complete formatted text in one column of an excel with my proc. ?

    Appreciate your help !

    Thanks,
    Jiddesh

  28. Jiddesh says:

    Hello,

    I am trying to send an attachment via an SMTP email that fetches data from a Database.
    I have created a database procedure that would do the complete job however I face one challenge as stated belows :

    – One of the columns in the table that the proc fetches data from is of type varchar that stores text. Here, for your ref. assume the text in that column is of the format just like this comment of mine with “multiple lines”. So, when a run a database cursor to fetch all the rows and then when I loop the rows to be printed in an excel with a variable, “the new lines” (from that column) are printed in new lines on the excel.

    Would like to know how can I print the complete formatted text in one column of an excel with my proc. ?

    Appreciate your help !

    NOTE : Posting the comment with another mail address of mine. Feel free to reach me any where.

    Thanks,
    Jiddesh

    • Aykut Akin says:

      Hello,

      How do you generate the excel file? For example, I use a workaround to create excel file with generating html content using this package. When I generate html content and save it with xls format, it will open via MS Office and will be seen as table. Since the file only include html, I can replace the enter characters (/n) with html break line character (
      ). With this replacement it will be showed formatted in only one cell.

      Best regards,
      Aykut

      • Jiddesh says:

        Hello Aykut,

        Thank You for your replies !

        I am generating an excel with below code :

        1) The column headers :
        v_attach_clob1 := ‘Month : ‘ || CURRENT || chr(13) || chr(10) || chr(10) || ‘PD REWARDS DAILY REPORT’ || chr(13) || chr(10) || chr(10) || ‘SR. NO.’ || chr(9) || ‘RECEIVERSSO’ || chr(9) || ‘REWARD AMOUNT’ || chr(9) || ‘STATUS’ || chr(9) || ‘TEXT’ || chr(9) || ‘MANAGER SSO’ || chr(9) || ‘SENDER SSO’ || chr(9) || ‘RECEIVER COUNTRY’ || chr(9) || ‘LAST MODIFIED’ ||chr(10) || CHR(13) ;
        v_attach_name := ‘PD_REWARDS_’ || CURRENT || ‘.xls’;

        2) Data in rows are filled with a cursor as belows :
        FOR rec1 IN CUR_DATA1
        LOOP
        v_attach_clob1 := v_attach_clob1 || rec1.rownum || chr(9) || rec1.receiversso || chr(9) || rec1.Reward_Amount || chr(9) || rec1.STATUS || chr(9) || rec1.TEXT || chr(9) || rec1.MANAGERSSO || chr(9) || rec1.SENDERSSO || chr(9) || rec1.RECEIVERCOUNTRY || chr(9) || rec1.LASTMODIFIED || chr(13);
        END LOOP;

        –Here above, the rec1.TEXT is assumed to contain multi-line data fetched from a DB cell.

        3) And then finally the below code to attach the excel and send via email.
        l_mail_conn := UTL_SMTP.open_connection(mailhost, mailport);
        UTL_SMTP.helo(l_mail_conn, mailhost);
        UTL_SMTP.mail(l_mail_conn, v_from);
        UTL_SMTP.RCPT(l_mail_conn, ‘jiddesh.shewale@ge.com’);
        UTL_SMTP.open_data(l_mail_conn);
        UTL_SMTP.write_data(l_mail_conn, ‘From: ‘ || v_from || UTL_TCP.crlf);
        UTL_SMTP.WRITE_DATA(l_mail_conn, ‘To’ || ‘: ‘ || ‘jiddesh.shewale@ge.com’ || UTL_TCP.CRLF);
        UTL_SMTP.write_data(l_mail_conn, ‘Subject: ‘ || v_subject || UTL_TCP.crlf);
        –MIME header.
        UTL_SMTP.write_data(l_mail_conn, ‘MIME-Version: 1.0’ || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, ‘Content-Type: multipart/mixed; boundary=”‘ || l_boundary || ‘”‘ || UTL_TCP.crlf || UTL_TCP.crlf);

        IF v_text_msg IS NOT NULL THEN
        UTL_SMTP.write_data(l_mail_conn, ‘–‘ || l_boundary || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn,
        ‘Content-Type: text/html; charset=”iso-8859-1″‘ || UTL_TCP.crlf || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, UTL_TCP.CRLF || v_text_msg);
        UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
        END IF;
        IF v_attach_name IS NOT NULL THEN
        UTL_SMTP.write_data(l_mail_conn, ‘–‘ || l_boundary || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, ‘Content-Type: ‘ || NULL || ‘; name=”‘ || v_attach_name || ‘”‘ || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, ‘Content-Disposition: attachment; filename=”‘ || v_attach_name || ‘”‘ || UTL_TCP.crlf || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, v_attach_clob1);
        UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
        END IF;
        — close email
        UTL_SMTP.write_data(l_mail_conn, ‘–‘ || l_boundary || ‘–‘ || UTL_TCP.crlf);
        UTL_SMTP.close_data(l_mail_conn);
        UTL_SMTP.quit(l_mail_conn);

        —-
        If you could suggest of a code change to make the process short and sweet that I shall make in above logic to make it work would be appreciative !

        I tried to replace the new lines with space initially –> REPLACE(TEXT, CHR(10), ‘ ‘) while populating the cursor. However, that isn’t the ideal way I’d like to go-ahead messing the real data.

        Thanks,
        Jiddesh

      • Aykut Akin says:

        Hello,

        Actually, you are using another workaround similar to mine. Rather than creating html formatted data, you are creating tab separated data and save it in xls format. Excel automatically parse the data and format it properly. I just tried one possible solution manually and it seems like what I’ve tried may solve your problem. Can you try to use double quotes (“) when you are concatenating multi-line column? For example, if the TEXT column is multi-line just try this: chr(9) || chr(34) || rec1.TEXT || chr(34) || chr(9)

        Best regards,
        Aykut

      • Jiddesh says:

        Thank You Aykut !!

        Happy to inform you that your suggestion worked. Appreciate your help !
        Have a great weekend.

        Thanks,
        Jiddesh

      • Jiddesh says:

        Hello Aykut,

        I learned while testing that if the TEXT field itself contains any double quotes for example : –> Hello, this is “World” welcome! then the rows in the MS-excel gets printed on new lines after encountering double quotations. I have three such rows with double quoted characters in between and only the first row amongst these gets spoils printing the next texts over new lines while the other rows are printed picture perfect. Is there any work-around that you could suggest to have these double quotes from TEXT field still be printed as it is in the cells without spoiling the look of the Excel file ?

        Just for ref. – I have Open Office software and the below code works well taking care of double quotes in Open Office softwares only however this piece of code spoils the output over MS-excel. My aim is to make the MS-Excel output look perfect just like we see in database without spoiling it.
        chr(9) || chr(34) || chr(13) rec1.TEXT || chr(13) || chr(34) || chr(9)

        Appreciate your help !

        Thanks,
        Jiddesh

      • Aykut Akin says:

        Hello Jiddesh,

        You have to escape quotes in the TEXT field. As my search in the internet you can escape them repeating quotes. Can you try this code: chr(9) || chr(34) || replace(rec1.TEXT,'"', '""') || chr(34) || chr(9)

        Best regards,
        Aykut

  29. Heena says:

    hello …i try to send mail with multiple attachment with dynamic table data..how can i do please tell me

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s