Writing data into OS text file from Database Using UTL_FILE

Today we are going to have a look at “how to write data into OS text file from Database Using UTL_FILE?” In the journey of DBA sometimes in some cases, we need to perform this activity. To prepare every time to let’s have look on the post.

Before we start it we must know what does UTl_File do? , What is the UTL_FILE? 

What is UTL_File?

The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).

For more details click here

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

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp