Send Database Mail in HTML Table Format from Oracle

As a fishing trip approaches, I like to plan out my expected strategy for my first day. I look at the expected weather (especially wind direction and speed), and try to decide where active fish may be biting. However, once I get out there plans can change very quickly. I remember a trip up north quite a few years ago where, once out on the water, I was facing 25 – 30 mph winds. I was also fishing in a rowboat, and was unable to fish where I wanted because I was drifting through the prime areas too quickly. I had to make an adjustment and focus on spots on the upwind side of the lake.
Database administrators also need to be able to make adjustments. It is fairly common to try something one way and find it doesn’t work well or doesn’t work as expected, and have to find another way. In my last post, I demonstrated how to use ListAgg to make a common delimited list in Oracle. I had planned to use this function to build a stored procedure that would take a query as its parameters and send an email in an html table format with the results of that query. Upon building the query, I found ListAgg has a severe limitation. It returns its results in a VARCHAR2 format only. This means the result set, which is a string of html, could only use 4000 characters, the limit of VARCHAR2. This may seem like a lot, but for an entire result set along with html tags it may not be sufficient. Instead, I used some of Oracle’s XML functions to complete the procedure. First I’ll show the procedure and a sample of its use, then I’ll do a brief description of the xml functions used.
Here is the SQL for the stored procedure:

CREATE OR REPLACE PROCEDURE       SCOTT.SENDDBEMAILWITHTABULARQUERY 
(
  QSELECT IN VARCHAR2
, FIELDLIST IN VARCHAR2
, QFROM IN VARCHAR2
, QWHERE IN VARCHAR2 
, QGROUPBY IN VARCHAR2 
, QHAVING IN VARCHAR2 
, QORDERBY IN VARCHAR2 
, RECIPIENTS IN VARCHAR2 
, SUBJECT IN VARCHAR2 
, TITLE IN VARCHAR2 
, SENDEMAILWITHNORESULTS IN VARCHAR2 
) AS 

  XML CLOB;
  body CLOB;
  varsql      VARCHAR2(4000);
  tblfieldheader NVARCHAR2(2000) := '';
  tempfield NVARCHAR2(2000)      := '';
  i          INT                          := 1;
  j          INT                          := 1;
  SendEmail  INT;
  splitcnt   INT;
  fieldcount INT ;
TYPE AllFieldNames
IS
  TABLE OF VARCHAR2(2000);
  TempFields AllFieldNames;
BEGIN
  --Find the number of fields in the query
  splitcnt := LENGTH(fieldlist)-LENGTH(REPLACE(fieldlist,'|',''));
  --Loop through the fields and put each on into the #Fields temp table as a new record
  FOR j IN 1..splitcnt
  LOOP
    SELECT x.s BULK COLLECT
    INTO TempFields
    FROM
      (SELECT regexp_substr (fieldlist, '[^|]+', 1, rownum) s,
        rownum rn
      FROM dual
        CONNECT BY LEVEL <= LENGTH (regexp_replace (fieldlist, '[^|]+')) + 1
      ) x;
  END LOOP;
  --SELECT fieldcount = splitcnt + 1 --Will be the splitcnt + 1, otherwise MAX(ID) FROM TempFields
  --Start setting up the sql statement for the query.
  varsql := qSELECT || ' xmlagg(xmlelement("tr",xmlforest(';
  --Loop through the #Fields table to get the field list
  FOR i IN TempFields.first..TempFields.last
  LOOP
    --------------------------------------------------------------------------------------------------------------------------------------------------------------
    --This next section is required in case a field is aliased.  For the xml, we need to get rid of the aliases, the table header will only require the aliases.
    --NULL values need to be shown as a string = 'NULL' or the html table will just skip the cell and all values after that in the row will be shifted left.
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------
    IF SUBSTR(TempFields(i),-1) = ']' OR INSTR(UPPER(TempFields(i)),' AS ') = 0 THEN
      --Set the xml field to be the entire field name
      varsql := varsql || 'NVL(CAST(' || TempFields(i) || ' AS VARCHAR2(2000)),''NULL'') as "td", ';
      --Set the table header field to be the entire field name
      tblfieldheader := tblfieldheader || '<th>' || TempFields(i) || '</th>';
    ELSE
      --Set the xml field to be the field name minus the alias
      varsql := varsql || 'NVL(CAST(' || SUBSTR(TempFields(i),1,(INSTR(UPPER(TempFields(i)),' AS ',1))-1) || ' AS VARCHAR2(2000)),''NULL'') as "td", ';
      --Set the table header field to be the field name's alias
      tblfieldheader := tblfieldheader || '<th>' || SUBSTR(TempFields(i),INSTR(UPPER(TempFields(i)),' AS ',-1)+4) || '</th>';
    END IF;
    --Increment the counter.
  END LOOP;
  --Trim the extra two characters of the end of sql.
  varsql := SUBSTR(varsql,1, LENGTH(varsql)-2);
  --Add the end of the table tag
  varsql := varsql || '))).GetClobVal() ';
  --Add the from, where, group by, having, and order by clause to the select statement.
  varsql := varsql || qFROM || ' ' || qWHERE || ' ' || qGroupBy || ' ' || qHaving || ' ' || qOrderBy;
  --Run the sql that will create the xml.
  EXECUTE IMMEDIATE varsql INTO XML;
  --Capture whether or not any rows were returned
  IF LENGTH(XML) > 0 THEN
    --Create the body of the email, which contains the xml results of the query.
    body := to_clob('<html><body><H3>') || Title || '</H3><table border = 1><tr>' || tblfieldheader || '</tr>' || XML || '</table></body></html>';
    --If rows were returned, send the email.
    sys.utl_mail.SEND( 'Oracledbmail@domain.com', recipients,NULL, NULL, subject , boddy, 'text/html', 3);
    --If no results, check whether user wants an email anyway
  ELSIF SendEmailWithNoResults != '0' THEN
    sys.utl_mail.SEND( 'Oracledbmail@domain.com', recipients,NULL, NULL, subject, 'Query returned no results', 'text/html', 3);
  END IF;
END SENDDBEMAILWITHTABULARQUERY;

To call the stored procedure, we’d use this syntax:

DECLARE
  QSELECT VARCHAR2(200);
  FIELDLIST VARCHAR2(200);
  QFROM VARCHAR2(200);
  QWHERE VARCHAR2(200);
  QGROUPBY VARCHAR2(200);
  QHAVING VARCHAR2(200);
  QORDERBY VARCHAR2(200);
  RECIPIENTS VARCHAR2(200);
  SUBJECT VARCHAR2(200);
  TITLE VARCHAR2(200);
  SENDEMAILWITHNORESULTS VARCHAR2(200);
BEGIN
  QSELECT := 'SELECT';
  FIELDLIST := 'emp.ename AS Employee|emp.job|emp.sal as Salary|dept.dname AS Department|dept.loc';
  QFROM := 'FROM scott.emp
            INNER JOIN scott.dept
            ON emp.deptno = dept.deptno';
  QWHERE := 'WHERE emp.sal > 1000';
  QGROUPBY := NULL;
  QHAVING := NULL;
  QORDERBY := NULL;
  RECIPIENTS := 'dgugg@domain.com';
  SUBJECT := 'Scott''s Employees';
  TITLE := 'Salary Greater Than 1,000';
  SENDEMAILWITHNORESULTS := '1';

  Scott.SENDDBEMAILWITHTABULARQUERY(
    QSELECT => QSELECT,
    FIELDLIST => FIELDLIST,
    QFROM => QFROM,
    QWHERE => QWHERE,
    QGROUPBY => QGROUPBY,
    QHAVING => QHAVING,
    QORDERBY => QORDERBY,
    RECIPIENTS => RECIPIENTS,
    SUBJECT => SUBJECT,
    TITLE => TITLE,
    SENDEMAILWITHNORESULTS => SENDEMAILWITHNORESULTS
  );
END;

Here’s how the resulting email looks:

A couple of things to be aware of:

• You first need to configure database mail before you can send email from the database.  You can do this in Database/Enterprise manager by going to Setup -> Notification Methods

• You need to setup a Access Control List for your user to get to the email server.  This can be done with these SQL statements, run as SYS.

--Create a new ACL
BEGIN
  --Creat the list
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => 'utl_mail.xml',
    description => 'utl mail for Scott',
    principal   => 'Scott',
    is_grant    => true,
    privilege   => 'connect',
    start_date  => NULL,
    end_date    => NULL);
  --Add an initial privilege to it
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
    acl         => 'utl_mail.xml',
    principal   => 'Scott',
    is_grant    => true,
    privilege   => 'connect',
    start_date  => NULL,
    end_date    => NULL);
  --Assign a host to the ACL
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl        => 'utl_mail.xml',
    host       => 'hostname',
    lower_port => 25,
    upper_port => 25);
END;
/
COMMIT;

Without this you will get the following error:

Now I’ll briefly touch on the functionality of the XML functions.

1. xmlagg – This takes multiple rows of xml and changes it into a single row.  This is needed for this stored procedure because we want the html table to control the line breaks, not line break characters created by SQL.
2. xmlelement – This simply turns a returned column into an xml element.  The first argument is the xml element name and the second argument is the field.
3. xmlforest – This behaves similarly to xmlelement, but it lets you do multiple columns at the same time.  You use comma to separate the columns.  Confusingly, opposite from xmlelement you have to first specify the field, than you put the xml element name second.

When I first got the requirement to send emails from Oracle, I took a look around the web and couldn’t find any ready-made solutions for sending out query results in an html table.  Hopefully this post will help some of you who are looking for the same thing.