Oracle Database Discussions

- Can you use the \

 tag before and after your code so that it is readable?  (all lower case, 6 total characters including the \{ and } characters)
- The error you are getting indicates that one or more of the email addresses you are using are malformed.  Can you post the email addresses (or, more likely, a string with the same format but the domain replaced with something like foo.com) or show me where those are in your code?  The code is hard to read, so I may well have missed it if it is in there.

Justin                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

That seems to be due to how the e-mail addresses are parsed and verified by the two different mail server. The Exchange server may expect to find valid addresses in its global address book.

As I always say, dealing with mail errors is much easier when you are directly connected to it - and not dealing with it via another software layer (like Java or PL/SQL). Which is why I prefer using telnet to connect and test. I've posted a [telnet example here|http://forums.oracle.com/forums/thread.jspa?messageID=3761898&#3761898] just the other day.

What you may want to try is the VRFY command. This is implemented by some mail servers and is used to verify an e-mail address as "valid" within the context of that SMTP server.

The list of SMTP commands are defined in the [RFC 821|http://www.faqs.org/rfcs/rfc821.html].

dear all.
Well. The problem was solution. i suspect that the email address is not the same when you configured with linux and when you configured with exchange server.
My users describe in Cc and Bcc field receive the mesage wothout problem without put "Bcc" or "Cc".


In my code you can view the two lines that i was commented:
utl_smtp.rcpt(conn, 'cc:'||mycc(x).email); --> THIS LINE WAS COMMENTED
utl_smtp.rcpt(conn, 'bcc:'||mybcc(x).email); --> THIS LINE WAS COMMENTED

I have pust mi code again:

CREATE OR REPLACE PROCEDURE send_email_mio (
   efrom      IN   VARCHAR2,
   eto        IN   VARCHAR2,
   esubject   IN   VARCHAR2,
   ebody      IN   VARCHAR2,
   ecc        IN   CHAR := NULL,
   ebcc       IN   CHAR := NULL
)
IS
BEGIN
   DECLARE
      msg       VARCHAR2 (14000);
      conn      UTL_SMTP.connection;
      crlf      VARCHAR2 (2)        := CHR (13) || CHR (10);

      TYPE registro IS RECORD (
         NAME    VARCHAR2 (200),
         email   VARCHAR2 (200)
      );

      TYPE addresslist_tab IS TABLE OF registro
         INDEX BY BINARY_INTEGER;

      addrcnt   BINARY_INTEGER      := 0;
      myfrom    addresslist_tab;
      myto      addresslist_tab;
      mycc      addresslist_tab;
      mybcc     addresslist_tab;
      cmyfrom   BINARY_INTEGER      := 0;
      cmyto     BINARY_INTEGER      := 0;
      cmycc     BINARY_INTEGER      := 0;
      cmybcc    BINARY_INTEGER      := 0;
      mes       NUMBER (2);
      nmes      CHAR (3);
      rawdata   RAW (32767);
      
      msgaux    VARCHAR2 (14000);
      aux       VARCHAR2 (4000);

      FUNCTION separate (inemailx VARCHAR2)
         RETURN addresslist_tab
      IS
      BEGIN
         DECLARE
            inemail   VARCHAR2 (4000);
            p         NUMBER;
            rr        addresslist_tab;
            p1        VARCHAR2 (512);
            p2        VARCHAR2 (512);
            n1        VARCHAR2 (100);
            n2        VARCHAR2 (100);

            PROCEDURE getname (inadd VARCHAR2)
            IS
            BEGIN
               DECLARE
                  pp   NUMBER;
                  qq   NUMBER;
               BEGIN
                  pp := INSTR (inadd, '<');
                  qq := INSTR (inadd, '>');

                  IF pp = 0
                  THEN
                     n1 := NULL;
                     n2 := inadd;
                  ELSE
                     n1 := LTRIM (RTRIM (SUBSTR (inadd, 1, pp - 1)));
                     n2 :=
                          LTRIM (RTRIM (SUBSTR (inadd, pp + 1, qq - pp - 1)));
                  END IF;
               END;
            END;
         BEGIN
            addrcnt := 0;

            IF inemailx IS NULL
            THEN
               RETURN (rr);
            END IF;

            inemail := REPLACE (inemailx, ';', ',');
            p := INSTR (inemail, ',');

            IF p = 0
            THEN
               p := INSTR (inemail, ';');
            END IF;

            IF p = 0
            THEN
               getname (inemail);
               rr (1).NAME := n1;
               rr (1).email := n2;
               addrcnt := 1;
            ELSE
               p2 := inemail;

               LOOP
                  p1 := LTRIM (RTRIM (SUBSTR (p2, 1, p - 1)));

                  IF NOT p1 IS NULL
                  THEN
                     addrcnt := addrcnt + 1;
                     getname (p1);
                     rr (addrcnt).NAME := n1;
                     rr (addrcnt).email := n2;
                  END IF;

                  p2 := SUBSTR (p2, p + 1, 2000);

                  IF NOT p2 IS NULL
                  THEN
                     p := INSTR (p2, ',');

                     IF p = 0
                     THEN
                        p := INSTR (p2, ';');
                     END IF;

                     IF p = 0
                     THEN
                        addrcnt := addrcnt + 1;
                        getname (LTRIM (RTRIM (p2)));
                        rr (addrcnt).NAME := n1;
                        rr (addrcnt).email := n2;
                        EXIT;
                     END IF;
                  ELSE
                     EXIT;
                  END IF;
               END LOOP;
            END IF;

            RETURN (rr);
         END;
      END;
   BEGIN
      msg := NULL;
      myfrom := separate (efrom);
      cmyfrom := addrcnt;
      myto := separate (eto);
      cmyto := addrcnt;

--  if ecc is not null and trim(ecc)<>'' then mycc:=separate(ecc); cmycc:=addrcnt; else cmycc:=0; end if;
  --if ebcc is not null and trim(ebcc)<>'' then mybcc:=separate(ebcc); cmybcc:=addrcnt; else cmybcc:=0; end if;
      IF ecc IS NOT NULL AND LENGTH (TRIM (ecc)) > 0
      THEN
         mycc := separate (ecc);
         cmycc := addrcnt;
      ELSE
         cmycc := 0;
      END IF;

      IF ebcc IS NOT NULL AND LENGTH (TRIM (ebcc)) > 0
      THEN
         mybcc := separate (ebcc);
         cmybcc := addrcnt;
      ELSE
         cmybcc := 0;
      END IF;

      DBMS_OUTPUT.put_line (mybcc (1).email);
      DBMS_OUTPUT.put_line (mycc (1).email);    
      conn := UTL_SMTP.open_connection ('mailserver.foo.com', 25);
      UTL_SMTP.helo (conn, 'bdserver');

      IF cmyfrom <> 0
      THEN
         UTL_SMTP.mail (conn, myfrom (1).email);
      ELSE
         UTL_SMTP.mail (conn, 'sistemas@foo.com');
      END IF;

      FOR x IN 1 .. cmyto
      LOOP
         UTL_SMTP.rcpt (conn, myto (x).email);
      END LOOP;

      DBMS_OUTPUT.put_line (' hola 0');

      IF cmycc <> 0
      THEN
         FOR x IN 1 .. cmycc
         LOOP
            DBMS_OUTPUT.put_line (mycc (x).email);
--          utl_smtp.rcpt(conn, 'cc:'||mycc(x).email); --> THIS LINE WAS  COMMENTED 
            UTL_SMTP.rcpt (conn, mycc (x).email);
         END LOOP;
      END IF;

      DBMS_OUTPUT.put_line (' hola 1');

      IF cmybcc <> 0
      THEN
         FOR x IN 1 .. cmybcc
         LOOP
            UTL_SMTP.rcpt (conn, mybcc (x).email);
--          utl_smtp.rcpt(conn, 'bcc:'||mybcc(x).email); --> THIS LINE WAS  COMMENTED 
         END LOOP;
      END IF;

      DBMS_OUTPUT.put_line (' hola 2');
      mes := TO_CHAR (SYSDATE, 'mm');
      nmes :=
          SUBSTR ('JanFebMarAprMayJunJulAugSepOctNovDec', (mes - 1) * 3 + 1,
                  3);
--   msg:='Date: '||to_char(sysdate,'dd Mon yy hh24:mi:ss')||crlf;
      msg :=
            'Date: '
         || TO_CHAR (SYSDATE, 'dd ')
         || nmes
         || TO_CHAR (SYSDATE, ' yyyy hh24:mi:ss')
         || ' -0500'
         || crlf;

      IF cmyfrom <> 0
      THEN
         IF myfrom (1).NAME IS NOT NULL
         THEN
            msg :=
                  msg
               || 'From: '
               || CHR (34)
               || myfrom (1).NAME
               || CHR (34)
               || ' <'
               || myfrom (1).email
               || '>'
               || crlf;
         ELSE
            IF myfrom (1).email IS NOT NULL
            THEN
               msg := msg || 'From: ' || myfrom (1).email || crlf;
            ELSE
               msg :=
                     msg
                  || 'From: '
                  || CHR (34)
                  || 'Default'
                  || CHR (34)
                  || ' <sistemas@foo.com>'
                  || crlf;
            END IF;
         END IF;
      ELSE
         msg :=
               msg
            || 'From: '
            || CHR (34)
            || 'Default'
            || CHR (34)
            || ' <sistemas@foo.com>'
            || crlf;
      END IF;

      FOR x IN 1 .. cmyto
      LOOP
         IF myto (x).NAME IS NOT NULL
         THEN
            msg :=
                  msg
               || 'To: '
               || CHR (34)
               || myto (x).NAME
               || CHR (34)
               || ' <'
               || myto (x).email
               || '>'
               || crlf;
         ELSE
            msg := msg || 'To: ' || myto (x).email || crlf;
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line (' hola 3');

      IF cmycc <> 0
      THEN
         FOR x IN 1 .. cmycc
         LOOP
            IF mycc (x).NAME IS NOT NULL
            THEN
--     msg:=msg||'To: '||chr(34)||mycc(x).name||chr(34)||' <'||mycc(x).email||'>'||crlf;
               msg :=
                     msg
                  || 'Cc: '
                  || CHR (34)
                  || mycc (x).NAME
                  || CHR (34)
                  || ' <'
                  || mycc (x).email
                  || '>'
                  || crlf;
            ELSE
               msg := msg || 'Cc: ' || mycc (x).email || crlf;
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line (' hola 4');
      END IF;

      IF cmybcc <> 0
      THEN
         FOR x IN 1 .. cmybcc
         LOOP
            IF mybcc (x).NAME IS NOT NULL
            THEN
--     msg:=msg||'To: '||chr(34)||mybcc(x).name||chr(34)||' <'||mybcc(x).email||'>'||crlf;
               msg :=
                     msg
                  || 'Bcc: '
                  || CHR (34)
                  || mybcc (x).NAME
                  || CHR (34)
                  || ' <'
                  || mybcc (x).email
                  || '>'
                  || crlf;
            ELSE
               msg := msg || 'Bcc: ' || mybcc (x).email || crlf;
            END IF;
         END LOOP;
      END IF;

      DBMS_OUTPUT.put_line (' hola 5');
      msg := msg || 'Precedence: bulk' || crlf;
      msg := msg || 'Subject: ' || esubject || crlf;
      --
      --msg:=msg||'MIME-Version: 1.0'||crlf;
      --msg:=msg||'Content-Type: text/plain; charset=utf-8'||crlf;
      --msg:=msg||'Content-Transfer-Encoding: 8bit'||crlf;
      --
      msg := msg || '' || crlf;
      /*  12/11/2007*/
      msgaux := msg || ebody || ' ' || crlf;
      DBMS_OUTPUT.put_line (' hola 6');
      UTL_SMTP.open_data (conn);
      DBMS_OUTPUT.put_line (' hola 7');
      UTL_SMTP.write_data (conn, msg);
      msg := ebody || ' ' || crlf;
      rawdata := UTL_RAW.cast_to_raw (msg);
      DBMS_OUTPUT.put_line (' hola 8');
      --utl_smtp.write_data(conn, msg);
      UTL_SMTP.write_raw_data (conn, rawdata);
      DBMS_OUTPUT.put_line (' hola 9');
      UTL_SMTP.close_data (conn);
      --msg:=ebody;
      --utl_smtp.data(conn, msg);
      UTL_SMTP.quit (conn);

      /*  - 12/11/2007 */
      BEGIN
         INSERT INTO SYSTEM.log_mail
                     (MESSAGE, status, error
                     )
              VALUES (msgaux, 'OK', NULL
                     );

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      /* */
      BEGIN
         INSERT INTO registro_correos
                     (usuario, fecha, destino, titulo
                     )
              VALUES (USER, SYSDATE, eto, esubject
                     );

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   EXCEPTION
      WHEN OTHERS
      THEN
         BEGIN
            --  - 12/11/2007
            BEGIN
               msgaux :=
                     'From: '
                  || efrom
                  || crlf
                  || 'To: '
                  || eto
                  || crlf
                  || 'Subject :'
                  || esubject
                  || crlf
                  || 'Body: '
                  || ebody
                  || crlf
                  || 'CC: '
                  || NVL (ecc, ' ')
                  || crlf
                  || 'BCC: '
                  || NVL (ebcc, ' ');
               aux := TO_CHAR (SQLCODE) || ' - ' || SQLERRM;

               INSERT INTO SYSTEM.log_mail
                           (MESSAGE, status, error
                           )
                    VALUES (msgaux, 'FALLO', aux
                           );

               COMMIT;
            EXCEPTION
               WHEN OTHERS
               THEN
                  NULL;
            END;

            UTL_SMTP.quit (conn);
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;
         END;
   END;
END;
/

Thanks to all.