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;

 

Oracle APEX - export multi language application components with APEX_EXPORT API

If You want to export multi language application components with APEX_EXPORT API (function apex_export.get_application) it is pretty tricky to get them exported for other languages.

Parameter p_with_translations is not working. Scripts will always be generated for main language.

But hopefully there is a way to get component scripts for translated applications.

In my testing case I have the main application in English language (app ID 124) and 2 translations to Dutch (app ID 999) and French (app ID 998). See pic below.

To get the component scripts for page 1 and Yes/No LOV (shared component) from main application ID 124 in English language the syntax is 

    lrFiles := apex_export.get_application(
        p_application_id => 124,
        p_split => false,
        p_with_translations => true,
        p_components => apex_t_varchar2( 'PAGE:1', 'LOV:39437346231509935' )
    );

But to get those scripts for translated application ID 999 in Dutch language the syntax is 

    lrFiles := apex_export.get_application(
        p_application_id => 999,
        p_split => false,
        p_with_translations => true,
        p_components => apex_t_varchar2( 'PAGE:1.999', 'LOV:39437346231509935.999' )

    );

So, the main component ID stays the same but it is followed by dot plus application ID.

Generated script:

Oracle APEX - export static application files as installation scripts

If You want to export static application files as components from APEX GUI... well, it is not working.

Static application files are provided in component type list... but if You select them and click on GO... a list is empty and You can not select any static file to export:

Also, static application files can not be found in APEX_APPL_EXPORT_COMPS view. If You execute following script for application which contains static application files to get all component types... there are no static app files.

SELECT DISTINCT type_name

FROM apex_appl_export_comps

WHERE application_id = 400

;

Fortunately, there is a way to get those script files using PLSQL and APEX_EXPORT package.

Let's look at following example - application 400 and static file "js/page6100.js". 

First You need to locate exact component file name for desired file with PLSQL code:

DECLARE

    l_files apex_t_export_files;

BEGIN
    --get files
    l_files := APEX_EXPORT.get_application(
        p_application_id => 400,
        p_split => true,
        p_with_translations => true,
        p_with_comments => true
    );

    FOR t IN 1 .. l_files.count LOOP
        dbms_output.put_line(l_files(t).name);
    END LOOP;

END;

Result:

Then You can add IF statement in PLSQL code and get script content for page "js/page6100.js" component:

DECLARE

    l_files apex_t_export_files;

BEGIN

    --get files

    l_files := APEX_EXPORT.get_application(

        p_application_id => 400,

        p_split => true,

        p_with_translations => true,

        p_with_comments => true

    );

    FOR t IN 1 .. l_files.count LOOP

        --dbms_output.put_line(l_files(t).name);

        

        if l_files(t).name = 'f400/application/shared_components/files/js_page6100_js.sql' then

            dbms_output.put_line(l_files(t).contents);

        end if; 

    END LOOP;

    

END;

Result:

In this example I used DBMS_OUTPUT package to get script files but You can also insert them in some table / CLOB column.

Generated script contains only procedure call to crate a file (wwv_flow_api.create_app_static_file). So, if You need to remove existing file first then You should modify this script manually and add wwv_flow_api.remove_app_static_file procedure call.

Oracle APEX selected components export from API (APEX_EXPORT)

 Oracle APEX 20.1 introduced new functionality for application components export. Function APEX_EXPORT.get_application got a new parameter named P_COMPONENTS.

So, with this parameter it is possible to make a list of components for which a scripts will be returned. No need to export and split full application.

But beware!

P_SPLIT parameter (boolean value) is still functional and it has a great influence on function result!

If value of P_SPLIT parameter is FALSE, function will return one script for all components from P_COMPONENTS parameter.

If value is TRUE it will return multiple scripts. 

For every component 2 scripts will be created:

• delete component script
• create component script

Plus, additional scripts will be generated:

• set environment (as first script in collection)
• end environment (as second last script in collection)
• main install script which executes all generated scripts (as last script in collection)

So, do not make a mistake like me - to set a P_SPLIT parameter to TRUE and expect one script per component to be generated (for example 3 scripts for 3 pages). There are only two scenarios - one script for everything OR multiple scripts (environment, delete components, create components, finish install, main install script).