我一直在撰寫代碼來實作這一點，但完全卡住了
在這種情況下需要你的幫助
我使用 Oracle APEX 創建了一個應用程式，在我的頁面中帶有互動式報告和表單
我發送郵件的程式
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_subject IN VARCHAR2,
p_html_msg IN VARCHAR2 DEFAULT NULL,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 25)
AS
l_mail_conn UTL_SMTP.connection;
l_boundary VARCHAR2(50) := '----=*#abc1234321cba#*=';
BEGIN
l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
UTL_SMTP.helo(l_mail_conn, p_smtp_host);
UTL_SMTP.mail(l_mail_conn, p_from);
UTL_SMTP.rcpt(l_mail_conn, p_to);
UTL_SMTP.open_data(l_mail_conn);
UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
IF p_html_msg IS NOT NULL THEN
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.write_data(l_mail_conn, p_html_msg);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
END IF;
UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
UTL_SMTP.close_data(l_mail_conn);
UTL_SMTP.quit(l_mail_conn);
END;
/
在發送郵件按鈕上單擊我下面的代碼運行完美
declare
l_context apex_exec.t_context;
l_emailsidx pls_integer;
l_namesids pls_integer;
l_region_id number;
l_html VARCHAR2(32767);
begin
select region_id
into l_region_id
from apex_application_page_regions
where application_id = :APP_ID
and page_id = 1
and static_id = 'CUSTOMERS';
l_context := apex_region.open_query_context (
p_page_id => 1,
p_region_id => l_region_id );
-- Get the column positions for EMAIL and NAME columns
l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
l_namesids := apex_exec.get_column_position( l_context, 'NAME' );
while apex_exec.next_row( l_context ) loop
send_mail(
p_to => apex_exec.get_varchar2( l_context, l_emailsidx ),
p_from => '[email protected]',
p_subject => :P2_SUBJECT,
p_html_msg => :P3_HTML,
p_smtp_host => 'smtp.mycompany.com');
Exception
when OTHERS THEN
// log my error to ERROR table
CONTINUE;
end loop;
apex_exec.close( l_context );
exception
when others then
apex_exec.close( l_context );
raise;
end;
但是，如果任何電子郵件 ID 無效并且我的 UTL SMTP 無法發送郵件，則會引發錯誤，我想捕獲該錯誤 -> 使用電子郵件 ID 存盤到我下面的錯誤表中
create table employee_error(
emailid clob,
error_msg clob
);
第一個代碼嘗試
Exception
when OTHERS THEN
Insert into employee_error values(p_to,sqlerr);
CONTINUE;
第二個代碼嘗試：我也嘗試了這個，但它拋出了錯誤：ORA-06550
while apex_exec.next_row( l_context ) loop
begin
send_mail(
p_to => apex_exec.get_varchar2( l_context, l_emailsidx ),
p_from => '[email protected]',
p_subject => :P2_SUBJECT,
p_html_msg => :P3_HTML,
p_smtp_host => 'smtp.mycompany.com');
Exception
when OTHERS THEN
Insert into employee_error (emailid, error_msg)
values
(apex_exec.get_varchar2( l_context, l_emailsidx ), sqlerrm);
end;
end loop;
當我嘗試使用第二個代碼時，出現以下錯誤
ORA-06550: column not allowed , SQL statement ignored
即使發生錯誤，我發送郵件的處理也不應該停止，所以我添加了 continue 到我的例外。
以便將錯誤記錄到錯誤表并繼續選擇下一個 id 以觸發郵件
uj5u.com熱心網友回復：
您已經很接近了-只需將另一個BEGIN-EXCEPTION-END塊嵌入到回圈中即可。像這樣的東西：
while apex_exec.next_row( l_context ) loop
begin
send_mail(
p_to => apex_exec.get_varchar2( l_context, l_emailsidx ),
p_from => '[email protected]',
p_subject => :P2_SUBJECT,
p_html_msg => :P3_HTML,
p_smtp_host => 'smtp.mycompany.com');
Exception
when OTHERS THEN
Insert into employee_error (emailid, error_msg)
values
(apex_exec.get_varchar2( l_context, l_emailsidx ), sqlerrm);
end;
end loop;
uj5u.com熱心網友回復：
我通過參考作者@littlefoot 來做到這一點并且它有效
declare
l_context apex_exec.t_context;
l_emailsidx pls_integer;
l_namesids pls_integer;
l_region_id number;
l_html VARCHAR2(32767);
v_errm varchar2(4000 char);
v_email varchar2(4000 char);
begin
select region_id
into l_region_id
from apex_application_page_regions
where application_id = :APP_ID
and page_id = 1
and static_id = 'CUSTOMERS';
l_context := apex_region.open_query_context (
p_page_id => 1,
p_region_id => l_region_id );
-- Get the column positions for EMAIL and NAME columns
l_emailsidx := apex_exec.get_column_position( l_context, 'EMAIL' );
l_namesids := apex_exec.get_column_position( l_context, 'NAME' );
while apex_exec.next_row( l_context ) loop
begin
send_mail(
p_to => apex_exec.get_varchar2( l_context, l_emailsidx ),
p_from => '[email protected]',
p_subject => :P2_SUBJECT,
p_html_msg => :P3_HTML,
p_smtp_host => 'smtp.mycompany.com');
Exception
when OTHERS THEN
v_errm := sqlerrm;
v_email := apex_exec.get_varchar2( l_context, l_emailsidx );
Insert into employee_error values (v_errm ,v_email);
end;
end loop;
apex_exec.close( l_context );
exception
when others then
apex_exec.close( l_context );
raise;
end;
uj5u.com熱心網友回復：
您需要使用自治事務來實作日志記錄功能。它不會中斷您的流程邏輯，并且會在回滾時保留記錄的條目。
create table log_table ( dttm timestamp default systimestamp, err_code int, err_message varchar2(1000) )
create table t ( dttm timestamp default systimestamp, val int )
create procedure log_error ( code int, msg varchar2 ) as pragma autonomous_transaction; begin insert into log_table (dttm, err_code, err_message) values(systimestamp, code, msg); commit; end; /
create procedure test ( p_iter int, p_err_stop int default null ) as err_cnt int := 0; begin for i in 1..p_iter loop begin if mod(i, 2) = 0 then err_cnt := err_cnt 1; raise_application_error( -20001, i ); end if; insert into t (val) values (i); exception when others then log_error(sqlcode, sqlerrm); if err_cnt > p_err_stop then /*Stop execution*/ raise_application_error(-20002, 'Too many errors'); end if; end; end loop; end; /
begin test(10); commit; test(10, 2); commit; end;/ORA-20002: 錯誤太多
ORA-06512：在“FIDDLE_TBTPMHPKCNYEUKUQEFOW.TEST”第 26 行 ORA-06512：在第 4 行
select * from log_tableDTTM | ERR_CODE | ERR_MESSAGE :------------------------- | -------: | :------------ 2022-06-19 11:15:49.761825 | -20001 | ORA-20001: 2 2022-06-19 11:15:49.762820 | -20001 | ORA-20001: 4 2022-06-19 11:15:49.763268 | -20001 | ORA-20001: 6 2022-06-19 11:15:49.763810 | -20001 | ORA-20001: 8 2022-06-19 11:15:49.764210 | -20001 | ORA-20001: 10 2022-06-19 11:15:49.765069 | -20001 | ORA-20001: 2 2022-06-19 11:15:49.765624 | -20001 | ORA-20001: 4 2022-06-19 11:15:49.766282 | -20001 | ORA-20001: 6
select * from tDTTM | VAL :------------------------- | --: 2022-06-19 11:15:49.754656 | 1 2022-06-19 11:15:49.762612 | 3 2022-06-19 11:15:49.763093 | 5 2022-06-19 11:15:49.763500 | 7 2022-06-19 11:15:49.764034 | 9
db<>fiddle here
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/494509.html