Home » SQL & PL/SQL » SQL & PL/SQL » SMTP permanent error: 501 Syntax error while sending mail from procedure (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi)
SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550678] Wed, 11 April 2012 08:47 Go to next message
saipradyumn
Messages: 175
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel,

While sending a mail from procedure following error is coming.
I am able to get DBMS statements up to utl_smtp.helo procedure.
But when associating the sender email id with mail_conn exception was raised

I have gone through google, previous forms also.


declare
  mail_conn utl_smtp.connection;
  -- mail_host varchar2(20) := '## remove at OP request #';
  mail_host varchar2(20) := '## remove at OP request #';

  sender varchar2(50) := '## remove at OP request #';
  /*receiver varchar2(50) := '## remove at OP request #';
  message  varchar2(5000) := 'kappu this is from procedure';
   */
begin
  mail_conn := utl_smtp.open_connection(mail_host);
  dbms_output.put_line('connection obtained');
  utl_smtp.helo(mail_conn, mail_host);
  dbms_output.put_line('association between host and conn object obtained');
  utl_smtp.mail(mail_conn, sender);
  /* utl_smtp.rcpt(mail_conn, receiver);
  utl_smtp.data(mail_conn, message);
  utl_smtp.quit(mail_conn);
  utl_smtp.close_data(mail_conn);
  */
end;
 
ORA-29279: SMTP permanent error: 501 Syntax error, parameters in command "MAIL FROM:rthirunagari@vitechinc.com" unrecognized or missing
ORA-06512: at "SYS.UTL_SMTP", line 20
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at line 16
 


Could you please help me


Thanks
Sai Pradyumn

[Updated on: Thu, 12 April 2012 07:04] by Moderator

Report message to a moderator

Re: SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550680 is a reply to message #550678] Wed, 11 April 2012 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do not use utl_smtp but utl_mail.

Regards
Michel
Re: SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550745 is a reply to message #550680] Thu, 12 April 2012 01:52 Go to previous messageGo to next message
saipradyumn
Messages: 175
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel ,

utl_mail is not available in my Database(10 g),even though it's available in 10g.
Even i tried by using utl_tcp ,then also i am getting the same kind of problem at the same position


SQL> set serveroutput on 
SQL> 
SQL> declare
  2    c           utl_tcp.connection;
  3    msg_from    varchar2(50) := '## remove at OP request #';
  4    msg_to      varchar2(50) := '## remove at OP request #';
  5    /*msg_from    varchar2(50) := 'kmaddineni';
  6    msg_to      varchar2(50) := 'rthirunagari';*/
  7    msg_subject varchar2(50) := 'hi';
  8    msg_text    varchar2(50) := 'mail from procedure ';
  9    rc          integer;
 10  BEGIN
 11  
 12    -- c := utl_tcp.open_connection('## remove at OP request #', 25); -- open the SMTP port 25 on local machine by using ip address --## remove at OP request #
 13    c := utl_tcp.open_connection('## remove at OP request #', 25); -- open the SMTP port 25 on local machine by using name
 14    dbms_output.put_line(utl_tcp.get_line(c, TRUE));
 15    rc := utl_tcp.write_line(c, 'HELO localhost');
 16    dbms_output.put_line(utl_tcp.get_line(c, TRUE));
 17    rc := utl_tcp.write_line(c, 'MAIL FROM: ' || msg_from);
 18    dbms_output.put_line(utl_tcp.get_line(c, TRUE));
 19    rc := utl_tcp.write_line(c, 'RCPT TO: ' || msg_to);
 20    dbms_output.put_line(utl_tcp.get_line(c, TRUE));
 21    rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
 22    dbms_output.put_line(utl_tcp.get_line(c, TRUE));
 23    rc := utl_tcp.write_line(c, 'Subject: ' || msg_subject);
 24    dbms_output.put_line(utl_tcp.get_line(c, TRUE));
 25    rc := utl_tcp.write_line(c, msg_text);
 26    rc := utl_tcp.write_line(c, '.'); -- End of message body
 27    dbms_output.put_line(utl_tcp.get_line(c, TRUE));
 28    rc := utl_tcp.write_line(c, 'QUIT');
 29    dbms_output.put_line(utl_tcp.get_line(c, TRUE));
 30    utl_tcp.close_connection(c); -- Close the connection
 31  
 32  EXCEPTION
 33    when others then
 34      raise_application_error(-20000,
 35                              'Unable to send e-mail message from pl/sql because of: ' ||
 36                              sqlerrm);
 37  END;
 38  /
 
