Ayuda con UTL_SMTP

[kquitral]

Hola a todos, mi problema es que necesito enviar un mail cada vez que se inserte una tabla a la base de datos, para esto se que debo utilizar UTL_SMTP. Pero como soy nueva en esto no tengo idea de que debo poner en los campos que detallo mas abajo:

UTL_SMTP.OPEN_CONNECTION('DEBO PONER ALGO REFEDIDO A LA BASE O AL SERVIDOR DE CORRERO ELCTRONICO')

UTL_SMTP.HELO('QUE DEBO PONER??', 'QUE DEBO PONER ??')

Por favor, se agradece su ayuda.

[matanga]

Hola,

Mejor que utilizar UTL_SMTP es utilizar UTL_MAIL, es mucho mas simple, yo implemente SMTP una vez y me tuve que leer toda la RFC 821 para entender como construir el formato de un correo.

Código:

UTL_MAIL.SEND (
   sender      IN    VARCHAR2 CHARACTER SET ANY_CS,
   recipients  IN    VARCHAR2 CHARACTER SET ANY_CS,
   cc          IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   bcc         IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   subject     IN    VARCHAR2 CHARACTER SET ANY_CS DEFAULT NULL,
   message     IN    VARCHAR2 CHARACTER SET ANY_CS,
   mime_type   IN    VARCHAR2 DEFAULT 'text/plain; charset=us-ascii',
   priority    IN    PLS_INTEGER DEFAULT NULL);

Pd, si necesitas UTL_SMTP, el lunes te puedo postear el codigo que tengo en el trabajo.

Saludos

[kquitral]

Hola , justamente estaba viendo esa opcion, pero me surgio un problema porque dice que debo instalar el utl_mail desde el SYS
¿Como se hace?

[matanga]

Hola,

Sip, tienes razon, no viene instalado por defecto, hay que ejecutar dos scripts para instalar los paquetes.

Código:

SQL> conn / as sysdba
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql

Package created.


Synonym created.

SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

Package body created.

No errors.

La parte donde me conecto como "/ as sysdba" puede que sea diferente en tu base de datos y tengas que poner "conn sys/pass as sysdba".

Ademas hay que definir el parametro de Oracle SMTP_OUT_SERVER, que puede ser a nivel de base de datos o a nivel de sesion, este parametro contiene el valor del servidor de correo.

Código:

SQL> alter session set SMTP_OUT_SERVER='192.168.1.1';

Session altered.

Saludos

[kquitral]

Disculpa mi ignorancia pero sabes como puedo ejecutar esas lineas en TOAD. Gracias.

[matanga]

Hola,

Supongo que puedes conectarte con el TOAD y el usuario SYS, es como conectarse con cualquier otro usuario, mas la opcion de SYSDBA que aparece en el dialogo de login.

$ORACLE_HOME significa la carpeta de instalacion de Oracle, supongamos C:\oracle\ora10 donde encontraras otras carpetas, ahi debes buscar por el script en rdbms\admin\utlmail.sql.

El problema lo vas a tener con el otro script, la extension plb significa que esta encriptado, y me parece que el TOAD no puede ejecutar esto en la base de datos, aqui me parece que no tienes mas remedio que el SQL*Plus

Desde la linea de comandos de Windows, cmd, hay que ejecutar

Código:

oracle@buo:~> sqlplus sys/manager as sysdba @/u01/app/oracle/product/10.2/db_1/rdbms/admin/prvtmail.plb

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 2 04:10:38 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


Package body created.

No errors.

Reemplazando el directorio de instalacion de Oracle por la tuyo.

Saludos

[kquitral]

Hola matanga, te cuento que voy a implemetar el UTL_MAIL, pero te queria pedir si igualmente me puedes enviar el ejemplo que tienes de UTL_SMTP, es que quiero demostrar que el UTL_MAIL es mas efectivo y facil que el UTL_SMTP. te lo agardeceria mucho.
Saludos

[matanga]

Hola,

1. Soy un poco vago para programar, asi que cualquier mejora que veas posteala por aqui.
2. El paquete no tiene mucha funcionalidad, por ejemplo no se puede enviar adjuntos, pero cubre las necesidades que tenia.
3. El parser de direcciones de correo de destino puede que tenga algun bug, ahora no me acuerdo.
4. El tamaño maximo del body, creo, es menor al permitido para varchar2 en PL/SQL.
5. El manejo de exception es malo, puedes mejorarlo tranquilamente.
6. El codigo esta adaptado para este post, algunas cosas quite o renombre, puede que tenga algun error, testealo bien.


Definicion del Paquete.

Código:

