APEX 21.1 Application Data Loading

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:

• The table to be loaded into
• Specify unique columns
• Define any transformation rules
• Define any table lookups

• Data Load Source
• Data / Table Mapping
• Data Validation
• Data Load Results

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.

Data Load Definition

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:

• Copy an existing Data Load definition (or create one from scratch)
• Load into a table or a collection (previously this was table only)

• Create the Data Load definition based on sample data

• Create the Data Load definition based on files in csv, xlsx, xml or json format (previously only comma-separated or tab-delimited files were allowed)

And now the following steps are applied during the definition phase by the developer rather than at run time by the end user:

1. Select to upload a file or copy and paste data (now sample data to build the definition)
2. Specify whether the first line contains column headers
3. Specify the File Encoding
4. Perform the Column Mapping, which itself allows the developer to:
   • Map the data source column to the table/collection column
   • Specify a format mask for each column (there is also a preview page which displays how the sample data is interpreted, which is quite useful)
   • Group separator and decimal characters can now be specified at the column level
   • Identify columns as primary key columns

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:

• Loading Method – Append, Merge or Replace
• Error Handling – Ignore, Stop, Log into Collections, Log into Error Log

It's useful to note that help is available on both these parameters to understand the different settings.

• Data Profile where it is possible to:
  • Re-sequence columns
  • Specify an XLSX worksheet name
  • Skip rows in the source file before data parsing starts
  • Add new columns (based on Data, Lookup, SQL Expression, SQL Query)
  • Amend column mapping attributes, including whether column mapping selection is based on Name, Sequence or Regular Expression
  • Configure Transformation Rules

Data Loading Page

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.

• Specify the page number, name mode
• Specify navigation: breadcrumb, navigation menu entry
• Select the data load definition that the page will use (i.e. the one created in the steps above)

• Determine whether the data will be uploaded from a file or from pasting delimited data (only available for definitions based on CSV)

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:

• LOAD_COL_HEAD
• LOAD_CONTENT
• NOT_LOAD_CONTENT
• PARSE_COL_HEAD
• SPREADSHEET_CONTENT

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.

APEX_DATA_LOADING Package

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.

Conclusion

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.

DSP-Explorer Oracle APEX Services

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! Get in touch with our experts or book a meeting today...

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.