Tech Trainings

How to create file in Oracle Database Using UTL_FILE Approach

February 1, 2025 by admin

--DDL Script to create directory 

CREATE DIRECTORY  XXORA_DIR  AS '/u01/oracle/VIS/fs2/EBSapps/comn/temp' ;
 
--Validate the directory 

  select * from DBA_DIRECTORIES; 

--PLSQL Code to create file in Oracle Database using UTL_FILE Approach
DECLARE
  CURSOR EMP_CUR
  IS
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         SALARY,
         TO_CHAR(HIRE_DATE, 'DD/MM/RRRR') HIRE_DATE
  FROM EMPLOYEES
   ;
  
  V_FILE_TYP UTL_FILE.FILE_TYPE;
  V_FILE_NAME VARCHAR2(300) :='EmployeeData_'||TO_CHAR(SYSDATE,'DDMMYYHHMMSS')||'.csv' ;
BEGIN
  
  V_FILE_TYP := UTL_FILE.FOPEN('XXORA_DIR', V_FILE_NAME, 'W', MAX_LINESIZE => 32767);
  UTL_FILE.PUT_LINE(V_FILE_TYP, 'EMPLOYEE_ID'||','||'FIRST_NAME'||','||'LAST_NAME'||','||'SALARY'||','||'HIRE_DATE');
  FOR I IN EMP_CUR
  LOOP
    UTL_FILE.PUT_LINE(V_FILE_TYP, I.EMPLOYEE_ID||','||I.FIRST_NAME||','||I.LAST_NAME||','||I.SALARY||','||I.HIRE_DATE);
  END LOOP;
  UTL_FILE.FCLOSE(V_FILE_TYP);
EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(V_FILE_TYP);
    RAISE;
END;

Comments |0|