Blog do Marcio Portes

ops$marcio@ORA10G> create or replace package mail
  2  as
  3      type array is table of varchar2(255);
  4
  5  procedure send( p_sender_email in varchar2,
  6                  p_from         in varchar2 default NULL,
  7                  p_to           in array default array(),
  8                  p_cc           in array default array(),
  9                  p_bcc          in array default array(),
 10                  p_subject      in varchar2 default NULL,
 11                  p_body         in long  default NULL );
 12  end;
 13  /

Package created.

ops$marcio@ORA10G> create or replace package body mail
  2  as
  3
  4  g_crlf        char(2) default chr(13)||chr(10);
  5  g_mail_conn   utl_smtp.connection;
  6  g_mailhost    varchar2(255) := 'smtp.uol.com.br';
  7
  8  function address_email( p_string in varchar2,
  9                          p_recipients in array ) return varchar2
 10  is
 11      l_recipients long;
 12  begin
 13     for i in 1 .. p_recipients.count
 14     loop
 15        utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
 16        if ( l_recipients is null )
 17        then
 18            l_recipients := p_string || p_recipients(i)  ;
 19        else
 20            l_recipients := l_recipients || ', ' || p_recipients(i) ;
 21        end if;
 22     end loop;
 23     return l_recipients;
 24  end;
 25
 26
 27  procedure send( p_sender_email in varchar2,
 28                  p_from         in varchar2 default NULL,
 29                  p_to           in array default array(),
 30                  p_cc           in array default array(),
 31                  p_bcc          in array default array(),
 32                  p_subject      in varchar2 default NULL,
 33                  p_body         in long  default NULL )
 34  is
 35      l_to_list   long;
 36      l_cc_list   long;
 37      l_bcc_list  long;
 38      l_date      varchar2(255) default
 39                  to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );
 40
 41      procedure writeData( p_text in varchar2 )
 42      as
 43      begin
 44          if ( p_text is not null )
 45          then
 46              utl_smtp.write_data( g_mail_conn, p_text || g_crlf );
 47          end if;
 48      end;
 49  begin
 50      g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);
 51
 52      utl_smtp.helo(g_mail_conn, g_mailhost);
 53      utl_smtp.mail(g_mail_conn, p_sender_email);
 54
 55      l_to_list  := address_email( 'To: ', p_to );
 56      l_cc_list  := address_email( 'Cc: ', p_cc );
 57      l_bcc_list := address_email( 'Bcc: ', p_bcc );
 58
 59      utl_smtp.open_data(g_mail_conn );
 60
 61      writeData( 'Date: ' || l_date );
 62      writeData( 'From: ' || nvl( p_from, p_sender_email ) );
 63      writeData( 'Subject: ' || nvl( p_subject, '(no subject)' ) );
 64
 65      writeData( l_to_list );
 66      writeData( l_cc_list );
 67
 68      utl_smtp.write_data( g_mail_conn, '' || g_crlf );
 69      utl_smtp.write_data(g_mail_conn, p_body );
 70      utl_smtp.close_data(g_mail_conn );
 71      utl_smtp.quit(g_mail_conn);
 72  end;
 73
 74
 75  end;
 76  /

Package body created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> show error
No errors.
ops$marcio@ORA10G>
ops$marcio@ORA10G> drop table t purge;

Table dropped.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create table t
  2  (
  3          id      number,
  4          dt      date,
  5          nome    varchar2(50)
  6  )
  7  /

Table created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> insert into t
  2  select user_id, created, username
  3    from all_users;

14 rows created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> create or replace procedure p( p_dt in date )
  2  is
  3          l_body          long := chr(10) || lpad('id', 10, ' ') || ' '
  4                                  || rpad('Nome', 50, ' ') || chr(10) ||
  5                                  rpad('-', 10, '-') || ' ' ||
  6                                  rpad('-', 50, '-') || chr(10);
  7  begin
  8          for x in ( select id, nome
  9                       from t
 10                      where dt >= p_dt )
 11          loop
 12                  l_body := l_body || lpad(x.id, 10, ' ') || ' ' ||
 13                            rpad(x.nome, 50, ' ') || chr(10);
 14          end loop;
 15          mail.send
 16          ( p_sender_email => '<marcio.portes@uol.com.br>',
 17            p_from => 'Marcio Portes <marcio.portes@uol.com.br>',
 18            p_to => mail.array( '<mportes-blog@uol.com.br>'),
 19            p_subject => 'Exemplo para o Blog! ',
 20            p_body => l_body );
 21  end;
 22  /

Procedure created.

ops$marcio@ORA10G>
ops$marcio@ORA10G> show error
No errors.
ops$marcio@ORA10G>
ops$marcio@ORA10G> exec p( trunc(sysdate, 'y') )

PL/SQL procedure successfully completed.

From - Thu Jul 21 19:55:29 2005
X-Account-Key: account1
X-UIDL: 6629
X-Mozilla-Status: 0001
X-Mozilla-Status2: 00000000
Return-Path: 
Received: from saturno15.mail.sys.intranet (172.26.14.205) by saturno28.mail.sys.intranet (7.1.006)
        id 42B9568E00973357 for marcio.portes@uol.com.br; Thu, 21 Jul 2005 19:51:00 -0300
Delivered-To: mportes-blog@uol.com.br
Received: from shadow5-1.uol.com.br (172.26.5.186) by saturno15.mail.sys.intranet (7.1.006)
        id 41DACD340279D525 for mportes-blog@uol.com.br; Thu, 21 Jul 2005 19:51:00 -0300
Received: from smtp.uol.com.br (neovia.neoviatelecom.com.br [200.205.213.225])
 by scorpio.uol.com.br (Postfix) with SMTP id 840AC8AF7
 for ; Thu, 21 Jul 2005 19:51:00 -0300 (BRT)
Date: 21 Jul 05 19:54:37
From: Marcio Portes 
Subject: Exemplo para o Blog! 
To: 
Message-Id: <20050721225100.840ac8af7@scorpio.uol.com.br>


        id Nome                                              
---------- --------------------------------------------------
       130 UTIL                                              
        63 OPS$MARCIO                                        
       129 PAY                                               
        66 PERFSTAT                                          
       127 PAY_DBA                                           
       128 PAY_BIN            

# posted by Marcio : 7:49 PM