Oracle UTL_FILE
Oracle database file I/O is required for data loading, ETL processes, data extraction, Oracle SQLLDR, ETL by external tables, XML file creation and other data transaction related activities. To create output files, a database directory is required.
In earlier Oracle versions, SPOOL and SPOOL OFF commands with output file settings (SET FEEDBACK OFF, LINESIZE etc.) were used in queries/ SQL script files to create database output files.

Oracle UTL_FILE
DECLARE
 i_db_directory  VARCHAR2(30);
 i_file_out      VARCHAR2(30);
 o_status        NUMBER;

 PROCEDURE calendar_summary(i_db_dir IN VARCHAR2,
  i_filename  IN VARCHAR2,
  o_stat OUT NUMBER) AS

  CURSOR c_cal IS
   SELECT q_tab.v_val||','||
    DECODE(GREATEST(q_tab.v_val,4), q_tab.v_val,
     DECODE(GREATEST(q_tab.v_val,7), q_tab.v_val,
      DECODE(GREATEST(q_tab.v_val,10), q_tab.v_val,4,3),2),1)||',"'||
    '01/'||LPAD(q_tab.v_val,2,'0')||'/'||TO_CHAR(SYSDATE,'yyyy')||'","'||
    TO_CHAR(LAST_DAY(TO_DATE(LPAD(q_tab.v_val,2,'0')||
     TO_CHAR(SYSDATE,'yyyy'),'mmyyyy')),'mm/dd/yyyy')||'",'||
    TO_NUMBER(1+(LAST_DAY(TO_DATE(LPAD(q_tab.v_val,2,'0')||
     TO_CHAR(SYSDATE,'yyyy'),'mmyyyy'))) - TO_DATE(LPAD(q_tab.v_val,2,'0')||
      TO_CHAR(SYSDATE,'yyyy'),'mmyyyy'))||','||
    SUM(1+LAST_DAY(TO_DATE(LPAD(q_tab.v_val,2,'0')||
      TO_CHAR(SYSDATE,'yyyy'),'mmyyyy')) - TO_DATE(LPAD(q_tab.v_val,2,'0')||
       TO_CHAR(SYSDATE,'yyyy'),'mmyyyy')) OVER(ORDER BY q_tab.v_val)   cal_data
   FROM (SELECT LEVEL v_val FROM DUAL CONNECT BY LEVEL <=12) q_tab;

  output_file  UTL_FILE.file_type;
  v_filename   VARCHAR2(30) := 'calendar_summary_'||TO_CHAR(SYSDATE,'yyyy')||'.txt';
  v_file_hdr   VARCHAR2(100);
  v_load_id    PLS_INTEGER;

 BEGIN
  IF i_filename IS NOT NULL THEN
     v_filename := i_filename;
  END IF;

  SELECT 'Calendar Summary Created on: '||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')
  INTO  v_file_hdr
  FROM  DUAL;

   output_file := UTL_FILE.fopen (i_db_dir, v_filename, 'W');

   UTL_FILE.put_line(output_file, v_file_hdr);
   UTL_FILE.put_line(output_file, '    ');
   UTL_FILE.put_line(output_file,'Month_Num,Quarter,
                      First_Day,Last_Day,Month_Days,YTD_Days');
   UTL_FILE.put_line(output_file, '    ');

   FOR cal_rec IN c_cal
    LOOP
       UTL_FILE.put_line(output_file, cal_rec.cal_data);
    END LOOP;

  UTL_FILE.FCLOSE(output_file);

 EXCEPTION 
  WHEN UTL_FILE.INVALID_PATH THEN
    o_stat := SQLCODE;

  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    o_stat := SQLCODE;

  WHEN OTHERS Then
    o_stat := SQLCODE;
    UTL_FILE.FCLOSE(output_file);

 END calendar_summary;

BEGIN
 calendar_summary(i_db_dir => i_db_directory,
                  i_filename => i_file,
                  o_stat => o_status);
END;
/

Refer: output data

Oracle registered trademark of Oracle Corporation.

Last Revised On: October 13, 2013

  1417