Oracle PLSQL Script for generate CSV file from table

PLSQL Script for generate csv file from table and mail of Oracle Audit Report

Audit Report is need to track the database about users activities. Suppose database is used for Financial system then it mandatory to track the things of user which user is update,insert or delete on table for maintaining the security of database.

DBA_COMMON_AUDIT_TRAIL is the view to see the audit information. The following Query in Step 1 is fetching data from view and write it in the CSV format of last 7 days activities:

Step 1: PLSQL script generate CSV file from Oracle audit table. Save it as query.sql file

cursor c_direct_reports is
select session_id,statementid,extended_timestamp,LOGOFF_TIME,db_user,os_user,userhost,object_schema,
object_name,statement_type,scn,priv_used from dba_common_audit_trail
where EXTENDED_TIMESTAMP between trunc(systimestamp)-7 and trunc(systimestamp);
wfile_handle utl_file.file_type;
v_wstring varchar2(5000);
v_header varchar2(5000);
v_file varchar2(100);
v_date varchar2(20);
v_iname varchar2(20);

select to_char(sysdate,'ddmmyyyy') into v_date from dual;
select instance_name into v_iname from v$instance;

v_header :='SESSION ID'||chr(9)||'STATEMENT ID'||chr(9)||'LOG ON'||chr(9)||'LOG OFF'||chr(9)||'DB USER'||chr(9)||'OS USER'||chr(9)||'USER HOST'||chr(9)||'SCHEMA'||chr(9)||'OBJECT NAME'||chr(9)||'STATEMENT TYPE'||chr(9)||'SCN'||chr(9)||'PRIV USED';
v_file := 'Audit_reports_'||v_iname||'_'||v_date||'.xls';
wfile_handle := utl_file.fopen('AUD_DIR',v_file, 'W');
for Mnrec in c_direct_reports loop
v_wstring := to_char(Mnrec.session_id)||chr(9)||to_char(Mnrec.statementid)||chr(9)||to_char(Mnrec.extended_timestamp,'DD/MM/YYYY HH24:MI:SS')||chr(9)||to_char(Mnrec.LOGOFF_TIME,'DD/MM/YYYY HH24:MI:SS')||chr(9)||to_char(Mnrec.db_user)||chr(9)||to_char(Mnrec.os_user)||chr(9)||to_char(Mnrec.userhost)||chr(9)||to_char(Mnrec.object_schema)||chr(9)||to_char(Mnrec.object_name)||chr(9)||to_char(Mnrec.statement_type)||chr(9)||to_char(Mnrec.scn)||chr(9)||to_char(Mnrec.priv_used);
end loop;

Step 2: Save in bat file and send mail utility is used for mail

@echo off
set Stamp=%DATE:~-7,2%%DATE:~-10,2%%DATE:~-4%
cd E:\scripts\audit
del *.xls
del *.zip

xcopy /e /y /z "\\\E$\scripts\audit\" E:\scripts\audit\*.*

xcopy /e /y /z "\\\E$\scripts\audit\" E:\scripts\audit\*.*

E:\scripts\audit\sendEmail\sendEmail -f -t -u IFSDB Weekly Audit Report : %DATE%  -a E:\scripts\audit\

E:\scripts\audit\ -s  -o message-content-type=html -o message-file=E:\scripts\audit\message_body.html

Step 3: Save the message body as message_body.html file used by send mail utility for body of mail:


Please find the attached audit reports.

DBA Team


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.