A dica de hoje é simples e útil: Vou mostrar como criar uma Oracle Wallet com os certificados do SMTP do Gmail, permitindo o envio de e-mails pelo database.
Apesar de não recomendar, sempre existe o cliente teimoso que deseja usar o SMTP do Gmail no Database.
Criei um diretório para ser usado pela wallet, não utilize o diretorio e a wallet do database, se estiver utilizando Oracle RAC, crie um acfs ou um nfs e faça a montagem em todos os nodes:
[oracle@srv02 wallet_email]$ mkdir -p /backup/wallet_email/Baixei o certificado do Google: https://pki.goog/repository/
[oracle@srv02 wallet_email]$ wget https://i.pki.goog/r1.pem
--2025-05-07 20:41:33-- https://i.pki.goog/r1.pem
Resolving i.pki.goog (i.pki.goog)... 142.250.219.195, 2800:3f0:4001:807::2003
Connecting to i.pki.goog (i.pki.goog)|142.250.219.195|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1911 (1.9K) [application/x-pem-file]
Saving to: ‘r1.pem’
r1.pem 100%[=================================================>] 1.87K --.-KB/s in 0s
2025-05-07 20:41:34 (13.8 MB/s) - ‘r1.pem’ saved [1911/1911]
[oracle@srv02 wallet_email]$Criei a Wallet:
[oracle@srv02 wallet_email]$ orapki wallet create -wallet /backup/wallet_email -pwd Oracle123 -auto_login
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
[oracle@srv02 wallet_email]$
[oracle@srv02 wallet_email]$ ls -lart
total 12
-rw-r--r-- 1 oracle oinstall 1911 Aug 10 2023 r1.pem
drwxrwxr-x 9 oracle oinstall 115 Apr 24 15:39 ..
-rw------- 1 oracle oinstall 0 May 7 20:43 ewallet.p12.lck
-rw------- 1 oracle oinstall 225 May 7 20:43 ewallet.p12
-rw------- 1 oracle oinstall 0 May 7 20:43 cwallet.sso.lck
drwxrwxrwx 3 oracle oinstall 115 May 7 20:43 .
-rw------- 1 oracle oinstall 270 May 7 20:43 cwallet.ssoAdicionei o certificado r1.pem do Google na wallet:
[oracle@srv02 wallet_email]$ orapki wallet add -wallet /backup/wallet_email/ewallet.p12 -trusted_cert -cert r1.pem -pwd Oracle123
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Operation is successfully completed.
[oracle@srv02 wallet_email]$
[oracle@srv02 wallet_email]$ orapki wallet display -wallet /backup/wallet_email/ewallet.p12 -pwd Oracle123
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Trusted Certificates:
Subject: CN=GTS Root R1,O=Google Trust Services LLC,C=US
[oracle@srv02 wallet_email]$Criei a ACL para liberar o envio pelo SMTP no database para o usuário USR_01:
begin
dbms_network_acl_admin.create_acl (
acl => 'acl_gmail.xml',
description => 'Libera conexao ao GMAIL',
principal => 'USR_01',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
dbms_network_acl_admin.add_privilege (
acl => 'acl_gmail.xml',
principal => 'USR_01',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null
);
dbms_network_acl_admin.assign_acl (
acl => 'acl_gmail.xml',
host => '*.gmail.com',
lower_port => 587,
upper_port => 587
);
commit;
end;
/Testando o envio de email com o smtp do gmail, segue um exemplo de procedure que pode ser usado: https://glufke.net/oracle/viewtopic.php?t=10796
SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
begin
P_SEND_MAIL(TO_NAME => '[email protected]',
SUBJECT => 'Teste STMP',
MESSAGE => 'Teste STMP '||SYSDATE);
end;
/
Session altered.
SQL> 2 3 4 5 6
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
SQL>
Links das documentações:
https://docs.oracle.com/middleware/1213/wls/JDBCA/oraclewallet.htm
https://docs.oracle.com/cd/E92519_02/pt856pbr3/eng/pt/tsvt/concept_UnderstandingOracleWallet.html?pli=ul_d96e224_tsvt
https://docs.oracle.com/pt-br/iaas/autonomous-database-serverless/doc/external-calls-with-customer-managed-wallet.html