PLSQL Proc to run sql script and save output to windows directory in excel or csv format

Question

I have requirement to run custom oracle sql script and save output as csv/excel to windows directory where we have pl/sql is installed .

script has around 30 columns .for sample below query can be used

SELECT emp_idno,emp_fname,emp_lname,emp_dept FROM emp_details 

sample data

emp_idno    emp_fname   emp_lname   emp_dept
839139      Maria       Foster      57
127323      Michale     Robbin      57
843795      Enric       Dosio       57
847674      Kuleswar    Sitaraman   57
555935      Alex        Manuel      57

the output of the file should be saved to windows D:/Output/file.xlsx

Is there a way I can make this exportable into a CSV or Excel format from pl/sql block? or can we have some kind of automated in pl/sql to do this ? Can anyone provide me with some guidance here? Thanks!

Answer 1

If you have a SQL script, why would you then involve a PL/SQL procedure into it?

From my point of view:

• SQL script should contain the full code which "exports" data
  • in other words, it utilizes the SQL*Plus spool command, result will be a CSV file
  • there are different set options you could/should use to make the output pretty
• create an operating system batch file (on MS Windows, that's the .BAT file); it should call the sqlplus executable and run your SQL file
• use your operating system's scheduling tool; on MS Windows, it is Task Scheduler
  • schedule execution of the BAT file (e.g. every day at 05:00)

That's all; when you come to work, file will be ready for further processing.