220 ## remove at OP request # ESMTP Service (Lotus Domino Release 8.5.1) ready at Thu, 12 Apr 2012 02:36:38 -0400
250 ## remove at OP request # Hello localhost ([## remove at OP request #]), pleased to meet you
501 Syntax error, parameters in command "MAIL FROM: ## remove at OP request #" unrecognized or missing
501 Syntax error, parameters in command "RCPT TO: ## remove at OP request #" unrecognized or missing
503 Issue RCPT TO: command before DATA command
500 Syntax error, command "Subject: hi" unrecognized
501 Syntax error, parameters in command "mail from procedure" unrecognized or missing
500 Syntax error, command "." unrecognized
 
PL/SQL procedure successfully completed
 
SQL> 


Could you please help me


Thanks
SaiPradyumn

[Updated on: Thu, 12 April 2012 08:18] by Moderator

Report message to a moderator

Re: SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550748 is a reply to message #550745] Thu, 12 April 2012 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
utl_mail is not available in my Database(10 g),even though it's available in 10g.


Ask your DBA to install it.

Regards
Michel
Re: SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550756 is a reply to message #550748] Thu, 12 April 2012 02:41 Go to previous messageGo to next message
saipradyumn
Messages: 175
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel ,

I am sorry to say like this, as our DBA is on vacation and I need to complete this issue as early as possible .No one is there to help me in DBA activities .

It's not possible by using utl_smtp , utl_tacp packages
which are also available in older versions .
As we should not violate the existing functionality in latest version

Could you please let me the problem with procedure.


Thanks
SaiPradyumn


Re: SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550758 is a reply to message #550756] Thu, 12 April 2012 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you mean there is no one that restart the database if it is down or restore it if it is broken?
Anyway, I will not take time to debug something that can be easily workaround in one minute by installing the appropriate package.

In addition, there are many WORKING examples on the web with UTL_SMPT, pick one and stop debugging your procedure.

Regards
Michel

Re: SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550763 is a reply to message #550758] Thu, 12 April 2012 03:24 Go to previous messageGo to next message
saipradyumn
Messages: 175
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel ,
These two procedures are written by me directly . I have taken from our orafaq previous threads only .
There are also its' like this only .

Link :

http://www.orafaq.com/wiki/Send_mail_from_PL/SQL#Send_mail_with_UTL_SMTPS



Sorry for troubling this many times

Could you please help me to find out the actual problem


Thanks in advance
SaiPradyumn
Re: SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550767 is a reply to message #550763] Thu, 12 April 2012 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 57599
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
These two procedures are written by me directly .


Which 2 procedures?
If you just copy and paste the procedure in the link you posted, it works (at least for me):
SQL> l
  1  DECLARE
  2    v_From      VARCHAR2(80) := '<remove for confidentiality>';
  3    v_Recipient VARCHAR2(80) := '<remove for confidentiality>';
  4    v_Subject   VARCHAR2(80) := 'test subject';
  5    v_Mail_Host VARCHAR2(30) := '<remove for confidentiality>';
  6    v_Mail_Conn utl_smtp.Connection;
  7    crlf        VARCHAR2(2)  := chr(13)||chr(10);
  8  BEGIN
  9   v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
 10   utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
 11   utl_smtp.Mail(v_Mail_Conn, v_From);
 12   utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
 13   utl_smtp.Data(v_Mail_Conn,
 14     'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
 15     'From: '   || v_From || crlf ||
 16     'Subject: '|| v_Subject || crlf ||
 17     'To: '     || v_Recipient || crlf ||
 18     crlf ||
 19     'some message text'|| crlf || -- Message body
 20     'more message text'|| crlf
 21   );
 22   utl_smtp.Quit(v_mail_conn);
 23  EXCEPTION
 24   WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
 25     raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
 26* END;
SQL> /

Procédure PL/SQL terminée avec succès.

Regards
Michel
Re: SMTP permanent error: 501 Syntax error while sending mail from procedure [message #550779 is a reply to message #550767] Thu, 12 April 2012 04:34 Go to previous message
saipradyumn
Messages: 175
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel ,

The two procedures are

1) By using util_smtp
2) By using utl_tcp

As per the example given in the link I have written my procedure. I have configured the proper mail host server and i am able to see the DBMS related statements after the server configuration . But when I am associating sender mail id with conn object I am getting the exceptions

Are there any configuration to the mail id in server to send a mail from procedure


Thanks
SaiPradyumn





Previous Topic: SQL query (merged)
Next Topic: Is it correct way
Goto Forum:
  


Current Time: Wed Apr 16 06:15:47 CDT 2014

Total time taken to generate the page: 0.15947 seconds