PROCEDIMIENTO PARA ENVIAR UN CORREO DESDE LA BASE DE DATOS ORACLE

create or replace PROCEDURE     P_ENVIA_MAIL (v_msg in varchar2, -- messages's body
                                               v_subject in varchar2,  -- message's subject
                                               v_from in varchar2, -- from
                                        v_to in varchar2  -- to , only one recipient at once
                                        ) IS

/******************************************************************************
   NAME:      P_ENVIA_MAIL
   PURPOSE:    Envia mails a las personas definidas en la tabla vc_ref para cada personal DE
   REVISIONS:
   Ver        Date              Author                 Description
   ---------  ----------------------  -------------------------  ------------------------------------

 
******************************************************************************/

SenderAddress  Varchar2(100)  := v_from;       /* Sender's e-mail address */
ReceiverAddress varchar2(100) := v_to;       /* Recipient's e-mail */
EmailServer    varchar2(50) ;     /* SMTP Server*/
Port number  := 25;            /*  Port where SMTP server is listening to requests */
v_mesg varchar2(32767) := null;
crlf varchar2(2):= chr(13)||chr(10);

conn UTL_SMTP.CONNECTION;

BEGIN

 
    EmailServer    := 'mailhost';
    /* Open Connection */
      conn:= utl_smtp.open_connection( EmailServer, Port);

    /* Hand Shake */
      utl_smtp.helo( conn, EmailServer );
 
    /* Configure Sender and Recipient  with UTL SMTP */
      utl_smtp.mail( conn, SenderAddress);

     utl_smtp.rcpt( conn, ReceiverAddress );
       
     /*v_mesg := 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
                'From: '||SenderAddress|| crlf ||
                'Subject: '|| v_subject ||crlf ||
                'To: '||ReceiverAddress || crlf ||
                '' || v_msg||'';*/
      --v_mesg := 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
      --               'From:'||SenderAddress|| crlf ||
       --              'Subject: '|| v_subject ||crlf ||
       --             'To: '||ReceiverAddress || crlf ||
       --              '' || crlf ||v_msg||'';
     
       v_mesg:=         
          --  'Date: '||TO_CHAR(SYSTIMESTAMP,'MM/DD/YYYY HH24:MI:SS') ||    UTL_TCP.CRLF||       
            'From:'||SenderAddress|| crlf ||         
            'Subject:'||v_subject ||crlf ||         
            'To: '||ReceiverAddress || crlf ||         
            '' || crlf ||v_msg||'';           
     /* Configure Sending Message */
     /* Put 'MIME-Version: 1.0' (case-sensitive) */
     /* Content-Type-Encoding is actually Content-Transfer-Encoding. */
     /* The MIME-Version, Content-Type, Content-Transfer-Encoding should */
     /* be the first 3 data items in your message */
       
     utl_smtp.data(conn, 'MIME-Version: 1.0' ||CHR(13)||
     CHR(10)||'Content-type: text/html' || CHR(13)||CHR(10)
     ||v_mesg);
   
     utl_smtp.quit( conn );

EXCEPTION
 when others then
   raise_application_error(-20000,'Err.Mailx.'||sqlerrm);

END P_ENVIA_MAIL;