PL/SQL procedure sending email with variables

Question

I've written a procedure in oracle that will send an email. I would like to include variables in the body of the message that should refer to the variables calculated in another procedure.

As an example let's say I have procedure:

select a1+a2
into a
from table c;

And the next step would be to send an email, including a, like:

create or replace PROCEDURE MAIL AS 
BEGIN
DECLARE
  l_html VARCHAR2(32767);
BEGIN
  l_html := '<html>
<body>
  <p>a</p>  <!-- here I'd like my result from another procedure to appear-->
 </body>
  </html>';
    utl_mail.send(sender => 'something@bla.com',
                                 recipients => 'bla@bla.com',
                                 message => l_html,               
                                 mime_type => 'text/html'
                                 );

The code is oversimplified to show the general idea, what I am looking for is how to make a connection between two procedures like these. Since the first procedure is quite huge, including sending an mail within the same procedure is not an option. Thanks in advance for any tips/ideas!

Post some code please, this way it's quite impossible to answer. However, whatever your code is, global variables are never a good solution for me. — Aleksej, May 03 '16 at 14:16
Some reasons [here](http://programmers.stackexchange.com/questions/148108/why-is-global-state-so-evil) — Aleksej, May 03 '16 at 15:12

Aleksej · Answer 1 · 2016-05-03T15:46:15.030

You can simply call a different function / procedure within your mail procedure; for example:

/* define a function that computes some needed result */
create or replace function someFunction return varchar2 is
    retVal  varchar2(1000);
begin
    select 'my content'
    into retVal
    from dual;        
    return retVal;
end;                  
create or replace procedure MAIL as
    html varchar2(32767);
    variableContent varchar2(1000);    
begin
    /* use your function to get your variable content */
    variableContent := someFunction();
    html := '
            <html>
              <body>
                <p>' || variableContent || '</p>
              </body>
            </html>';
    dbms_output.put_line(html);
    /*
    utl_mail.send(sender => 'something@bla.com',
                  recipients => 'bla@bla.com',
                  message => html,               
                  mime_type => 'text/html'
                  );
    */
end;

You can call it this way:

SQL> exec MAIL;

            <html>
              <body>
                <p>my content</p>

</body>
            </html>

PL/SQL procedure successfully completed.

This is a very simple example; the interface of the function/procedure to call depends on your code, you only have to edit your procedure so that it can be called and return the needed result.

But now the two functions/procedures need to be implemented in a single procedure, or am I wrong? — Janna Sherazi, May 03 '16 at 15:40
No need for that; you can use a package to contain them both, but it's not strictly necessary; the example I posted is somehow "working" as is, just edited to show the procedure call — Aleksej, May 03 '16 at 15:43

PL/SQL procedure sending email with variables

1 Answers1