Create an Excel File (.xlsx) using PL/SQL
I've seen a package called as_xlsx
by Anton Scheffer, Create an Excel-file with PL/SQL and it addressed my problem.I also modified it a bit to put in Worksheet Names and to allow SYS_REFCURSOR
as a Parameter instead of a VARCHAR2
as required in my Previous Post (Return the SQL Statement of an Explicit Cursor).
I added this in the Package Specification for Procedure Overloading:
procedure query2sheet( p_cur IN OUT SYS_REFCURSOR, p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_sheetname varchar2 := null);
I added this in the Package Body for Procedure Overloading (note: the line comments were lines I modified):
procedure query2sheet( p_cur IN OUT SYS_REFCURSOR, p_column_headers boolean := true, p_directory varchar2 := null, p_filename varchar2 := null, p_sheet pls_integer := null, p_sheetname varchar2 := null)is t_sheet pls_integer; t_c integer; t_col_cnt integer; t_desc_tab2 dbms_sql.desc_tab2; t_desc_tab dbms_sql.desc_tab; d_tab dbms_sql.date_table; n_tab dbms_sql.number_table; v_tab dbms_sql.varchar2_table; t_bulk_size pls_integer := 200; t_r integer; t_cur_row pls_integer; t_d number;begin -- Changed if p_sheetname is not null then new_sheet(p_sheetname); else new_sheet; end if; -- End of Change --t_c := dbms_sql.open_cursor; --dbms_sql.parse( t_c, p_sql, dbms_sql.native ); t_d := DBMS_SQL.TO_CURSOR_NUMBER(p_cur); --dbms_sql.describe_columns2( t_c, t_col_cnt, t_desc_tab ); dbms_sql.describe_columns( t_d, t_col_cnt, t_desc_tab ); for c in 1 .. t_col_cnt loop if p_column_headers then cell( c, 1, t_desc_tab( c ).col_name, p_sheet => t_sheet ); end if; --dbms_output.put_line( t_desc_tab( c ).col_name || ' ' || t_desc_tab( c ).col_type ); case when t_desc_tab( c ).col_type in ( 2, 100, 101 ) then --dbms_sql.define_array( t_c, c, n_tab, t_bulk_size, 1 ); dbms_sql.define_array( t_d, c, n_tab, t_bulk_size, 1 ); when t_desc_tab( c ).col_type in ( 12, 178, 179, 180, 181 , 231 ) then --dbms_sql.define_array( t_c, c, d_tab, t_bulk_size, 1 ); dbms_sql.define_array( t_d, c, d_tab, t_bulk_size, 1 ); when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 ) then --dbms_sql.define_array( t_c, c, v_tab, t_bulk_size, 1 ); dbms_sql.define_array( t_d, c, v_tab, t_bulk_size, 1 ); else null; end case; end loop; -- t_cur_row := case when p_column_headers then 2 else 1 end; t_sheet := nvl( p_sheet, workbook.sheets.count() ); -- --t_r := dbms_sql.execute( t_c ); loop --t_r := dbms_sql.fetch_rows( t_c ); t_r := dbms_sql.fetch_rows( t_d ); if t_r > 0 then for c in 1 .. t_col_cnt loop case when t_desc_tab( c ).col_type in ( 2, 100, 101 ) then --dbms_sql.column_value( t_c, c, n_tab ); dbms_sql.column_value( t_d, c, n_tab ); for i in 0 .. t_r - 1 loop if n_tab( i + n_tab.first() ) is not null then cell( c, t_cur_row + i, n_tab( i + n_tab.first() ), p_sheet => t_sheet ); end if; end loop; n_tab.delete; when t_desc_tab( c ).col_type in ( 12, 178, 179, 180, 181 , 231 ) then --dbms_sql.column_value( t_c, c, d_tab ); dbms_sql.column_value( t_d, c, d_tab ); for i in 0 .. t_r - 1 loop if d_tab( i + d_tab.first() ) is not null then cell( c, t_cur_row + i, d_tab( i + d_tab.first() ), p_sheet => t_sheet ); end if; end loop; d_tab.delete; when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 ) then --dbms_sql.column_value( t_c, c, v_tab ); dbms_sql.column_value( t_d, c, v_tab ); for i in 0 .. t_r - 1 loop if v_tab( i + v_tab.first() ) is not null then cell( c, t_cur_row + i, v_tab( i + v_tab.first() ), p_sheet => t_sheet ); end if; end loop; v_tab.delete; else null; end case; end loop; end if; exit when t_r != t_bulk_size; t_cur_row := t_cur_row + t_r; end loop; --dbms_sql.close_cursor( t_c ); dbms_sql.close_cursor( t_d ); if ( p_directory is not null and p_filename is not null ) then save( p_directory, p_filename ); end if;exceptionwhen othersthen --if dbms_sql.is_open( t_c ) if dbms_sql.is_open( t_d ) then --dbms_sql.close_cursor( t_c ); dbms_sql.close_cursor( t_d ); end if;end query2sheet;
This is a Sample Block in my Concurrent Request that Creates the File:
Procedure EMP_ROSTER_REPORT (p_empno per_all_people_f.employee_number%type , p_bg_id per_business_groups.business_group_id%type , p_email_add per_all_people_f.email_address%type)is l_fh UTL_FILE.FILE_TYPE; l_directory VARCHAR2(30) := 'EXT_TAB_DATA'; l_filename VARCHAR2(100); emp_cur SYS_REFCURSOR; l_message varchar2(100); g_stage varchar2(100); g_zipped_blob blob; cursor p_payroll_cur is select payroll_id , payroll_name , business_group_id from pay_all_payrolls_f where business_group_id = p_bg_id;BEGIN ----------------------------------- g_stage := 'setting the filename'; ----------------------------------- l_filename := 'EMPLOYEE_ROSTER_REPORT_'||TO_CHAR(SYSDATE, 'DD-MON-YYYY-HHMISS'); ------------------------------------------ g_stage := 'Assigning Emp SysRefCursor'; ------------------------------------------ for i in p_payroll_cur loop OPEN emp_cur FOR SELECT 'extra long query here with parameters' from table_a where payroll_id = i.payroll_id; ---------------------------------------------------------- g_stage := 'open Employee Cursor and write into the File'; ---------------------------------------------------------- as_xlsx.query2sheet( p_cur => emp_cur -- Uses Sys_RefCursor Instead of Dynamic SQL (Varchar2) , p_sheetname => i.payroll_name); -- This is where we assign the Sheet Names as_xlsx.freeze_pane( 1,1 ); -- Freeze the topmost and rightmost pane in the Excel Sheet end loop; ------------------------------ g_stage := 'Create the File'; ------------------------------ as_xlsx.save( l_directory , l_filename||'.xlsx');END EMP_ROSTER_REPORT;
Hope this helps someone! :)
If you need to produce XLSX file and send it to business users it sounds rather like a job for some services outside DB. You should prepare procedure that returns ref cursor
with conten of report and then setup some service that consume data generate PDF or XLSX and send it.
If you can't do it outside DB you can still use Java inside Oracle. You can create Java procedure that will create XLSX. Here is an example of Java creating Excel file. But creating complicated Java procedure is not the best solution and probably will need you to install some jars on DB server so I would create procedure that returns ref cursor
with data and small program that creates file and send it outside DB.
There is no support for xls
or xlsx
files in Oracle database as such.
What you can do is create CSV (Comma Separated) file and then, compress it into a zip file using a custom java stored procedure which uses java.util.zip
, or a PL/SQL procedure that uses UTL_COMPRESS
.