Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Script run with error

Avatar of dinhchung82
dinhchung82Flag for Viet Nam asked on
Oracle Database
7 Comments1 Solution1502 ViewsLast Modified:
Below is my script , it has error ,  i need to advise because i cannot find the reason
or advise me a solution for sending mail from pl/sql  or
combine a language with ORACLE DATABSE

The error is :

ORA-20000: Unable to send mail: ORA-29279: SMTP permanent error: 501 5.5.2 Syntax: MAIL FROM: <address>
DECLARE
  v_From      VARCHAR2(80) := 'dinhchung@vpb.com.vn';
  v_Recipient VARCHAR2(80) := 'dinhchung82@gmail.com';
  v_Subject   VARCHAR2(80) := 'test subject';
  v_Mail_Host VARCHAR2(30) := '210.245.63.86';
  v_Mail_Conn utl_smtp.Connection;
  crlf        VARCHAR2(2)  := chr(13)||chr(10);
  v_username varchar2(80);
  v_password varchar2(80);
BEGIN
 v_username := 'dinhchung@vpb.com.vn';
 v_password := '*****';
 
 
 v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host,25);
 
 
 utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
 utl_smtp.command( v_Mail_Conn, 'AUTH LOGIN');
 utl_smtp.command ( v_mail_conn
,utl_raw.cast_to_varchar2 (
utl_encode.base64_encode (
utl_raw.cast_to_raw ( v_username )
)
)
);
 
utl_smtp.command ( v_mail_conn
,utl_raw.cast_to_varchar2 (
utl_encode.base64_encode (
utl_raw.cast_to_raw ( v_password )
)
)
);
 
 utl_smtp.Mail(v_Mail_Conn, 'dinhchung@vpb.com.vn');
 utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
 utl_smtp.Data(v_Mail_Conn,
   'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
   'From: '   || v_From || crlf ||
   'Subject: '|| v_Subject || crlf ||
   'To: '     || v_Recipient || crlf ||
   crlf ||
   'some message text'|| crlf ||        
   'more message text'|| crlf  );
 
 
 
 utl_smtp.Quit(v_mail_conn);
 
 
EXCEPTION
 WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
   raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;