英文:
Oracle Apex send email
问题
以下是您提供的代码的翻译部分：
我之前在我的Oracle Apex应用程序中使用utl_smtp来发送电子邮件，一切都正常工作，但在我的邮件服务器更新到TLS 1.2后，我的Oracle包不再工作了！
我已经尝试添加钱包和其他解决方案，但都不起作用！
我遇到不同的错误，如：服务不可用或证书验证失败！
有人知道我应该如何解决吗？或者有没有不需要任何TLS证书的邮件提供商？
请注意，我只翻译了您提供的文本，不包括代码部分。如果您需要更多翻译或有其他问题，请告诉我。
英文:
I was using utl_smtp to send email from my oracle Apex Application and every thing was working fine, after my mail server updating to TLS 1.2 my Oracle Package doesnt work any more !!!
I've tested adding Wallet and other solutions but it doesnt work!
I get different errors like: Service unavailable or Certificate validation failed!!
any one knows how should I solve it? or is there any mail provider, who doesnt need any TLS certification?
CREATE OR REPLACE PACKAGE BODY send_email
IS
-- constants
c_username VARCHAR2 (50) := 'my_sender';
c_password VARCHAR2 (50) := 'my_password';
the_connection UTL_SMTP.connection;
-- Functions
FUNCTION build_address_string (p_string IN VARCHAR2, p_rcps IN VARCHAR2, p_rcps_names IN VARCHAR2)
RETURN VARCHAR2
IS
i INTEGER;
v_recipients VARCHAR2 (5000);
v_reply UTL_SMTP.reply;
BEGIN
v_recipients := p_string || p_rcps ;
v_reply := UTL_SMTP.rcpt (the_connection, p_rcps );
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| 'UTL_SMTP.rcpt p_recipient');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
DBMS_OUTPUT.PUT_LINE ( 'v_recipients: '|| v_recipients);
RETURN v_recipients;
END;
-- procedures
--
----------------------------------------------------------------------------------------
PROCEDURE send_email (p_to_rcps IN VARCHAR2, p_to_rcps_names IN VARCHAR2, p_cc_rcps IN VARCHAR2,
p_cc_rcps_names IN VARCHAR2, p_subject IN VARCHAR2, p_message_body IN VARCHAR2)
IS
i INTEGER;
v_adr_to VARCHAR2 (5000);
v_adr_cc VARCHAR2 (5000);
v_host_name VARCHAR2 (65);
v_reply UTL_SMTP.reply;
v_replies UTL_SMTP.replies;
v_smtp_port NUMBER;
v_smtp_server VARCHAR2 (100);
v_smtp_sender VARCHAR2 (100);
v_smtp_user VARCHAR2 (100);
BEGIN
v_host_name := 'the host name';
v_smtp_server := 'smtp server name';
v_smtp_port := 25;
v_smtp_sender := 'email sender';
v_smtp_user := 'email user';
v_reply := UTL_SMTP.open_connection (v_smtp_server, v_smtp_port, the_connection);
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' open connection reply');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
v_replies := UTL_SMTP.ehlo (the_connection, v_smtp_server);
i := v_replies.FIRST;
WHILE (i IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE ( 'count i '|| i|| ' ehlo replies');
DBMS_OUTPUT.PUT_LINE ( 'v_reply '|| v_replies (i).code);
DBMS_OUTPUT.PUT_LINE ( 'v_reply '|| v_replies (i).text);
i := v_replies.NEXT (i);
END LOOP;
IF (v_smtp_user != 'ANONYMOUS')
THEN
-- BEGIN AUTHENTICATION
v_reply := UTL_SMTP.command (the_connection, 'AUTH LOGIN'); -- should receive a 334 response, prompting for username
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' AUTH LOGIN');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
v_reply := UTL_SMTP.command (the_connection, UTL_ENCODE.text_encode (c_username, 'WE8ISO8859P1', UTL_ENCODE.BASE64)); -- should receive a 334 response, prompting for password
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' username reply');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
v_reply := UTL_SMTP.command (the_connection, UTL_ENCODE.text_encode (c_password, 'WE8ISO8859P1', UTL_ENCODE.BASE64)); -- should receive a 235 response, you are authenticated
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' pwd reply');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
-- END AUTHENTICATION
ELSE
v_reply.code := 235;
END IF;
IF (v_reply.code = 235)
THEN
-- Check the sender
v_reply := UTL_SMTP.mail (the_connection, v_smtp_sender);
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' UTL_SMTP.mail sender');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
-- Creating Adsresses
v_adr_to := build_address_string ('To: ', p_to_rcps, p_to_rcps_names);
DBMS_OUTPUT.PUT_LINE ( 'v_adr_to: '|| v_adr_to);
v_adr_cc := build_address_string ('Cc: ', p_cc_rcps, p_cc_rcps_names);
DBMS_OUTPUT.PUT_LINE ( 'v_adr_cc: '|| v_adr_cc);
-- Writing the data
v_reply := UTL_SMTP.open_data (the_connection);
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' UTL_SMTP.open_data ');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
UTL_SMTP.write_data(the_connection, 'From: ' || v_host_name || UTL_TCP.crlf);
UTL_SMTP.write_data(the_connection, 'Subject: ' || NVL (p_subject, '(no subject)') || UTL_TCP.crlf);
--UTL_SMTP.write_data(the_connection, 'Reply-To: ' || v_adr_to || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(the_connection, v_adr_to || UTL_TCP.crlf);
UTL_SMTP.write_data(the_connection, v_adr_cc || UTL_TCP.crlf);
UTL_SMTP.write_data (the_connection, '' || UTL_TCP.crlf);
UTL_SMTP.write_data(the_connection, p_message_body || UTL_TCP.crlf || UTL_TCP.crlf);
-- sending email and closing the connection
UTL_SMTP.close_data (the_connection);
v_reply := UTL_SMTP.quit (the_connection);
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' UTL_SMTP.quit');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
ELSE
DBMS_OUTPUT.PUT_LINE ( 'authentication failure ');
v_reply := UTL_SMTP.quit (the_connection);
DBMS_OUTPUT.PUT_LINE ( 'v_reply.code: '|| v_reply.code|| ' UTL_SMTP.quit');
DBMS_OUTPUT.PUT_LINE ( 'v_reply.text: '|| v_reply.text);
END IF;
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
BEGIN
UTL_SMTP.quit (the_connection);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
RAISE;
-- have a connection to the server. The quit call will
-- raise an exception that we can ignore.
END;
raise_application_error (-20000, 'Failed to send mail due to the following error: ' || SQLERRM);
WHEN OTHERS
THEN
RAISE;
END send_email;
END send_email;
/
答案1
得分: 1
使用内置的 apex_mail 包。以下是一个发送“普通”邮件的过程示例，或者 - 如果您还传递附件名称和 CLOB 参数值 - 发送带有附件的邮件。
CREATE PROCEDURE p_send_mail (i_from IN VARCHAR2,
i_to IN VARCHAR2,
i_cc IN VARCHAR2,
i_bcc IN VARCHAR2,
i_id_email IN NUMBER,
i_subject IN VARCHAR2,
i_text_msg IN VARCHAR2 DEFAULT NULL,
i_attach_name IN VARCHAR2 DEFAULT NULL,
i_attach_mime IN VARCHAR2 DEFAULT NULL,
i_attach_clob IN CLOB DEFAULT NULL)
IS
l_id NUMBER;
BEGIN
l_id :=
apex_mail.send (p_from => i_from,
p_to => i_to,
p_cc => i_cc,
p_bcc => i_bcc,
p_replyto => NULL,
p_subj => i_subject,
p_body => i_text_msg,
p_body_html => i_text_msg);
IF i_attach_name IS NOT NULL
AND i_attach_clob IS NOT NULL
THEN
apex_mail.add_attachment (p_mail_id => l_id,
p_filename => i_attach_name,
p_mime_type => i_attach_mime,
p_attachment => i_attach_clob);
END IF;
END p_send_mail;
如果您需要非常简单的东西，只需调用
apex_mail.send (p_from => i_from,
p_to => i_to,
p_subj => i_subject,
p_body => i_text_msg);
英文:
Use the apex_mail built-in package. Here's an example of a procedure that sends an "ordinary" mail or - if you also pass attachment name and CLOB parameter values - sends mail with an attachment.
CREATE PROCEDURE p_send_mail (i_from IN VARCHAR2,
i_to IN VARCHAR2,
i_cc IN VARCHAR2,
i_bcc IN VARCHAR2,
i_id_email IN NUMBER,
i_subject IN VARCHAR2,
i_text_msg IN VARCHAR2 DEFAULT NULL,
i_attach_name IN VARCHAR2 DEFAULT NULL,
i_attach_mime IN VARCHAR2 DEFAULT NULL,
i_attach_clob IN CLOB DEFAULT NULL)
IS
l_id NUMBER;
BEGIN
l_id :=
apex_mail.send (p_from => i_from,
p_to => i_to,
p_cc => i_cc,
p_bcc => i_bcc,
p_replyto => NULL,
p_subj => i_subject,
p_body => i_text_msg,
p_body_html => i_text_msg);
IF i_attach_name IS NOT NULL
AND i_attach_clob IS NOT NULL
THEN
apex_mail.add_attachment (p_mail_id => l_id,
p_filename => i_attach_name,
p_mime_type => i_attach_mime,
p_attachment => i_attach_clob);
END IF;
END p_send_mail;
If you need something really simple, just call
apex_mail.send (p_from => i_from,
p_to => i_to,
p_subj => i_subject,
p_body => i_text_msg);
答案2
得分: 1
你可以使用内置的Apex_mail服务来实现更简单和有效的方法。
https://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_mail.htm#AEAPI343
英文:
Better you can use Apex_mail inbuild service for easy and effective method.
https://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_mail.htm#AEAPI343
答案3
得分: 1
Oracle Apex有一个简单的邮件发送选项，无需程序单元。
英文:
Oracle apex have a simple mail-send option without a program unit.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论