APEX application pages provide a mechanism for application users to load data from CSV files into application tables via Data Loading.
In prior versions of APEX (pre-21.1) the Data Load definition would be specified during the Data Loading 'create page wizard'. The wizard would incorporate the following steps:
Define the Data Load:
Once the 'create page wizard' was completed, the Data Load definition created could be viewed and amended in 'Shared Components' under ‘Data Sources’.
The process has moderately changed with version 21.1.
Now the Data Load needs to be defined in 'Shared Components' prior to the creation of any associated pages. The idea behind this is to simplify the Data Load process for the end user where the same loads are repeated. Instead of the data source, mapping and validation being specified by the user in the wizard pages, they can now be defined by the developer for reuse by the user.
Creating a Data Load definition is now a pre-requisite of building the Data Load pages and is undertaken by selecting ‘Data Load Definitions’ under Data Sources in Shared Components and clicking the ‘Create’ button.
The wizard to create a Data Load definition is broadly similar (with knobs on) to a combination of the steps taken in previous versions by the developer to create the definition and the user to perform a Data Load. Now, it uses sample data to help build the definition. It includes options to:
And now the following steps are applied during the definition phase by the developer rather than at run time by the end user:
A preview of the data can be viewed via a second tab to assist with the mapping.
The wizard provides the developer with the choice of continuing to create an associated Data Loading page or saving the Data Load definition at this stage (the Data Loading page can then be created later using the Create Page wizard).
Once the Create Data Load wizard is completed the definition can be viewed and edited.
At this point it is possible to configure or amend the:
It's useful to note that help is available on both these parameters to understand the different settings.
Once the Data Load definition has been completed, the Data Loading page can be created. As previously mentioned, this can be completed as part of an extended Data Load Definition wizard or separately in the 'Create Page' wizard. Either way, the steps are the same, and simplified compared to the Data Loading create page wizard of previous versions as the Data Load has already been defined.
Now the Data Load and page are ready to be run.
The application page is now much simpler. The end user simply has to select the data e.g. choose a file with the File Browse page item or paste the delimited data.
Upon selecting the file (after clicking the ‘Next’ button if pasted delimited data is used), the data is shown in preview. It’s worth noting that the data hasn’t yet been loaded into a collection.
Then, click the ‘Load Data’ button and the Data Load is executed.
In prior version, the Data Load wizard made use of a number of APEX collections to manage the data load, such as:
In version 21.1, these are dispensed with. The data, either from file or pasted data, is presented in 'Preview' on the application page by making use of the APEX_DATA_PARSER package.
I have previously written a blog on the APEX_DATA_PARSER package which can be found here.
The new APEX_DATA_LOADING package provides the ability to load data by calling an application Data Loading definition. This can be used in place of native data loading.
The APEX_DATA_LOADING.LOAD_DATA function performs the load and there are two broadly similar signatures, one for loading BLOB data e.g. from a file, another for loading CLOB data e.g. from copied data pasted into a text area.
The LOAD_DATA function returns load status information containing processed rows and error rows in the ‘t_data_load_result’ type.
So, I created a page to select a file and load it as blob into APEX_APPLICATION_TEMP_FILES (the blob could equally have been from another table or created from a file in a directory).
Then, as part of a post-submit process, I executed the APEX_DATA_LOADING.LOAD_DATA function specifying the name of the Data Load definition (p_static _id) and the blob (p_data_to_load) to be loaded.
The Excel file contents were successfully loaded into the collection, which I was then able to report on.
APEX 21.1 changed the way Application Data Loads are created so that a greater emphasis of the definition sits with the developer.
This simplifies the end-user process, removing the requirement for users to perform tasks that may be repeated from one Data Load to the next for the same definition e.g. file definition and data mapping.
In addition, with the availability of the APEX_DATA_LOADING API, the Data Load definition can be executed in stored procedures, which could help bypass user interaction and simplify code compared to using the APEX_DATA_PARSER API alone.
The change represents quite an overhaul of the application Data Load process, so it will be worth checking when future versions of APEX are released, whether Data Loads' built-in versions prior to 21.1 become deprecated or are removed.
If this blog was of interest, be sure to discover more about our Oracle APEX Services, we'd be happy to discuss the art-of-the-possible with you!
Author: Philip Ratcliffe
Job Title: Oracle APEX Development Consultant
Bio: Philip is a Development Consultant at DSP-Explorer. Building on considerable experience in development including using Oracle PL/SQL and supporting Oracle EBS, Philip is employing APEX to provide quality, bespoke software solutions to a range of organisations.