Multiple Row Record Insert Into One Field And Email [message #673964] |
Thu, 20 December 2018 00:35 |
|
:rec:=null;
declare
CURSOR C1 IS
SELECT distinct voucher_no,upper(decode(status,'Insert Record','Insert Records',status)) status,ip_address,to_char(edate,'dd/mm/yy hh:mi:ss am') edate,credit,debit
FROM jv_audit
where to_char(edate,'ddmmyy')=to_char(sysdate-1,'ddmmyy')
order by edate;
val VARCHAR2(10000);
BEGIN
FOR i IN C1 LOOP
IF val IS NOT NULL THEN
val := val||chr(10)||' | '||i.voucher_no||' | '||i.status||' | '||i.ip_address||' | '||i.edate||' | '||i.credit||' | '||i.debit;
ELSE
val :=' JV Voucher Activity Audit Report'||chr(10)||' | '|| i.voucher_no||' | '||i.status||' | '||i.ip_address||' | '||i.edate||' | '||i.credit||' | '||i.debit;
END IF;
:rec:=val;
END LOOP;
Mailout('abc@xxx.net','xx@xxxx.NET','xxx@xxxx.NET','JV Activity Audit Report',:REC);
END;
|
|
|
|
|
|
Re: Multiple Row Record Insert Into One Field And Email [message #673978 is a reply to message #673976] |
Thu, 20 December 2018 07:18 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
To_char is misused a lot but I don't see anything inherently with the line you picked on other than the fact it'll prevent any possibility of using an index on edate (unless there's an FBI). And your suggestion has the same problem.
The best way to rewrite it is this:
where edate >= trunc(sysdate -1) and edate < trunc(sysdate)
|
|
|