How to Export Data to PDF and Excel and Load Files Using APEX_DATA_EXPORT

Report downloads in Oracle APEX, the CSV, Excel, PDF, and HTML files users get from an interactive report, are produced by one engine: APEX_DATA_EXPORT. You can call it yourself from PL/SQL to build files from any query, with headings, format masks, column groups, subtotals, highlighted cells, and PDF page settings, and then send them to the browser or store them. Two related packages cover the other direction: APEX_DATA_LOADING loads a file into a table through a Data Load Definition, and APEX_REST_SOURCE_SYNC keeps a local table in step with a REST Data Source.

This guide covers all three with tested examples and their real output.

Quick Reference

How to Run These Examples

All three packages need an APEX session, because query contexts, Data Load Definitions, and REST Data Sources belong to an application. In a script, create one first with APEX_SESSION.CREATE_SESSION for application 200, page 1, as shown in the guide to creating APEX sessions and managing session state from PL/SQL. Run the examples as your workspace schema with server output switched on. The output under each example is exactly what the database printed in Oracle APEX 26.1.

The export examples read the orb_products and orb_orders tables of the Orbit Outfitters sample schema, from the orb_tables repository on GitHub. The loading example uses a Data Load Definition with the static ID price-list that merges SKU and UNIT_PRICE into ORB_PRODUCTS, and the sync examples use a REST Data Source with the static ID city-geocoding, pointing to the Open-Meteo geocoding API and set to synchronize into a table named LAB_CITIES with the type Replace. Create equivalents in your own application to follow along.

Every export starts from an APEX_EXEC query context, covered in the guide to querying data sources from PL/SQL with APEX_EXEC.

Exporting: APEX_DATA_EXPORT

EXPORT and ADD_COLUMN

EXPORT reads a query context and returns a t_export record with file_name (the extension added), format, mime_type, row_count, as_clob, and the content in content_blob, or in content_clob when p_as_clob is true for text formats. The formats are apex_data_export.c_format_csv, c_format_html, c_format_json, c_format_xml, c_format_xlsx, and c_format_pdf, plus c_format_pjson and c_format_pxml, the documents APEX sends to a print server. ADD_COLUMN picks the columns to export and sets each one's heading, format mask, alignment, width, column group, and whether it breaks the rows into groups; without it, every column is exported.

Syntax:

apex_data_export.export(p_context in apex_exec.t_context, p_format in t_format, p_as_clob in boolean default false,
    p_columns in t_columns default c_empty_columns, p_column_groups in t_column_groups default c_empty_column_groups,
    p_aggregates in t_aggregates default c_empty_aggregates, p_highlights in t_highlights default c_empty_highlights,
    p_file_name in varchar2 default null, p_print_config in t_print_config default c_empty_print_config,
    p_page_header in varchar2 default null, p_page_footer in varchar2 default null,
    p_supplemental_text in varchar2 default null, p_csv_enclosed_by in varchar2 default null,
    p_csv_separator in varchar2 default null, p_pdf_accessible in boolean default null,
    p_xml_include_declaration in boolean default null) return t_export
apex_data_export.add_column(p_columns in out nocopy t_columns, p_name in varchar2, p_heading in varchar2 default null,
    p_format_mask in varchar2 default null, p_heading_alignment in varchar2 default null,
    p_value_alignment in varchar2 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)

This example needs a session of application 200, page 1.

Example:

declare
    l_context apex_exec.t_context;
    l_columns apex_data_export.t_columns;
    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 => q'~select sku, product_name, unit_price, launch_date
                                         from orb_products where sku like 'TNT-100%' order by sku
                                        fetch first 3 rows only~');

    -- which columns, with headings and format masks
    apex_data_export.add_column(p_columns => l_columns, p_name => 'SKU');
    apex_data_export.add_column(p_columns => l_columns, p_name => 'PRODUCT_NAME', p_heading => 'Product');
    apex_data_export.add_column(p_columns => l_columns, p_name => 'UNIT_PRICE',   p_heading => 'Price',
                                p_format_mask => 'FML999G990D00');

    l_export := apex_data_export.export(p_context   => l_context,
                                        p_format    => apex_data_export.c_format_csv,
                                        p_columns   => l_columns,
                                        p_file_name => 'tents',
                                        p_as_clob   => true);
    apex_exec.close(l_context);

    dbms_output.put_line(l_export.file_name || ' - ' || l_export.mime_type || ' - ' || l_export.row_count || ' rows');
    dbms_output.put_line(l_export.content_clob);
