Oracle UTL_SMTP和SSL

我下面的脚本可以正常工作，但是，我相信gmail证书已过期。
使用OpenSSL> s_client -connect smtp.gmail.com/1687 -starttls smtp，我为我的钱包生成了另一个证书并更新了acl，但仍然有一个错误要在l_reply行上发送: = sys.utl_smtp.starttls (L_MAIL_CONN ); ORA-28759: 打开文件ora-28759失败，如果我得到命令，那就是工作。
选择UTL_HTTP.request(url =>'http://www.google.com',
wallet_path => '文件:/oracle/ora11g/owm/钱包/'，
wallet_password => 'xxxxxxxxx') 来自对偶;

CREATE OR REPLACE PROCEDURE PR_JUN_MAIL_NF_DEVOL
AS
  VAR_CONTROLE  NUMBER;
  VAR_MENSAGEM    VARCHAR2(32767);
  L_MAILHOST        VARCHAR2(64) := 'smtp.gmail.com';
  P_USERNAME_       VARCHAR2(50) := 'adminjun@jundia.net';
  P_PASSWORD_       VARCHAR2(50) := 'xxxxxxxx';
  L_DE              VARCHAR2(64) := 'Intranet';
  L_FROM            VARCHAR2(64) := 'adminjun@jundia.net';
  k_wallet_path     CONSTANT VARCHAR2(100) := 'file:/oracle/ora11g/owm/wallets';
  k_wallet_password CONSTANT VARCHAR2(100) := 'xxxxxxxxxxxxx';
  k_domain          CONSTANT VARCHAR2(100) := 'localhost';
  l_port            INTEGER       := 587;
  l_reply           utl_smtp.reply;
  l_replies         utl_smtp.replies;
  
  TO_NAME         VARCHAR2(200):= 'ti@gmail.net';
  SUBJECT         VARCHAR2(200):= 'NOTAS FISCAIS DE DEVOLUCAO EMITIDAS';
  L_MAIL_CONN     UTL_SMTP.CONNECTION;

