Oracle Script to write any SQL query output to excel

This can be done very easily with an excellent package AS_XLSX to write to excel by Anton Sheffer. Code for the same can be got from the below path. 

Download AS_XLSX 

Point to note for this package is it uses Oracles UTL_FILE to write data to a file. 

UTL_FILE writes to a file in a given directory path on the Oracle server and not on the Oracle client machine. To use this package we need to create an oracle directory and make sure there are sufficient rights on this folder path to the Oracle user running the script on the directory. Creating this oracle directory in Oracle 21c database and Windows 10 pro provides some challenges as you will get "Invalid directory object" "Invalid path" errors and will not be able to save the file in that path. Before you can write you code to save query data to excel I recommend you first try a sample script to save any file to the Oracle directory path you have created. Create the oracle directory path as below.

create or replace directory TEST_DIR as 'D:\temp';

If the directory is created by system or any DBA user, make sure the required grants are given to the user running the script. grant read, write on directory TEST_DIR to HR; Run a sample script to check if you are able to save a file to the path.

DECLARE
File    UTL_FILE.FILE_TYPE;
BEGIN
File:= UTL_FILE.FOPEN('TEST_DIR','a.txt','W');
UTL_FILE.PUT_LINE(File,'Test writing to file');
UTL_FILE.FCLOSE(File);
END;
/

In Oracle 21c, I am getting the below error when I run the test script in spite of make sure the required rights on the OS path have been given the the Oracle OS user etc..

Error report -

ORA-29283: invalid file operation: nonexistent file or path [29434]
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4

29283. 00000 -  "invalid file operation"
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.

To work around this issue, you can take an existing oracle directory created by SYS user like OPATCH_INST_DIR OPATCH_LOG_DIR OPATCH_SCRIPT_DIR ORACLECLRDIR Or in any of these OS directory paths you can create your own directory path. For example, ORACLECLRDIR points to C:\app\Administrator\product\21c\dbhomeXE\bin\clr. In this directory I have created a temp directory on the OS and created my own oracle directory as follows

create or replace directory APP_DIR as 'C:\app\Administrator\product\21c\dbhomeXE\bin\clr\temp';
grant read, write on directory APP_DIR to HR;

The sample code below will take any query as input and save the output to an excel file. Since the number of columns in the select clause may vary, the corresponding number of columns in the excel file will also vary. To get the columns in the select clause the dbms_sql oracle package can be used. Below example is used to print the employee id, first name, last name, job title and salary from employees table for department 50.

select emp.employee_id , emp.first_name , emp.last_name, job.job_title, emp.salary from employees emp ,jobs job where job.job_id = emp.job_id and emp.department_id = 50

create or replace procedure pr_write_to_excel(p_query  IN VARCHAR2) is
    v_rc sys_refcursor;
	APP_DIR constant all_directories.directory_name%type := 'APP_DIR'; -- declare the oracle directory in which the excel is to be created on the server
	v_fname varchar2(250);
  i_dept NUMBER := 50;
  
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    
begin

-- sample query below    
--p_query := 'select emp.employee_id , emp.first_name , emp.last_name, job.job_title, emp.salary from employees emp ,jobs job where job.job_id = emp.job_id and emp.department_id = 50';	
	v_fname := 'salary_for_dept_'||i_dept||'.xlsx';
	
	as_xlsx.new_sheet('Department Salary');
  
    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
	 
    open v_rc for p_query;
      
    as_xlsx.query2sheet( v_rc, p_sheet => 1);
     
    
    for i in 1 .. l_colCnt loop
      as_xlsx.set_column_width( p_col => l_colCnt, p_width => 25, p_sheet => 1);
    end loop;
    
   as_xlsx.save( APP_DIR, v_fname);
end pr_write_to_excel;