CREATE OR REPLACE PACKAGE smtpMail
  IS

  --Información para el envío de correo
  smtp_to      VARCHAR2(900) := '';
  smtp_from    VARCHAR2(900) := '';
  smtp_server  VARCHAR2(256) := '';

  smtp_to_cc   VARCHAR2(900) := '';
  smtp_to_bcc  VARCHAR2(900) := '';
  smtp_subject VARCHAR2(900) := '';

  smtp_port    NUMBER(4)    := 25;

  --Autenticación SMTP
  smtp_auth    BOOLEAN       NOT NULL := FALSE;
  smtp_user    VARCHAR2(64) := '';
  smtp_pass    VARCHAR2(64) := '';

  procedure smMailSend(lsz_body VARCHAR2);


END;

Cuerpo del paquete.

Código:

CREATE OR REPLACE PACKAGE BODY smtpMail
IS

  TYPE IList IS TABLE OF VARCHAR2(300);

  FUNCTION smMailList (
    lsz_data                               VARCHAR2,
    lsz_add_list                  OUT      smtpMail.IList,
    lsz_add_str                   OUT      VARCHAR2
  )
    RETURN NUMBER
  IS


    i              NUMBER(10)    := 1;

    sz_data        VARCHAR2(900) := NULL;
    sz_address     VARCHAR2(300) := NULL;

    sz_cb CONSTANT CHAR(1)       := CHR(60);
    sz_ce CONSTANT CHAR(1)       := CHR(62);

  BEGIN

    sz_data      := lsz_data;
    lsz_add_list := smtpMail.IList();

    WHILE ( LENGTH(sz_data) > 0 ) LOOP

      sz_address := SUBSTR(
                  sz_data,
                  1,
                   CASE
                   WHEN INSTR(sz_data,',',1,1) = 0 THEN LENGTH(sz_data)
                   ELSE INSTR(sz_data,',',1,1) - 1
                   END
                  );

      sz_data := REPLACE(sz_data, sz_address || ',', NULL);
      sz_data := REPLACE(sz_data, sz_address, NULL);

      lsz_add_list.extend(1);
      lsz_add_list(i) := sz_address;

      lsz_add_str := lsz_add_str || sz_cb || sz_address || sz_ce || ',';

      i := i + 1;

    END LOOP;

    lsz_add_str := SUBSTR(lsz_add_str,1,LENGTH(lsz_add_str) - 1);

    RETURN 0;

    EXCEPTION
      WHEN OTHERS THEN
        RETURN SQLCODE;

  END;






  PROCEDURE smMailSend (lsz_body VARCHAR2)
  IS

    sz_to      VARCHAR2(900) := NULL;
    sz_from    VARCHAR2(900) := NULL;
    sz_server  VARCHAR2(256) := NULL;

    sz_to_cc   VARCHAR2(900) := NULL;
    sz_to_bcc  VARCHAR2(900) := NULL;
    sz_subject VARCHAR2(900) := NULL;

    b_auth     BOOLEAN       := NULL;
    sz_user    VARCHAR2(64)  := NULL;
    sz_pass    VARCHAR2(64)  := NULL;

    nv_port    NUMBER(4)    := 25;

    sz_header  VARCHAR2(3000) := NULL;

    crlf       VARCHAR2(2)   := CHR( 13 ) || CHR( 10 );


    --Listas de direcciones
    sz_to_list      smtpMail.IList := Ilist();
    sz_to_cc_list   smtpMail.IList := Ilist();
    sz_to_bcc_list  smtpMail.IList := Ilist();

    sz_to_list_str      VARCHAR2(900) := NULL;
    sz_to_cc_list_str   VARCHAR2(900) := NULL;
    sz_to_bcc_list_str  VARCHAR2(900) := NULL;

    --connection and replay smtp
    c          UTL_SMTP.CONNECTION;
    r          UTL_SMTP.REPLY;

    --support
    i         NUMBER(10)              := 1;
    nv_result NUMBER(10)              := NULL;


  BEGIN

    --obligatorias
    sz_to      := TRIM(smtpMail.smtp_to);
    sz_from    := TRIM(smtpMail.smtp_from);
    sz_server  := TRIM(smtpMail.smtp_server);

    --opcionales
    sz_to_cc   := TRIM(smtpMail.smtp_to_cc);
    sz_to_bcc  := TRIM(smtpMail.smtp_to_bcc);
    sz_subject := TRIM(smtpMail.smtp_subject);

    --autenticacion
    b_auth     := smtpMail.smtp_auth;
    sz_user    := TRIM(smtpMail.smtp_user);
    sz_pass    := TRIM(smtpMail.smtp_pass);

    --puerto smtp
    nv_port    := NVL(smtpMail.smtp_port,25);



    IF (sz_to IS NOT NULL AND sz_from IS NOT NULL AND sz_server IS NOT NULL) THEN

      BEGIN

        --Conexión y handshake con el servidor de correo
        c := UTL_SMTP.OPEN_CONNECTION(sz_server, nv_port);
        r := UTL_SMTP.HELO(c, sz_server);

        --Dirección de Origen
        r := UTL_SMTP.MAIL(c, sz_from);


        --Autenticacion SMTP
        IF ( b_auth ) THEN

          r := UTL_SMTP.COMMAND(c, 'AUTH LOGIN');

          IF ( r.code != 334 ) THEN
            dbms_output.put_line('Autenticación Básica SMTP (auth login) no esta soportada por el servidor de correo');
          END IF;

          r := UTL_SMTP.COMMAND(c, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(sz_user))));
          r := UTL_SMTP.COMMAND(c, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(sz_pass))));
        END IF;


        --Dirección de Destino - To
        i := 1;
        nv_result := smMailList(sz_to, sz_to_list, sz_to_list_str);

        IF ( nv_result = 0 ) THEN

          WHILE ( i <= sz_to_list.COUNT ) LOOP

            r := UTL_SMTP.RCPT(c, sz_to_list(i));
            i := i + 1;

          END LOOP;

        ELSE
          DBMS_OUTPUT.put_line('Error en Direcciones To');
          dbms_output.put_line(SQLERRM(nv_result));
        END IF;


        --Dirección de Destino con Copia - CC
        i := 1;
        nv_result := smMailList(sz_to_cc, sz_to_cc_list, sz_to_cc_list_str);

        IF ( nv_result = 0 ) THEN

          WHILE ( i <= sz_to_cc_list.COUNT ) LOOP

            r := UTL_SMTP.RCPT(c, sz_to_cc_list(i));
            i := i + 1;

          END LOOP;

        ELSE
          DBMS_OUTPUT.put_line('Error en Direcciones Cc');
          DBMS_OUTPUT.put_line(SQLERRM(nv_result));
        END IF;


        --Dirección de Destino con Copia Oculta - BCC
        i := 1;
        nv_result := smMailList(sz_to_bcc, sz_to_bcc_list, sz_to_bcc_list_str);

        IF ( nv_result = 0 ) THEN

          WHILE ( i <= sz_to_bcc_list.COUNT ) LOOP

            r := UTL_SMTP.RCPT(c, sz_to_bcc_list(i));
            i := i + 1;

          END LOOP;

        ELSE
          DBMS_OUTPUT.put_line('Error en Direcciones Bcc');
          dbms_output.put_line(SQLERRM(nv_result));
        END IF;


        --Header del mensaje - RFC 822
        sz_header :='Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
                    'From: '|| sz_from || crlf ||
                    'Subject: ' || sz_subject || crlf ||
                    'To: '  || sz_to_list_str || crlf ;

        IF ( LENGTH(sz_to_cc_list_str) > 0 ) THEN

          sz_header := sz_header ||
                  'Cc: '  || sz_to_cc_list_str || crlf;
        END IF;

        IF ( LENGTH(sz_to_bcc_list_str) > 0 ) THEN

            sz_header := sz_header ||
                  'Bcc: ' || sz_to_bcc_list_str || crlf;
        END IF;


        --Body del mensaje: lsz_body
        --Envío de datos al servidor de correo y cierre de la conexión
        r := UTL_SMTP.DATA(c, sz_header || lsz_body);
        r := UTL_SMTP.QUIT(c);

        dbms_output.put_line('');
        dbms_output.put_line('Mensaje Enviado');
        dbms_output.put_line(sz_header);

        EXCEPTION
          WHEN UTL_SMTP.INVALID_OPERATION THEN
            dbms_output.put_line(SQLERRM);
            r := UTL_SMTP.QUIT(c);
          WHEN UTL_SMTP.TRANSIENT_ERROR THEN
            dbms_output.put_line(SQLERRM);
            r := UTL_SMTP.QUIT(c);
          WHEN UTL_SMTP.PERMANENT_ERROR THEN
            dbms_output.put_line(SQLERRM);
            r := UTL_SMTP.QUIT(c);
          WHEN OTHERS THEN
            dbms_output.put_line(SQLERRM);
            r := UTL_SMTP.QUIT(c);

      END;


    ELSE

      dbms_output.put_line('Error en la configuración de correo: ');
      dbms_output.put_line('  Servidor SMTP : ' || CHR(39) || sz_server || CHR(39) );
      dbms_output.put_line('  Puerto SMTP : ' || CHR(39) || TO_CHAR(nv_port) || CHR(39) );
      dbms_output.put_line('  Cuenta Destino (TO)  : ' || CHR(39) || sz_to || CHR(39) );
      dbms_output.put_line('  Cuenta Origen (FROM) : ' || CHR(39) || sz_from || CHR(39) );

    END IF;


  END;
  
