Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Dave.

Asked: February 04, 2025 - 12:55 pm UTC

Last updated: February 05, 2025 - 9:35 am UTC

Version: 19.25My question is around sending signed emails from oracle. We were hoping to utilize an oracle built in functionality but it seems oracle requires a hardcoded email address. Even oracle support stated I had to hardcode the password. I am coming here be

Viewed 100+ times

You Asked

Good Morning.

My question is sending signed emails from oracle. We were hoping to utilize an oracle built in functionality but it seems oracle requires a hardcoded passwords. Even oracle support stated I had to hardcode the password. I am coming here because I have been provided incorrect information from them before and the knowledge from the community is amazing. Also didnt want to reinvent the wheel if a solution is already available.

So does it require a hardcoded password to use SMTP package? We do have wallets configured. We have one configured for TLS login and would like to untilize that if possible.

—this was sample from oracle support

DECLARE
mailhost VARCHAR2(64) := 'mailserverhostname';
sender VARCHAR2(64) := 'user@domainname';
recipient VARCHAR2(64) := 'user@domainname';
wallet_pwd VARCHAR2(64) := 'walletpassword'; <== this would never pass a security audit
wallet_loc VARCHAR2(64) := 'file:/etc/ORACLE/WALLETS/';
user_name VARCHAR2(64) := 'myemailusername';
user_pwd VARCHAR2(64) := 'myemailuserpassword'; <== this would never pass a security audit
mail_connection utl_smtp.connection;
BEGIN

-- Make a secure connection using the SSL port configured with your SMTP server
-- Note: The sample code here uses the default of 465 but check your SMTP server settings

mail_connection := utl_smtp.open_connection
(
host => mailhost,
port => 465,
wallet_path => wallet_loc,
wallet_password => wallet_pwd,
secure_connection_before_smtp => TRUE
);

utl_smtp.ehlo(mail_connection , mailhost);
utl_smtp.starttls(mail_connection);
utl_smtp.ehlo(mail_connection , mailhost);

-- Call the Auth procedure to authorized a user for access to the mail server
-- Schemes should be set appropriatelty for your mail server
-- See the UTL_SMTP documentation for a list of constants and meanings
UTL_SMTP.AUTH(
c => mail_connection,
username => user_name,
password => user_pwd,
schemes => 'LOGIN'
);

-- Set up and make the the basic smtp calls to send a test email
utl_smtp.mail(mail_connection, sender);
utl_smtp.rcpt(mail_connection, recipient);
utl_smtp.open_data(mail_connection);
utl_smtp.write_data(mail_connection, 'This is a test message using SSL with SMTP.' || chr(13));
utl_smtp.write_data(mail_connection, 'This test requires an Oracle Wallet be properly configured.' || chr(13));
utl_smtp.close_data(mail_connection);
utl_smtp.quit(mail_connection);
exception when others then
dbms_output.put_line('Error in the anonymous plsql block: '|| sqlerrm);
end;
/


Thnaks in advanced.
Dave

and Chris said...

You do need to pass a password to these procedures; they don't have to be hardcoded though. For example you could:

- Pass them as parameters when calling the function
- Store them in tables and lookup the values

These have their own drawbacks though.

Another option is to install APEX and use APEX_MAIL. This is essentially a wrapper over UTL_SMTP, so hopefully provides what you need.

You can configure the various passwords in the APEX instance. For details see:

https://docs.oracle.com/en/database/oracle/apex/24.2/aeadm/configuring-email.html#GUID-336F4872-8C9C-4F8A-86F4-6DCE463D58C3

You can then send emails without referencing the passwords in your code.

Rating

  (1 rating)

Comments

Oracle Apex

T1dsoldier, February 05, 2025 - 1:55 am UTC

Thanks, I did just stumble across seeing Apex as an option.
Then refining my google search "send signed emails with Apex" this question with your response came up.
I just registered for the Oracle Apex fundamentals, I don't know anything about it but quick reading it seems very useful.
Thanks again


Chris Saxon
February 05, 2025 - 9:35 am UTC

:) You're welcome; hope APEX works out for you.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library