oracle sql output for outlook email body

The results you see in Outlook are the result of it using a proportional font, which skews the alignment.  If you send simple text, you have no control over this, as it is purely an Outlook issue.

you could try using DBMS_XMLGEN.  Here's a procedure that uses it to report blocking sessions, to give you an example:

DECLARE    ctxh          DBMS_XMLGEN.ctxHandle;    query_result  XMLTYPE;    xslt_tranfsorm XMLTYPE;    i  PLS_INTEGER;    mailbody      CLOB;BEGIN    -- SQL to get data:    ctxh        := DBMS_XMLGEN.newContext      ('select NVL(s1.username,''null'') username1,                s1.machine machine1,                s1.sid sid1,                s1.serial# serial1,                s1.osuser osuser1,                s1.program program1,                lo.object_id locked_object,                NVL(s2.username,''null'') username2,                s2.machine machine2,                s2.sid sid2,                s2.serial# serial2,                s2.osuser osuser2,                s2.program program2,                sqlt.sql_text sql_text2        from  v$lock l1,                v$session s1,                v$lock l2,                v$session s2,                v$locked_object lo,                v$sqltext sqlt        where s1.sid=l1.sid          and s2.sid=l2.sid          and l1.BLOCK=1 and l2.request > 0          and l1.id1 = l2.id1          and l2.id2 = l2.id2          and s1.sid = lo.session_id          and s2.sql_address = sqlt.address          and s2.sql_hash_value = sqlt.hash_value          and sqlt.piece = 0');    -- XSLT with HTML Template for Table :    BEGIN    xslt_tranfsorm := NEW XMLTYPE('        <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">          <xsl:template match="/ROWSET">            <table>              <tr>                <th>Blocking User</th>                <th>Machine</th>                <th>SID</th>                <th>SERIAL#</th>                <th>OSUSER</th>                <th>PROGRAM</th>                <th>OBJID</th>                <th>Blocked User</th>                <th>Machine</th>                <th>SID</th>                <th>SERIAL#</th>                <th>OSUSER</th>                <th>Program</th>                <th>SQL</th>              </tr>              <xsl:for-each select="ROW">                <tr>                  <td> <xsl:value-of select="USERNAME1"/>    </td>                  <td> <xsl:value-of select="MACHINE1"/>    </td>                  <td> <xsl:value-of select="SID1"/>    </td>                  <td> <xsl:value-of select="SERIAL1"/>    </td>                  <td> <xsl:value-of select="OSUSER1"/>    </td>                  <td> <xsl:value-of select="PROGRAM1"/>    </td>                  <td> <xsl:value-of select="LOCKED_OBJECT"/>    </td>                  <td> <xsl:value-of select="USERNAME2"/>    </td>                  <td> <xsl:value-of select="MACHINE2"/>    </td>                  <td> <xsl:value-of select="SID2"/>    </td>                  <td> <xsl:value-of select="SERIAL2"/>    </td>                  <td> <xsl:value-of select="OSUSER2"/>    </td>                  <td> <xsl:value-of select="PROGRAM2"/>    </td>                  <td> <xsl:value-of select="SQL_TEXT2"/>    </td>                </tr>              </xsl:for-each>            </table>          </xsl:template>        </xsl:stylesheet>');    query_result := DBMS_XMLGEN.getXMLType(ctxh).transform(xslt_tranfsorm);    mailbody := '<h3>Blocking Session(s)</h3>' || Chr(13);    Dbms_Lob.append(mailbody,query_result.getClobVal());    EXCEPTION        WHEN OTHERS THEN        i := 0;    END;    i := dbms_xmlgen.getNumRowsProcessed(ctxh);    if i > 0 THEN    --- call to local procedure that is really just a wrapper around util_smtp.  END IF;end;/

EdStevens:

If the OP was creating and sending the email from within the database (eg APEX_MAIL),  Your approach would be perfect.  (I'll have to bookmark your answer.  I've seen this request in apex a few times)

OP is already using SQL*Plus which has automatic HTML creation of the results built in.

Just add SET MARKUP HTML ON at the beginning of the script.

https://docs.oracle.com/cd/B12037_01/server.101/b12170/ch8.htm

MK