Skip to Main Content

Oracle data to send as Excel table format by email

User_AWHMXApr 24 2019 — edited Apr 25 2019

Dear Team,

I have a package where i will get the data and send as attachment through email.

Actually below is the package i was using

CREATE OR REPLACE PROCEDURE GW.SALES_EMAIL IS

BEGIN

    DECLARE

    REQ   UTL_HTTP.REQ;

    RESP  UTL_HTTP.RESP;

    VALUE VARCHAR2(1024);

    SMSLINK  VARCHAR2(1024);

    TOT NUMBER ;

    COUNTRIES VARCHAR2(10024);

    STORES NUMBER;

    STORE_NAME VARCHAR2(10024);

    TRANS_DATE DATE;

    MESSAGE VARCHAR2(10024) ;

    REP VARCHAR2(4024);   

    TOS VARCHAR2(600);

    CCS VARCHAR2(600);

    MESSSUB VARCHAR2(5000);

    MESSBODY VARCHAR2(2000);

    FROMNAME VARCHAR2(100);

  CURSOR SA IS

   SELECT SUM (DECODE (STH_ITS_CHAR_ID, 'SALE', QCF_STH_NET_VALUE_LC, 0) -

               DECODE (STH_ITS_CHAR_ID, 'SALE_RETURN', QCF_STH_NET_VALUE_LC, 0))   QCS_NET_VALUE,

           W.WH_COUNTRY_REF_ID COUNTRY,

           C.STH_WH_ID STORES,

           W.WH_ABR_NAME STORE_NAME,

           TO_CHAR(STH_DATE,'DD/MON/YYYY') TRANS_DATE

   FROM SL_TRX_V C ,

         INV_WAREHOUSE_V W

   WHERE W.WH_ID=C.STH_WH_ID

   AND   W.WH_COM_ID = C.STH_COM_ID

   AND   C.STH_COM_ID = '1'

   AND     STH_STATUS_REF_NUM     >= 3

   AND     W.WH_ID <> 102 --WAREHOUS SALE

   AND     STH_DATE BETWEEN TO_DATE (TO_CHAR (SYSDATE,'dd-mm-yyyy')||'00:00', 'dd-mm-yyyyhh24:mi')

   AND TO_DATE (TO_CHAR (SYSDATE,'dd-mm-yyyy')||'23:59', 'dd-mm-yyyyhh24:mi')

   AND   NVL(C.STD_CANCEL_FLAG,0)='0'

   AND   NVL(C.STH_CANCEL_FLAG,0)='0'

   GROUP BY W.WH_COUNTRY_REF_ID,STH_WH_ID,W.WH_ABR_NAME,TO_CHAR(STH_DATE,'DD/MON/YYYY')

   ORDER BY W.WH_COUNTRY_REF_ID,STH_WH_ID;

  BEGIN

   SMSLINK := ''; TOT := 0;COUNTRIES := '' ; STORES := '' ;STORE_NAME := '' ;TRANS_DATE := '' ;

   FOR C IN SA LOOP

         COUNTRIES :=  COUNTRIES||C.COUNTRY||','||STORES||C.STORES||'-'||C.STORE_NAME||','||(C.QCS_NET_VALUE) || '

' ;

        TOT := TOT + C.QCS_NET_VALUE;

        TRANS_DATE := C.TRANS_DATE;

   END LOOP;

MESSAGE := 'DATE:'||

(TRANS_DATE)||'

'||

'SALES!

Total : ' || (TOT) || '

' || COUNTRIES;

   MESSBODY := MESSAGE;

   MESSAGE := UTL_URL.ESCAPE(MESSAGE);

BEGIN

SEND_MAIL_MULTIPLE_USERS (P_TO          => 'test@test.com',

                                       P_FROM        => 'test@test.com',

                                        P_SUBJECT     => 'Sales By Country',

                                       P_CC         => null,

                                       P_BCC       => NULL,

                                       P_TEXT_MSG    => MESSBODY,

                                       P_ATTACH_NAME =>'Allcountries.csv',

                                       P_ATTACH_MIME  => 'text/plain',

                                       P_ATTACH_CLOB => MESSBODY,

                                       P_SMTP_HOST   => 'MAIL.test.COM' );

END;

  INSERT INTO  SA_SMS_LOG(REPLY,SMESSAGE) VALUES ( REP ,MESSAGE);

   COMMIT;

   END;

  

  EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

Result i am getting in excel like below :

  

DATE:24-APR-19
SALES!
Total : 10260.4125
Canada911-City210.75
canada912-Srs388.6875
US913-KRS42
US914-SRK53.625
US915-SSK227.25
US916-LLK193.5
US917-MMI28.5

But i need the above result as with bold grid lines in excel and in email text body like below. How can i achieve this?

pastedImage_6.png

Comments
Post Details
Added on Apr 24 2019
2 comments
335 views