Home » SQL & PL/SQL » SQL & PL/SQL » help about Utl_smtp
help about Utl_smtp [message #263239] Wed, 29 August 2007 08:30 Go to next message
balaji23_d
Messages: 123
Registered: February 2007
Senior Member
hi


pls give me some ideas how to write the programming for send the mail with attachement from oracle to other user

* please send some sample examples
Re: help about Utl_smtp [message #263240 is a reply to message #263239] Wed, 29 August 2007 08:32 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
http://www.psoug.org/reference/utl_smtp.html
Re: help about Utl_smtp [message #263248 is a reply to message #263239] Wed, 29 August 2007 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
http://asktom.oracle.com/pls/asktom/f?p=100:1 and enter "mail" in search field.

Regards
Michel
Re: help about Utl_smtp [message #263301 is a reply to message #263248] Wed, 29 August 2007 11:09 Go to previous message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
I just happen to be working on some code that contains an example. The example was pulled from a package and extraneous information was removed
  PROCEDURE extract IS
    CURSOR cur IS
    SELECT 
    ... remaining removed ...
  BEGIN
    ... details removed ...

   /*
    * The following code creates the content of the attachment in a VARCHAR2
    * variable.  An alternative would be to create a LOB variable into which 
    * you could load a file from the server 
    */
    log_data := RPAD('JOB_DESCRIPTION', 25) || '  ' ||
                RPAD('START_TIME', 11) || '  ' ||
                RPAD('STOP_TIME',11) || '  ' ||
                RPAD('TOTAL_TIME', 10) || '  ' ||
                LPAD('ROW_COUNT', 10) || '  ' ||
                LPAD('%CHG (DAY)', 10) || '  ' ||
                LPAD('%CHG (YEAR)', 11) || '  ' ||
                RPAD('COMMENTS', 15) || CHR(13) || CHR(10);

    log_data := log_data ||
                RPAD('-', 25, '-') || '  ' ||
                RPAD('-', 11, '-') || '  ' ||
                RPAD('-', 11, '-') || '  ' ||
                RPAD('-', 10, '-') || '  ' ||
                RPAD('-', 10, '-') || '  ' ||
                RPAD('-', 10, '-') || '  ' ||
                RPAD('-', 11, '-') || '  ' ||
                RPAD('-', 15, '-') || CHR(13) || CHR(10);

    FOR rec IN cur LOOP
      log_data := log_data ||
                  RPAD(rec.job_description, 25) || '  ' ||
                  RPAD(rec.start_time, 11) || '  ' ||
                  RPAD(rec.stop_time, 11) || '  ' ||
                  LPAD(rec.total_time, 10) || '  ' ||
                  LPAD(rec.row_count, 10) || '  ' ||
                  LPAD(rec.chg_day, 10) || '  ' ||
                  LPAD(rec.chg_annual, 10) || '  ' ||
                  RPAD(rec.comments, 15) || CHR(13) || CHR(10);

      num_rows := cur%ROWCOUNT;
    END LOOP;

    log_data := log_data || CHR(13) || CHR(10) || CHR(13) || CHR(10) ||
                num_rows || ' rows selected';

    utl_mail.send_attach_varchar2
     (sender       => '<email address>'
     ,recipients   => '<email address>'
     ,cc           => NULL
     ,bcc          => NULL
     ,subject      => '<Subject Goes Here'
     ,message      => 'This is a system generated message originating from the ' 
                      || l_db_name || ' Oracle database.  See the attached log file ' ||
                      'for more information.'
     ,mime_type    => 'text/plain; charset=us-ascii'
     ,priority     => 3
     ,attachment   => TO_CLOB(log_data)
     ,att_inline   => FALSE
     ,att_filename => 'nightly_jobs_run.log' );
  END extract;
Previous Topic: query
Next Topic: Copying Large amounts of Data Effectively and Efficiently
Goto Forum:
  


Current Time: Sat Dec 03 18:20:58 CST 2016

Total time taken to generate the page: 0.10052 seconds