Oracle 11g数据库发送邮件遇ORA-29279错误求助

Hey there! That ORA-29279: 530 5.7.0 authentication required error is spelling out exactly what's wrong—your SMTP server demands a login with a username and password, but your current stored procedure skips that critical step entirely. Let's get this sorted out quickly.

Why the Error Pops Up #

Right now, your code only opens a connection to the SMTP host and sends a HELO message. But most modern SMTP servers (like Gmail, Outlook, or internal corporate servers) won't let you send mail without verifying your identity first. That missing authentication step is why you're hitting this roadblock.

Updated Stored Procedure with Authentication #

I've modified your procedure to include SMTP authentication, adding parameters for username and password, and inserting the key UTL_SMTP.login call right after the HELO command. Here's the full working code:

CREATE OR REPLACE PROCEDURE send_mail_deepak_test (
    p_to            IN VARCHAR2,
    p_from          IN VARCHAR2,
    p_subject       IN VARCHAR2,
    p_message       IN VARCHAR2,
    p_smtp_host     IN VARCHAR2,
    p_smtp_port     IN NUMBER DEFAULT 25,
    p_smtp_username IN VARCHAR2, -- New parameter for SMTP login
    p_smtp_password IN VARCHAR2  -- New parameter for SMTP password
) AS
    l_mail_conn UTL_SMTP.connection;
BEGIN
    l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
    
    -- Introduce yourself to the SMTP server
    UTL_SMTP.helo(l_mail_conn, p_smtp_host);
    
    -- Critical step: Log in to the SMTP server with your credentials
    UTL_SMTP.login(l_mail_conn, p_smtp_username, p_smtp_password);
    
    -- Set sender and recipient addresses
    UTL_SMTP.mail(l_mail_conn, p_from);
    UTL_SMTP.rcpt(l_mail_conn, p_to);
    
    -- Compose and send the email content
    UTL_SMTP.open_data(l_mail_conn);
    UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf); -- Separate headers from body
    UTL_SMTP.write_data(l_mail_conn, p_message);
    UTL_SMTP.close_data(l_mail_conn);
    
    -- Cleanly close the connection
    UTL_SMTP.quit(l_mail_conn);
EXCEPTION
    WHEN OTHERS THEN
        -- Ensure connection gets closed even if an error occurs
        UTL_SMTP.quit(l_mail_conn);
        RAISE;
END send_mail_deepak_test;
/

Important Things to Check #

• Permissions: Make sure the Oracle user running this procedure has EXECUTE access to UTL_SMTP and UTL_TCP. If not, ask your DBA to run:

  GRANT EXECUTE ON UTL_SMTP TO your_oracle_username;
  GRANT EXECUTE ON UTL_TCP TO your_oracle_username;
  

• SMTP Port: Some servers use port 587 (for TLS encryption) instead of 25. If port 25 fails, try 587. If TLS is required, add UTL_SMTP.starttls(l_mail_conn); right after HELO and before login.
• Credentials: Double-check your SMTP username and password—typos here will throw the same authentication error.
• Error Handling: The exception block ensures the connection closes properly if something goes wrong, preventing hanging connections to the SMTP server.

How to Call the Updated Procedure #

When running the procedure, just include your SMTP credentials alongside the other parameters:

EXEC send_mail_deepak_test(
    p_to            => 'recipient@example.com',
    p_from          => 'your_email@example.com',
    p_subject       => 'Test Email from Oracle',
    p_message       => 'This is a test email with SMTP authentication!',
    p_smtp_host     => 'smtp.example.com',
    p_smtp_port     => 587,
    p_smtp_username => 'your_smtp_username',
    p_smtp_password => 'your_smtp_password'
);

内容的提问来源于stack exchange，提问作者Deepak.Pal