end;
/

Output:

tents.csv - text/csv - 3 rows
SKU,Product,Price
TNT-1001,Trailblazer 1-Person Tent,$239.99
TNT-1002,Trailblazer 2-Person Tent,$274.99
TNT-1003,Basecamp 4-Person Tent,$204.99

Only the three chosen columns were exported, with the headings and currency mask applied, and launch_date was left out. The next example runs every format against the same small query:

Example:

declare
    l_context apex_exec.t_context;
    l_export  apex_data_export.t_export;
begin
    for f in (select column_value as format
                from table(apex_t_varchar2('CSV', 'HTML', 'JSON', 'PJSON', 'XML', 'PXML', 'XLSX', 'PDF'))) loop
        l_context := apex_exec.open_query_context(
                         p_location  => apex_exec.c_location_local_db,
                         p_sql_query => 'select sku, unit_price from orb_products fetch first 2 rows only');
        begin
            l_export := apex_data_export.export(p_context => l_context, p_format => f.format);
            dbms_output.put_line(rpad(f.format, 6) || rpad(l_export.file_name, 12) || rpad(l_export.mime_type, 66)
                || dbms_lob.getlength(l_export.content_blob) || ' bytes');
        exception when others then
            dbms_output.put_line(rpad(f.format, 6) || regexp_replace(sqlerrm, 'ORA-\d+: '));
        end;
        apex_exec.close(l_context);
    end loop;
end;
/

Output:

CSV   export.csv  text/csv                                                          47 bytes
HTML  export.html text/html                                                         3574 bytes
JSON  export.json application/json                                                  101 bytes
PJSON export.json application/json                                                  1348 bytes
XML   export.xml  text/xml                                                          212 bytes
PXML  export.xml  text/xml                                                          255 bytes
XLSX  export.xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet 4277 bytes
PDF   export.pdf  application/pdf                                                   1246 bytes

PDF is built in and needs no print server, which makes this the easiest way to produce a simple PDF listing from PL/SQL. For richer, designed PDF documents, see creating PDF reports in Oracle APEX.

ADD_COLUMN_GROUP, ADD_AGGREGATE, and ADD_HIGHLIGHT

ADD_COLUMN_GROUP adds a heading over several columns and returns its index in p_idx, for ADD_COLUMN's p_column_group_idx. ADD_AGGREGATE prints a value below each group and at the end; the value comes from a column of the query (p_value_column, p_overall_value_column), usually an analytic sum() over (...), and appears under p_display_column. ADD_HIGHLIGHT colors a cell or row where the column p_value_column is not null. They affect HTML, PDF, XLSX, and the print formats.

Syntax:

apex_data_export.add_column_group(p_column_groups in out nocopy t_column_groups, p_idx out pls_integer,
    p_name in varchar2, p_alignment in varchar2 default c_align_center, p_parent_group_idx in pls_integer default null)
apex_data_export.add_aggregate(p_aggregates in out nocopy t_aggregates, p_label in varchar2, p_format_mask in varchar2 default null,
    p_display_column in varchar2, p_value_column in varchar2, p_overall_label in varchar2 default null,
    p_overall_value_column in varchar2 default null)
apex_data_export.add_highlight(p_highlights in out nocopy t_highlights, p_id in pls_integer, p_value_column in varchar2,
    p_display_column in varchar2 default null, p_text_color in varchar2 default null, p_background_color in varchar2 default null)

This example needs a session of application 200, page 1. It exports to PJSON so the output shows the metadata that drives subtotals and highlights.

Example:

