Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Error Certificate Validation Failure When Create Stored Procedure Send Email with PL/SQL

Rosa AmandaJan 3 2025 — edited Jan 7 2025

Hello everyone

I have a Stored Procedure for sending emails but error

CREATE OR REPLACE PROCEDURE send_email (
   p_recipient IN VARCHAR2,
   p_subject IN VARCHAR2,
   p_message IN VARCHAR2
) AS
   l_mailhost VARCHAR2(255) := 'smtp.gmail.com';
   l_port     NUMBER := 587; 
   l_mail_conn UTL_SMTP.connection;
   l_username  VARCHAR2(255) := 'example@gmail.com'; 
   l_password  VARCHAR2(255) := 'example'; 
   l_response  VARCHAR2(32767);
BEGIN
   l_mail_conn := UTL_SMTP.open_connection(l_mailhost, l_port);
   
   UTL_SMTP.helo(l_mail_conn, l_mailhost);
   UTL_SMTP.starttls(l_mail_conn, 'NO_CERTIFICATE_VERIFY');
   
   UTL_SMTP.write_data(l_mail_conn, 'AUTH PLAIN '  UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(l_username  CHR(0)  l_username  CHR(0)  l_password))  UTL_TCP.CRLF);
   
   UTL_SMTP.open_data(l_mail_conn);
   UTL_SMTP.write_data(l_mail_conn, 'From: '  l_username  UTL_TCP.CRLF);
   UTL_SMTP.write_data(l_mail_conn, 'To: '  p_recipient  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); -- Blank line to separate headers from body
   UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.CRLF);
   
   UTL_SMTP.close_data(l_mail_conn);
   UTL_SMTP.quit(l_mail_conn);
   
   DBMS_OUTPUT.put_line('Email sent successfully to ' || p_recipient);
EXCEPTION
   WHEN OTHERS THEN
       BEGIN
           UTL_SMTP.quit(l_mail_conn);
       EXCEPTION
           WHEN OTHERS THEN
               NULL;
       END;
       RAISE;
END send_email;

why error certificate validation failure, Is there a special setting for the oracle stored procedure send email?

I just need to send some emails but not get spammed and make sure they were successfully sent.

Can someone help me solve this problem please.

Thanks

This post has been answered by BluShadow on Jan 3 2025
Jump to Answer
Comments
Post Details
Added on Jan 3 2025
4 comments
139 views