Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to create spreadsheet with UTL File

Sandy310Apr 19 2010 — edited Apr 21 2010
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 19 2010
Added on Apr 19 2010
9 comments
3,452 views