SendMail PL/SQL Gmail

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
Avatar do usuário
fabissilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Seg, 09 Set 2019 4:29 pm
Localização: Sorocaba - SP
Fabi
INSERT INTO possivel VALUES ('Nada é impossível para aquele que persiste');

desenvolvi em PL / SQL uma procedure que envia email que está funcionando perfeitamente. Porém foi mudado o servidor de email para Gmail, que requer autenticação e o tipo de conexão é SSL. Poderiam me ajudar com essas alterações?
A versão do banco de dados é o 11.

Selecionar tudo

CREATE OR REPLACE PROCEDURE PRC_JUN_SEND_MAIL
(
TO_NAME VARCHAR2,
SUBJECT VARCHAR2,
MESSAGE VARCHAR2
)
IS
L_BODY VARCHAR2(32767);

L_MAILHOST VARCHAR2(64) := ''smtp.gmail.com'';
P_USERNAME_ VARCHAR2(50) := ''admin@jun.net'';
P_PASSWORD_ VARCHAR2(50) := ''***********'';
L_DE VARCHAR2(64) := ''Remetente'';
L_FROM VARCHAR2(64) := ''admin@jun.net'';
l_port VARCHAR(7) := ''587'';
L_MAIL_CONN UTL_SMTP.CONNECTION;


BEGIN
L_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(L_MAILHOST,l_port);
Dbms_Output.Put_Line (''apos conectar'');
UTL_SMTP.HELO(L_MAIL_CONN, L_MAILHOST);

Dbms_Output.Put_Line (''hello'');
UTL_SMTP.COMMAND (L_MAIL_CONN, ''AUTH LOGIN'');
Dbms_Output.Put_Line (''AUTH LOGIN'');
UTL_SMTP.COMMAND (L_MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2(
UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_USERNAME_))));
UTL_SMTP.COMMAND (L_MAIL_CONN, UTL_RAW.CAST_TO_VARCHAR2( UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(P_PASSWORD_))));
UTL_SMTP.MAIL(L_MAIL_CONN, L_FROM);
UTL_SMTP.RCPT(L_MAIL_CONN, TO_NAME);

UTL_SMTP.OPEN_DATA(L_MAIL_CONN);

UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(''FROM:'' ||L_DE||''<''|| L_FROM|| ''>'' || UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(''TO:'' ||TO_NAME||UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(''SUBJECT:'' ||SUBJECT||UTL_TCP.CRLF));
UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(''CONTENT-TYPE: TEXT/HTML; CHARSET=ISO-8859-1''||UTL_TCP.CRLF));
UTL_SMTP.WRITE_DATA(L_MAIL_CONN, '' ''||UTL_TCP.CRLF);

UTL_SMTP.WRITE_RAW_DATA(L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(UTL_TCP.CRLF||MESSAGE));

UTL_SMTP.CLOSE_DATA(L_MAIL_CONN);

UTL_SMTP.QUIT(L_MAIL_CONN);
END;
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Acho que você pode testar esse exemplo:

Antes, criar o ACL.

Selecionar tudo

-- create acl
begin
        dbms_network_acl_admin.create_acl (
                acl             => 'gmail.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
end;
/
-- add priviliege to acl
begin
  dbms_network_acl_admin.add_privilege ( 
  acl       => 'gmail.xml',
  principal    => '<YOUR SCHEMA USER>',
  is_grant    => TRUE, 
  privilege    => 'connect', 
  start_date    => null, 
  end_date    => null); 
end;
/
-- assign host, port to acl
begin
  dbms_network_acl_admin.assign_acl (
  acl => 'gmail.xml',
  host => 'localhost',
  lower_port => 1925,
  upper_port => 1925);
end;
/
Package:

Selecionar tudo

create or replace package apex_mail_p
is
   g_smtp_host      varchar2 (256)     := 'localhost';
   g_smtp_port      pls_integer        := 1925;
   g_smtp_domain    varchar2 (256)     := 'gmail.com';
   g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
   -- send mail using UTL_SMTP
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   );
end;
/
create or replace package body apex_mail_p
is
   -- Write a MIME header
   procedure write_mime_header (
      p_conn in out nocopy utl_smtp.connection
    , p_name in varchar2
    , p_value in varchar2
   )
   is
   begin
      utl_smtp.write_data ( p_conn
                          , p_name || ': ' || p_value || utl_tcp.crlf
      );
   end;
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   )
   is
      l_conn           utl_smtp.connection;
      nls_charset    varchar2(255);
   begin
      -- get characterset
      select value
      into   nls_charset
      from   nls_database_parameters
      where  parameter = 'NLS_CHARACTERSET';
      -- establish connection and autheticate
      l_conn   := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
      utl_smtp.ehlo(l_conn, g_smtp_domain);  
      utl_smtp.command(l_conn, 'auth login');
      utl_smtp.command(l_conn,utl_encode.text_encode('<your gmail account including @gmail.com>', nls_charset, 1));
      utl_smtp.command(l_conn, utl_encode.text_encode('<your gmail account password>', nls_charset, 1));
      -- set from/recipient
      utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
      utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
      -- write mime headers
      utl_smtp.open_data (l_conn);
      write_mime_header (l_conn, 'From', p_sender);
      write_mime_header (l_conn, 'To', p_recipient);
      write_mime_header (l_conn, 'Subject', p_subject);
      write_mime_header (l_conn, 'Content-Type', 'text/plain');
      write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
      utl_smtp.write_data (l_conn, utl_tcp.crlf);
      -- write message body
      utl_smtp.write_data (l_conn, p_message);
      utl_smtp.close_data (l_conn);
      -- end connection
      utl_smtp.quit (l_conn);
   exception
      when others
      then
         begin
           utl_smtp.quit(l_conn);
         exception
           when others then
             null;
         end;
         raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);   
   end;
