Call Procedure inside another

Question

I need to create a precedent enviar_email_servidores call in another pr_envia_email.

The term to be called (pr_envia_email) has the function of sending email, and I need the message sent to this email is the result of a query (ex: send email to all servers that do birthday in the current month).

Below the code of the Procedure sending 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;

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;