declare
    l_context    apex_exec.t_context;
    l_columns    apex_data_export.t_columns;
    l_groups     apex_data_export.t_column_groups;
    l_aggregates apex_data_export.t_aggregates;
    l_highlights apex_data_export.t_highlights;
    l_export     apex_data_export.t_export;
    l_order_grp  pls_integer;
begin
    -- the query computes the values: break sums (per status) and the grand total,
    -- and a highlight flag, as an Interactive Report does
    l_context := apex_exec.open_query_context(
                     p_location  => apex_exec.c_location_local_db,
                     p_sql_query => q'~select status, order_number, order_total,
                                              sum(order_total) over (partition by status) as status_total,
                                              sum(order_total) over ()                    as grand_total,
                                              case when order_total > 1000 then 1 end      as big
                                         from orb_orders
                                        where order_id in (1, 2, 3, 4, 5, 6)
                                        order by status, order_number~');

    apex_data_export.add_column_group(p_column_groups => l_groups, p_idx => l_order_grp, p_name => 'Order');   -- returns the index
    apex_data_export.add_column(l_columns, p_name => 'STATUS', p_heading => 'Status', p_is_column_break => true);
    apex_data_export.add_column(l_columns, p_name => 'ORDER_NUMBER', p_heading => 'Number', p_column_group_idx => l_order_grp);
    apex_data_export.add_column(l_columns, p_name => 'ORDER_TOTAL',  p_heading => 'Total',  p_column_group_idx => l_order_grp,
                                p_format_mask => '999G990D00');

    apex_data_export.add_aggregate(p_aggregates => l_aggregates, p_label => 'Sum', p_format_mask => '999G990D00',
                                   p_display_column => 'ORDER_TOTAL', p_value_column => 'STATUS_TOTAL',
                                   p_overall_label => 'Total', p_overall_value_column => 'GRAND_TOTAL');
    apex_data_export.add_highlight(p_highlights => l_highlights, p_id => 1, p_value_column => 'BIG',
                                   p_display_column => 'ORDER_TOTAL', p_background_color => '#FFF3C4');

    l_export := apex_data_export.export(p_context => l_context, p_format => apex_data_export.c_format_pjson,
                                        p_columns => l_columns, p_column_groups => l_groups,
                                        p_aggregates => l_aggregates, p_highlights => l_highlights,
                                        p_as_clob => true);
    apex_exec.close(l_context);
    -- PJSON, the format APEX sends to a print server, shows the rows with their metadata
    dbms_output.put_line(json_object_t.parse(l_export.content_clob).get_array('rowset').to_clob);
end;
/

Output:

[{"order_number":"ORD-10001","order_total":35164.61,"apex$metadata":{"highlights":[1],"controlBreak":{"status":"CANCELLED"}}},{"apex$metadata":{"aggregates":{"1":35164.61}}},{"order_number":"ORD-10002","order_total":44.99,"apex$metadata":{"controlBreak":{"status":"DELIVERED"}}},{"order_number":"ORD-10003","order_total":1159.92,"apex$metadata":{"highlights":[1]}},{"order_number":"ORD-10004","order_total":34.99},{"order_number":"ORD-10005","order_total":39.99},{"order_number":"ORD-10006","order_total":649.95},{"apex$metadata":{"aggregates":{"1":1929.84}}},{"apex$metadata":{"overallAggregates":{"1":37094.45}}}]

The export does not calculate subtotals itself: the query supplies them through analytic functions, and ADD_AGGREGATE only says where to print them. The highlights came from the BIG flag column, which the query set for orders over 1,000.

GET_PRINT_CONFIG

Returns a t_print_config for PDF and print server output: page size and orientation (c_size_letter, c_size_a4, and others, with c_orientation_portrait or _landscape), page header and footer, and the fonts, colors, and borders of the header row and body. Units follow the paper size, so A4 switches to millimeters.

This example needs a session of application 200, page 1.

Example:

declare
    l_context apex_exec.t_context;
    l_config  apex_data_export.t_print_config;
    l_export  apex_data_export.t_export;
begin
    l_config := apex_data_export.get_print_config(
                    p_orientation           => apex_data_export.c_orientation_landscape,
                    p_paper_size            => apex_data_export.c_size_a4,
                    p_page_header           => 'ORBIT Outfitters - Tents',
                    p_header_bg_color       => '#1F3A5F',
                    p_header_font_color     => '#FFFFFF',
                    p_header_font_weight    => apex_data_export.c_font_weight_bold,
                    p_body_font_family      => apex_data_export.c_font_family_courier);

    l_context := apex_exec.open_query_context(
                     p_location  => apex_exec.c_location_local_db,
                     p_sql_query => q'~select sku, product_name from orb_products where sku like 'TNT%'~');
    l_export := apex_data_export.export(p_context => l_context, p_format => apex_data_export.c_format_pjson,
                                        p_print_config => l_config, p_as_clob => true);
    apex_exec.close(l_context);

    -- the settings a PDF export (or a print server) uses
    dbms_output.put_line(json_object_t.parse(l_export.content_clob).get_object('printConfig').to_clob);
end;
/

Output:

{"units":"MILLIMETERS","paperSize":"A4","width":297,"height":210,"orientation":"HORIZONTAL","pageHeader":"ORBIT Outfitters - Tents","pageHeaderFontColor":"#000000","pageHeaderFontFamily":"Helvetica","pageHeaderFontWeight":"normal","pageHeaderFontSize":"12","pageHeaderAlignment":"CENTER","pageFooterFontColor":"#000000","pageFooterFontFamily":"Helvetica","pageFooterFontWeight":"normal","pageFooterFontSize":"12","headerBgColor":"#1F3A5F","headerFontColor":"#FFFFFF","headerFontFamily":"Helvetica","headerFontWeight":"bold","headerFontSize":"10","bodyBgColor":"#FFFFFF","bodyFontColor":"#000000","bodyFontFamily":"Courier","bodyFontWeight":"normal","bodyFontSize":"10","borderWidth":0.5,"borderColor":"#666666"}

DOWNLOAD

Sends an export to the browser: the HTTP headers with the MIME type and file name, followed by the content, as an attachment or, with p_content_disposition set to apex_data_export.c_inline, for the browser to display. By default it then stops the APEX engine, like apex_application.stop_apex_engine, which ends the page process.

Syntax:

apex_data_export.download(p_export in out nocopy t_export, p_content_disposition in t_content_disposition default c_attachment,
    p_add_file_extension in boolean default true, p_stop_apex_engine in boolean default true)

This example needs a session of application 200, page 1. It sets up a fake web request so the response headers can be read back in a script, and keeps the engine running for that reason only.

Example:

declare
    l_context apex_exec.t_context;
    l_export  apex_data_export.t_export;
    l_page    htp.htbuf_arr;
    l_rows    integer := 999;
    l_name    owa.vc_arr;
    l_val     owa.vc_arr;
begin
    l_name(1) := 'REQUEST_CHARSET'; l_val(1) := 'AL32UTF8';     -- a web request, as ORDS sets it up
    owa.init_cgi_env(1, l_name, l_val);
    htp.init;

    l_context := apex_exec.open_query_context(
                     p_location  => apex_exec.c_location_local_db,
                     p_sql_query => 'select sku, unit_price from orb_products fetch first 2 rows only');
    l_export := apex_data_export.export(p_context => l_context, p_format => apex_data_export.c_format_csv,
                                        p_file_name => 'prices');
    apex_exec.close(l_context);

    -- in a page process, leave p_stop_apex_engine at its default (true)
    apex_data_export.download(p_export => l_export, p_stop_apex_engine => false);

    owa.get_page(l_page, l_rows);                 -- what the browser receives
    for i in 1 .. l_rows loop
        dbms_output.put(l_page(i));
    end loop;
    dbms_output.new_line;
end;
/

Output:

Content-Type:text/csv; charset=utf-8
X-Content-Type-Options:nosniff
X-Xss-Protection:1; mode=block
Referrer-Policy:strict-origin
Content-Security-Policy:default-src 'self' 'nonce-4t9CaVhMWa_6qFsWew1iJg'; script-src 'self' 'nonce-4t9CaVhMWa_6qFsWew1iJg' 'wasm-unsafe-eval'; connect-src 'self' https://elocation.oracle.com; img-src 'self' data: blob: https://elocation.oracle.com; font-src 'self' data:; worker-src 'self' blob:; object-src 'none'; frame-ancestors 'self';
Content-Length:47
Content-Disposition:attachment; filename="prices.csv"; filename*=utf-8''prices.csv

In a page process behind a Download button, EXPORT followed by DOWNLOAD is all you need. For older hand-built approaches, compare downloading CSV using a PL/SQL procedure and exporting data into Excel using PL/SQL; APEX_DATA_EXPORT replaces both with a few lines.

Loading Files: APEX_DATA_LOADING

LOAD_DATA and GET_FILE_PROFILE

LOAD_DATA loads a file, given as a BLOB or a CLOB of text, using a Data Load Definition of the application: its file profile, target table, and loading method (append, merge, or replace). It returns a t_data_load_result with processed_rows and error_rows. How errors are handled is a property of the definition; with Abort, the first bad row raises an exception. GET_FILE_PROFILE returns the definition's profile in the same JSON format APEX_DATA_PARSER uses.

Syntax:

apex_data_loading.load_data(p_application_id in number default {current}, p_static_id in varchar2,
    p_data_to_load in blob | clob, p_xlsx_sheet_name in varchar2 default null) return t_data_load_result
apex_data_loading.get_file_profile(p_application_id in number default {current}, p_static_id in varchar2) return clob

This example needs a session of application 200, page 1, and the price-list Data Load Definition. It changes two prices and then puts them back.

Example:

declare
    l_old    apex_t_number;
    l_result apex_data_loading.t_data_load_result;
begin
    select unit_price bulk collect into l_old from orb_products
     where sku in ('TNT-1001', 'TNT-1002') order by sku;

    -- the lab's Data Load Definition "price-list" merges SKU and UNIT_PRICE into ORB_PRODUCTS
    l_result := apex_data_loading.load_data(
                    p_static_id    => 'price-list',
                    p_data_to_load => 'SKU,UNIT_PRICE'  || chr(10) ||
                                      'TNT-1001,249.99' || chr(10) ||
                                      'TNT-1002,284.99');
    dbms_output.put_line('processed: ' || l_result.processed_rows || ', errors: ' || l_result.error_rows);

    for r in (select sku, unit_price from orb_products where sku in ('TNT-1001', 'TNT-1002') order by sku) loop
        dbms_output.put_line(r.sku || ' now ' || r.unit_price);
    end loop;

    -- put the old prices back
    l_result := apex_data_loading.load_data(
                    p_static_id    => 'price-list',
                    p_data_to_load => 'SKU,UNIT_PRICE' || chr(10) || 'TNT-1001,' || l_old(1)
                                                       || chr(10) || 'TNT-1002,' || l_old(2));
    dbms_output.put_line('restored: ' || l_result.processed_rows);

    dbms_output.put_line(json_object_t.parse(apex_data_loading.get_file_profile(p_static_id => 'price-list')).to_clob);
end;
/

Output:

processed: 2, errors: 0
TNT-1001 now 249.99
TNT-1002 now 284.99
restored: 2
{"file-type":2,"single-row":false,"file-encoding":"WE8ISO8859P1","headings-in-first-row":true,"csv-enclosed":"\"","columns":[{"name":"SKU","data-type":1,"data-type-len":4000,"selector":"SKU","clob-column":0,"is-json":false},{"name":"UNIT_PRICE","data-type":2,"decimal-char":".","selector":"UNIT_PRICE","clob-column":0,"is-json":false}],"skip-rows":0}

Using a Data Load Definition keeps column mapping, transformations, and error handling in one declarative place, shared by the Data Upload page and your code. Parsing files without a definition is covered in the guide to parsing CSV, Excel, and ZIP files with APEX_DATA_PARSER and APEX_ZIP.

Synchronizing REST Data Sources: APEX_REST_SOURCE_SYNC

A REST Data Source can copy its data into a local table on a schedule, configured in the source's Synchronization settings with a table, a type (append, merge, or replace), and an interval. Reports then read the fast local copy instead of calling the API on every page view.

SYNCHRONIZE_DATA, GET_SYNC_TABLE_DEFINITION_SQL, SYNCHRONIZE_TABLE_DEFINITION, GET_LAST_SYNC_TIMESTAMP, and IS_RUNNING

GET_SYNC_TABLE_DEFINITION_SQL returns the DDL that creates the table, or alters it to match the data profile; SYNCHRONIZE_TABLE_DEFINITION runs it, dropping columns no longer in the profile only when p_drop_unused_columns is true. SYNCHRONIZE_DATA runs a synchronization now, or as a background job with p_run_in_background true. GET_LAST_SYNC_TIMESTAMP returns when the last one succeeded, and IS_RUNNING whether one is running.

Syntax:

apex_rest_source_sync.get_sync_table_definition_sql(p_module_static_id in varchar2,
    p_application_id in number default {current}, p_include_drop_columns in boolean default false) return clob
apex_rest_source_sync.synchronize_table_definition(p_module_static_id in varchar2,
    p_application_id in number default {current}, p_drop_unused_columns in boolean default false)
apex_rest_source_sync.synchronize_data(p_module_static_id in varchar2, p_run_in_background in boolean default false,
    p_application_id in number default {current})
apex_rest_source_sync.get_last_sync_timestamp(p_module_static_id in varchar2, p_application_id in number default {current})
    return timestamp with time zone
apex_rest_source_sync.is_running(p_application_id in number default {current}, p_module_static_id in varchar2) return boolean

This example needs a session of application 200, page 1, and the city-geocoding source. Before it ran, any existing LAB_CITIES table was dropped, so the definition SQL creates it from scratch.

Example:

declare
    l_ddl clob;
begin
    -- the lab's REST Data Source "city-geocoding" synchronizes into the table LAB_CITIES,
    -- which does not exist yet: the definition SQL creates it
    l_ddl := apex_rest_source_sync.get_sync_table_definition_sql(p_module_static_id => 'city-geocoding');
    dbms_output.put_line(substr(l_ddl, 1, instr(l_ddl, '"ADMIN2"') - 1) || '...');

    apex_rest_source_sync.synchronize_table_definition(p_module_static_id => 'city-geocoding');

    apex_rest_source_sync.synchronize_data(p_module_static_id => 'city-geocoding');   -- uses the default name=Portland
    dbms_output.put_line('last sync: ' || case when apex_rest_source_sync.get_last_sync_timestamp('city-geocoding')
                                                    > systimestamp - interval '1' minute then 'just now' end);
    dbms_output.put_line('running: ' || case when apex_rest_source_sync.is_running(p_module_static_id => 'city-geocoding')
                                             then 'yes' else 'no' end);
end;
/

select count(*) as cities, min(name) as name from lab_cities;

Output:

/*
-- This API call dynamically synchronizes the table columns to the
-- REST Data Source's data profile by executing ALTER TABLE statements.
-- If a column data type cannot be changed using ALTER TABLE, an error
-- message will be raised.
---------------------------------------------------------------------
begin
apex_rest_source_sync.synchronize_table_definition(
p_application_id      => apex_application_install.get_application_id,
p_module_static_id    => 'city-geocoding',
p_drop_unused_columns => false );
end;
/
*/

create table "ORBIT"."LAB_CITIES"(
    "ID"                        NUMBER
   ,"NAME"                      VARCHAR2(4000)
   ,"ADMIN1"                    VARCHAR2(4000)
   ,...
last sync: just now
running: no

CITIES NAME
------ -----------
    10 Blue Island

The generated DDL even includes, in a comment, the call to keep the table in step with the profile later, which is useful in installation scripts.

DYNAMIC_SYNCHRONIZE_DATA

Runs a synchronization with other parameter values or an external filter instead of the synchronization steps defined on the source, for example to load the data for one customer, one region, or one search. p_sync_static_id names the run in the synchronization log.

Syntax:

apex_rest_source_sync.dynamic_synchronize_data(p_module_static_id in varchar2, p_sync_static_id in varchar2,
    p_sync_external_filter_expr in varchar2 default null,
    p_sync_parameters in apex_exec.t_parameters default apex_exec.c_empty_parameters,
    p_application_id in number default {current})

This example needs a session of application 200, page 1, and the city-geocoding source.

Example:

declare
    l_params apex_exec.t_parameters;
begin
    -- a one-off synchronization with other parameter values: the sync type (REPLACE)
    -- and target table are the REST Data Source's
    apex_exec.add_parameter(l_params, 'name', 'Aurora');
    apex_rest_source_sync.dynamic_synchronize_data(
        p_module_static_id          => 'city-geocoding',
        p_sync_static_id            => 'aurora-us',
        p_sync_external_filter_expr => null,
        p_sync_parameters           => l_params);
end;
/

select name, admin1, country_code from lab_cities order by population desc nulls last fetch first 4 rows only;

Output:

NAME   ADMIN1   COUNTRY_CODE
------ -------- ------------
Aurora Colorado US
Aurora Illinois US
Aurora Ohio     US
Aurora Missouri US

Because the source's sync type is Replace, the table now holds the Aurora results in place of the earlier ones.

ENABLE, DISABLE, and RESCHEDULE

ENABLE turns on scheduled synchronization and schedules the job by the source's interval; DISABLE turns it off. RESCHEDULE moves the next run to p_next_run_at, and passing systimestamp runs it now.

Syntax:

apex_rest_source_sync.enable | disable(p_application_id in number default {current}, p_module_static_id in varchar2)
apex_rest_source_sync.reschedule(p_application_id in number default {current}, p_module_static_id in varchar2,
    p_next_run_at in timestamp with time zone default systimestamp)

This example needs a session of application 200, page 1, and reads the APEX_APPL_WEB_SRC_MODULES dictionary view to show the effect.

Example:

declare
    procedure show(p_label varchar2) is
    begin
        for m in (select sync_is_active, next_synchronization from apex_appl_web_src_modules
                   where application_id = 200 and module_static_id = 'city-geocoding') loop
            dbms_output.put_line(rpad(p_label, 12) || 'active: ' || m.sync_is_active || ', next: '
                || case when m.next_synchronization is null then '-'
                        when m.next_synchronization > systimestamp + interval '1' day then 'in more than a day'
                        else 'within a day' end);
        end loop;
    end;
begin
    show('initially');
    apex_rest_source_sync.enable(p_module_static_id => 'city-geocoding');     -- schedules the job
    show('enabled');
    apex_rest_source_sync.reschedule(p_module_static_id => 'city-geocoding',
                                     p_next_run_at => systimestamp + interval '2' day);
    show('rescheduled');
    apex_rest_source_sync.disable(p_module_static_id => 'city-geocoding');
    show('disabled');
end;
/

Output:

initially   active: No, next: -
enabled     active: Yes, next: within a day
rescheduled active: Yes, next: in more than a day
disabled    active: No, next: -

REST Data Sources and their synchronization settings are covered in the guide to data sources, data loads, and duality views.

Conclusion

APEX_DATA_EXPORT turns any APEX_EXEC query context into CSV, HTML, JSON, XML, Excel, or PDF, with chosen columns, headings, format masks, column groups, query-supplied subtotals, highlights, and PDF page settings, and DOWNLOAD sends the result straight to the browser. APEX_DATA_LOADING loads files through a Data Load Definition, keeping mapping and error handling declarative. APEX_REST_SOURCE_SYNC creates and maintains a local copy of a REST Data Source, on demand, with one-off parameters, or on a schedule. Together they cover most of the file and data movement an APEX application needs without hand-written CSV or Excel code.