Using the APEX_DATA_EXPORT package directly from PL/SQL

As Data Warehouse developers, there is frequently a need for us to produce user reports in a variety of formats (ok, Excel).
Often these reports are the output of processes running as part of an unattended batch.
In the past I’ve written about some of the solutions out there for creating csv files and, of course Excel.

The good news is that, since APEX 20.2, Oracle provides the ability to do this out-of-the-box by means of the APEX_DATA_EXPORT PL/SQL package.
The catch is that you need to have an active APEX session to call it.
Which means you need to have an APEX application handy.

Fortunately, it is possible to initiate an APEX session and call this package without going anywhere near the APEX UI itself, as you’ll see shortly.

Specfically what we’ll cover is :

• generating comma-separated (CSV) output
• generating an XLSX file
• using the ADD_AGGREGATE procedure to add a summary
• using the ADD_HIGHLIGHT procedure to apply conditional formatting
• using the GET_PRINT_CONFIG function to apply document-wide styling

Additionally, we’ll explore how to create a suitable APEX Application from a script if one is not already available.

Incidentally, the scripts in this post can be found in this Github Repo.

Before I go any further, I should acknowledge the work of Amirreza Rastandeh, who’s LinkedIn article inspired this post.

A quick word on the Environment I used in these examples – it’s an Oracle supplied VirtualBox appliance running Oracle 23c Free Database and APEX 22.2.

Generating CSV output

APEX_DATA_EXPORT offers a veritable cornucopia of output formats. However, to begin with, let’s keep things simple and just generate a CSV into a CLOB object so that we can check the contents directly from within a script.

We will need to call APEX_SESSION.CREATE_SESSION and pass it some details of an Apex application in order for this to work so the first thing we need to do is to see if we have such an application available :

select workspace, application_id, page_id, page_name
from apex_application_pages
order by page_id
/

WORKSPACE                      APPLICATION_ID    PAGE_ID PAGE_NAME           
------------------------------ -------------- ---------- --------------------
HR_REPORT_FILES                           105          0 Global Page         
HR_REPORT_FILES                           105          1 Home                
HR_REPORT_FILES                           105       9999 Login Page          

As long as we get at least one row back from this query, we’re good to go. Now for the script itself (called csv_direct.sql) :

set serverout on size unlimited
clear screen

declare

    cursor c_apex_app is
        select ws.workspace_id, ws.workspace, app.application_id, app.page_id
        from apex_application_pages app
        inner join apex_workspaces ws
            on ws.workspace = app.workspace
        order by page_id;    

    v_apex_app c_apex_app%rowtype;    
   
    v_stmnt varchar2(32000);
    v_context apex_exec.t_context;
    v_export apex_data_export.t_export;
begin

    dbms_output.put_line('Getting app details...');
    -- We only need the first record returned by this cursor 
    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);
    
    dbms_output.put_line('Creating session');

    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'anynameyoulike' -- this parameter is mandatory but can be any string apparently
    );
    
    v_stmnt := 'select * from departments';

    dbms_output.put_line('Opening context');
    
    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    
    
    dbms_output.put_line('Running Report');
    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => 'CSV', -- patience ! We'll get to the Excel shortly.
        p_as_clob => true -- by default the output is saved as a blob. This overrides to save as a clob
    );
    
    apex_exec.close( v_context);

    dbms_output.put_line(v_export.content_clob);
end;
/
        

Running this we get :

Getting app details...
Creating session
Opening context
Running Report
DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID
10,Administration,200,1700
20,Marketing,201,1800
30,Purchasing,114,1700
40,Human Resources,203,2400
50,Shipping,121,1500
60,IT,103,1400
70,Public Relations,204,2700
80,Sales,145,2500
90,Executive,100,1700
100,Finance,108,1700
110,Accounting,205,1700
120,Treasury,,1700
130,Corporate Tax,,1700
140,Control And Credit,,1700
150,Shareholder Services,,1700
160,Benefits,,1700
170,Manufacturing,,1700
180,Construction,,1700
190,Contracting,,1700
200,Operations,,1700
210,IT Support,,1700
220,NOC,,1700
230,IT Helpdesk,,1700
240,Government Sales,,1700
250,Retail Sales,,1700
260,Recruiting,,1700
270,Payroll,,1700



