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
72917