APEX_DATA_EXPORT # X-FACTOR ORACLE APEX

The APEX_DATA_EXPORT package contains the implementation to export data from Oracle Application Express. Supported filetypes include: PDF, XLSX, HTML, CSV, XML and JSON. Use the EXPORT function to pass a query context from the APEX_EXEC package and return the t_export type, which includes the contents in a LOB.

 DECLARE

    l_context         apex_exec.t_context; 
    l_print_config    apex_data_export.t_print_config;
    l_export          apex_data_export.t_export;
    l_report_format   apex_data_export.t_format;
BEGIN
    l_context := apex_exec.open_query_context(
        p_location    => apex_exec.c_location_local_db,
        p_sql_query   => 'select * from FOS_emp' );

    l_print_config := apex_data_export.get_print_config(
        p_orientation     => apex_data_export.c_orientation_portrait,
        p_border_width    => 2 );
    
    IF :P4_REPORT_FORMAT = 'P' THEN
        l_report_format := apex_data_export.c_format_pdf;
    elsIF :P4_REPORT_FORMAT = 'E' THEN
        l_report_format := apex_data_export.c_format_xlsx;
    elsIF :P4_REPORT_FORMAT = 'C' THEN
        l_report_format := apex_data_export.c_format_csv;
    elsIF :P4_REPORT_FORMAT = 'J' THEN
        l_report_format := apex_data_export.c_format_json;
    elsIF :P4_REPORT_FORMAT = 'X' THEN
        l_report_format := apex_data_export.c_format_xml; 
    elsIF :P4_REPORT_FORMAT = 'H' THEN
        l_report_format := apex_data_export.c_format_html;                               
    END IF;
    
    l_export := apex_data_export.export (
        p_context         => l_context,
        p_print_config    => l_print_config,
        p_format          => l_report_format);

    apex_exec.close( l_context );

    apex_data_export.download( p_export => l_export );

EXCEPTION
    when others THEN
        apex_exec.close( l_context );
        raise;
END;