END;

Un ejemplo de la llamada puede ser.

Código:

set serveroutput on size 10000

BEGIN	
  
  smtpMail.smtp_to      := '[email protected],[email protected]';
  smtpMail.smtp_subject := 'Prueba';
  
  smtpMail.smtp_from    := '[email protected]';
  smtpMail.smtp_server  := 'server.dominio.es';
  smtpMail.smtp_port    := 25;
  
  smtpMail.smtp_auth    := FALSE;
  smtpMail.smtp_user    := 'User';
  smtpMail.smtp_pass    := 'Pass';
  
  
  smtpMail.smMailSend('Hola Mundo!');
  
END;
/

Saludos

[jc3000]

Mañana ( si me acuerdo y tengo tiempo, que ahora ando muy liado ) pongo un pequeño pl que utilizo yo para mandar correos utilizando el UTL_SMTP.

Es muy sencillo y ocupa menos de la mitad del código que el ejemplo que ha puesto el compañero Matanga ( Aunque basicamente los dos tienen la misma funcionalidad ).

Utiliza el que mas te guste.

[jc3000]

Este es el pl que utilizo yo, es funcional 100x100 y rula muy bien.

Código HTML:

PROCEDURE     ENVIAR_MAIL
( PARA      IN VARCHAR2 default '[email protected]' ,
  ASUNTO    IN VARCHAR2 DEFAULT 'Hola, que tal' , 
  P_MENSAJE IN VARCHAR2 default 'Te invito a cenar')

