Create an Excel File (.xlsx) using PL/SQL

admin

a package called as_xlsx by Anton Scheffer, Create an Excel-file with PL/SQL  modified it a bit to put in Worksheet Names and to allow SYS_REFCURSOR as a Parameter instead of a VARCHAR2  (Return the SQL Statement of an Explicit Cursor).

added this in the Package Specification for Procedure Overloading:

1. procedure query2sheet
   
2. ( p_cur             IN OUT      SYS_REFCURSOR
   
3. , p_column_headers  boolean     := true
   
4. , p_directory       varchar2    := null
   
5. , p_filename        varchar2    := null
   
6. , p_sheet           pls_integer := null
   
7. , p_sheetname       varchar2    := null
   

複製代碼

The Package Body for Procedure Overloading

1. <font face="inherit"></font><font color="#b09600"></font>procedure query2sheet
   
2. ( p_cur IN OUT SYS_REFCURSOR
   
3. , p_column_headers boolean := true
   
4. , p_directory varchar2 := null
   
5. , p_filename  varchar2 := null
   
6. , p_sheet     pls_integer := null
   
7. , p_sheetname varchar2 := null
   
8. )
   
9. is
   
10.     t_sheet     pls_integer;
    
11.     t_c         integer;
    
12.     t_col_cnt   integer;
    
13.     t_desc_tab2 dbms_sql.desc_tab2;
    
14.     t_desc_tab  dbms_sql.desc_tab;
    
15.     d_tab       dbms_sql.date_table;
    
16.     n_tab       dbms_sql.number_table;
    
17.     v_tab       dbms_sql.varchar2_table;
    
18.     t_bulk_size pls_integer := 200;
    
19.     t_r         integer;
    
20.     t_cur_row   pls_integer;
    
21.     t_d         number;
    
22. begin
    
23.     -- Changed
    
24.     if p_sheetname is not null then
    
25.         new_sheet(p_sheetname);      
    
26.     else
    
27.         new_sheet;
    
28.     end if;
    
29.     -- End of Change
    
30.     --t_c := dbms_sql.open_cursor;                       
    
31.     --dbms_sql.parse( t_c, p_sql, dbms_sql.native );
    
32. 
    
33.     t_d := DBMS_SQL.TO_CURSOR_NUMBER(p_cur);
    
34. 
    
35.     --dbms_sql.describe_columns2( t_c, t_col_cnt, t_desc_tab );
    
36.     dbms_sql.describe_columns( t_d, t_col_cnt, t_desc_tab );
    
37. 
    
38.     for c in 1 .. t_col_cnt
    
39.     loop
    
40.         if p_column_headers
    
41.         then
    
42.         cell( c, 1, t_desc_tab( c ).col_name, p_sheet => t_sheet );
    
43.         end if;
    
44.         --dbms_output.put_line( t_desc_tab( c ).col_name || ' ' || t_desc_tab( c ).col_type );
    
45.         case
    
46.         when t_desc_tab( c ).col_type in ( 2, 100, 101 )
    
47.         then
    
48.             --dbms_sql.define_array( t_c, c, n_tab, t_bulk_size, 1 );
    
49.             dbms_sql.define_array( t_d, c, n_tab, t_bulk_size, 1 );
    
50.         when t_desc_tab( c ).col_type in ( 12, 178, 179, 180, 181 , 231 )
    
51.         then
    
52.             --dbms_sql.define_array( t_c, c, d_tab, t_bulk_size, 1 );
    
53.             dbms_sql.define_array( t_d, c, d_tab, t_bulk_size, 1 );
    
54.         when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 )
    
55.         then
    
56.             --dbms_sql.define_array( t_c, c, v_tab, t_bulk_size, 1 );
    
57.             dbms_sql.define_array( t_d, c, v_tab, t_bulk_size, 1 );
    
58.         else
    
59.             null;
    
60.         end case;
    
61.     end loop;
    
62.     --
    
63.     t_cur_row := case when p_column_headers then 2 else 1 end;
    
64.     t_sheet := nvl( p_sheet, workbook.sheets.count() );
    
65.     --
    
66.     --t_r := dbms_sql.execute( t_c );
    
67.     loop
    
68.         --t_r := dbms_sql.fetch_rows( t_c );
    
69.         t_r := dbms_sql.fetch_rows( t_d );
    
70.         if t_r > 0
    
71.         then
    
72.         for c in 1 .. t_col_cnt
    
73.         loop
    
74.             case
    
75.             when t_desc_tab( c ).col_type in ( 2, 100, 101 )
    
76.             then
    
77.                 --dbms_sql.column_value( t_c, c, n_tab );
    
78.                 dbms_sql.column_value( t_d, c, n_tab );
    
79.                 for i in 0 .. t_r - 1
    
80.                 loop
    
81.                 if n_tab( i + n_tab.first() ) is not null
    
82.                 then
    
83.                     cell( c, t_cur_row + i, n_tab( i + n_tab.first() ), p_sheet => t_sheet );
    
