2

I have a procedure to write a report in Xls file using utl_file.

This procedure works correctly but my question is: is it possible to open this file directly in Excel after executed? I ask it because, when it's executing now, I must open the folder path then open Xls file.

My Edit Code:

  create or replace procedure write_file_daily(emp_id in number,Start_date in  date,End_date in date, Job_Name in varchar2 ) is
 file_handle UTL_FILE.file_type;
begin
  file_handle := utl_file.fopen('CSV_DIR', 'REP.xls', 'W');
  utl_file.put_line(file_handle, 'id'||   CHR(9) || 'name' ||   CHR(9) || 'department' ||   CHR(9) || 'unit' || CHR(9) || 'position' ||   CHR(9) || 'job' 
    );
  for rws in (
    select ID,
       NAME,
       DEPARTMENT,
       UNIT,
       POSITION,
       JOB  
  from (select t.ID1  as ID,
               t.NAME as Name,
               (select d.divname from compdivs d where d.divid = t.COMP_id2) as DEPARTMENT, 
               decode((select d.parent1name from compdivs d where d.divid = t.COMP),
                       'XXX',
                       (select d.divname from compdivs d where d.divid = t.COMP),
                       (select d.parent1name from compdivs d where d.divid = t.COMP)) as UNIT,
               (select d.dname from const_dt d where d.mid = 3 and d.no = t.WORKK) as Position,
               (select d.dname from const_dt d where d.mid = 8 and d.no = t.EDUCATION_ID) as Job,
          from    th1 t  
      )
 WHERE ( ID = emp_id or  emp_id = -1)
  and (Day_date  between    Start_date   and   End_date    )
   and( NVL(lower(JOB),' ') like NVL(lower('%'||  Job_Name ||'%'),NVL(lower(JOB),' ')))  
   )

  loop
    utl_file.put_line(file_handle,  rws.id ||   CHR(9) || rws.name ||   CHR(9)  || rws.department ||   CHR(9) || rws.unit ||   CHR(9) || rws.position ||   CHR(9) || rws.job  
    );
  end loop;
  utl_file.fclose(file_handle);
  
end write_file_daily;
M.Youssef
  • 146
  • 9
  • Hi M. Welcome to posting on StackOverflow. Thanks for your question. However, please do not post links to code. The code should be included in the question to maximise the value to the community. Tips here: [How to Ask](https://stackoverflow.com/help/how-to-ask) and [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve). Kind Regards. – Elletlar Aug 10 '20 at 21:59
  • Is there any way to illustrate the specific issue you're having in less than 113 lines of code? – William Robertson Aug 10 '20 at 22:00
  • You can actually make the excel file open in browser after the excel export gets completed with all information. – MdBasha Aug 11 '20 at 06:15
  • thanks for your replay, I edit my post and put my code above @Elletar – M.Youssef Aug 11 '20 at 10:33
  • hi Willam, thanks for your replay, I have a windows application with database oracle and I have to do a report but in general status, it takes a long time so I using utl_file. – M.Youssef Aug 11 '20 at 10:39
  • hi MdBasha, can you tell me how? – M.Youssef Aug 11 '20 at 10:41

1 Answers1

1

Looks to me you want a feature of launching external program (MS Excel in your case) from within PLSQL Developer, triggered by SQL/PLSQL script execution, ideally with script result as input parameter. I can imagine it would be cool and did some exploration this way in the past, though, unfortunately PLSQL Developer seems not to provide any such feature.

(Note I mean client machine, not db server. At db server side, there are some ways to invoke operating system command but I expect it does not apply to your case because your db server is probably different from your client machine.)

Tomáš Záluský
  • 10,735
  • 2
  • 36
  • 64