Create an Excel File (.xlsx) using PL/SQL
In relation to my previous question Return the SQL Statement of an Explicit Cursor,
i was able to generate an Excel (.xls) file using DBMS_SQL and UTL_FILE and passing a SYS_REFCURSOR (code is in the previous question).
However, one challenge i'm encountering is the generated file is over 25 MB.
I will be transmitting this over Email to Business Users and it would surely fill up their mailbox size.
When I do manual extracts to xlsx using SQL Developer it just generates around 4 MB of data.
To Address this, would it be possible through PL/SQL to do the following?
- Generate a file using a later version of Excel (
.xlsx) to compress the size - Compress the
.xlsfile before transmitting
I've also reviewed similar posts here in SO such as Writing in ExcelSheet using UTL_FILE package in Oracle,
but as the answer stated, it needs to use Java. So its not applicable to me.
Another post, Create an Excel Spreadsheet from a Oracle Database, is also using xls. So its not applicable as well.
Any thoughts?
Oracle Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Solution 1:
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;
exception
when others
then
--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! :)