开始

  VAR_CONTROLE:=1;

  开始
   
   SELECT distinct 0
     INTO VAR_CONTROLE
     from também_JUN_NFE nfe
    inner join bgm_notafiscal bnf on nfe.codintnf_bgmnf = bnf.codintnf
    inner join esfclass e on e.codclassfisc = bnf.codclassfisc   
    INNER join BGM_CLIENTE CLI on bnf.Codcli = CLI.CODCLI
    INNER join CTR_FILIAL F on nfe.empresa = f.codigoempresa and nfe.filial = f.codigofl
    WHERE NFE.DATAEMISSAO > to_date('01/02/2017', 'dd/mm/yyyy')
      and upper(e.descclassfisc) like '%DEVOLU%'
      AND NFe.Codintnf_Bgmnf  || upper(decode(nfe.status,'A','AUTORIZADA', 'C','CANCELADA', 'I','INUTILIZADA', 'N','NORMAL', 'P','PROCESSANDO', 'R','REJEITADA')) not in (select jc.codintnf || upper(jc.statunf) from CONTROLE_NF_DEV JC);
          
  EXCEPTION
    WHEN OTHERS THEN
    VAR_CONTROLE:=1;
  end;

  if VAR_CONTROLE = 0 then
    
    l_reply := UTL_SMTP.OPEN_CONNECTION(
                host             => L_MAILHOST,
                port             => l_port,
                c                => L_MAIL_CONN,
                wallet_path      => k_wallet_path,
                wallet_password  => k_wallet_password,
                secure_connection_before_smtp => FALSE);
    Dbms_Output.Put_Line ('apos conectar');   
    
    l_replies := sys.utl_smtp.ehlo(L_MAIL_CONN, k_domain);
    l_reply   := sys.utl_smtp.starttls(L_MAIL_CONN);
    l_replies := sys.utl_smtp.ehlo(L_MAIL_CONN, k_domain);
    l_reply   := sys.utl_smtp.auth(L_MAIL_CONN, P_USERNAME_, P_PASSWORD_, utl_smtp.all_schemes);
    l_reply   := sys.utl_smtp.mail(L_MAIL_CONN, L_FROM);
    l_reply   := sys.utl_smtp.RCPT(L_MAIL_CONN, TO_NAME);
    l_reply := UTL_SMTP.OPEN_DATA(L_MAIL_CONN);
    
    UTL_SMTP.WRITE_DATA( L_MAIL_CONN, 'FROM:'    ||L_DE||'<'|| L_FROM|| '>' || UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA( L_MAIL_CONN, 'TO:'      ||TO_NAME||UTL_TCP.CRLF);

    UTL_SMTP.WRITE_DATA( L_MAIL_CONN, 'SUBJECT:' ||SUBJECT||UTL_TCP.CRLF);

    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'Content-Type: text/plain; boundary="gc0p4Jq0M2Yt08jU534c0p"'||UTL_TCP.CRLF);
    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'MIME-Version: 1.0'||UTL_TCP.CRLF);

    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, '--gc0p4Jq0M2Yt08jU534c0p' );
    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, 'Content-Type: text/plain' );
    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, ' '||UTL_TCP.CRLF);

    VAR_MENSAGEM:='Nova(s) NFs de Devolucao, segue detalhes abaixo:' || chr(13) || chr(10) ;
    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, VAR_MENSAGEM||UTL_TCP.CRLF);

  for cur_NFDEV in (
    
             select nfe.empresa,
                     TO_CHAR(nfe.FILIAL) || '-' || F.CIDADEFL AS FILIAL,
                     bnf.numeronf AS NUMERO,
                     TO_CHAR(NFe.Dataemissao, 'dd/mm/yyyy') AS EMISSAONF,
                     to_char(bNF.Valortotalnf,  '999G999G990D00','NLS_NUMERIC_CHARACTERS='',.''') AS VALOR,
                     decode(bNF.Lanctointegradocpg, 'S', 'Sim', 'não') as INTEGRADA,
                     decode(nfe.status,'A','AUTORIZADA','C','CANCELADA','I','INUTILIZADA','N','NORMAL','P','PROCESSANDO','R','REJEITADA') as STATUS_NF,
                     fn_jun_ret_nf('O', nfE.Codintnf_Bgmnf) as NFentrada,
                     FORN.NRFORN,
                     FORN.RSOCIALFORN,
                     NFE.Codintnf_Bgmnf AS codnf
                from também_JUN_NFE nfe
               INNER join bgm_notafiscal bnf on nfe.codintnf_bgmnf = bnf.codintnf
               inner join esfclass e on e.codclassfisc = bnf.codclassfisc       
               INNER join BGM_CLIENTE CLI  on bnf.Codcli = CLI.CODCLI
               INNER join CTR_FILIAL F  on nfe.empresa = f.codigoempresa and nfe.filial = f.codigofl
               INNER JOIN BGM_FORNECEDOR FORN ON CLI.NRINSCRICAOCLI = FORN.NRINSCRICAOFORN
               WHERE NFE.DATAEMISSAO > to_date('01/02/2017', 'dd/mm/yyyy')
                 and upper(e.descclassfisc) like '%DEVOLU%'
                 AND NFe.Codintnf_Bgmnf  || upper(decode(nfe.status,'A','AUTORIZADA','C','CANCELADA','I','INUTILIZADA','N','NORMAL','P',
                            'PROCESSANDO','R','REJEITADA')) not in(select jc.codintnf || upper(jc.statunf) from CONTROLE_NF_DEV JC) 
                 ORDER BY NFE.Codintnf_Bgmnf
  ) loop
    VAR_MENSAGEM:='Num. NF: '  || cur_NFDEV.NUMERO  || chr(13) || chr(10);
    VAR_MENSAGEM:=VAR_MENSAGEM || 'Emp/ Filial: '   || cur_NFDEV.Empresa ||' / '|| cur_NFDEV.Filial || chr(13) || chr(10);
    VAR_MENSAGEM:=VAR_MENSAGEM || 'Data Emissao: '  || cur_NFDEV.Emissaonf || chr(13) || chr(10);
    VAR_MENSAGEM:=VAR_MENSAGEM || 'NF devolvida: '  || cur_NFDEV.NFentrada || chr(13) || chr(10);
    VAR_MENSAGEM:=VAR_MENSAGEM || 'Valor: R$ '      || cur_NFDEV.Valor || chr(13) || chr(10);
    VAR_MENSAGEM:=VAR_MENSAGEM || 'Integrado CPG: ' || cur_NFDEV.Integrada || chr(13) || chr(10);
    VAR_MENSAGEM:=VAR_MENSAGEM || 'Status da NF: '  || cur_NFDEV.Status_Nf || chr(13) || chr(10);
    VAR_MENSAGEM:=VAR_MENSAGEM || 'Nr. Fornec.: '   || cur_NFDEV.Nrforn || chr(13) || chr(10);
    VAR_MENSAGEM:=VAR_MENSAGEM || 'Razao Social: '  || cur_NFDEV.Rsocialforn || chr(13) || chr(10);

    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, VAR_MENSAGEM || UTL_TCP.CRLF);
    VAR_MENSAGEM:='------------------------------------------------------------------';
    UTL_SMTP.WRITE_DATA(L_MAIL_CONN, VAR_MENSAGEM || UTL_TCP.CRLF);

    insert into controle_nf_dev (codintnf, numeronf, statunf, envio_email_abe, data_envio_emis)
    values (cur_NFDEV.codnf, cur_NFDEV.NUMERO, cur_NFDEV.Status_Nf, 'S', sysdate);
  END LOOP;
  end if;

  IF VAR_CONTROLE <> 1 THEN
    commit;
    
  l_reply := UTL_SMTP.CLOSE_DATA(L_MAIL_CONN);
  l_reply := UTL_SMTP.QUIT(L_MAIL_CONN);
  
  END IF;
