Selecting data from table and Writing to file in Oracle PL/SQL using UTL_FILE (File handling)

Using Oracle supplied package UTL_FILE we can perform file operations like Reading a File, Writing a File, Deleting File & also coping files.
Before you start make sure to create DB directory & to provide required required grants to your user.


DB Directory creation Example :
CREATE DIRECTORY UP_CUST as 'C:\TESTLOC';
Providing Grants:
GRANT READ, WRITE ON DIRECTORY UP_CUST TO SCOTT;

Steps to Write a file:
1) Open file in write mode using FOPEN
2) Writing the data using Put_Line procedure of UTL_FILE.
3) Closing the file using FClose.

create or replace PROCEDURE FSCM_Customer_txt
AS
  CURSOR c_data
  IS
    SELECT customer_name,customerid, customer_mobile FROM customer_data;
  v_file UTL_FILE.FILE_TYPE;
  f_name    VARCHAR2(50);
  v_batchid NUMBER;
  V_COUNT   NUMBER;
  row_count NUMBER;
BEGIN
select count(*) INTO row_count from FSCM_Customer_Download;
--IF NO DATA IN TABLE THEN NO FILE TO BE GENERATED
IF row_count = 0 THEN
null;
Else
--file name with curent Date & Time stamp
f_name := 'Customer_'||TO_CHAR(sysdate,'DDMMYYYY')||'_'||TO_CHAR(sysdate,'HHMISS')||'.txt';
--'UP_CUST' is the directory Name ; in order to write a file it should be opened in write mode 'w'
v_file := UTL_FILE.FOPEN(location => 'UP_CUST', filename => f_name, open_mode => 'w', max_linesize => 32767);
--for selecting customer data one by one from
FOR cur_rec IN c_data
LOOP
RECORD_STRING := customer_name||','||customerid||','|| customer_mobile; --comma separated values
--WRITing DATA TO FILE
UTL_FILE.PUT_LINE(v_file,RECORD_STRING);
END LOOP;
UTL_FILE.FCLOSE(v_file);
END IF;
END;
/