How to send SQL script results as a table in email body via Automic

Question

I have a workflow which looks like below.

The first task of this workflow runs a simple select * query and the next one sends an email. They are working fine individually. What I want is to send the output of the SQL task as an input to the email task so that it can be attached to the email being sent.

I have tried to manually enter the runId of the SQL task in below field of notification object and it works as expected. But how to make this field take dynamic value from its predecessor instead of a hardcoded one?

Also, is there a way I can include the output of the select * in the email body as a table ?

Update --1

I was able to get a hold of runId of preceding task via the below script. Now only need help with including it in the mail body as opposed to attachement.

:SET &NR# = SYS_ACT_PREV_NR()
:PRINT "RunID of the previous task is &NR#."

Answer 1

Firstly;

Setup

The package is loaded by running the following scripts.

sys/passwordord AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb
In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;

-- Instance restart only necessary in 10gR1.

SHUTDOWN IMMEDIATE

STARTUP

I would suggest you use a mail relay on the database server, rather than connecting directly to an external mail server. The mail relay configuration can be simple, with a reference to "localhost" in the SMTP_OUT_SERVER parameter. Any complexities about connecting to your external mail server are then hidden in the mail relay configuration.

Send Emails With the configuration complete we can now send a mail using the SEND procedure. It accepts the following parameters.

SENDER : This should be a valid email address.
RECIPIENTS : A comma-separated list of email addresses.
CC : An optional comma-separated list of email addresses.
BCC : An optional comma-separated list of email addresses.
SUBJECT : The subject line for the email.
MESSAGE : The email body.
MIME_TYPE : Set to 'text/plain; charset=us-ascii' by default.
PRIORITY : (1-5) Set to 3 by default.
REPLYTO : Introduced in 11gR2. A valid email address.

Below is an example of the usage.

BEGIN
  UTL_MAIL.send(sender     => '[email protected]',
                recipients => '[email protected],[email protected]',
                cc         => '[email protected]',
                bcc        => '[email protected]',
                subject    => 'UTL_MAIL Test',
                message    => 'If you get this message it worked!');
END;
/

Send Emails with Attachments

The package also supports sending mails with RAW and VARCHAR2 attachments using the SEND_ATTACH_RAW and SEND_ATTACH_VARCHAR2 packages respectively. They work in a similar fashion to the SEND procedure, with a few extra parameters.

ATTACHMENT : The contents of the attachment. This should be VARCHAR2 or RAW depending on which procedure you call.

ATT_INLINE : Indicates if the attachment should be readable inline. Default FALSE.
ATT_MIME_TYPE : The default is 'text/plain; charset=us-ascii'.
ATT_FILENAME : The name for the attachment.

Below is an example of sending an email with a text attachment.

BEGIN
  UTL_MAIL.send_attach_varchar2 (
    sender       => '[email protected]',
    recipients   => '[email protected],[email protected]',
    cc           => '[email protected]',
    bcc          => '[email protected]',
    subject      => 'UTL_MAIL Test',
    message      => 'If you get this message it worked!',
    attachment   => 'The is the contents of the attachment.',
    att_filename => 'my_attachment.txt'    
  );
END;

/