PL/SQL procedure successfully completed.

We can get away with using DBMS_OUTPUT as the result set is comparatively small. Under normal circumstances, you’ll probably want to save it into a table ( as in Amirreza’s post), or write it out to a file.

In fact, writing to a file is exactly what we’ll be doing with the Excel output we’ll generate shortly.

First though, what if you don’t have a suitable APEX application lying around…

Creating an APEX application from SQL

NOTE – you only need to do this if you do not already have a suitable APEX Application available.
If you do then feel free to skip to the next bit, where we finally start generating XLSX files !

First, we need to check to see if there is an APEX workspace present for us to create the Application in :

select workspace, workspace_id
from apex_workspaces;

If this returns any rows then you should be OK to pick one of the workspaces listed and create your application in that.

Otherwise, you can create a Workspace by connecting to the database as a user with the APEX_ADMINISTRATOR_ROLE and running :

exec apex_instance_admin.add_workspace( p_workspace => 'HR_REPORT_FILES', p_primary_schema => 'HR');

…where HR_REPORT_FILES is the name of the workspace you want to create and HR is a schema that has access to the database objects you want to run your reports against.

Next, following Jeffrey Kemp’s sage advice , I can just create and then export an Application on any compatible APEX instance and then simply import the resulting file.
That’s right, it’ll work irrespective of the environment on which the export file is created, as long as it’s a compatible APEX version.
If you need them, the instructions for exporting an APEX application are here.

I’ve just clicked through the Create Application Wizard to produce an empty application using the HR schema and have then exported it to a file called apex22_hr_report_files_app.sql.

To import it, I ran the following script:

begin
    
    apex_application_install.set_workspace('HR_REPORT_FILES');
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset; 
end;    
/

@/home/mike/Downloads/apex22_hr_report_files_app.sql

Once it’s run we can confirm that the import was successful :

select application_id, application_name,
    page_id, page_name
from apex_application_pages
where workspace = 'HR_REPORT_FILES'
/

APPLICATION_ID APPLICATION_NAME        PAGE_ID PAGE_NAME           
-------------- -------------------- ---------- --------------------
           105 Lazy Reports                  0 Global Page         
           105 Lazy Reports                  1 Home                
           105 Lazy Reports               9999 Login Page          

The add_workspace.sql script in the Github Repo executes both the create workspace and application import steps described here.

Right, where were we…

Generating and Excel file

First we’ll need a directory object so we can write our Excel file out to disk. So, as a suitably privileged user :

create or replace directory hr_reports as '/opt/oracle/hr_reports';

grant read, write on directory hr_reports to hr;

OK – now to generate our report as an Excel…

set serverout on size unlimited
clear screen

declare

    cursor c_apex_app is
    select ws.workspace_id, ws.workspace, app.application_id, app.page_id
    from apex_application_pages app
    inner join apex_workspaces ws
        on ws.workspace = app.workspace
    order by page_id;    

    v_apex_app c_apex_app%rowtype;    

    v_stmnt varchar2(32000);
    v_context apex_exec.t_context;
    
    v_export apex_data_export.t_export;
    
    -- File handling variables
    v_dir all_directories.directory_name%type := 'HR_REPORTS';
    v_fname varchar2(128) := 'hr_departments_reports.xlsx';

    v_fh utl_file.file_type;
    v_buffer raw(32767);
    v_amount integer := 32767;
    v_pos integer := 1;
    v_length integer;
    
begin

    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);

    
    v_stmnt := 'select * from departments';

    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'whatever' -- any string will do !
    );
    
   -- Create the query context...
    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    

    -- ...and export the data into the v_export object
    -- this time use the default - i.e. export to a BLOB, rather than a CLOB
    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => apex_data_export.c_format_xlsx -- XLSX
    );
    
    apex_exec.close( v_context);
    
    dbms_output.put_line('Writing file');

    -- Now write the blob out to an xlsx file

    v_length := dbms_lob.getlength( v_export.content_blob);
    v_fh := utl_file.fopen( v_dir, v_fname, 'wb', 32767);
        
    while v_pos <= v_length loop
        dbms_lob.read( v_export.content_blob, v_amount, v_pos, v_buffer);
        utl_file.put_raw(v_fh, v_buffer, true);
        v_pos := v_pos + v_amount;
    end loop;
        
    utl_file.fclose( v_fh);
    dbms_output.put_line('File written to HR_REPORTS');