end;
/
Exemplo:

Selecionar tudo

begin
   apex_mail_p.mail('<your gmail address>', '<recipient address>', '<Subject>', '<message body>');
end;
/
Este exemplo veio daqui: http://monkeyonoracle.blogspot.com/2009 ... h-ssl.html
Ali cita que você precisa instalar um utilitário chamado STUNNEL.
Installing and configuring Stunnel
Go to stunnel.org and download the latest Windows binaries
Install Stunnel (take note of the installation path), in my example it is c:\stunnel
Edit the file stunnel.conf located in installation folder to (just backup the original, and replace all the original text with the text below):
; Use it for client mode
client = yes

[ssmtp]
accept = 1925
connect = smtp.gmail.com:465
Here I use port 1925 on my localhost (unused as far as I know) to connect to smtp.gmail.com.

Start Stunnel.exe, and test the configuration:
Start cmd
Write: telnet localhost 1925
You should then see something like "220 mx.google.com ESMTP 5sm18031572eyh.34"
Write: quit

Outra fonte que também cita a mesma solução se encontra aqui:
http://glufke.net/oracle/viewtopic.php?f=2&t=5599
Avatar do usuário
fabissilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Seg, 09 Set 2019 4:29 pm
Localização: Sorocaba - SP
Fabi
INSERT INTO possivel VALUES ('Nada é impossível para aquele que persiste');

Retornando...

Consegui resolver... 8) inserindo a informação wallet no oracle

obs. é necessário inserir no wallet o certificado do gmail

Selecionar tudo

CREATE OR REPLACE PROCEDURE PRC_JUN_SEND_MAIL
(
TO_NAME VARCHAR2,
SUBJECT VARCHAR2,
MESSAGE VARCHAR2
)
IS
  L_BODY VARCHAR2(32767);
  L_MAILHOST        VARCHAR2(64) := '74.125.196.108'; --IP Google / gmail
  P_USERNAME_       VARCHAR2(50) := 'email@gmail.net';
  P_PASSWORD_       VARCHAR2(50) := '*********';
  L_DE              VARCHAR2(64) := 'Remetente';
  L_FROM            VARCHAR2(64) := 'email@gmail.net';
  k_wallet_path     CONSTANT VARCHAR2(100) := 'file:/oracle/ora11g/owm/wallets';
  k_wallet_password CONSTANT VARCHAR2(100) := '*********';
  k_domain          CONSTANT VARCHAR2(100) := 'localhost';
  l_port            INTEGER       := 587;
  L_MAIL_CONN       UTL_SMTP.CONNECTION;
  l_reply           utl_smtp.reply;
  l_replies         utl_smtp.replies;

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

     
  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   := UTL_SMTP.MAIL(L_MAIL_CONN, L_FROM);
  l_reply   := UTL_SMTP.RCPT(L_MAIL_CONN, TO_NAME);
  l_reply   := UTL_SMTP.OPEN_DATA(L_MAIL_CONN);

  UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW('FROM:'    ||L_DE||'<'|| L_FROM|| '>' || UTL_TCP.CRLF));
  UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW('TO:'      ||TO_NAME||UTL_TCP.CRLF));
  UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW('SUBJECT:' ||SUBJECT||UTL_TCP.CRLF));
  UTL_SMTP.WRITE_RAW_DATA( L_MAIL_CONN, UTL_RAW.CAST_TO_RAW('CONTENT-TYPE: TEXT/HTML; CHARSET=ISO-8859-1'||UTL_TCP.CRLF));
  UTL_SMTP.WRITE_DATA(L_MAIL_CONN, ' '||UTL_TCP.CRLF);

  UTL_SMTP.WRITE_RAW_DATA(L_MAIL_CONN, UTL_RAW.CAST_TO_RAW(UTL_TCP.CRLF||MESSAGE));
  l_reply := UTL_SMTP.CLOSE_DATA(L_MAIL_CONN);
  l_reply := UTL_SMTP.QUIT(L_MAIL_CONN);
