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:

Troubleshooting: