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:
20150209 SendHTML Received Mail
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

20150209 SendHTML Setup DBMail

  • 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:
20150209 SendHTML DBMail ACL

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.

Advertisements

Send Database Mail in HTML Table Format from SQL Server

I gave a sharp tug on my rod, jerking the Bulldawg into the edge of my field of vision. It was still about 15 feet from the boat, and the morning sun reflecting off the water left nothing in view but a silloutte of the lure hoovering a few feet below the glassy surface. I paused a moment before giving another hard jerk, and this time when the lure stopped I felt a hard tug on the end of my pole. I quickly looked over to see the muskie that had chomped down on my lure, still holding the soft rubber in its mouth. I yanked hard to drive the hooks into the hard boney mouth, but I ended up coming up empty. The fish spit the lure and swam off. I was disappointed to have missed the opportunity to fight with the big fish, but I was grateful to have even seen him. I wouldn’t have made contact with the fish were it not for my attention to presentation. The muskie hit out of reaction, not because it was hungry. I was able to elicit the reaction strike by using hard jerks, which caused the lure to pop into view of the muskie range and then stop.
It’s a common practice to have the database send out alerts regarding data inconsistency or problems to affected internal users. I have created a stored procedure that will send out these emails in an html format, which is considerably easier to read than the standard database mail.
To demonstrate, I’ll create a simple set of data to email:

CREATE TABLE ##temp (LakeName VARCHAR(50), Acreage DECIMAL(8,2), Rating INT)
INSERT INTO ##temp
        ( LakeName, Acreage, Rating )
VALUES  ( 'Lake Tomahawk',3462,4),
       ('Big Arbor Vitae',1070,3),
       ('Pewaukee',2437,3),
       ('Okauchee',1210,4),
       ('Random Lake',212,1)

Next I’ll query this table using the normal databasemail stored procedure:

EXEC msdb.dbo.sp_send_dbmail
       @recipients = 'dgugg@domain.com',
       @subject = 'My Lake List',
       @body = Lakes,
       @query = 'select top 5 lakename, acreage, rating from ##temp order by rating';

You can see the results look a bit hard to read:
20150121 DBMail PlainImagine how much harder to read it would be with more columns and records!

Next I’ll query using my specially built stored procedure:

EXEC DBMaint.dbo.SendDBEmailwithTabularQuery 
   @qSELECT = N'SELECT TOP 5', 
    @fieldlist = N'LakeName|Acreage|Rating', 
    @qFROM = N'FROM ##temp', 
    @qWHERE = '', 
    @qGroupBy = '',
    @qHaving = '', 
    @qOrderBy = N'ORDER BY Rating', 
    @recipients = N'dgugg@domain.com', 
    @subject = N'My Lake List', 
    @Title = N'Lakes', 
    @SendEmailWithNoResults = 1 





Much easier to read!

20150121 DBMail Tabular

So here is the stored procedure. It is set up to send no email if there are no results, but that can be toggled when you call it.

CREATE PROCEDURE [dbo].[SendDBEmailwithTabularQuery]
(
   @qSELECT NVARCHAR(100), --The select part of the sql statement, which can include top X
   @fieldlist NVARCHAR(MAX), --Pipe delimited list of fields, which can include aliases
   @qFROM NVARCHAR(MAX), --The from part of the sql statment, which can include joins
   @qWHERE NVARCHAR(MAX) = '', --The where part of the sql statement
   @qGroupBy NVARCHAR(MAX) = '',--The group by clause
   @qHaving NVARCHAR(MAX) = '',--The having clause
   @qOrderBy NVARCHAR(MAX) = '', --The order by part of the sql statement
   @recipients NVARCHAR(4000), --The recipients of the email
   @subject NVARCHAR(400), --The subject of the email
   @Title NVARCHAR(4000) = '', --The title of the html table that holds the query results
   @SendEmailWithNoResults BIT = 0 --Send an email even if no records are generated
)

