CODEWITHSUNDEEP
sqloracle-databaseemail
user2755525
user2755525

Reputation: 219

Give credentials to UTL_MAIL.SEND to bypass ORA-29278

I am trying to send email from a PL/SQL program using UTL_MAIL.Send packaged procedure. But I am getting the following error:

ORA-29278: SMTP transient error: 421 Service not available

I searched on web, and found that this error occurs because there is no SMTP service on the host running my Oracle database.

Now if I want to use another SMTP server, for example , hotmail's "smtp.live.com", i need my user name and password.

How can I give my password into the UTL_MAIL.Send procedure call?

(according to my understanding, in order to use any other SMTP server, I have to provide my username/password). I know that to use UTL_MAIL package, we set the SMTP server with an initialization parameter, and we can give username in "Sender" parameter, but the question is where should we give the password?

Upvotes: 4

Views: 17562

Answers (2)

Sylvain Leroux
Sylvain Leroux

Reputation: 52040

Basically you have to use the "lower level" UTL_SMTP package in order to send the various SMTP messages required by the distant SMTP server.

Authentication

From Stefano Ghio's blog:

 -- prepare base64 encoded username and password
 l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));  
 l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));

 -- Open connection and send EHLO and AUTH messages  
 l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);  
 UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO  
 UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');  
 UTL_SMTP.command(l_conn, l_encoded_username);  
 UTL_SMTP.command(l_conn, l_encoded_password);

The main issue here is that you need be able to send the AUTH message using the "right" authentication scheme for your server. I can't say for "smtp.live.com" specifically, but depending the server's configuration, they might be different authentication scheme, like PLAIN and LOGIN, DIGEST_MD5, ... Usually (always ?) the parameters (username, password) are base64 encoded.

Sending mail

But the bad news is, since you are now using a low-level library, you have to implement the client part of the SMTP protocol yourself. From the same source as above (edited by myself to only keep the absolutely minimum necessary stuff):

UTL_SMTP.mail(l_conn, mailFrom);
UTL_SMTP.rcpt(l_conn, rcptTo);
[...]

--start multi line message
UTL_SMTP.open_data(l_conn);

--prepare mail header
UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || crlf);
UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || crlf);
UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || crlf);

--include the message body
UTL_SMTP.write_data(l_conn, messageBody || crlf || crlf);

--send the email and close connection
UTL_SMTP.close_data(l_conn);
UTL_SMTP.quit(l_conn);

Using SSL/TLS

And now, for the very bad news: some server required a secure connection. Claiming something like 530 Must issue a STARTTLS command first. Unfortunately, UTL_SMTP.STARTTLS is only supported starting from Oracle Database 11g release 2 (11.2.0.2).

If you are lucky enougth to use a recent version of Oracle, you should write something like that to open a secure connection with your server:

l_conn := UTL_SMTP.open_connection(l_conn, smtpHost,
    wallet_path => 'file:/oracle/wallets/smtp_wallet',
    wallet_password => 'password',
    secure_connection_before_smtp => FALSE);
UTL_SMTP.starttls(l_conn);

To quote Oracle's documentation:

SSL/TLS requires an Oracle wallet which must be specified when the connection was opened by the OPEN_CONNECTION Functions.

Please see the corresponding documentation to see how to create and manage wallet

Few more readings:

Upvotes: 5

myte
myte

Reputation: 877

found this online. seems like either of these should do the trick:

AUTH PLAIN

v_plain_string :=
     UTL_RAW.cast_to_varchar2(
        UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('my_user_name'||chr(0)||'my_user_name'||chr(0)||'my_secret_password'))
    );

v_connection := UTL_SMTP.open_connection(:v_smtp_server);
UTL_SMTP.ehlo(v_connection, 'mydomain.com');    -- Must use EHLO  vs HELO
UTL_SMTP.command(v_connection, 'AUTH', 'PLAIN ' || v_plain_string);

AUTH LOGIN

v_username_b64 :=
    UTL_RAW.cast_to_varchar2(
        UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_username))
    );
v_password_b64 :=
    UTL_RAW.cast_to_varchar2(
        UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(:v_password))
    );
v_connection := UTL_SMTP.open_connection(:v_smtp_server);
UTL_SMTP.ehlo(v_connection, 'mydomain.com');    -- Must use EHLO  vs HELO
UTL_SMTP.command(v_connection, 'AUTH', 'LOGIN');  -- should receive a 334 response, prompting for username
UTL_SMTP.command(v_connection, v_username_b64);   -- should receive a 334 response, prompting for password
UTL_SMTP.command(v_connection, v_password_b64);   -- should receive a 235 response, you are authenticated

also i think when you talk about the "Sender" parameter you might be referring to the "From" header for the email itself.

Upvotes: 0

Related Questions