SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Generate a Excel file and then send it as attachment using PL/SQL

Zafar IqbalApr 10 2013 — edited Jun 12 2013

Dear All,

I have a requirement to send email with attachment using PL/SQL procedure of 10g database. We will be querying data from database, generating excel(csv) file and attaching to email. How can I achieve this goal.
I have a procedure that will send email as HTML but it has limitations of sending 32K data.

CREATE OR REPLACE procedure send_email(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in varchar2 default null,
    p_html          in varchar2 default null,
    p_smtp_hostname in varchar2,
    p_smtp_portnum  in varchar2)
is
    l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection    utl_smtp.connection;
    l_body_html     clob := empty_clob;  --This LOB will be the email message
    l_offset        number;
    l_ammount       number;
    l_temp          varchar2(32767) default null;
begin
    l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
    utl_smtp.helo( l_connection, p_smtp_hostname );
    utl_smtp.mail( l_connection, p_from );
    utl_smtp.rcpt( l_connection, p_to );

    l_temp := l_temp || 'MIME-Version: 1.0' ||  chr(13) || chr(10);
    l_temp := l_temp || 'Date: ' || to_char( sysdate, 'dd Mon yy hh24:mi:ss' ) || chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from ||  chr(13) || chr(10);
    l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' || 
                         chr(34) || l_boundary ||  chr(34) || chr(13) || 
                         chr(10);

    ----------------------------------------------------
    -- Write the headers
    dbms_lob.createtemporary( l_body_html, false, 10 );
    dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


    ----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp   := '--' || l_boundary || chr(13)||chr(10);
    l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' || 
                  chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    ----------------------------------------------------
    -- Write the plain text portion of the email
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

    ----------------------------------------------------
    -- Write the HTML boundary
    l_temp   := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary || 
                    chr(13) || chr(10);
    l_temp   := l_temp || 'content-type: text/html;' || 
                   chr(13) || chr(10) || chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    ----------------------------------------------------
    -- Write the HTML portion of the message
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

    ----------------------------------------------------
    -- Write the final html boundary
    l_temp   := chr(13) || chr(10) || '--' ||  l_boundary || '--' || chr(13);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


    ----------------------------------------------------
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset  := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
        utl_smtp.write_data(l_connection,
                            dbms_lob.substr(l_body_html,l_ammount,l_offset));
        l_offset  := l_offset + l_ammount ;
        l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
    end loop;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit( l_connection );
    dbms_lob.freetemporary(l_body_html);
end;
/

Database Version:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Edited by: Zafar Iqbal on Apr 10, 2013 2:28 PM

BluShadow

It it just one attachment that needs sending?
If so, have you looked at using the UTL_MAIL package instead (it needs installing but it comes as part of the database installation).

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_mail.htm#ARPLS71198

1 person found this helpful

Billy Verreynne

Look at {message:id=10898700} for an example.

The 32KB is not a SMTP limit. It is a PL/SQL string size limited. In fact, strings send to SMTP (via the DATA command) should not exceed a 1000 characters per line, as specified in RFC 2821.

1 person found this helpful

Zafar Iqbal

Dear All,

I have created a CSV file with the help of following procedure, now how can I attach this file with email??

CREATE OR REPLACE procedure gen_excel(i_file_name in varchar2) as
  filename  utl_file.file_type;
  filename1 varchar2(1000);
  cursor c1 is
    select * from store_setup;
  varc1 c1%rowtype;
begin

  filename1   := 'TEST_' || i_file_name || '_' || sysdate || '.CSV';

  filename    := utl_file.fopen('TEMP_DIR', filename1, 'W');

  /* THIS WILL CREATE THE HEADING IN EXCEL SHEET */
    utl_file.put_line(filename,
                   'Store' || ',' || 'Description' || ',' || 'Dispatch Rule' || ',' ||
                    'Expired Stock Email(Y/N)' || ',' || 'Quanrantine Stock Email(Y/N)');
  open c1;
  loop
    fetch c1
       into varc1;
    exit when c1%notfound;
     /*  THIS WILL PRINT THE RECORDS IN EXCEL SHEET AS PER THE QUERY IN CURSOR */
    utl_file.put_line(filename,
                      '"' || varc1.store_code || '"' || ' ,' || '"' ||
                      varc1.description || '"' || ' ,' || '"' ||
                      varc1.dispatch_rule || '"' || ' ,' || '"' ||
                      varc1.exp_stk_mail || '"' || ' ,' || '"' ||
                      varc1.qrtn_stk_mail|| '"');


  end loop;

  utl_file.fclose(filename);

