Hi,
How to create a excel file using UTL_File? I have used following proc and file name as .xls extension but it puts all columns in one cell in excel file.
Suppose there are 5 database fields then it should be in 5 seperate columns.
create or replace
PROCEDURE "PRC_COMPANY_RECOGN_PROG_EXT"
IS
cursor cur_ba is
select ma.businessunit
from accrual a, member m,
partnerprogram pp, memberalias ma
where m.memberid = ma.memberid(+)
and a.memberid = m.memberid(+)
order by ma.businessunit;
c_delim CONSTANT VARCHAR2(1) := '|';
--file variables
V_FILE_HANDLER UTL_FILE.FILE_TYPE;
V_FILE_PATH VARCHAR2 (100) := FNC_WHR_GET_DATA_VARIABLE ('UTL_FILE_DIR','RPT');
v_file_name VARCHAR2(100) := 'COMPANY_RECOGN_PROG_EXT_'||TO_CHAR(SYSDATE,'YYYYMMDD')||'.xls';
BEGIN
--open file
v_stage := '1';
v_file_handler := UTL_FILE.FOPEN(v_file_path,v_file_name,'w');
--write header
v_stage := '2';
UTL_FILE.PUT_LINE(v_file_handler,'Award Date|BU|Cost Center|Recognition Type|Employee Name');
FOR rec_bu in cur_ba loop
v_cnt := v_cnt + 1;
open cur_crp(rec_bu.businessunit);
loop
fetch cur_crp into rec_crp;
exit when cur_crp%notfound;
v_stage := '2';
v_text := rec_crp.arrivaldateid ||c_delim||
rec_crp.bu ||c_delim||
rec_crp.cost_center ||c_delim||
rec_crp.recognition_type ||c_delim||
rec_crp.manager_issuing_points ;
UTL_FILE.PUT_LINE(v_file_handler,v_text);
v_rec_counter := v_rec_counter + 1;
END LOOP;
CLOSE cur_crp;
END LOOP;
--write trailer
v_stage := '3';
UTL_FILE.PUT_LINE(v_file_handler,'Total # of rows per BU '||rec_crp.bu||c_delim||v_rec_counter);
UTL_FILE.FCLOSE_ALL;
COMMIT;
END;
thanks
sandy