Writing data into OS text file from Database Using UTL_FILE

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');

Comments