exception when others then
    dbms_output.put_line(sqlerrm);
    if utl_file.is_open( v_fh) then
        utl_file.fclose(v_fh);
    end if;    
end;
/

As you can see, this script is quite similar to the csv version. The main differences are that, firstly, we’re saving the output as a BLOB rather than a CLOB, simply by not overriding the default behaviour when calling APEX_DATA_EXPORT.EXPORT.

Secondly, we’re writing the result out to a file.

Once we retrieve the file and open it, we can see that indeed, it is in xlsx format :

As well as generating a vanilla spreadsheet, APEX_DATA_EXPORT does have a few more tricks up it’s sleeve…

Adding an Aggregation

We can add a row count to the bottom of our report by means of the ADD_AGGREGATE procedure.
To do so, we need to modify the report query to produce the data to be used by the aggregate :

select department_id, department_name, manager_id, location_id,  
    count( 1) over() as record_count 
 from departments';

As we don’t want to list the record count on every row, we need to exclude the record_count column from the result set by specifying the columns that we do actually want in the output. We can do this with calls to the ADD_COLUMN procedure :

    apex_data_export.add_column( p_columns => v_columns, p_name => 'DEPARTMENT_ID');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'DEPARTMENT_NAME');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'MANAGER_ID');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'LOCATION_ID');

NOTE – column names passed in the P_NAME parameter in this procedure need to be in UPPERCASE.

Finally, we need to specify an aggregate itself using the ADD_AGGREGATE procedure :

    apex_data_export.add_aggregate
    (
        p_aggregates => v_aggregates,
        p_label => 'Data Row Count',
        p_display_column => 'DEPARTMENT_ID',
        p_value_column => 'RECORD_COUNT'
    );    

The finished script is called excel_aggregate.sql :

set serverout on size unlimited
clear screen

declare

    cursor c_apex_app is
        select ws.workspace_id, ws.workspace, app.application_id, app.page_id
        from apex_application_pages app
        inner join apex_workspaces ws
            on ws.workspace = app.workspace
        order by page_id;    

    v_apex_app c_apex_app%rowtype;    
   
    v_stmnt varchar2(32000);
    
    v_columns apex_data_export.t_columns;
    v_aggregates  apex_data_export.t_aggregates;
    
    v_context apex_exec.t_context;
    v_export apex_data_export.t_export;
    
    -- File handling variables
    v_dir all_directories.directory_name%type := 'HR_REPORTS';
    v_fname varchar2(128) := 'aggregate.xlsx';

    v_fh utl_file.file_type;
    v_buffer raw(32767);
    v_amount integer := 32767;
    v_pos integer := 1;
    v_length integer;
    
begin

    -- We only need the first record returned by this cursor 
    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);
    
    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'anynameyoulike' 
    );
    
    
    -- Add a row with a count of the records in the file
    -- We need to add the relevant data to the query and then format it
    v_stmnt := 
        'select department_id, department_name, manager_id, location_id,  
            count( 1) over() as record_count 
         from departments';

    -- Make sure only the data columns to display on the report, not the record_count
    -- NOTE - in all of these procs, column names need to be passed as upper case literals
    apex_data_export.add_column( p_columns => v_columns, p_name => 'DEPARTMENT_ID');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'DEPARTMENT_NAME');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'MANAGER_ID');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'LOCATION_ID');
    
    apex_data_export.add_aggregate
    (
        p_aggregates => v_aggregates,
        p_label => 'Data Row Count',
        p_display_column => 'DEPARTMENT_ID',
        p_value_column => 'RECORD_COUNT'
    );    

    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    

    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => apex_data_export.c_format_xlsx, -- XLSX
        p_columns => v_columns,
        p_aggregates => v_aggregates
    );
    
    apex_exec.close( v_context);
    
    v_length := dbms_lob.getlength( v_export.content_blob);
    v_fh := utl_file.fopen( v_dir, v_fname, 'wb', 32767);
    
    while v_pos <= v_length loop
        dbms_lob.read( v_export.content_blob, v_amount, v_pos, v_buffer);
        utl_file.put_raw(v_fh, v_buffer, true);
        v_pos := v_pos + v_amount;
    end loop;
        
    utl_file.fclose( v_fh);

    dbms_output.put_line('File written to HR_REPORTS');
    
