Oracle APEX - export and import XLIFF files for multiple pages

So... we are developing an Oracle APEX multi language application for a customer and recently they decided to deal with problematic translations on multiple pages for multiple languages through the application. It goes for approximately 300 pages and 2 languages (Dutch and French).

And they requested us to provide them separate XLIFF files for every single page and language - about 600 files - it was easier for them to translate this way. 

After the translation is done we should import all files back into the application translations repository.

While dealing with this task I figured out it was not so easy to accomplish, because the APEX App Builder does not fully support this functionality out-of-the-box. Therefore I have written a utility package, which can be downloaded from my GitHub repository

https://github.com/zorantica/db_apex_utils

App Builder built-in functionality for XLIFF export/import

Oracle APEX provides a functionality to export XLIFF files directly from App Builder... 

but it goes either for a complete application in one file OR a single page.

There is no option to select multiple pages and languages, as was the requirement. 😓

For importing XLIFF files there is a similar problem. Files need to be open one by one - there is no bulk upload and import.

Plus, there is no automatic language detection after the files upload regardless of the fact that the target language is contained in the XLIFF file:

My Solution

Since the APEX version 23.1 APEX_LANG API provides a function to generate a XLIFF file for a single page and language combination:

I wrote a PL/SQL code, which loops through all requested pages and languages, gets XLIFF files via APEX_LANG API and stores them as a single ZIP file. And this part of the requirement was fulfilled.

Regarding the import, there is a procedure in APEX_LANG API, which can be used to import / apply the XLIFF file:

So again, I wrote a PL/SQL code, which gets a ZIP file containing all XLIFF files, unzips XLIFF files, loops through all XLIFF files, for each file it determines the target language and applies files via APEX_LANG API. And this part of the requirement was also fulfilled.

At the end I wrapped all the code in the single package and added some additional functionalities like splitting XLIFF files in separate folders named by page groups, conducting seed and publish during the import...

And as I already mentioned, this package can be freely downloaded from the GitHib

https://github.com/zorantica/db_apex_utils

The example of the usage is 

DECLARE
    l_zip blob;
    
BEGIN
    --create a ZIP file
    l_zip := apex_lang_utils.get_xliff_per_page (
        p_app_id => 123,
        p_pages => '1,2,3',  --comma separated list of pages
        p_languages => 'nl-be,fr-be',  --comma separated list of languages
        p_folder_per_group_yn => 'Y',  --separate files per folders
        p_only_modified_elements_yn => 'N'
    );
    
    --store the ZIP file into TEST table
    DELETE test;
    INSERT INTO test (id, blob_doc)
    VALUES (1, l_zip);
    
    COMMIT;
END;