Oracle script to write to multiple sheets in the same excel file

A common requirement is to write data to multiple sheets in the same excel. 

For details on writing to excel kindly refer to below link

Oracle Script to write any SQL query output to excel 

Below is an example of writing department wise employee data to multiple sheets in the same excel.

create or replace procedure pr_departwise_salary_to_excel
AS
  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;
  
  v_query VARCHAR2(1000);
  
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
    l_deptCnt       NUMBER := 0;
    
    CURSOR cur_dept
    IS
    SELECT DEPARTMENT_ID
    FROM DEPARTMENTS;
    
begin

-- sample query below  
  v_fname := 'ALL_DEPARTMENT_SALARY.xlsx';
  FOR rec IN cur_dept
  LOOP --{
  l_deptCnt := l_deptCnt + 1;
  
  v_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 = ' || rec.DEPARTMENT_ID;	
--	v_fname := 'salary_for_dept_'||i_dept||'.xlsx'';
    
	  as_xlsx.new_sheet('DEPARTMENT_SALARY_' || rec.DEPARTMENT_ID);
  
    dbms_sql.parse(  l_theCursor,  v_query, dbms_sql.native );
    dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
	 
    open v_rc for v_query;
      
    as_xlsx.query2sheet( v_rc, p_sheet => l_deptCnt);
    
    for i in 1 .. l_colCnt loop
      as_xlsx.set_column_width( p_col => l_colCnt, p_width => 25, p_sheet => l_deptCnt);
    end loop;
  END LOOP; --}  
   as_xlsx.save( APP_DIR, v_fname);
end pr_departwise_salary_to_excel;