RE: Help! I need to generate excel files from PL/ SQL

           chtml_email(p_to =3D> c.rep_destination,
                      p_from =3D> 'fromemail@xxxxxxxx',
                      p_subject =3D> c.rep_dest_comments,
                      p_text =3D> c.rep_dest_comments,
                      p_html =3D> v_rclob, // HERE I PASS an HTML =
document containing the data formatted as a table
                      p_smtp_hostname =3D> 'mailserver,
                      p_smtp_portnum =3D> '25',
                      p_content_type =3D> 'content-type: =
application/vnd.ms-excel; charset=3DWINDOWS-1251; name=3D'||p_fn); =
//p_fn is the file name to appear as an attachment



procedure chtml_email(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in varchar2 default null,
    p_html          in clob, --varchar2 default null,
    p_smtp_hostname in varchar2,
    p_smtp_portnum  in varchar2,
    p_content_type in varchar2 default 'content-type: =
application/vnd.ms-excel; charset=3D"windows-1251"'||
    ' Content-Transfer-Encoding: 8bit  name=3Dtest.xls')
is
    l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection    utl_smtp.connection;
    l_body_html     clob :=3D empty_clob;  --This LOB will be the email =
message
    l_offset        number;
    l_ammount       number;
    l_temp          varchar2(32767) default null;
begin
    l_connection :=3D 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 :=3D l_temp || 'MIME-Version: 1.0' ||  chr(13) || chr(10);
    l_temp :=3D l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp :=3D l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp :=3D l_temp || 'Subject: ' || p_subject || chr(13) || =
chr(10);
    l_temp :=3D l_temp || 'Reply-To: ' || p_from ||  chr(13) || chr(10);
-- ilian: Changed for Attachments
    l_temp :=3D l_temp || 'Content-Type: multipart/mixed; boundary=3D' =
||=20
                         chr(34) || l_boundary ||  chr(34) || chr(13) || =

                         chr(10);
/*    l_temp :=3D l_temp || 'Content-Type: multipart/alternative; =
boundary=3D' ||=20
                         chr(34) || l_boundary ||  chr(34) || chr(13) || =

                         chr(10);*/

    dbms_lob.createtemporary( l_body_html, false, 10 );
    dbms_lob.write(l_body_html,length(l_temp),1,l_temp);



    l_offset :=3D dbms_lob.getlength(l_body_html) + 1;
    l_temp   :=3D '--' || l_boundary || chr(13)||chr(10);
    l_temp   :=3D l_temp || 'content-type: text/plain; =
charset=3D"windows-1251" '||
    'Content-Transfer-Encoding: 8bit  ' ||=20
                  chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


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


    -- HTML ( Attachment ) boundary
    l_temp   :=3D chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary =
||=20
                    chr(13) || chr(10);
--    l_temp   :=3D l_temp || 'content-type: text/html;' ||=20
    l_temp   :=3D l_temp || p_content_type ||--'content-type: =
application/winzip; name=3Dblabla.zip' || --<-- imeto na fajla e =
optional
                   chr(13) || chr(10) || chr(13) || chr(10);
    l_offset :=3D dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


    -- HTML portion of the message
    l_offset :=3D dbms_lob.getlength(l_body_html) + 1;
    --ILIAN =
dbms_lob.write(l_body_html,dbms_lob.getlength(p_html),l_offset,p_html);
    dbms_lob.append(l_body_html,p_html);

    ----------------------------------------------------
    -- Write the final html boundary
    l_temp   :=3D chr(13) || chr(10) || '--' ||  l_boundary || '--' || =
chr(13);
    l_offset :=3D 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  :=3D 1;
    l_ammount :=3D 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
--utl_raw.cast_to_raw(
/*        utl_smtp.write_data(l_connection,
                            =
dbms_lob.substr(l_body_html,l_ammount,l_offset));*/
/*    v_raw :=3D utl_raw.convert(
            utl_raw.cast_to_raw( =
dbms_lob.substr(l_body_html,l_ammount,l_offset)=20
                                )
           =
,'american_america.CL8ISO8859P5','american_america.CL8MSWIN1251')*/
        utl_smtp.write_raw_data(l_connection,utl_raw.convert(
            utl_raw.cast_to_raw( =
dbms_lob.substr(l_body_html,l_ammount,l_offset)=20
                                )
          , =
'american_america.CL8MSWIN1251','american_america.CL8ISO8859P5'));       =
                    =20
        l_offset  :=3D l_offset + l_ammount ;
        l_ammount :=3D 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;



Hope this helps... try it and if you have any questions, ask!


Ilian

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Luis deUrioste
Sent: Wednesday, February 11, 2004 10:51 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Help! I need to generate excel files from PL/ SQL

Thanks to All,

Looks like I'm on my way.

Luis

-----Original Message-----
From: Luis deUrioste=3D20
Sent: Wednesday, February 11, 2004 2:40 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Help! I need to generate excel files from PL/ SQL


Hi Listers,

I have a requirement to submit some reports via e-mail in excel format, =
=3D
=3D3D
can somebody help me?

Thanks

Luis
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: