Home » SQL & PL/SQL » SQL & PL/SQL » Multiple Row Record Insert Into One Field And Email (Oracle Forms 6i ( User Activity Audit Logs ))
Multiple Row Record Insert Into One Field And Email [message #673964] Thu, 20 December 2018 00:35 Go to next message
ka_wish
Messages: 87
Registered: October 2007
Location: karachi
Member


: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 #673967 is a reply to message #673964] Thu, 20 December 2018 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Re: Multiple Row Record Insert Into One Field And Email [message #673973 is a reply to message #673967] Thu, 20 December 2018 05:13 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
We know nothing about your data.
We don't know what mailout does (it's not a standard oracle thing).
And you haven't posted an actual question.
Re: Multiple Row Record Insert Into One Field And Email [message #673976 is a reply to message #673973] Thu, 20 December 2018 06:49 Go to previous messageGo to next message
EdStevens
Messages: 1248
Registered: September 2013
Senior Member
Ok, now that you've shown us some bad code, what is your question?

Regardless of what your unstated question might be, I immediately see the following:

where to_char(edate,'ddmmyy')=to_char(sysdate-1,'ddmmyy')

If EDATE is a DATE (as it appears to be), and sysdate is certainly a DATE, why area you converting them to strings to compare them? Why not just
where trunc(edate)=trunc(sysdate-1)

But aside from that the fundamental question remains. What are you expecting from us when you simply post some code with no comments or questions.
Re: Multiple Row Record Insert Into One Field And Email [message #673978 is a reply to message #673976] Thu, 20 December 2018 07:18 Go to previous message
cookiemonster
Messages: 13894
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)
Previous Topic: Execution plan alters with DDL change
Next Topic: Count function
Goto Forum:
  


Current Time: Sun Sep 20 07:07:32 CDT 2020