--- title: Exporting data Using APEX_DATA_EXPORT description: This blog explores the features of APEX_DATA_EXPORT, making it an essential asset for APEX users looking for efficient data export, reporting and analysis. image: https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%20thumbnail.jpg --- [![DSP From Ground to Cloud](https://content.dsp.co.uk/hubfs/Logos/DSP%20From%20Ground%20to%20Cloud/DSP-Logo-Colour-From-Ground-to-Cloud.svg "DSP From Ground to Cloud")](https://dsp.co.uk) [![DSP From Ground to Cloud](https://content.dsp.co.uk/hs-fs/hubfs/2023/Logos/DSP/DSP-Logo-2019-White-600px.png?width=70&height=72&name=DSP-Logo-2019-White-600px.png "DSP From Ground to Cloud")](https://dsp.co.uk) - Services - Consulting Services - [Oracle](https://www.dsp.co.uk/oracle-consultancy) - [Microsoft](https://www.dsp.co.uk/sql-server-consultancy) - [Google](https://www.dsp.co.uk/google-cloud-consultancy) - [Architecture](https://www.dsp.co.uk/solution-design-and-architecture) - Managed Services - [Oracle Managed Services](https://www.dsp.co.uk/oracle-managed-services) - [SQL Server Managed Services](https://www.dsp.co.uk/sql-server-managed-services) - [OCI Managed Services](https://www.dsp.co.uk/oracle-cloud-infrastructure-managed-services) - [Azure Managed Services](https://www.dsp.co.uk/azure-managed-services) - [On Premise Apps Managed Services](https://www.dsp.co.uk/application-managed-services) - [Cloud Apps Managed Services](https://www.dsp.co.uk/oracle-cloud-applications-services) - Licensing and SAM - [Oracle Licensing](https://www.dsp.co.uk/oracle-licensing-services) - [Software Asset Management](https://www.dsp.co.uk/oracle-software-asset-management-sam) - Application Services - [Application Development](https://www.dsp.co.uk/software-development) - [Application Modernisation](https://www.dsp.co.uk/legacy-software-modernisation) - [Oracle Forms to Oracle APEX](https://www.dsp.co.uk/oracle-forms-to-oracle-apex) - [Application Extensions](https://www.dsp.co.uk/software-customisation) - [Archiving Solutions](https://www.dsp.co.uk/application-archiving-solution) - [Application Hosting](https://www.dsp.co.uk/oracle-apex-hosting-services) - [Development Training](https://www.dsp.co.uk/oracle-apex-training) - Data Science - [AI Strategy](https://www.dsp.co.uk/ai-strategy) - [Responsible AI and Governance](https://www.dsp.co.uk/responsible-ai) - [Agentic AI](https://www.dsp.co.uk/agentic-ai) - [Data Management for AI](https://www.dsp.co.uk/data-management) - [Power BI](https://www.dsp.co.uk/power-bi-services) - [Microsoft Fabric](https://www.dsp.co.uk/microsoft-fabric-services) - [Machine Learning](https://www.dsp.co.uk/oci-machine-learning) - Cloud Migration and Management - [Cloud Readiness and Design](https://www.dsp.co.uk/oracle-cloud-readiness-assessment) - [Migration to Fusion and Cloud Applications](https://www.dsp.co.uk/oracle-cloud-application-modernisation) - [Migration to Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-migration) - [Private Cloud](https://www.dsp.co.uk/oracle-private-cloud-services) - [Database@ Hyperscaler](https://www.dsp.co.uk/multicloud-management) - [FinOps and Optimisation](https://www.dsp.co.uk/optimise-oracle-cloud-infrastructure) - Integration and APIs - [API Management](https://www.dsp.co.uk/api-development-services) - [GoldenGate](https://www.dsp.co.uk/oracle-goldengate-services) - Disaster Recovery and Security - [Disaster Recovery](https://www.dsp.co.uk/oracle-database-disaster-recovery-solutions) - [Security Compliance](https://www.dsp.co.uk/data-security) - [Cloud Security](https://www.dsp.co.uk/cloud-security)   ![Oracle Partner, Microsoft Partner and Google Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/multiple-vendor/rectangle-2022/Partner-logos-rectangle-Nov21.svg) #### Getting to know us - [About us](https://www.dsp.co.uk/about) - [What we do](https://www.dsp.co.uk/what-we-do) - [Leadership](https://www.dsp.co.uk/dsp-product-leads) - [Careers](https://www.dsp.co.uk/careers) - Technologies - Oracle Cloud Infrastructure - [Oracle Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-infrastructure-services) - [IaaS](https://www.dsp.co.uk/iaas) - [PaaS](https://www.dsp.co.uk/paas-security) - Oracle Database - [Oracle Database](https://www.dsp.co.uk/oracle-database-consulting) - [Autonomous Database](https://www.dsp.co.uk/oracle-autonomous-database-services) - [MySQL and Heatwave](https://www.dsp.co.uk/mysql-services) - [Oracle APEX](https://www.dsp.co.uk/oracle-apex-services) - Oracle Engineered Systems - [Oracle Exadata](https://www.dsp.co.uk/exadata-database-service-consultancy) - [Oracle Database Appliance](https://www.dsp.co.uk/oracle-database-appliance) - Oracle Applications - [Oracle Fusion and Cloud Applications](https://www.dsp.co.uk/oracle-fusion-services) - [Oracle E-Business Suite](https://www.dsp.co.uk/oracle-ebs-services) - [JD Edwards](https://www.dsp.co.uk/jd-edwards-services) - [Peoplesoft](https://www.dsp.co.uk/oracle-peoplesoft-services) - [Hyperion](https://www.dsp.co.uk/oracle-hyperion-services) - Microsoft - [SQL Server](https://www.dsp.co.uk/sql-server-consultancy) - [Microsoft Azure](https://www.dsp.co.uk/microsoft-azure-services) - [Power BI](https://www.dsp.co.uk/power-bi-services) - [Microsoft Fabric](https://www.dsp.co.uk/microsoft-fabric-services) - Google - [Google Cloud Platform (GCP)](https://www.dsp.co.uk/google-cloud-partner) - Multicloud and Database Hyperscaler - [Oracle Database@Azure](https://www.dsp.co.uk/oracle-databaseazure-partner) - [Oracle Database@GCP](https://www.dsp.co.uk/google-cloud-machine-learning) - [Oracle Database@AWS](https://www.dsp.co.uk/amazon-web-services-aws) - [Oracle Azure Interconnect](https://www.dsp.co.uk/oracle-microsoft-multicloud) - Open Source Database - [PostgreSQL](https://www.dsp.co.uk/postgresql-services) - [MySQL](https://www.dsp.co.uk/mysql-services) - [MariaDB](https://www.dsp.co.uk/migration-from-mariadb-to-mysql) #### Managed Services - [Oracle Database](https://www.dsp.co.uk/oracle-managed-services) - [SQL Server](https://www.dsp.co.uk/sql-server-managed-services) - [MySQL](https://www.dsp.co.uk/mysql-support) #### Application Services - [Oracle Applications](https://www.dsp.co.uk/oracle-application-cloud-migration) - [Oracle APEX](https://www.dsp.co.uk/oracle-apex-services) - [Microsoft](https://www.dsp.co.uk/microsoft-partnership) - [ISV Services](https://www.dsp.co.uk/isv-support-and-services) #### Cloud Services - [Oracle Cloud Infrastructure](https://www.dsp.co.uk/oracle-cloud-services) - [Microsoft Azure](https://www.dsp.co.uk/microsoft-azure-services) - [Google Cloud Platform](https://www.dsp.co.uk/gcp-database) - [Multi-cloud](https://www.dsp.co.uk/oracle-microsoft-multicloud) #### Data Science - [Machine Learning](https://www.dsp.co.uk/machine-learning-consulting-services) - [Business Intelligence](https://www.dsp.co.uk/business-intelligence-services) - [Artificial Intelligence](https://www.dsp.co.uk/artificial-intelligence-consulting-services) #### Consulting Services - [Oracle](https://www.dsp.co.uk/oracle-consultancy) - [Microsoft](https://www.dsp.co.uk/sql-server-consultancy) - [Google](https://www.dsp.co.uk/google-cloud-consultancy) - [Cloud Migration](https://www.dsp.co.uk/cloud-migrations) #### Technology Solutions - [Engineered Systems](https://www.dsp.co.uk/oracle-engineered-systems-partner) - [Data Security](https://www.dsp.co.uk/data-security) - [Database Architecture](https://www.dsp.co.uk/solution-design-and-architecture) - [Disaster Recovery](https://www.dsp.co.uk/database-disaster-recovery) - Industries - [Finance](https://www.dsp.co.uk/finance) - [Healthcare](https://www.dsp.co.uk/healthcare-it-services) - [Travel and Transport](https://www.dsp.co.uk/database-solutions-for-travel-and-transport) - [Higher Education](https://www.dsp.co.uk/higher-education) - [Public Sector](https://www.dsp.co.uk/public-sector) - [Manufacturing](https://www.dsp.co.uk/manufacturing) - [Retail](https://www.dsp.co.uk/retail) - [Software Vendors](https://www.dsp.co.uk/isv-support-and-services) ### Recent case study: #### Oracle EBS Cloud Deployment Consolidating and Migrating assets into Oracle Cloud Infrastructure. [**![Oracle EBS Cloud Deployment](https://content.dsp.co.uk/hs-fs/hubfs/stonewater-logo%20(1).png?width=250&name=stonewater-logo%20(1).png)**](https://www.dsp.co.uk/stonewater)   #### Most Visited Pages - [Oracle Licensing](https://www.dsp.co.uk/oracle-licensing) - [Oracle Cloud Calculator](https://www.oracle-cloud-calculator.com/) - [Oracle Cloud Migration](https://www.dsp.co.uk/oracle-cloud-migration) - [Oracle Exadata Services](https://www.dsp.co.uk/oracle-exadata-services) - [Artificial Intelligence Services](https://www.dsp.co.uk/artificial-intelligence-consulting-services) #### - - [SQL Server Support](https://www.dsp.co.uk/sql-server-support) - [Oracle Support](https://www.dsp.co.uk/oracle-support) - [Google Cloud Consultancy](https://www.dsp.co.uk/google-cloud-consultancy) - [New Application Development](https://www.dsp.co.uk/oracle-apex-services) - [Azure Virtual Desktop](https://www.dsp.co.uk/azure-virtual-desktop) - Resources - [Gartner ](https://www.dsp.co.uk/complimentary-gartner-download) - [ISG](https://www.dsp.co.uk/isg-oracle-report) - [Blogs](https://www.dsp.co.uk/blogs) - [Case Studies](https://www.dsp.co.uk/case-studies) - [Testimonials](https://www.dsp.co.uk/testimonials) - [Events](https://www.dsp.co.uk/events) - [Webinars](https://www.dsp.co.uk/webinars) - [Insights](https://www.dsp.co.uk/insights) - [Technical Resources](https://www.dsp.co.uk/technical-resources) ### DSP-Explorer acquires leading Oracle Applications Managed Services Provider, Claremont, to further extend its data management capabilities.   [![Read More](https://no-cache.hubspot.com/cta/default/3321273/c7e13c9c-65e1-46e7-8541-8da86a4fc305.png)](https://cta-redirect.hubspot.com/cta/redirect/3321273/c7e13c9c-65e1-46e7-8541-8da86a4fc305) #### Resources - [Blogs](https://www.dsp.co.uk/blogs) - [Webinars](https://www.dsp.co.uk/webinars) - [Case Studies](https://www.dsp.co.uk/case-studies) - [Testimonials](https://www.dsp.co.uk/testimonials) - About - [About DSP](https://www.dsp.co.uk/about) - [What We Do](https://www.dsp.co.uk/what-we-do) - [DSP Group Board ](https://www.dsp.co.uk/dsp-group-board) - [Careers](https://www.dsp.co.uk/careers) - [Project Harar](https://www.dsp.co.uk/project-harar) - [Customer Area](https://www.dsp.co.uk/contact-us) - [Customer Support Portal](https://dspgroup-ism.ivanticloud.com/) - [Customer Resources](https://www.dsp.co.uk/welcome) - [Contact us](https://www.dsp.co.uk/contact-us) # Exporting data Using APEX_DATA_EXPORT [ Joe Marley ](https://content.dsp.co.uk/author/joe-marley) 07-Nov-2023 15:48:39 ### Contents [03-May-2023 14:41:50 Exporting APEX Applications using SQLcl ](https://content.dsp.co.uk/exporting-apex-applications-using-sqlcl) [23-Aug-2023 14:24:34 Exporting Custom Interactive Reports via REST ](https://content.dsp.co.uk/exporting-custom-interactive-reports-via-rest) [27-Mar-2025 11:10:02 Oracle APEX Support for Hierarchical REST Source Data Profiles ](https://content.dsp.co.uk/oracle-apex-support-for-hierarchical-rest-source-data-profiles) ## What is APEX_DATA_EXPORT APEX_DATA_EXPORT was introduced along with APEX_REGION.EXPORT_DATA in [APEX](https://www.dsp.co.uk/oracle-apex-services) 20.2, to allow developers to easily extract and download data. APEX_DATA_EXPORT allows various file formats to be exported, using SQL queries of varying complexity, to output data using only a few commands. In this blog, I will examine the process and cover the functionality that allows you to customise your output file, e.g. determining which columns to show/adding highlighting etc. ## Constants and Data Types There is an array of constants in APEX_DATA_EXPORT to aid in creating your code. There are export format constants such as c_format_xml or c_format_html. Alignment constants are used in the add_column, add_column_group and get_print_config methods. In the download procedure (mentioned further down), there are the constants c_attachment constant and c_inline. Lastly, there is an array of constants to set the printing configuration for your file, such as fonts or sizings. All constants can be found [here](https://docs.oracle.com/en/database/oracle/apex/23.1/aeapi/APEX_DATA_EXPORT-Global-Constants.html). The datatypes associated with the procedure can be found [here for reference](https://docs.oracle.com/en/database/oracle/apex/23.1/aeapi/APEX_DATA_EXPORT-Data-Types.html). ## Basic Example Say, for example, it was requested that there would be a button on an [APEX](https://www.dsp.co.uk/oracle-apex-services) page that printed the name, title and contact type of all contacts in the EBA_CUST_CONTACTS table; you would use code like the below: ``` CREATE OR REPLACE PACKAGE BODY "APEX_DATA_EXPORT_DEMO" AS   PROCEDURE download_contacts AS       l_context apex_exec.t_context;       l_export apex_data_export.t_export;   BEGIN       l_context := apex_exec.open_query_context(p_location => apex_exec.c_location_local_db, p_sql_query => 'SELECT name, title, company FROM eba_cust_contacts;'       );       l_export := apex_data_export.export(p_context => l_context, p_format => apex_data_export.c_format_pdf, p_file_name => 'Customer_Contacts'       );       apex_exec.close(l_context);       apex_data_export.download(p_export => l_export);   EXCEPTION       WHEN OTHERS THEN           apex_exec.close(l_context);           RAISE;   END;END "APEX_DATA_EXPORT_DEMO"; ``` In a basic APEX page, you could have a button that submits the page where a process calls the download_contacts procedure. This should then show the download dialog for your filesystem and allow you to download a PDF, which will look something like the below: [![Exporting data Using APEX_DATA_EXPORT](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%201.jpg?width=1788&height=351&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%201.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%201.jpg) ## Additional APEX_DATA_EXPORT procedures The APEX_DATA_EXPORT package has functionality that allows you to dynamically select the columns you will want in the export using ADD_COLUMN: ``` PROCEDURE ADD_COLUMN (  p_columns             IN OUT NOCOPY t_columns,  p_name                IN            apex_exec.t_column_name,  p_heading             IN            VARCHAR2                  DEFAULT NULL,  p_format_mask         IN            VARCHAR2                  DEFAULT NULL,  p_heading_alignment   IN            t_alignment               DEFAULT NULL,  p_value_alignment     IN            t_alignment               DEFAULT NULL,  p_width               IN            NUMBER                    DEFAULT NULL,  p_is_column_break     IN            BOOLEAN                   DEFAULT FALSE,  p_is_frozen           IN            BOOLEAN                   DEFAULT FALSE,  p_column_group_idx    IN            PLS_INTEGER               DEFAULT NULL ); ``` If column groupings are required, you can do this via the ADD_COLUMN_GROUP procedure: ``` PROCEDURE ADD_COLUMN_GROUP (    p_column_groups     IN OUT NOCOPY   t_column_groups,    p_idx               OUT             PLS_INTEGER,    p_name              IN              VARCHAR2,    p_alignment         IN              t_alignment         DEFAULT c_align_center,    p_parent_group_idx  IN              PLS_INTEGER         DEFAULT NULL ); ``` Aggregations can be achieved quite easily using the ADD_AGGREGATE procedure: ``` PROCEDURE ADD_AGGREGATE(  p_aggregates            IN OUT NOCOPY t_aggregates,  p_label                 IN            t_label,  p_format_mask           IN            VARCHAR2                  DEFAULT NULL,  p_display_column        IN            apex_exec.t_column_name,  p_value_column          IN            apex_exec.t_column_name,  p_overall_label         IN            t_label                   DEFAULT NULL,  p_overall_value_column  IN            apex_exec.t_column_name   DEFAULT NULL ); ``` Highlighting can be done using the below procedure: ``` PROCEDURE ADD_HIGHLIGHT (  p_highlights        IN OUT NOCOPY t_highlights,  p_id                IN            pls_integer,  p_value_column      IN            apex_exec.t_column_name,  p_display_column    IN            apex_exec.t_column_name DEFAULT NULL,  p_text_color        IN            t_color                 DEFAULT NULL,  p_background_color  IN            t_color                 DEFAULT NULL ); ``` So, say, for example, it was requested to print a report with the following criteria: - display all orders by customer, showing the order ID, order date, customer ID and order status with column headings included - aggregate the number of orders per customer - highlight any orders that are not complete with a different highlight for specific statuses The following procedure should produce a report that meets the requested criteria. The column names in the select statement and how they are used in the different procedures should give an idea of their functionality. ``` PROCEDURE download_orders AS      l_aggregates      apex_data_export.t_aggregates;      l_columns         apex_data_export.t_columns;      l_context         apex_exec.t_context;      l_export          apex_data_export.t_export;          l_column_groups   apex_data_export.t_column_groups;        l_order_det_idx   pls_integer;      l_highlights      apex_data_export.t_highlights;    BEGIN        l_context := apex_exec.open_query_context(          p_location    => apex_exec.c_location_local_db,          p_sql_query   => 'SELECT                              customer_id,                              order_id,                              order_datetime,                              order_status,                              COUNT(order_id) OVER(PARTITION BY customer_id) AS ORDERS_PER_CUSTOMER,                              COUNT(order_id) OVER() AS TOTAL_ORDERS,                              CASE WHEN ORDER_STATUS = ''COMPLETE'' THEN 1                                   WHEN ORDER_STATUS = ''OPEN'' THEN 2                                   WHEN ORDER_STATUS = ''REFUNDED'' THEN 3                                   WHEN ORDER_STATUS = ''CANCELLED'' THEN 4                              END as ORDER_STATUS_HIGHLIGHT                            FROM                              orders                            ORDER BY                              customer_id;' );        --COMPLETE        apex_data_export.add_highlight(          p_highlights          => l_highlights,          p_id                  => 1,          p_value_column        => 'ORDER_STATUS_HIGHLIGHT',          p_display_column      => 'ORDER_STATUS',          p_background_color    => '#7AFF33',          p_text_color          => '#03021D');        --OPEN        apex_data_export.add_highlight(          p_highlights          => l_highlights,          p_id                  => 2,          p_value_column        => 'ORDER_STATUS_HIGHLIGHT',          p_display_column      => 'ORDER_STATUS',          p_background_color    => '#FF3C33',          p_text_color          => '#03021D' );        --REFUNDED        apex_data_export.add_highlight(          p_highlights          => l_highlights,          p_id                  => 3,          p_value_column        => 'ORDER_STATUS_HIGHLIGHT',          p_display_column      => 'ORDER_STATUS',          p_background_color    => '#3F33FF',          p_text_color          => '#03021D' );        --CANCELLED        apex_data_export.add_highlight(          p_highlights          => l_highlights,          p_id                  => 4,          p_value_column        => 'ORDER_STATUS_HIGHLIGHT',          p_display_column      => 'ORDER_STATUS',          p_background_color    => '#FFC133',          p_text_color          => '#03021D' );         apex_data_export.add_aggregate(            p_aggregates              => l_aggregates,            p_label                   => 'Total Customer Orders:',            p_display_column          => 'ORDER_ID',            p_value_column            => 'ORDERS_PER_CUSTOMER',            p_overall_label           => 'Total Orders:',            p_overall_value_column    => 'TOTAL_ORDERS' );        --Adding Order Details Group        apex_data_export.add_column_group(        p_column_groups   => l_column_groups,        p_idx             => l_order_det_idx,        p_name            => 'Order Details' );         --Including a column break on customer ID         apex_data_export.add_column(           p_columns => l_columns,           p_name => 'CUSTOMER_ID',           p_heading => 'Customer ID',          p_is_column_break => true );          apex_data_export.add_column(           p_columns => l_columns,          p_name => 'ORDER_ID',           p_heading => 'Order ID');         apex_data_export.add_column(           p_columns => l_columns,           p_name => 'ORDER_DATETIME',           p_heading => 'Order Date',          p_column_group_idx    => l_order_det_idx);         apex_data_export.add_column(          p_columns => l_columns,           p_name => 'ORDER_STATUS',           p_heading => 'Order Status',          p_column_group_idx    => l_order_det_idx);         l_export := apex_data_export.export (          p_context       => l_context,          p_format        => apex_data_export.c_format_pdf,          p_columns       => l_columns,          p_aggregates    => l_aggregates,          p_column_groups => l_column_groups,          p_highlights    => l_highlights,          p_file_name     => 'Customer_Orders_Run_On_' || TO_CHAR(SYSDATE, 'YYYY_MM_DD'));         apex_exec.close( l_context );         apex_data_export.download( p_export => l_export );     EXCEPTION      WHEN others THEN          apex_exec.close( l_context );          raise;    END; ``` Producing the below PDF: [![Exporting data Using APEX_DATA_EXPORT](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%202.jpg?width=1240&height=775&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%202.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%202.jpg) If you want row highlighting, leave out the p_display_column parameter from the apex_data_export.add_highlight code: ## ![Exporting data Using APEX_DATA_EXPORT - 3](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%203.jpg?width=1812&height=265&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%203.jpg) Passing in parameters As part of setting the query context to be used in the apex export, parameters can be set using the ADD_PARAMETER procedure, which is part of the APEX_EXEC package: ``` PROCEDURE download_orders_parameter_example (      in_cust_id in  orders.customer_id%type) AS       l_aggregates      apex_data_export.t_aggregates;      l_columns         apex_data_export.t_columns;      l_context         apex_exec.t_context;      l_export          apex_data_export.t_export;          l_column_groups   apex_data_export.t_column_groups;        l_order_det_idx   pls_integer;      l_highlights      apex_data_export.t_highlights;      l_parameters      apex_exec.t_parameters;    BEGIN         --Adding customer ID parameter                apex_exec.add_parameter( l_parameters, 'CUST_ID', in_cust_id);         l_context := apex_exec.open_query_context(          p_location    => apex_exec.c_location_local_db,          p_sql_query   => 'SELECT                              customer_id,                              order_id,                              order_datetime,                              order_status,                              COUNT(order_id) OVER(PARTITION BY customer_id) AS ORDERS_PER_CUSTOMER,                              COUNT(order_id) OVER() AS TOTAL_ORDERS,                              CASE WHEN ORDER_STATUS = ''COMPLETE'' THEN 1                                   WHEN ORDER_STATUS = ''OPEN'' THEN 2                                   WHEN ORDER_STATUS = ''REFUNDED'' THEN 3                                   WHEN ORDER_STATUS = ''CANCELLED'' THEN 4                              END as ORDER_STATUS_HIGHLIGHT                            FROM                              orders                            WHERE customer_id = :CUST_ID                            ORDER BY                              customer_id;',          p_sql_parameters  => l_parameters);                 --COMPLETE        apex_data_export.add_highlight(          p_highlights          => l_highlights,          p_id                  => 1,          p_value_column        => 'ORDER_STATUS_HIGHLIGHT',          p_display_column      => 'ORDER_STATUS',          p_background_color    => '#7AFF33',          p_text_color          => '#03021D');        --OPEN        apex_data_export.add_highlight(          p_highlights          => l_highlights,          p_id                  => 2,          p_value_column        => 'ORDER_STATUS_HIGHLIGHT',          p_display_column      => 'ORDER_STATUS',          p_background_color    => '#FF3C33',          p_text_color          => '#03021D' );        --REFUNDED        apex_data_export.add_highlight(          p_highlights          => l_highlights,          p_id                  => 3,          p_value_column        => 'ORDER_STATUS_HIGHLIGHT',          p_display_column      => 'ORDER_STATUS',          p_background_color    => '#3F33FF',          p_text_color          => '#03021D' );        --CANCELLED        apex_data_export.add_highlight(          p_highlights          => l_highlights,          p_id                  => 4,          p_value_column        => 'ORDER_STATUS_HIGHLIGHT',          p_display_column      => 'ORDER_STATUS',          p_background_color    => '#FFC133',          p_text_color          => '#03021D' );         apex_data_export.add_aggregate(            p_aggregates              => l_aggregates,            p_label                   => 'Total Customer Orders:',            p_display_column          => 'ORDER_ID',            p_value_column            => 'ORDERS_PER_CUSTOMER',            p_overall_label           => 'Total Orders:',            p_overall_value_column    => 'TOTAL_ORDERS' );         --Adding Order Details Group        apex_data_export.add_column_group(        p_column_groups   => l_column_groups,        p_idx             => l_order_det_idx,        p_name            => 'Order Details' );         --Including a column break on customer ID         apex_data_export.add_column(           p_columns => l_columns,           p_name => 'CUSTOMER_ID',           p_heading => 'Customer ID',          p_is_column_break => true );          apex_data_export.add_column(           p_columns => l_columns,          p_name => 'ORDER_ID',           p_heading => 'Order ID');         apex_data_export.add_column(           p_columns => l_columns,           p_name => 'ORDER_DATETIME',           p_heading => 'Order Date',          p_column_group_idx    => l_order_det_idx);         apex_data_export.add_column(          p_columns => l_columns,           p_name => 'ORDER_STATUS',           p_heading => 'Order Status',          p_column_group_idx    => l_order_det_idx);         l_export := apex_data_export.export (          p_context         => l_context,          p_format          => apex_data_export.c_format_pdf,          p_columns         => l_columns,          p_aggregates      => l_aggregates,          p_column_groups   => l_column_groups,          p_highlights      => l_highlights,          p_file_name       => 'Customer_Orders_Run_On_' || TO_CHAR(SYSDATE, 'YYYY_MM_DD'));         apex_exec.close( l_context );         apex_data_export.download( p_export => l_export );     EXCEPTION      WHEN others THEN          apex_exec.close( l_context );          raise;    END; ``` Parameters can easily be set using Oracle page items for user input and pass them in during the [Oracle APEX](https://www.dsp.co.uk/oracle-apex-services) page process code, i.e., ``` begin  APEX_DATA_EXPORT_DEMO.download_orders_parameter_example(:P2_CUSTOMER_ID);end; ``` Output: [![Exporting data Using APEX_DATA_EXPORT](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%204.jpg?width=1788&height=559&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%204.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%204.jpg) It would certainly be beneficial to have a look at the [APEX_EXEC documentation here](https://docs.oracle.com/en/database/oracle/apex/23.1/aeapi/APEX_EXEC.html) for building export context. ## Storing files in the database This is also quite easy to do with the power of APEX_DATA_EXPORT. All you need to do is replace your apex_data_export.download code with something like: ``` insert into reports (report,filename,run_on) values (l_export.content_blob , l_export.file_name,sysdate); ``` The export return type apex_data_export.export is: ``` type t_export is record (    file_name    varchar2(32767),    format       t_format,    mime_type    varchar2(32767),    row_count    number,    as_clob      boolean,    content_blob blob,    content_clob clob ); ``` ## Additional Notes / Personal Picks ### Changing report type This can easily be implemented using PL/SQL logic, and with APEX, the user can select their preferred report type using page items, i.e., a radio group: [![Exporting data Using APEX_DATA_EXPORT - 5](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%205.jpg?width=1090&height=253&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%205.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%205.jpg) ``` --variable declared of t_format typel_report_format   apex_data_export.t_format; --Setting formatIF upper(in_report_type) = 'P' THEN            l_report_format := apex_data_export.c_format_pdf;        ELSIF upper(in_report_type) = 'E' THEN            l_report_format := apex_data_export.c_format_xlsx;        ELSIF upper(in_report_type) = 'C' THEN            l_report_format := apex_data_export.c_format_csv;        ELSIF upper(in_report_type) = 'J' THEN            l_report_format := apex_data_export.c_format_json;        ELSIF upper(in_report_type) = 'X' THEN            l_report_format := apex_data_export.c_format_xml;         ELSIF upper(in_report_type) = 'H' THEN            l_report_format := apex_data_export.c_format_html;                                       END IF; --l_report_format used in export procedurel_export := apex_data_export.export(          p_context => l_context,           p_format => l_report_format,           p_file_name => 'Customer_Contacts'); ``` ### XML and JSON files are supported: [![Exporting data Using APEX_DATA_EXPORT](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%206.jpg?width=727&height=786&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%206.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%206.jpg) [![Exporting data Using APEX_DATA_EXPORT](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%207.jpg?width=523&height=729&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%207.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%207.jpg) ### Supplemental Text This can be used to add text to the top of the first page of the generated report and is done simply with another parameter on the export procedure: *p_supplemental_text => 'Customer Orders');* ### [![Exporting data Using APEX_DATA_EXPORT](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%208.jpg?width=1801&height=318&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%208.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%208.jpg)GET_PRINT_CONFIG This procedure is used to style your data export. There are a lot of parameters for this procedure, which can be found [here](https://docs.oracle.com/en/database/oracle/apex/23.1/aeapi/APEX_DATA_EXPORT-GET_PRINT_CONFIG-Procedure.html). To give an example of some parameters, I have added the below code to my customer order's download: ``` --print config variablel_print_config    apex_data_export.t_print_config; --setting print configurationsl_print_config := apex_data_export.get_print_config(          p_orientation       => apex_data_export.c_orientation_landscape,          p_border_width      => 2,          p_page_footer       => 'Footer Text',          p_body_font_family  => apex_data_export.c_font_family_times                       ); --add the variable to the export call:p_print_config    => l_print_config, ``` [![Exporting data Using APEX_DATA_EXPORT](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%209.jpg?width=1714&height=319&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%209.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%209.jpg) ### Content-Disposition Content-Disposition is part of the download procedure: ``` PROCEDURE DOWNLOAD (    p_export                IN OUT NOCOPY t_export,    p_content_disposition   IN t_content_disposition    DEFAULT c_attachment,    p_stop_apex_engine      IN BOOLEAN                  DEFAULT TRUE ); ``` Using the below constants: - c_attachment constant t_content_disposition := 'attachment'; - This will open a download dialog - c_inline constant t_content_disposition := 'inline'; - This will open your document in your current browser window ## Additional Benefits - The functionality available within the Oracle database means things like report scheduling or sending reports in emails can be easily carried out - Running multiple reports quickly - When downloading reports, code can be called outside of an APEX app, i.e., using ORDS, which can be done using APEX_SESSION.CREATE_SESSION and using 'p_stop_apex_engine;' in your download procedure call: ```   PROCEDURE DOWNLOAD (      p_export                IN OUT NOCOPY t_export,      p_content_disposition   IN t_content_disposition    DEFAULT c_attachment,      p_stop_apex_engine      IN BOOLEAN                  DEFAULT TRUE ); ``` ## Troubleshooting - If no download occurs, but the code executes without any errors, it may be that you are running the procedure through a dynamic action rather than a process - Ensure you have set the below page setting to 'Always' or that you are executing the download code through a 'before header' process. [![Exporting data Using APEX_DATA_EXPORT](https://content.dsp.co.uk/hs-fs/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%2010.jpg?width=1114&height=355&name=Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%2010.jpg)](https://content.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%2010.jpg) If neither option suits your current page, you could always download through a modal popup window. Feel free to reference the [official Oracle Docs](https://docs.oracle.com/en/database/oracle/apex/23.1/aeapi/APEX_DATA_EXPORT.html) for more info. ## Other options for exporting data Check out the below blogs written by the DSP team covering this area, including the export of reports via REST or printing from Oracle APEX report regions displayed on the page: - [Exporting Custom Interactive Reports via REST](https://content.dsp.co.uk/apex/exporting-custom-interactive-reports-via-rest) - [APEX 20.2 New Reporting Features](https://content.dsp.co.uk/apex/apex-20-2-new-reporting-features) ## Summary APEX_DATA_EXPORT is an asset for any APEX user seeking to export data from their applications. Its powerful capabilities, user-friendly interface, extensive customisation options, and support for large datasets make it an indispensable tool for analysis, reporting, and backup tasks. Whether you are a business analyst, a data scientist, or an IT professional, APEX_DATA_EXPORT simplifies the data export process, empowering you to unlock the full potential of your application data. Also, on the topic of report generation, there were teasers of a JSON to PDF document generator called DocGen. This was recently shown at Oracle Cloudworld 2023. Depending on the complexity of the templates allowed, this could be a game changer for generating reports through Oracle. For more information, check out our [APEX services](https://www.dsp.co.uk/oracle-apex-services), or [get in touch](mailto:enquiries@dsp.co.uk) with our experts today. If you liked this blog, check out our other [APEX blogs here](https://content.dsp.co.uk/apex). Remember to [follow us on LinkedIn](https://www.linkedin.com/company/dsp-development-apex). We publish insight blogs on the latest technology developments every week. [Oracle](https://content.dsp.co.uk/topic/oracle) [Reporting](https://content.dsp.co.uk/topic/reporting) [APEX](https://content.dsp.co.uk/topic/apex) [Oracle APEX](https://content.dsp.co.uk/topic/oracle-apex) [03-May-2023 14:41:50 Exporting APEX Applications using SQLcl ](https://content.dsp.co.uk/exporting-apex-applications-using-sqlcl) [23-Aug-2023 14:24:34 Exporting Custom Interactive Reports via REST ](https://content.dsp.co.uk/exporting-custom-interactive-reports-via-rest) [27-Mar-2025 11:10:02 Oracle APEX Support for Hierarchical REST Source Data Profiles ](https://content.dsp.co.uk/oracle-apex-support-for-hierarchical-rest-source-data-profiles) [Previous Oracle EBS Archive Managed Service: Simplify Your Data Management ](https://content.dsp.co.uk/oracle-ebs-archive-managed-service-simplify-your-data-management) [Next Is an Oracle Unlimited License Agreement (ULA) the right choice for my organisation? ](https://content.dsp.co.uk/is-an-oracle-unlimited-license-agreement-ula-the-right-choice-for-my-organisation) #### Contact Sales Enquiries [+44 (0) 203 880 1686](tel:+4420%2038801686) Technical Support +44 (0) 330 058 8367 [enquiries@dsp.co.uk](mailto:enquiries@dsp.co.uk) #### Follow us #### Office Locations [**London (Head Office)**](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,198m/data=!3m2!1e3!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MTAwOC4wIKXMDSoASAFQAw%3D%3D) [Chancery House](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [53-64 Chancery Lane](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [London](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [WC2A 1QS](https://www.google.com/maps/place/Fora+-+Chancery+House/@51.5174484,-0.1139686,19z/data=!3m1!5s0x48761adea956397d:0xb7585122cbe86fc0!4m6!3m5!1s0x48761b4b75bf77fb:0x97c4f0ad56af6d23!8m2!3d51.5174586!4d-0.1129066!16s%2Fg%2F11h_cy7xk5?entry=ttu&g_ep=EgoyMDI1MDkyOS4wIKXMDSoASAFQAw%3D%3D) [**Leeds** Richmond House, Lawnswood Business Park, Leeds, LS16 6QY](https://www.google.com/maps/dir/53.8405223,-1.6062773/DSP-Explorer/@53.8404788,-1.6061783,17z/data=!4m9!4m8!1m1!4e1!1m5!1m1!1s0x4879591e5d204ac9:0x4e41b926d72adca!2m2!1d-1.6064847!2d53.8404842) [**Derby**](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [Cubo Pride Park,](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [1 Pride Place](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [Derby](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) [DE24 8QR](https://www.google.com/maps/place/Cubo+Pride+Park/@52.9142877,-1.4558952,331m/data=!3m2!1e3!5s0x4879f10728c1eea1:0x877f85530d38bf6!4m15!1m8!3m7!1s0x4879f1072b0e2ebb:0x7c717035ff049b12!2sPride+Pl,+Derby+DE24+8QR!3b1!8m2!3d52.913692!4d-1.4543743!16s%2Fg%2F1th7mf0r!3m5!1s0x4879f16809382b33:0x6fccc05b8500d970!8m2!3d52.914146!4d-1.4544105!16s%2Fg%2F11w4j8v8z2?entry=ttu&g_ep=EgoyMDI1MDYyMi4wIKXMDSoASAFQAw%3D%3D) ** [Dublin](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu)** [Inniscarra,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Main Street,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Rathcoole,](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) [Dublin](https://www.google.com/maps/search/Inniscarra,+Main+Street,+Rathcoole,+Dublin/@53.2810032,-6.4764674,17z/data=!3m1!4b1?entry=ttu) #### About Us DSP is a Data Management and Cloud Platform MSP that delivers enterprise grade support & consulting services for Oracle, Microsoft and Multi-Cloud technologies. ![Oracle Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/Oracle-logos/Colour/o-prtnr-cmyk-250.png) ![Microsoft Partner](https://content.dsp.co.uk/hubfs/Microsoft%20partner%20logos%20-%20July%202023/mic-sol-part-colour@200x.png) ![Google Cloud Partner](https://content.dsp.co.uk/hubfs/logo-2021/Partner-logos/GCP-Logos/Google-Cloud-Partner%20(1).svg) Registered Office: 30 City Road, London, EC1Y 2AB. Company Registration Number: 03898451 ```json { "@context" : "https://schema.org", "@type" : "BlogPosting", "dateModified" : "2024-10-01", "datePublished" : "2024-10-01", "headline" : "Exporting data Using APEX_DATA_EXPORT", "image" : [ "https://www.dsp.co.uk/hubfs/Exporting%20data%20Using%20APEX_DATA_EXPORT%20-%20thumbnail.jpg" ], "mainEntityOfPage" : { "@id" : "https://content.dsp.co.uk/exporting-data-using-apex_data_export", "@type" : "WebPage" } } ```