exception when others then
    dbms_output.put_line(sqlerrm);
    if utl_file.is_open( v_fh) then
        utl_file.fclose(v_fh);
    end if;    
end;
/
       

When we run this, we now get a row count row at the bottom of the file :

Highlighting cells and rows

The ADD_HIGHLIGHT procedure allows us to apply conditional formatting to individual cells, or even entire rows.
Once again the values to determine this behaviour need to be included in the report query.
In this case, we specify a highlight_id value to be used in rendering the row.

This time, I have a new query and I want to apply formatting based on the value of the SALARY column.
If the value is below 6000 then I want to make the text red ( by applying highlighter_id 1).
Otherwise, I want to set the background colour to green for the entire row ( highlighter_id 2).
The query therefore is this :

select first_name, last_name, salary,
    case when salary < 6000 then 1 else 2 end as fair_pay
from employees
where job_id = 'IT_PROG'

Highlight 1 is :

    -- text in red for id 1
    apex_data_export.add_highlight(
        p_highlights => v_highlights,
        p_id => 1,
        p_value_column => 'FAIR_PAY',
        p_display_column => 'SALARY',
        p_text_color => '#FF0000' );    

…and highlight 2 is :

    -- Whole row with green background for id 2
    apex_data_export.add_highlight
    (
        p_highlights => v_highlights,
        p_id => 2,
        p_value_column => 'FAIR_PAY',
        p_text_color => '#000000', -- black
        p_background_color => '#00ffbf' -- green
    );

The finished script is excel_highlight.sql :

set serverout on size unlimited
clear screen

declare

    cursor c_apex_app is
        select ws.workspace_id, ws.workspace, app.application_id, app.page_id
        from apex_application_pages app
        inner join apex_workspaces ws
            on ws.workspace = app.workspace
        order by page_id;    

    v_apex_app c_apex_app%rowtype;    
   
    v_stmnt varchar2(32000);
    
    v_highlights apex_data_export.t_highlights;    
    
    v_context apex_exec.t_context;
    v_export apex_data_export.t_export;
    
    v_dir all_directories.directory_name%type := 'HR_REPORTS';
    v_fname varchar2(128) := 'programmer_pay.xlsx';

    v_fh utl_file.file_type;
    v_buffer raw(32767);
    v_amount integer := 32767;
    v_pos integer := 1;
    v_length integer;
    
begin

    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);
    
    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'anynameyoulike' 
    );
    
    
    -- Add a row with a count of the records in the file
    -- We need to add the relevant data to the query and then format it
    v_stmnt := 
        q'[select first_name, last_name, salary,
            case when salary < 6000 then 1 else 2 end as fair_pay
        from employees
        where job_id = 'IT_PROG']';

    -- text in red for id 1
    apex_data_export.add_highlight(
        p_highlights => v_highlights,
        p_id => 1,
        p_value_column => 'FAIR_PAY',
        p_display_column => 'SALARY',
        p_text_color => '#FF0000' );    
    
    -- Whole row with green background for id 2
    apex_data_export.add_highlight
    (
        p_highlights => v_highlights,
        p_id => 2,
        p_value_column => 'FAIR_PAY',
        p_text_color => '#000000', -- black
        p_background_color => '#00ffbf' -- green
    );
    
    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    


    -- Pass the highlights object into the export
    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => apex_data_export.c_format_xlsx,
        p_highlights => v_highlights 
    );
    
    apex_exec.close( v_context);
    
    v_length := dbms_lob.getlength( v_export.content_blob);
    v_fh := utl_file.fopen( v_dir, v_fname, 'wb', 32767);
    
    while v_pos <= v_length loop
        dbms_lob.read( v_export.content_blob, v_amount, v_pos, v_buffer);
        utl_file.put_raw(v_fh, v_buffer, true);
        v_pos := v_pos + v_amount;
    end loop;
        
    utl_file.fclose( v_fh);
    dbms_output.put_line('File written to HR_REPORTS');