IS 

c utl_smtp.connection;
 
  PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS
  BEGIN
    utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF);
  END;
 
BEGIN
  c := utl_smtp.open_connection('SERVIDOR SMTP');
  utl_smtp.helo(c, 'NOMBRE DEL SERVIDOR SMTP');
  utl_smtp.mail(c, 'REMITENTE');


  utl_smtp.rcpt(c, para);
  utl_smtp.open_data(c);
  send_header('Subject', Asunto);
  utl_smtp.write_data(c, utl_tcp.CRLF || P_MENSAJE );

  utl_smtp.close_data(c);
  utl_smtp.quit(c);
END;

[matanga]

Hola jc3000,

utl_tcp.CRLF, no sabia que existia :/

Saludos

[kquitral]

Gracias matanga y jc300 por ayudarme. Su ayuda me ha sido muy util.

[matanga]

Hola,

Una cosa mas, for the record only, hay una pequeña diferencia entre los dos ejemplos, el primero utiliza el procedimiento UTL_SMTP.DATA para el envio del mensaje y el segundo ejemplo utiliza UTL_SMTP.OPEN_DATA y UTL_SMTP.WRITE_DATA.

WRITE_DATA hace un append del mensaje, es decir, que si se lo ejecuta N veces, ira agregando mas informacion al mensaje, lo que libera al procedimiento de la limitacion de los 32k del tipo VARCHAR2 en PL/SQL, si bien un mensaje de 32k puede ser suficiente, no deja de ser una limitacion, limitacion que tambien tiene el procedimiento UTL_MAIL.SEND.

Esto lo comento porque mi necesidad de implementar este paquete en Oracle viene de un proceso batch que genera un reporte de actividad y lo envia por correo a algun responsable de la aplicacion, y recuerdo una vez haber desbordado el body del mensaje.

Saludos

[MarioHeredia]

Kquitral...

Basandome en tu pregunta inicial, encontre buscando en la web un lindo documento sobre el paquete de sistema UTL_SMTP, el cual explica parametro por parametro y alguna que otra consideracion.

Esta en español:
www.mediamax.com/forthes/Hosted/UTL-smtp.doc

Sldo Atte, Mario.

[widipa]

Hola a todos.

Yo uso Oracle 10g sobre AIX 5

Y me funciona el envio de correos, ahora tengo que adicionarle la autenticación del usuario que hace el envio, buscando en internet me encontre que se puede usar el command pero al adicionar las lineas

UTL_SMTP.command(v_conn,'AUTH LOGIN');
UTL_SMTP.command(v_conn, UTL_ENCODE.BASE64_ENCODE(UTL_RAW.cast_to_raw('usua rio')) );
UTL_SMTP.command(v_conn, UTL_ENCODE.BASE64_ENCODE(UTL_RAW.cast_to_raw('clav e')) );

Me da el error de que el comando 'AUTH LOGIN' no esta implementado

Como hago para corregir esto?