I have scheduled a shell script which is outputting the following email. I want to remove the unwanted/additional duplicated text which is being printed using the dual table below to give a heading to my SQL select output.
How can I do it?
Following is the spool SQL script. After the script execution is done (/home/oracle/MFS_ALERT/PGW_ALERTS_FRAUDS/PGW_ALERTS.out) file is added to the email body and sent to recipients.
Can you guide which set parameter command is missing OR which one is extra or unnecessary below?
This is shell script file code.
sqlplus user/pass@pgwdb @/home/oracle/MFS_ALERT/PGW_ALERTS_FRAUDS/PGW_ALERTS.sql
sleep 5
/bin/mail -s "MFS PGW Fraud Alerts (Day)" -r “test-email-1@email-domain.com” "test-email-2@email-domain.com" < /home/oracle/MFS_ALERT/PGW_ALERTS_FRAUDS/PGW_ALERTS.out
set echo off;
set feedback off ;
set verify off ;
set serveroutput off;
set heading on;
set trimspool on;
set headsep off;
set PAGESIZE 60;
set LINESIZE 400;
SET WRAP OFF;
SET COLSEP ' ';
set numw 20;
SPOOL /home/oracle/MFS_ALERT/PGW_ALERTS_FRAUDS/PGW_ALERTS.out append;
select '====== Transactions with count > 2 from same MSISDN ======' from dual;
SELECT '"'
|| MOBILENUMBER
|| '"' MSISDN,
AMOUNT/100 AMOUNT,
DECODE(TXNTYPEID,'6','MW', '0') TXN_TYPE,
MERCHANTCODE,
COUNT (*) COUNT
FROM TBL_TXN_LOG
WHERE TXNTYPEID ='6'
AND AMOUNT <=10000
AND TXNDATETIME>= SYSDATE -1
AND STATUS ='xxxx'
GROUP BY '"'
|| MOBILENUMBER
|| '"', AMOUNT/100, DECODE(TXNTYPEID,'6','MW', '0'), MERCHANTCODE
HAVING COUNT (*) > 2
UNION ALL
SELECT '"'
|| MOBILENUMBER
|| '"' MSISDN ,
AMOUNT/100 AMOUNT,
DECODE(TXNTYPEID,'164', 'Card', '0') TXN_TYPE,
MERCHANTCODE,
COUNT (*) COUNT
FROM TBL_TXN_LOG
WHERE TXNTYPEID ='164'
AND AMOUNT >=2500000
AND TXNDATETIME>= SYSDATE - 1
AND STATUS ='xxxx'
GROUP BY MOBILENUMBER,
AMOUNT,
DECODE(TXNTYPEID,'164','Card', '0') ,
MERCHANTCODE
HAVING COUNT (*) > 1
;
SPOOL OFF;
SET DEFINE ON
SET SERVEROUTPUT OFF
quit
The email which recipient is getting.,
-----Original Message----- From: destination.email@emaildomain.com Sent: Friday, December 8, 2017 7:36 PM To: destination.email@emaildomain.com Subject: MFS PGW Fraud Alerts (4 Hourly)
***> '======TRANSACTIONSWITHAMOUNT<100FORMW&>25000======'
--------------------------------------------------------------***
====== Transactions with Amount < 100 for MW & > 25000 ======
MSISDN AMOUNT TXN_ MERCHANTCODE COUNT ---------------------- -------------------- ---- ------------------------------ -------------------- "924008482888" 70 MW 00342386 1 "924008345433" 20 MW 002218387
1
I want to remove these extra lines
'======TRANSACTIONSWITHAMOUNT<100FORMW&>25000======'
Change your query a bit, use an alias for your heading, and turn off/on heading as shown:
set heading off
select '====== Transactions with count > 2 from same MSISDN ======' as info from dual;
select '<pre>' from dual;
set heading on
-- rest of your query
set heading off
select '</pre>' from dual;
That should do the trick if the message is being sent in HTML format.