结束;

我的ACL是这样设置的

ACL权限是 _ 授权反转
/sys/acl/utl_mail_monitor.xml连接真假
/sys/acls/utl_mail_monitor.xml使用-客户端-证书真假
/sys/acls/utl_mail_monitor.xml解决true false
/sys/acls/utl_mail_monitor.xml使用-密码真假

主机LOWER_PORT UPPER_PORT ACL
smtp.gmail.com 587 587 /sys/acls/utl_mail_monitor.xml
本地主机465 587 /sys/acls/utl_mail_monitor.xml


开始
DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL (
acl => 'utl_mail_monitor.xml '，
wallet_path => '文件:/oracle/ora11g/owm/钱包/');
提交;
结束;


我遵循了本页的指导，但没有成功https://github.com/YairPR/Oracle/wiki/Enviar-correo-con-Oracle:--ACL---UTL_SMTP---Wallet---SSL--TLS----Gmail

有人知道发生了什么吗？

让我们把它拨回一个小的测试用例。下面是MOS的标准演示脚本。您可以使用您的站点的适当参数对其进行测试吗 * 和 * 带有证书的全新钱包

DECLARE
  mailhost        VARCHAR2(64) := 'mailserverhostname';
  sender          VARCHAR2(64) := 'user@domainname';
  recipient       VARCHAR2(64) := 'user@domainname';
  wallet_pwd      VARCHAR2(64) := 'walletpassword';
  wallet_loc      VARCHAR2(64) := 'file:/etc/ORACLE/WALLETS/';
  user_name       VARCHAR2(64) := 'myemailusername';
  user_pwd        VARCHAR2(64) := 'myemailuserpassword';
  mail_connection utl_smtp.connection;
BEGIN

  -- Make a secure connection using the SSL port configured with your SMTP server
  -- Note: The sample code here uses the default of 465 but check your SMTP server settings

  mail_connection := utl_smtp.open_connection
  (
    host                          => mailhost,
    port                          => 465,
    wallet_path                   => wallet_loc,
    wallet_password               => wallet_pwd,
    secure_connection_before_smtp => TRUE
  );

  -- Call the Auth procedure to authorized a user for access to the mail server
  -- Schemes should be set appropriatelty for your mail server
  -- See the UTL_SMTP documentation for a list of constants and meanings
  UTL_SMTP.AUTH(
    c        => mail_connection,
    username => user_name,
    password => user_pwd,
    schemes  => 'LOGIN'
  );

  -- Set up and make the the basic smtp calls to send a test email
  utl_smtp.helo(mail_connection, mailhost);
  utl_smtp.mail(mail_connection, sender);
  utl_smtp.rcpt(mail_connection, recipient);
  utl_smtp.open_data(mail_connection);
  utl_smtp.write_data(mail_connection, 'This is a test message using SSL with SMTP.' || chr(13));
  utl_smtp.write_data(mail_connection, 'This test requires an Oracle Wallet be properly configured.' || chr(13));
  utl_smtp.close_data(mail_connection);
  utl_smtp.quit(mail_connection);
exception when others then
  dbms_output.put_line('Error in the anonymous plsql block: '|| sqlerrm);
end;
/

将结果作为评论发回，我们将从那里开始工作