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

Question

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 .xls file 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

hi @Leo, yes, i'm actually running a test for `.csv` right now. but mainly would prefer it in `.xlsx` since business users prefer it that way. — Migs Isip, Dec 23 '16 at 10:13
@Leo, no luck, the .csv file is almost the same size, 25 MB. — Migs Isip, Dec 23 '16 at 10:15
And what the real size of data ? Because in my mention csv = txt with delimiters — Leo, Dec 23 '16 at 10:22
@Leo, `.xls` has 25.9 MB and `.csv` has 25.4 MB. haven't looked into using text with delimiters yet. i'll check if i can manager to create a procedure for it. — Migs Isip, Dec 23 '16 at 10:28
Sounds like your company has grown to the point where a proper reporting solution is needed. Build a report and let the users request the report themselves. Better way to handle security around the data too. You don't want to get mired in having to manually send files all the time! — Gary_W, Dec 23 '16 at 14:31
hi @Gary_W, yes, that's the idea. They keep on asking us for extracts that's why i proposed to have a program that they can run on demand. :) Its going to be a Concurrent Program inside `Oracle EBS 12.1.3` — Migs Isip, Dec 23 '16 at 23:37

score 13 · Accepted Answer · edited Jul 01 '19 at 05:27

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! :)

When you create your XLSX file, it is stored where? On remote machine where DB resides? How to send this created file using UTL_MAIL? — sbrbot, Sep 19 '19 at 11:36
@sbrbot yes it's on the remote machine where the DB is. there are a lot of UTL_MAIL packages floating around that can send files as an attachment. — Migs Isip, Sep 20 '19 at 03:05
My biggest problem is that UTL_MAIL is very limitted in terms of attachment size (32K or something like that). So I coudn't send bigger file, now if attachment is saved externally will I be able to send it using UTL_MAIL? — sbrbot, Sep 20 '19 at 13:18
What are the dependencies of this package? Or is it a standalone? — ManuelJE, May 14 '20 at 13:07

score 1 · Answer 2 · edited Dec 26 '16 at 03:27

1

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.

edited Dec 26 '16 at 03:27

Jon Heller

34,999
6
74
132

answered Dec 23 '16 at 10:30

Gurwinder Singh

38,557
6
51
76

Hi @gurwinder-singh, i was able to find a native PL/SQL Package that addresses the need. kindly take a look at my answer below. thanks! – Migs Isip Dec 28 '16 at 05:43

score 0 · Answer 3 · edited May 23 '17 at 11:54

0

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.

edited May 23 '17 at 11:54

Community

1
1

answered Dec 23 '16 at 18:38

Kacper

4,798
2
19
34

Hi @kacper, yes that was the approach i took in my last post http://stackoverflow.com/questions/41281665/return-the-sql-statement-of-an-explicit-cursor, however, i'm having challenges with the filesize and the row limitations. I'm sending these reports via a Concurrent Program in Oracle EBS. – Migs Isip Dec 23 '16 at 23:36
hi @kacper, i was able to find a solution, kindly take a look at my answer in this question. i also did some modifications to the package specified and was able to pass `SYS_REFCURSOR` as a parameter. – Migs Isip Dec 28 '16 at 05:45

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

3 Answers3

Linked