Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Fabiana.

Asked: May 13, 2021 - 7:30 pm UTC

Last updated: May 14, 2021 - 6:47 am UTC

Version: 11

Viewed 10K+ times! This question is

You Asked

I have the script below that was working normally, however, I believe, that the gmail certificate has expired.
With OpenSSL> s_client -connect smtp.gmail.com/1687 -starttls smtp, I generated another certificate for my wallet and updated the ACLs, but still it still has an error to send on the l_reply line : = sys.utl_smtp.starttls (L_MAIL_CONN ); and ORA-28759: ora-28759 failure to open file, by if I get command bellow, it's work.
select UTL_HTTP.request(url => ' http://www.google.com',
wallet_path => 'file:/oracle/ora11g/owm/wallets/',
wallet_password => 'xxxxxxxxx') from dual;



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;

BEGIN

  VAR_CONTROLE:=1;

  BEGIN
   
   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;
END;


my ACL is set up this way

ACL PRIVILEGE IS_GRANT INVERT
/sys/acls/utl_mail_monitor.xml Connect true false
/sys/acls/utl_mail_monitor.xml use-client-certificates true false
/sys/acls/utl_mail_monitor.xml resolve true false
/sys/acls/utl_mail_monitor.xml use-passwords true false

HOST LOWER_PORT UPPER_PORT ACL
smtp.gmail.com 587 587 /sys/acls/utl_mail_monitor.xml
localhost 465 587 /sys/acls/utl_mail_monitor.xml


BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
acl => 'utl_mail_monitor.xml',
wallet_path => 'file:/oracle/ora11g/owm/wallets/');
COMMIT;
END;


I followed the guidance of this page, but without success https://github.com/YairPR/Oracle/wiki/Enviar-correo-con-Oracle:--ACL---UTL_SMTP---Wallet---SSL--TLS----Gmail

would anyone have any idea what's going on?

and Connor said...

Lets dial it back to a small test case. Below is the standard demo script from MOS. Can you test it with appropriate parameters for your site *and* a brand new wallet with the certs

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;
/


Post back the results as a comment and we'll work from there

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

error continues

Fabiana dos Santos Silva, May 14, 2021 - 8:16 pm UTC

with the script above I changed my ACLs and redid my wallet
ORA-24247:(access control list)

select * from V$PARAMETER where NAME = 'smtp_out_server';
NAME smtp_out_server varchar2(80), optional
TYPE 2 number, optional
VALUE smtp.gmail.com:465 varchar2(4000), optional
DISPLAY_VALUE smtp.gmail.com:465 varchar2(4000), optional
ISDEFAULT FALSE varchar2(9), optional

SELECT * FROM resource_view WHERE any_path like '/sys/acls/utl_mail_monitor.xml';

<Resource xmlns=" http://xmlns.oracle.com/xdb/XDBResource.xsd" >
<CreationDate>2021-05-07T18:22:16.215000</CreationDate>
<ModificationDate>2021-05-07T18:24:53.219000</ModificationDate>
<DisplayName>utl_mail_monitor.xml</DisplayName>
<Language>en-US</Language>
<CharacterSet>ISO-8859-15</CharacterSet>
<ContentType>application/vnd.oracle-csx</ContentType>
<RefCount>1</RefCount>
</Resource>

BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL(
acl => 'utl_mail_monitor.xml',
wallet_path => 'file:/oracle/ora11g/owm/wallets');
COMMIT;
END;
/

error continues

Fabiana dos Santos Silva, May 14, 2021 - 8:17 pm UTC


SELECT * FROM dba_network_acls;
HOST, LOWER_PORT, UPPER_PORT, ACL, ACLID
localhost 587 587 utl_mail_monitor.xml
localhost utl_mail_monitor.xml
smtp.gmail.com 465 465 utl_mail_monitor.xml
smtp.gmail.com 587 587 utl_mail_monitor.xml
localhost 465 465 utl_mail_monitor.xml

select utl_http.request(' http://www.tiger.com' ) from dual; // it's ok


<a:acl description="Permissions to access SMTP Server" xmlns:a=" http://xmlns.oracle.com/xdb/acl.xsd" xmlns:plsql=" http://xmlns.oracle.com/plsql" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation=" http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
<a:security-class>plsql:network</a:security-class>
<a:ace>
<a:grant>true</a:grant>
<a:principal>GBU</a:principal>
<a:privilege>
<plsql:connect xmlns:plsql=" http://xmlns.oracle.com/plsql"/ >
<plsql:resolve xmlns:plsql=" http://xmlns.oracle.com/plsql"/ >
<plsql:use-client-certificates xmlns:plsql=" http://xmlns.oracle.com/plsql"/ >
<plsql:use-passwords xmlns:plsql=" http://xmlns.oracle.com/plsql"/ >
</a:privilege>
</a:ace>
</a:acl>


file sqlnet.ora
SQLNET.AUTHENTICATION_SERVICES = (NTS)


I found this topic about wallet regarding alter sqlnet.ora https://www.morganslibrary.org/reference/wallet.html, do I have to do the same?

and for the wallet certificate I used openssl s_client -connect smtp.gmail.com:465 -crlf -ign_eof
I don't know what else to do, as I have this problem since 04/25/2021 and I couldn't find a solution.

Please help me.

the error still continues

Fabiana Silva, May 17, 2021 - 8:29 pm UTC

I executed in openssl s_client -host SMTP.GMAIL.COM -port 465 -prexit -showcerts

then the error came back as it was at the beginning of the post:

Error in the anonymous plsql block: ora-28759 failure to open file

Please, help me

Fabiana Silva, May 21, 2021 - 12:31 pm UTC


I need help

SOLVED

Fabiana Silva, May 21, 2021 - 7:14 pm UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library