end;
Desta forma consegui enviar email normalmente
Avatar do usuário
fabissilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Seg, 09 Set 2019 4:29 pm
Localização: Sorocaba - SP
Fabi
INSERT INTO possivel VALUES ('Nada é impossível para aquele que persiste');

boa tarde,

Por favor preciso de ajuda.

abri um tópico também em https://asktom.oracle.com/pls/apex/askt ... 4598696548
Avatar do usuário
fabissilva
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 19
Registrado em: Seg, 09 Set 2019 4:29 pm
Localização: Sorocaba - SP
Fabi
INSERT INTO possivel VALUES ('Nada é impossível para aquele que persiste');

Obrigada, deu certo!!!!

dr_gori escreveu:
Ter, 10 Set 2019 12:42 pm
Acho que você pode testar esse exemplo:

Antes, criar o ACL.

Selecionar tudo

-- create acl
begin
        dbms_network_acl_admin.create_acl (
                acl             => 'gmail.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
end;
/
-- add priviliege to acl
begin
  dbms_network_acl_admin.add_privilege ( 
  acl       => 'gmail.xml',
  principal    => '<YOUR SCHEMA USER>',
  is_grant    => TRUE, 
  privilege    => 'connect', 
  start_date    => null, 
  end_date    => null); 
end;
/
-- assign host, port to acl
begin
  dbms_network_acl_admin.assign_acl (
  acl => 'gmail.xml',
  host => 'localhost',
  lower_port => 1925,
  upper_port => 1925);
end;
/
Package:

Selecionar tudo

create or replace package apex_mail_p
is
   g_smtp_host      varchar2 (256)     := 'localhost';
   g_smtp_port      pls_integer        := 1925;
   g_smtp_domain    varchar2 (256)     := 'gmail.com';
   g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
   -- send mail using UTL_SMTP
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   );
end;
/
create or replace package body apex_mail_p
is
   -- Write a MIME header
   procedure write_mime_header (
      p_conn in out nocopy utl_smtp.connection
    , p_name in varchar2
    , p_value in varchar2
   )
   is
   begin
      utl_smtp.write_data ( p_conn
                          , p_name || ': ' || p_value || utl_tcp.crlf
      );
   end;
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   )
   is
      l_conn           utl_smtp.connection;
      nls_charset    varchar2(255);
   begin
      -- get characterset
      select value
      into   nls_charset
      from   nls_database_parameters
      where  parameter = 'NLS_CHARACTERSET';
      -- establish connection and autheticate
      l_conn   := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
      utl_smtp.ehlo(l_conn, g_smtp_domain);  
      utl_smtp.command(l_conn, 'auth login');
      utl_smtp.command(l_conn,utl_encode.text_encode('<your gmail account including @gmail.com>', nls_charset, 1));
      utl_smtp.command(l_conn, utl_encode.text_encode('<your gmail account password>', nls_charset, 1));
      -- set from/recipient
      utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
      utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
      -- write mime headers
      utl_smtp.open_data (l_conn);
      write_mime_header (l_conn, 'From', p_sender);
      write_mime_header (l_conn, 'To', p_recipient);
      write_mime_header (l_conn, 'Subject', p_subject);
      write_mime_header (l_conn, 'Content-Type', 'text/plain');
      write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
      utl_smtp.write_data (l_conn, utl_tcp.crlf);
      -- write message body
      utl_smtp.write_data (l_conn, p_message);
      utl_smtp.close_data (l_conn);
      -- end connection
      utl_smtp.quit (l_conn);
   exception
      when others
      then
         begin
           utl_smtp.quit(l_conn);
         exception
           when others then
             null;
         end;
         raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);   
   end;
end;
/
Exemplo:

Selecionar tudo

begin
   apex_mail_p.mail('<your gmail address>', '<recipient address>', '<Subject>', '<message body>');
end;
/
Este exemplo veio daqui: http://monkeyonoracle.blogspot.com/2009 ... h-ssl.html
Ali cita que você precisa instalar um utilitário chamado STUNNEL.
Installing and configuring Stunnel
Go to stunnel.org and download the latest Windows binaries
Install Stunnel (take note of the installation path), in my example it is c:\stunnel
Edit the file stunnel.conf located in installation folder to (just backup the original, and replace all the original text with the text below):
; Use it for client mode
client = yes

[ssmtp]
accept = 1925
connect = smtp.gmail.com:465
Here I use port 1925 on my localhost (unused as far as I know) to connect to smtp.gmail.com.

Start Stunnel.exe, and test the configuration:
Start cmd
Write: telnet localhost 1925
You should then see something like "220 mx.google.com ESMTP 5sm18031572eyh.34"
Write: quit

Outra fonte que também cita a mesma solução se encontra aqui:
http://glufke.net/oracle/viewtopic.php?f=2&t=5599
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Google Adsense [Bot] e 7 visitantes