end;
/

Billy Verreynne

An attachment means mail and data/attachment together.

There is a single e-mail that is send. This single e-mail can contain many parts. One of these parts in that single e-mail, can contain the contents of a file. Or as it is commonly called, a file attachment.

So it is kind of silly to use UTL_FILE to create a CSV file to attach to an e-mail to send via PL/SQL. As PL/SQL now needs to use UTL_FILE again to read the file's contents and write it as one of the "parts" of that single e-mail.

A lot of wasted I/O - writing CSV file to disk, only to read CSV file again from disk.

Much easier and faster to simple write CSV contents directly, as a "part", to the e-mail that needs to be send. As I've demonstrated in the sample code that the above link refers to.

nkvkashyap

/* Formatted on 2011/09/08 12:23 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE send_mail_nkv_new (
msg_from VARCHAR2,
----- MAIL BOX SENDING THE EMAIL
msg_to VARCHAR2,
----- MAIL BOX RECIEVING THE EMAIL
msg_subject VARCHAR2,
----- EMAIL SUBJECT
msg_text VARCHAR2
)
IS
c UTL_TCP.connection;
rc INTEGER;
crlf VARCHAR2 (2) := CHR (13) || CHR (10);
mesg VARCHAR2 (32767);
v_string VARCHAR2 (32767);
v_output1 LONG;
BEGIN
v_output1 := 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO';

FOR c1 IN (SELECT *
FROM scott.emp)
LOOP
v_output1 :=
v_output1
|| CHR (10)
|| c1.empno
|| ','
|| c1.ename
|| ','
|| c1.job
|| ','
|| c1.mgr
|| ','
|| c1.hiredate
|| ','
|| c1.sal
|| ','
|| c1.comm
|| ','
|| c1.deptno;
END LOOP;

c := UTL_TCP.open_connection ('GIVE UR OUTLOOK SERVER DETAILS HERE', 25);
----- OPEN SMTP PORT CONNECTION
rc := UTL_TCP.write_line (c, 'HELO GIVE UR OUTLOOK SERVER DETAILS HERE');
----- PERFORMS HANDSHAKING WITH SMTP SERVER
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'HELO GIVE UR OUTLOOK SERVER DETAILS HERE');
----- PERFORMS HANDSHAKING, INCLUDING EXTRA INFORMATION
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || msg_from);
----- MAIL BOX SENDING THE EMAIL
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'RCPT TO: ' || msg_to);
----- MAIL BOX RECIEVING THE EMAIL
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'DATA'); ----- EMAIL MESSAGE BODY START
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc :=
UTL_TCP.write_line (c,
'Date: '
|| TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
);
rc :=
UTL_TCP.write_line (c, 'From: ' || msg_from || ' <' || msg_from || '>');
rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');
rc := UTL_TCP.write_line (c, 'To: ' || msg_to || ' <' || msg_to || '>');
rc := UTL_TCP.write_line (c, 'Subject: ' || msg_subject);
rc := UTL_TCP.write_line (c, 'Content-Type: multipart/mixed;');
----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
rc := UTL_TCP.write_line (c, ' boundary="-----SECBOUND"');
----- SEPERATOR USED TO SEPERATE THE BODY PARTS
rc := UTL_TCP.write_line (c, '');
----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD
rc := UTL_TCP.write_line (c, '-------SECBOUND');
rc := UTL_TCP.write_line (c, 'Content-Type: text/plain');
----- 1ST BODY PART. EMAIL TEXT MESSAGE
rc := UTL_TCP.write_line (c, 'Content-Transfer-Encoding: 7bit');
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, msg_text); ----- TEXT OF EMAIL MESSAGE
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, '-------SECBOUND');
rc := UTL_TCP.write_line (c, 'Content-Type: text/plain;');
----- 2ND BODY PART.
rc := UTL_TCP.write_line (c, ' name="Test.csv"');
rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');
rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');
----- INDICATES THAT THIS IS AN ATTACHMENT
rc := UTL_TCP.write_line (c, ' filename="Test.csv"');
----- SUGGESTED FILE NAME FOR ATTACHMENT
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, v_output1);
rc := UTL_TCP.write_line (c, '-------SECBOUND--');
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, '.'); ----- EMAIL MESSAGE BODY END
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
rc := UTL_TCP.write_line (c, 'QUIT'); ----- ENDS EMAIL TRANSACTION
DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));
UTL_TCP.close_connection (c); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000, SQLERRM);
END;
/

DECLARE
MSG_FROM VARCHAR2(200);
MSG_TO VARCHAR2(200);
MSG_SUBJECT VARCHAR2(200);
MSG_TEXT VARCHAR2(200);

BEGIN
MSG_FROM := 'UR EMAIL ID';
MSG_TO := 'UR EMAIL ID';
MSG_SUBJECT := 'Testing';
MSG_TEXT := 'Hi All,'||chr(10)||chr(10)||'Testing purpose'||chr(10)||chr(10)||'Thanks and Regards,'||chr(10)||chr(10)||'Kashyap Varma N';

APPS.SEND_MAIL_NKV_NEW ( MSG_FROM, MSG_TO, MSG_SUBJECT, MSG_TEXT );
COMMIT;
END;
/

After creating the procedure and executing the above wrapper, you will get a mail to ur inbox with an attachment, attached file name will be test.csv.

Please changes these in thwe above code.

1) 'GIVE UR OUTLOOK SERVER DETAILS HERE (IP Address or NAME of the server)
2) 'UR EMAIL ID' (Example abc@gmail.com)

Regards,
Kashyap Varma N

BluShadow

nkvkashyap wrote:
v_output1 LONG;

LONG datatype? Really?

http://docs.oracle.com/cd/B19306_01/server.102/b14220/datatype.htm#sthref3806

EXCEPTION
WHEN OTHERS
THEN
raise_application_error (-20000, SQLERRM);

Appalling exception handling... masking the real location of the error, and replacing the error number of any exception with a fixed -20000. Absolutely pointless.

COMMIT;

What is there to commit? Poor practice to commit when it's not needed. Commit should only be used to end a logical business transaction.

nkvkashyap

I accept no need of COMMIT. When we take wrapper of a procedure/function/package...... from TOAD, it is a default key word.

if needed you can comment that one.

Anton Scheffer

What is the point of writing the csv content into a long and then using UTL_TCP.write_line to write it out as UTL_TCP.write_line uses a varchar2 input parmeter.
As soon as the "csv-file" is larger as 32767 bytes it will raise an error.

Billy Verreynne

nkvkashyap wrote:
----- SUGGESTED FILE NAME FOR ATTACHMENT
rc := UTL_TCP.write_line (c, '');
rc := UTL_TCP.write_line (c, v_output1);
rc := UTL_TCP.write_line (c, '-------SECBOUND--');

Wrong. You are violating SMTP specifications.

And using UTL_TCP is just silly when UTL_SMTP provides an application protocol interface.

Your code is also flawed as you are suppose to READ the SMTP server's responses to determine whether your command to it was accepted or rejected. IMO.. this code you've posted is junk and an example of how not to write a SMTP client in PL/SQL.

Perhaps next time not copy-and-paste and use code you found somewhere on the web, that you do not fully understand?

nkvkashyap

Billy,
Why are you thinking that I copied the code from some web and pasted here. It is the code working perfectly from my end.

Billy Verreynne

nkvkashyap wrote:

Why are you thinking that I copied the code from some web and pasted here.

Because of the quality of code. Because the code is NOT using the SMTP application protocol correctly. It violates RFC 5321. And is the typical of some code I've seen on the web, written by persons who do not undertand either application or wire protocols, posted as 'example" (very poor ones at that) of how to use TCP and SMTP.

It is the code working perfectly from my end.

It just happens to work as all conditions are met for the shoddy code to work. It is not robust. It is unable to deal with SMTP server responses (not only a SMTP RFC violation, but a basic failure in TCP client-server programming).

Sorry - I do not find that code acceptable. Irrespective of whether it just happens to work or not.

I have 2 fundamental criteria for code written by a programmer:
1) the programmer must explain WHAT that single line of code does
2) the programmer must explain WHY that line of code is needed

If a programmer can answer both these, it means a sound understanding of both the problem and the solution. And the code posted shows a failure in both aspects. Incorrect use of TCP. Thus not understanding what the TCP calls actually do.

1 person found this helpful

Zafar Iqbal

Sir,

I appreciate the valuable comments. It has limitations of 32K size. How can i generate email more than this size even in MBs ??

Regareds,

Billy Verreynne

The 32KB limit is a PL/SQL string size (varchar2) limit. Not an e-mail limit.

Where are you running into the 32KB limit?

Zafar Iqbal

Dear Billy Verreynne

I have a PL/SQL procedure that send html email of some expiry stock summary. when the size of msg variable exceeds 32,767 bytes then it show the error

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "EXPIRY_ALERT_EMAIL", line 104

How can I generate that mail with attachment. The attachment may be in MBs????

procedure that generate expiry alert mail.

create or replace procedure expiry_alert_email is
    msg clob := empty_clob();    
begin

    declare
        cursor c1 is
            select a.location_code,
                         a.item_code,
                         substr(b.description, 1, 25) item_desc,
                         to_char(a.manufact_date,'ddMonyy')mfg,
                         to_char((a.manufact_date + nvl(b.expiry_period,0)),'ddMonyy')expr,
                         to_char((a.manufact_date + nvl(b.qurantine_period,0)),'ddMonyy')qrtn,
                         round(nvl (b.expiry_period, 0) - (sysdate - a.manufact_date)) days,
                         a.closing_balance_posted qty
                    from wms_stock_current_balance_v a, wms_item_setup_mast b
                   where a.closing_balance > 0
                     and a.item_code = b.item_code
                     and nvl(b.expiry_period,0) > 0
                     and round(nvl (b.expiry_period, 0) - (sysdate - a.manufact_date)) <= 0
                order by a.item_code, a.location_code;

    begin
         
            msg:= '<table BORDER=1 cellspacing="2" cellpadding="5" width="500% <FONT SIZE="1"></FONT>';

            msg:=msg||
                       '<Td align="LEFT"><FONT SIZE="2"><B> Location</B></FONT>
                        <Td align="LEFT"><FONT SIZE="2"><B> Item Code</B></FONT>
                        <Td align="LEFT"><FONT SIZE="2"><B> Description</B></FONT>
                        <Td align="CENTER"><FONT SIZE="2"><B> MFG</B></FONT>
                        <Td align="CENTER"><FONT SIZE="2"><B> Expr</B></FONT>
                        <Td align="RIGHT"><FONT SIZE="2"><B> Days Elapsed</B></FONT>
                        <Td align="RIGHT"><FONT SIZE="2"><B> Quantity</B></FONT>
                        </TD></TR>';

            msg:=    msg||'<tr align="CENTER" valign="BASELINE">';

            for rec in c1 loop

                msg := msg||
                            '<td align="LEFT"><FONT SIZE="2">'||rec.location_code||
                            '<td align="LEFT"><FONT SIZE="2">'||rec.item_code||
                            '<td align="LEFT"><FONT SIZE="2">'||rec.item_desc||
                            '<td align="CENTER"><FONT SIZE="2">'||rec.mfg||
                            '<td align="CENTER"><FONT SIZE="2">'||rec.expr||
                            '<td align="RIGHT"><FONT SIZE="2">'||rec.days||
                            '<td align="RIGHT"><FONT SIZE="2">'||rec.qty||
                             '</td></TR>';
            end loop;

            msg:=msg||'</Table>';
            
            html_email('receiver@company.com', 'sender@company.com', 'Expiry Alert', msg, 'xxx.xxx.x.xx','25');
    end;
end;
{code}

HTML_EMIL procedure that with send the mail using utl_smtp
{code}
CREATE OR REPLACE procedure html_email(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in varchar2 default null,
    p_html          in clob default null,
    p_smtp_hostname in varchar2,
    p_smtp_portnum  in varchar2)
is
    l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection    utl_smtp.connection;
    l_body_html     clob := empty_clob;  --This LOB will be the email message
    l_offset        number;
    l_ammount       number;
    l_temp          varchar2(32767) default null;
begin
    l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
    utl_smtp.helo( l_connection, p_smtp_hostname );
    utl_smtp.mail( l_connection, p_from );
    utl_smtp.rcpt( l_connection, p_to );

    l_temp := l_temp || 'MIME-Version: 1.0' ||  chr(13) || chr(10);
    l_temp := l_temp || 'Date: ' || to_char( sysdate, 'dd Mon yy hh24:mi:ss' ) || chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from ||  chr(13) || chr(10);
    l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' || 
                         chr(34) || l_boundary ||  chr(34) || chr(13) || 
                         chr(10);

    ----------------------------------------------------
    -- Write the headers
    dbms_lob.createtemporary( l_body_html, false, 10 );
    dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


    ----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp   := '--' || l_boundary || chr(13)||chr(10);
    l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' || 
                  chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    ----------------------------------------------------
    -- Write the plain text portion of the email
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

    ----------------------------------------------------
    -- Write the HTML boundary
    l_temp   := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary || 
                    chr(13) || chr(10);
    l_temp   := l_temp || 'content-type: text/html;' || 
                   chr(13) || chr(10) || chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    ----------------------------------------------------
    -- Write the HTML portion of the message
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

    ----------------------------------------------------
    -- Write the final html boundary
    l_temp   := chr(13) || chr(10) || '--' ||  l_boundary || '--' || chr(13);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


    ----------------------------------------------------
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset  := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
        utl_smtp.write_data(l_connection,
                            dbms_lob.substr(l_body_html,l_ammount,l_offset));
        l_offset  := l_offset + l_ammount ;
        l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
    end loop;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit( l_connection );
    dbms_lob.freetemporary(l_body_html);
end;
{code}

Anton Scheffer

You show a procedure expiry_alert_email which has only 55 lines, but throws an error at line 104???
Anyway, use a to_char for your number fields, number fields can't be converted implicit to a clob.

'<td align="RIGHT"><FONT SIZE="2">'||to_char(rec.days)||
'<td align="RIGHT"><FONT SIZE="2">'||to_char(rec.qty)||

Edited by: ascheffer on May 15, 2013 1:57 PM

Paul Horth

nkvkashyap wrote:
Billy,
Why are you thinking that I copied the code from some web and pasted here. It is the code working perfectly from my end.

It's peculiar how the comments in your code match exactly with part of the code found here: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL

Looks copied and then munged about: nothing wrong with that though, except if you claim the code is your own idea.

Zafar Iqbal

My question is how big file can I attach in the following procedures.???

UTL_MAIL.SEND_ATTACH_VARCHAR2
UTL_MAIL.SEND_ATTACH_RAW

I have create a CSV file having size of round about 620KB. but when I attached that file I got the error ORA-03113: end-of-file on communication channel

Zafar

BluShadow

Zafar Iqbal wrote:
My question is how big file can I attach in the following procedures.???

UTL_MAIL.SEND_ATTACH_VARCHAR2
UTL_MAIL.SEND_ATTACH_RAW

I have create a CSV file having size of round about 620KB. but when I attached that file I got the error ORA-03113: end-of-file on communication channel

Zafar

Ah, the problem is in your code.... just about .... there ----->

Seriously, you should post code when you have an issue.

And as for your first question, you can look up the procedure and information in the documentation:

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_mail.htm#ARPLS71198

and the datatype limits...

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/datatypes.htm#CJAEDAEA

Billy Verreynne

Zafar Iqbal wrote:
My question is how big file can I attach in the following procedures.???

UTL_MAIL.SEND_ATTACH_VARCHAR2
UTL_MAIL.SEND_ATTACH_RAW

32KB is the max size of the raw and varchar2 data types in PL/SQL.

If you want to use LOBs and create these first, and then write the contents of these using UTM_SMTP, I suggest using the formal LOB interface, DBMS_LOB.

I do not recommend using concatenation and similar quick-code approaches to LOBs. The formal interface requires a proper and structured approach. Makes it easier to read LOB interaction code. Makes it easier to understand. Make it easier to maintain.

Have a look at {message:id=10886604} for how to properly use LOBs. Scroll down a mesage or two for an example of how to send a CSV file attachment - without having to construct a LOB first (thus no additional memory overheads, and no additional I/O overheads).

Zafar Iqbal

Yes i accept that i have taken help from the link that you have provided.

but my problem has not solved. My query fetch hundreds record. I want to generate an CSV file and attach it with email(PL/SQL).

Marwim

After the forum "upgrade" the link from Billy {message:id=10886604} now should be

Regards

Marcus

1 - 21

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 10 2013

Added on Apr 10 2013

#pl-sql, #plsql

21 comments

23,024 views

SQL & PL/SQL

Generate a Excel file and then send it as attachment using PL/SQL

Comments

Post Details