exception when others then
    dbms_output.put_line(sqlerrm);
    if utl_file.is_open( v_fh) then
        utl_file.fclose(v_fh);
    end if;    
end;
/

…and the output…

Formatting with GET_PRINT_CONFIG

The GET_PRINT_CONFIG function offers a plethora of document formatting options…and a chance for me to demonstrate that I’m really more of a back-end dev.

To demonstrate just some of the available options, I have :

• changed the header and body font family to Times (default is Helvetica)
• set the heading text to be White and bold
• set the header background to be Dark Gray ( or Grey, if you prefer)
• set the body background to be Turquoise
• set the body font colour to be Midnight Blue

All of which looks like this :

v_print_config := apex_data_export.get_print_config
(
    p_header_font_family => apex_data_export.c_font_family_times, -- Default is "Helvetica"
    p_header_font_weight => apex_data_export.c_font_weight_bold, --  Default is "normal"
    p_header_font_color => '#FFFFFF', -- White
    p_header_bg_color => '#2F4F4F', -- DarkSlateGrey/DarkSlateGray
    p_body_font_family => apex_data_export.c_font_family_times,
    p_body_bg_color => '#40E0D0', -- Turquoise
    p_body_font_color => '#191970' -- MidnightBlue
);        

Note that, according to the documentation, GET_PRINT_CONFIG will also accept HTML colour names or RGB codes.

Anyway, the script is called excel_print_config.sql :

set serverout on size unlimited
clear screen

declare
        cursor c_apex_app is
        select ws.workspace_id, ws.workspace, app.application_id, app.page_id
        from apex_application_pages app
        inner join apex_workspaces ws
            on ws.workspace = app.workspace
        order by page_id;    

    v_apex_app c_apex_app%rowtype;    
   
    v_stmnt varchar2(32000);
    
    v_context apex_exec.t_context;
   
    v_print_config apex_data_export.t_print_config;
    v_export apex_data_export.t_export;    

    -- File handling variables
    v_dir all_directories.directory_name%type := 'HR_REPORTS';
    v_fname varchar2(128) := 'crayons.xlsx';

    v_fh utl_file.file_type;
    v_buffer raw(32767);
    v_amount integer := 32767;
    v_pos integer := 1;
    v_length integer;
begin


    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);
    
    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'anynameyoulike'
    );

    v_stmnt := 'select * from departments';
    
    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    
    
    -- Let's do some formatting.
    -- OK, let's just scribble with the coloured crayons...
    v_print_config := apex_data_export.get_print_config
    (
        p_header_font_family => apex_data_export.c_font_family_times, -- Default is "Helvetica"
        p_header_font_weight => apex_data_export.c_font_weight_bold, --  Default is "normal"
        p_header_font_color => '#FFFFFF', -- White
        p_header_bg_color => '#2F4F4F', -- DarkSlateGrey/DarkSlateGray
        p_body_font_family => apex_data_export.c_font_family_times,
        p_body_bg_color => '#40E0D0', -- Turquoise
        p_body_font_color => '#191970' -- MidnightBlue
    );        

    -- Specify the print_config in the export
    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => apex_data_export.c_format_xlsx,
        p_print_config => v_print_config
    );
    
    apex_exec.close( v_context);
    
    v_length := dbms_lob.getlength( v_export.content_blob);
    v_fh := utl_file.fopen( v_dir, v_fname, 'wb', 32767);
    
    while v_pos <= v_length loop
        dbms_lob.read( v_export.content_blob, v_amount, v_pos, v_buffer);
        utl_file.put_raw(v_fh, v_buffer, true);
        v_pos := v_pos + v_amount;
    end loop;
    
    utl_file.fclose( v_fh);

    dbms_output.put_line('File written to HR_REPORTS');
    
exception when others then
    dbms_output.put_line(sqlerrm);
    if utl_file.is_open( v_fh) then
        utl_file.fclose(v_fh);
    end if;    
end;
/    

…and the resulting file is about as garish as you’d expect…

On the off-chance that you might prefer a more subtle colour scheme, you can find a list of HTML colour codes here.