Call procedure inside another

Question

Call procedure inside another

Navigation

#1 by (1 votes)

-1

I need to create a procedure enviar_email_servidores that calls another pr_envia_email .

The procedure to be called ( pr_envia_email ) has the function of sending e-mail, and I need the message sent to this email to be the result of a query (eg send e-mail to all servers that make a birthday in the current month ).

Below the procedure code that sends email ( pr_envia_email )

create or replace PROCEDURE      pr_envia_email
(
Arg_Mensagem varchar2,
Arg_Email_Origem varchar2,
Arg_Email_Destino varchar2
)
is

mail_conn UTL_SMTP.connection;
mailhost varchar2(100);

begin
   begin
      select end_serv_email into mailhost
      from srh2.tab_tribunal;
   end;
   begin
      mail_conn := utl_smtp.open_connection(mailhost, 25);
      utl_smtp.helo(mail_conn, mailhost);
      utl_smtp.mail(mail_conn, Arg_Email_Origem);
      utl_smtp.rcpt(mail_conn, Arg_Email_Destino);
      utl_smtp.open_data(mail_conn);
      utl_smtp.write_data(mail_conn, 'Content-Type: text/html;charset="iso-8859-1' || utl_tcp.crlf  );
--      UTL_SMTP.write_data(mail_conn, Arg_Mensagem);
      UTL_SMTP.write_raw_data(mail_conn, utl_raw.cast_to_raw(Arg_Mensagem));
      UTL_SMTP.close_data(mail_conn);
      utl_smtp.quit(mail_conn);
   end;

end;

sql procedure stored-procedures oracle10g

asked by anonymous 14.04.2016 / 19:41

1 answer

preg_replace - url and line break how do I grab a line from a field that has no boundaries and break in array?

score 1 · Answer 1

create or replace PROCEDURE PP_ENVIAR_EMAIL is 
CURSOR anv IS
SELECT distinct  mat_servidor, nom, dt_nasc, e_mail
 FROM srh2.servidor
WHERE MONTHNAME(DT_NASC) = MONTHNAME(SYSDATE);

SELECT distinct  mat_servidor, nom, dt_nasc, e_mail
FROM srh2.servidor
WHERE MONTHNAME(DT_NASC) = MONTHNAME(SYSDATE);

nome VARCHAR2(100);
msg VARCHAR2(600);
begin
 FOR Cnome in anv loop

msg := 'Servidor '|| cnome.nom||', por favor entre em contato para realizar o seu periódico.';
SRH2.PR_ENVIA_EMAIL(msg, 'e_mail_origem', 'e_mail_destino');

end loop;
INSERT INTO PERIODICO(mat_servidor, nom, dt_nasc, e_mail, data_email) SELECT mat_servidor, nom, dt_nasc, e_mail, SYSDATE FROM SRH2.SERVIDOR; -- Salvar as pessoas em uma outra tabela

END PP_ENVIAR_EMAIL;