Forum Stats

  • 3,853,654 Users
  • 2,264,249 Discussions
  • 7,905,426 Comments

Discussions

oracle sql output for outlook email body

HI to All,

I need to add output query into an email auto reporting body. Fut the sql script formatting output is misaligned.

on considering the below-set formatting:

set pages 5000

set feed off

spool on

set colsep "|"

set lines 256

set trimout on

set trimspool on

set feedback off;

set lines 256

COLUMN SET_NAME FORMAT A40

via sqlDeveloper runScript command I get the below output: (in the below output SE, REPORT_TIME columns was removed but the misalignment persist.)

xxx.png

but into email body report I get the below output:

yyy.png

Could you suggest a smart way for make compatible the Standard Oracle sql script output format with Microsoft Outlook body email format? ( on focus with MO 2016 version)

I'm available for dubs or questions,

thanks a lot,

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Feb 24, 2017 10:19AM

    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;/
  • 4d475ca6-d057-4177-9e38-6e94e7006b06
    edited Feb 24, 2017 10:49AM

    Hi EdStevens,



    thanks a lot for your quickly answer and collaboration. I'll try this solution and let u a feedback about this solution asap,

    thanks,

  • Mike Kutz
    Mike Kutz Member Posts: 6,199 Silver Crown
    edited Feb 24, 2017 3:34PM

    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

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Feb 24, 2017 5:53PM

    Mike, thanks for the reply.  I've never really investigated this or looked into possible improvements.  The script I presented is from a simple sql file being processed by sqlplus from a shell script.  It was written by my predecessor's predecessor's predecessor.   (Who, coincidentally, I recently found is working for a friend of mine at another company in town.)

This discussion has been closed.