How to send mail through oracle procedure
To send an email through an Oracle procedure, you can use the UTL_SMTP
package, which is a built-in package in Oracle that provides a way to send emails. Here's an example of how you can use it:
Step 1: Create a procedure
Create a procedure that will send the email. For example:
CREATE OR REPLACE PROCEDURE send_email(p_to IN VARCHAR2, p_subject IN VARCHAR2, p_body IN VARCHAR2) AS
l_smtp_host VARCHAR2(255) := 'your_smtp_host';
l_smtp_port NUMBER := 25;
l_smtp_username VARCHAR2(255) := 'your_smtp_username';
l_smtp_password VARCHAR2(255) := 'your_smtp_password';
BEGIN
DBMS_OUTPUT.PUT_LINE('Sending email to ' || p_to);
UTL_SMTP.OPEN_CONNECTION(l_smtp_host, l_smtp_port);
UTL_SMTP.HELO(l_smtp_host);
UTL_SMTP.MAIL(from => 'your_from_email');
UTL_SMTP.RCPT(to => p_to);
UTL_SMTP.DATA(p_subject);
UTL_SMTP.DATA(p_body);
UTL_SMTP.QUIT;
END send_email;
Step 2: Compile the procedure
Compile the procedure:
BEGIN
DBMS_OUTPUT.PUT_LINE('Compiling procedure...');
EXECUTE IMMEDIATE 'ALTER PROCEDURE send_email COMPILE';
END;
Step 3: Call the procedure
Call the procedure to send the email:
BEGIN
send_email('[email protected]', 'Subject of the email', 'Body of the email');
END;
Note:
- Replace
your_smtp_host
,your_smtp_port
,your_smtp_username
, andyour_smtp_password
with your actual SMTP server details. - Replace
your_from_email
with the email address that will be used as the sender of the email. - Make sure to replace
[email protected]
with the actual email address of the recipient. - The
UTL_SMTP
package uses theDATA
procedure to send the email body. You can use this procedure to send plain text or HTML emails.
Troubleshooting:
- If you encounter issues with sending emails, check the Oracle error logs for any errors.
- Make sure that the SMTP server is configured correctly and that the email account has the necessary permissions to send emails.
- You can also use the
UTL_MAIL
package instead ofUTL_SMTP
, which provides a simpler way to send emails. However,UTL_MAIL
has some limitations, such as not supporting attachments or HTML emails.