Oracle Database - When is the data in the user_errors view populated

So, today I've been playing with visible and invisible columns in Oracle database tables and noticed one interesting fact related to USER_ERRORS view data population.

The data is populated AFTER the first explicit or implicit compilation of the invalid object.

The example

I created a table named TICA_TEST

CREATE TABLE tica_test (
    id number primary key not null,
    c1 varchar2(100), 
    c2 varchar2(100), 
    c3 varchar2(100), 
    c4 varchar2(100), 
    c5 varchar2(100) 
);

Also created a procedure P_TICA_TEST, which is reading data from the tica_test table:

CREATE OR REPLACE PROCEDURE p_tica_test IS

    

    l_id number;

    l_c1 varchar2(100);

    l_c2 varchar2(100);

    l_c3 varchar2(100);

    l_c4 varchar2(100);

    l_c5 varchar2(100);

BEGIN

    SELECT *

    INTO l_id, l_c1, l_c2, l_c3, l_c4, l_c5

    FROM tica_test

    WHERE rownum = 1

    ;

END;

If I drop the column named C5 or make it invisible I expect that the procedure gets invalidated and that errors are stated in the view USER_ERRORS... 

But expectations are reality are not always aligned 😁

The column is dropped:

ALTER TABLE tica_test MODIFY (c5 invisible);

The status of the procedure is INVALID, as expected :

SELECT status
FROM user_objects
WHERE object_name = 'P_TICA_TEST';

But the USER_ERRORS view does not return any data?!

SELECT *
FROM user_errors
WHERE name = 'P_TICA_TEST';

And only when I compile the procedure (explicit compile) or execute the procedure (implicit compile) errors are shown and the view is populated:

ALTER PROCEDURE p_tica_test COMPILE;

or

BEGIN
    p_tica_test;
END;

Data from the view:

The conclusion

Do not be surprised if the object is invalid and there are no errors stated. 

Maybe indeed there are no errors... and the object is just marked as invalid... but the errors are going to be revealed only after the object compilation.

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;