84.                 end if;
    
85.                 end loop;
    
86.                 n_tab.delete;
    
87.             when t_desc_tab( c ).col_type in ( 12, 178, 179, 180, 181 , 231 )
    
88.             then
    
89.                 --dbms_sql.column_value( t_c, c, d_tab );
    
90.                 dbms_sql.column_value( t_d, c, d_tab );
    
91.                 for i in 0 .. t_r - 1
    
92.                 loop
    
93.                 if d_tab( i + d_tab.first() ) is not null
    
94.                 then
    
95.                     cell( c, t_cur_row + i, d_tab( i + d_tab.first() ), p_sheet => t_sheet );
    
96.                 end if;
    
97.                 end loop;
    
98.                 d_tab.delete;
    
99.             when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 )
    
100.             then
     
101.                 --dbms_sql.column_value( t_c, c, v_tab );
     
102.                 dbms_sql.column_value( t_d, c, v_tab );
     
103.                 for i in 0 .. t_r - 1
     
104.                 loop
     
105.                 if v_tab( i + v_tab.first() ) is not null
     
106.                 then
     
107.                     cell( c, t_cur_row + i, v_tab( i + v_tab.first() ), p_sheet => t_sheet );
     
108.                 end if;
     
109.                 end loop;
     
110.                 v_tab.delete;
     
111.             else
     
112.                 null;
     
113.             end case;
     
114.         end loop;
     
115.         end if;
     
116.         exit when t_r != t_bulk_size;
     
117.         t_cur_row := t_cur_row + t_r;
     
118.     end loop;
     
119.     --dbms_sql.close_cursor( t_c );
     
120.     dbms_sql.close_cursor( t_d );
     
121.     if ( p_directory is not null and  p_filename is not null )
     
122.     then
     
123.         save( p_directory, p_filename );
     
124.     end if;
     
125. exception
     
126. when others
     
127. then
     
128.     --if dbms_sql.is_open( t_c )
     
129.     if dbms_sql.is_open( t_d )
     
130.     then
     
131.     --dbms_sql.close_cursor( t_c );
     
132.     dbms_sql.close_cursor( t_d );
     
133.     end if;
     
134. end query2sheet;

複製代碼

This is a Sample Block in my Concurrent Request that Creates the File:

1. Procedure EMP_ROSTER_REPORT (p_empno        per_all_people_f.employee_number%type                              
   
2.                            , p_bg_id        per_business_groups.business_group_id%type
   
3.                            , p_email_add    per_all_people_f.email_address%type)
   
4. is
   
5. 
   
6.     l_fh            UTL_FILE.FILE_TYPE;
   
7.     l_directory     VARCHAR2(30) := 'EXT_TAB_DATA';
   
8.     l_filename      VARCHAR2(100);
   
9.     emp_cur         SYS_REFCURSOR;
   
10.     l_message       varchar2(100);
    
11.     g_stage         varchar2(100);
    
12.     g_zipped_blob   blob;
    
13. 
    
14.     cursor  p_payroll_cur is
    
15.     select  payroll_id
    
16.         ,   payroll_name
    
17.         ,   business_group_id
    
18.     from    pay_all_payrolls_f
    
19.     where   business_group_id = p_bg_id;
    
20. 
    
21. BEGIN
    
22. 
    
23.     -----------------------------------
    
24.     g_stage := 'setting the filename';
    
25.     -----------------------------------
    
26. 
    
27.     l_filename := 'EMPLOYEE_ROSTER_REPORT_'||TO_CHAR(SYSDATE, 'DD-MON-YYYY-HHMISS');
    
28. 
    
29.     ------------------------------------------
    
30.     g_stage := 'Assigning Emp SysRefCursor';
    
31.     ------------------------------------------
    
32. 
    
33.     for i in p_payroll_cur loop
    
34. 
    
35.         OPEN emp_cur FOR
    
36.         SELECT  'extra long query here with parameters'
    
37.         from    table_a
    
38.         where   payroll_id = i.payroll_id;
    
39. 
    
40.         ----------------------------------------------------------
    
41.         g_stage := 'open Employee Cursor and write into the File';
    
42.         ----------------------------------------------------------
    
43. 
    
44.         as_xlsx.query2sheet( p_cur          => emp_cur            -- Uses Sys_RefCursor Instead of Dynamic SQL (Varchar2)
    
45.                            , p_sheetname    => i.payroll_name);   -- This is where we assign the Sheet Names         
    
46.         as_xlsx.freeze_pane( 1,1 );                               -- Freeze the topmost and rightmost pane in the Excel Sheet
    
47. 
    
48.     end loop;
    
49. 
    
50.     ------------------------------
    
51.     g_stage := 'Create the File';
    
52.     ------------------------------
    
53. 
    
54.     as_xlsx.save( l_directory , l_filename||'.xlsx');
    
55. 
    
56. END EMP_ROSTER_REPORT;

複製代碼

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.