Here I have shown
an example how to write data in OS text file from database.
Example:
CREATE OR REPLACE DIRECTORY INT_AUDIT_LOG AS
'/download/dpdump/audit_logs';
CREATE OR REPLACE PROCEDURE int_log_export (
v_date_greater_than IN VARCHAR2,
v_date_less_than IN VARCHAR2,
v_filename IN VARCHAR2
)
IS
v_location
VARCHAR2(400) := 'INT_AUDIT_LOG';
fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (v_location,
v_filename , 'W', 32767);
v_line VARCHAR2(8000);
BEGIN
FOR rec IN (select
AUDITLOGITEMID,AUDITLOGID,CREATED,EVENTNAME,REFERENCETYPE,REFERENCE, DATA
from report.AUDIT_LOG where CREATED>=to_date(v_date_greater_than,'YYYYMMDD')
and CREATED<to_date(v_date_less_than,'YYYYMMDD'))
LOOP
v_line :=
rec.AUDITLOGITEMID || '|'
||
rec.AUDITLOGID
|| '|' ||
TO_CHAR (rec.CREATED, 'YYYY.MM/DD
HH24:MI:SS')
|| '|' ||
rec.EVENTNAME || '|'
||
rec.REFERENCETYPE
|| '|' ||
rec.REFERENCE || '|' ||
rec.DATA;
UTL_FILE.PUT_LINE (fid, v_line);
END LOOP;
UTL_FILE.FCLOSE (fid);
EXCEPTION
WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
END;
/
Execute report.int_log_export('20150316','20150317','20150316.txt'); |