Home » SQL & PL/SQL » SQL & PL/SQL » unable to send mail more then 100ids (oracle,9.2.0.8,window)
unable to send mail more then 100ids [message #376109] Tue, 16 December 2008 01:56 Go to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi All,

I have one table which store mail Ids. I want to send single mail for all IDs.
Through below procedure I am not able to get my expected result I am getting below error. Please let us know what change requires.

Procedure:

 CREATE OR REPLACE PROCEDURE send_mail_audit (to_mail IN VARCHAR2 ,body_content IN VARCHAR2)
                    --THIS PROCEDURE IS USED TO SEND THE MAIL IF AUDIT FAILS.
   IS
      connection   UTL_SMTP.connection;
      v_reply      UTL_SMTP.reply;
      MESSAGE      VARCHAR2 (32767);
      crlf         VARCHAR2 (2)        := CHR (13) || CHR (10);
      mailhost     VARCHAR2 (255)      := 'sj-webmail-3.cisco.com';
   BEGIN
      connection := UTL_SMTP.open_connection ('sj-webmail-3.cisco.com', 25);
      v_reply := UTL_SMTP.helo (connection, 'sj-webmail-3.cisco.com');
      v_reply := UTL_SMTP.mail (connection, 'kgundre@CISCO.COM');
                                                            --SENDER  MAIL ID
      v_reply := UTL_SMTP.rcpt (connection, to_mail);
                                                          --RECEIPENT MAIL ID
      MESSAGE :=
            'Date: '
         || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH:MIPM')
         || crlf
         || 'From: kgundre@CISCO.COM'
         || crlf
         || 'To:' || to_mail
         || crlf
         || 'Cc: kgundre@CISCO.COM'
         || crlf
         || 'Subject: Audit difference for redundant object'
         || crlf
         || ''
         || crlf
         || body_content;
      UTL_SMTP.DATA (connection,
                        'MIME-Version: 1.0'
                     || crlf
                     || 'Content-type: text/html'
                     || crlf
                     || MESSAGE
                    );
      UTL_SMTP.quit (connection);
   END send_mail_audit;
/



calling function
begin
  send_mail_audit'kgundre@cisco.com ;'||'gohol@cisco.com','test');
  
end;


Error:-

Message could not be delivered to the following recipient:

  kgundre@cisco.com ;gohol@cisco.com
   because: A mail transport failure occurred
Supplementary Information:
    500 5.5.1 Command unrecognized: ".   


Thanks,
Sagar


Re: unable to send mail more then 100ids [message #376113 is a reply to message #376109] Tue, 16 December 2008 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if you can do it outside Oracle.

Regards
Michel
Re: unable to send mail more then 100ids [message #376114 is a reply to message #376109] Tue, 16 December 2008 02:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
According to the documentation :

The "recipient" parameter in UTL_SMTP.rcpt is the recipient of the mail, not multiple recipients.

You have to call utl_smtp.rcpt for every recipient, you can't concatenate the addresses with ";" like you are doing it.

Re: unable to send mail more then 100ids [message #376116 is a reply to message #376114] Tue, 16 December 2008 02:35 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi ThomasG,

IS there any other option.

Thanks,
Sagar
Re: unable to send mail more then 100ids [message #376118 is a reply to message #376114] Tue, 16 December 2008 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to call "rcpt" procedure for each recipient, and put the list inside "To: " string (as far as I know separator is comma but maybe semi-colon also works).

Regards
Michel
Re: unable to send mail more then 100ids [message #376121 is a reply to message #376118] Tue, 16 December 2008 03:01 Go to previous messageGo to next message
vickey_sagar1
Messages: 77
Registered: August 2007
Location: pune
Member

Hi Michel,

Could you let me know some sample code.

Thanks in Advance,
Sagar
Re: unable to send mail more then 100ids [message #376126 is a reply to message #376121] Tue, 16 December 2008 03:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Some sample code :

...

v_reply := UTL_SMTP.rcpt (connection, 'kgundre@cisco.com');
v_reply := UTL_SMTP.rcpt (connection, 'gohol@cisco.com');

...


Re: unable to send mail more then 100ids [message #376292 is a reply to message #376126] Tue, 16 December 2008 18:04 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
An example of 10g UTL_MAIL
http://www.databasejournal.com/img/jsc_PLSQL_Enhancements_Listing2.html#List0206


The definitive discussion from a few years back
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:255615160805

[Updated on: Tue, 16 December 2008 18:07]

Report message to a moderator

Previous Topic: PL/SQL Problem
Next Topic: Can I update contents of a collection as if its a Table ? (merged)
Goto Forum:
  


Current Time: Sat Dec 10 22:46:57 CST 2016

Total time taken to generate the page: 0.10148 seconds