AS
BEGIN

   --Declare initial variable.
   DECLARE @xml NVARCHAR(MAX)
   DECLARE @body NVARCHAR(MAX)
   DECLARE @sql NVARCHAR(MAX)
   DECLARE @resultexist NVARCHAR(MAX)
   DECLARE @tblfieldheader NVARCHAR(MAX) = ''
   DECLARE @tempfield NVARCHAR(MAX) = ''
   CREATE TABLE #Fields (ID INT IDENTITY(1,1),field NVARCHAR(MAX))
   DECLARE @i INT = 1, @j INT = 0, @SendEmail INT
   DECLARE @splitcnt INT
   DECLARE @fieldcount INT  
  
   --Find the number of fields in the query  
   SELECT @splitcnt = LEN(@fieldlist)-LEN(REPLACE(@fieldlist,'|',''))
   --Loop through the fields and put each on into the #Fields temp table as a new record
   WHILE @j <= @splitcnt
       BEGIN
           INSERT INTO #Fields ( field ) SELECT s FROM DBMaint.dbo.SplitString(@fieldlist,'|') WHERE zeroBasedOccurance = @j
           SET @j += 1
       END
   
   SELECT @fieldcount = @splitcnt + 1 --Will be the splitcnt + 1, otherwise MAX(ID) FROM #Fields  

   --Start setting up the sql statement for the query.
   SET @sql = @qSELECT
   --Loop through the #Fields table to get the field list
   WHILE @i <= @fieldcount
       BEGIN
           SELECT @tempfield = field FROM #Fields WHERE ID = @i
           --------------------------------------------------------------------------------------------------------------------------------------------------------------
           --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 RIGHT(@tempfield,1) = ']' OR CHARINDEX(' as ',@tempfield) = 0
               BEGIN
                   --Set the xml field to be the entire field name
                   SET @sql = @sql + ' ISNULL(CAST(' + @tempfield + ' AS NVARCHAR(4000)),''NULL'') AS ''td'','''','
                   --Set the table header field to be the entire field name
                   SET @tblfieldheader = @tblfieldheader + '<th>' + @tempfield + '</th>'
               END          
           ELSE 
               BEGIN
                   --Set the xml field to be the field name minus the alias
                   SET @sql = @sql + ' ISNULL(CAST(' + LEFT(@tempfield,LEN(@tempfield) - (CHARINDEX(' sa ',REVERSE(@tempfield))+3)) + ' AS NVARCHAR(4000)),''NULL'') AS ''td'','''','
                   --Set the table header field to be the field name's alias
                   SET @tblfieldheader = @tblfieldheader + '<th>' + RIGHT(@tempfield,CHARINDEX(' sa ',REVERSE(@tempfield))-1) + '</th>'
               END
           --Increment the counter.
           SET @i += 1
       END
   --Trim the extra four characters of the end of @sql.      
   SET @sql = LEFT(@sql, LEN(@sql)-4)
   --Add the from, where, group by, having, and order by clause to the select statement.
   SET @sql = @sql + ' ' + @qFROM + ' ' + @qWHERE + ' ' +  @qGroupBy + ' ' + @qHaving + ' ' + @qOrderBy
   --Put the set xml command around the sql statement.
   SET @sql = 'SET @XML = CAST(( ' + @sql + ' FOR XML PATH(''tr''),ELEMENTS ) AS NVARCHAR(MAX))'
   --Run the sql that will create the xmll.
   EXEC sp_executesql @sql, N'@xml nvarchar(max) output', @xml OUTPUT
   
   --Create the body of the email, which contains the xml results of the query.
   SET @body = '<html><body><H3>' + @Title + '</H3><table border = 1><tr>' + @tblfieldheader + '</tr>' + @xml + '</table></body></html>'

   --Drop the fields temp table.
   DROP TABLE #Fields

   --Set the variable that will be tested to verify there was at least one result.
   SET @resultexist = 'IF NOT EXISTS(SELECT TOP 1 1 ' + @qFROM + ' ' + @qWHERE + ' ' + @qGroupBy + ' ' + @qHaving + ') SET @SendEmail = 0 ELSE SET @SendEmail = 1'
   --Capture whether or not any rows were returned
   EXEC sp_executesql @resultexist, N'@SendEmail int output', @SendEmail OUTPUT
   --Check the variable.
   IF @SendEmail = 1
       BEGIN  
           --If rows were returned, send the email.
           EXEC msdb.dbo.sp_send_dbmail
                   @recipients = @recipients,
                   @subject = @subject,
                   @body = @body,
                   @body_format = 'HTML';
       END
   ELSE IF @SendEmailWithNoResults = 1
       BEGIN
           EXEC msdb.dbo.sp_send_dbmail
                   @recipients = @recipients,
                   @subject = @subject,
                   @body = 'Query returned no results';
       END
END


GO

This stored procedure uses a string splitting table valued function. Many versions of this can be found online, but here is the one I use. Please note I am not the original author, but I am unsure of where I found this, so I can’t give credit to the correct persion.

CREATE FUNCTION [dbo].[SplitString] 
    (
        @str NVARCHAR(4000), 
        @separator CHAR(1)
    )
    RETURNS TABLE
    AS
    RETURN (
        WITH tokens(p, a, b) AS (
            SELECT 
                1, 
                1, 
                CHARINDEX(@separator, @str)
            UNION ALL
            SELECT
                p + 1, 
                b + 1, 
                CHARINDEX(@separator, @str, b + 1)
            FROM tokens
            WHERE b > 0
        )
        SELECT
            p-1 zeroBasedOccurance,
            SUBSTRING(
                @str, 
                a, 
                CASE WHEN b > 0 THEN b-a ELSE 4000 END) 
            AS s
        FROM tokens
      )