Tuesday 9 April 2024

Oracle APEX ZIP API - an Upgrade which is not officially documented

Recently I had a request from a customer to unzip files in the database. Of course, I used one of the most popular Oracle APEX APIs - APEX_ZIP - which is implemented in the APEX long time ago... I think in version 4 or 5.

At the moment I'm writing this blog I've been using the latest Oracle APEX 23.2. and I noticed some new functionality in this API... which is not yet officially documented in the Oracle API documentation.


So, what's new?

It goes for a new function named "get_dir_entries" and a new function "get_file_content" adapted for a new dir_entries approach.

Warning! Old function get_files is getting deprecated! This is stated in the package comments:

In which APEX version is this new functionality implemented?

I checked all APEX versions going from 23.2 toward previous versions and figured out that those new functionalities are implemented in version 21.2

Version 21.1:

Version 21.2:

But in the official documentation there is no mention of those new functions:

New approach vs old approach - benchmark

In the package comment it is stated that this new function is much faster and much more efficient.

So I tested performances on extracting 10112 files from a ZIP archive. There are 2 steps in the process: 

  1. get a list of all files from the ZIP file with get_dir_entries or ger_files function
  2. extract all files from the ZIP file with get_file_content function

Results are:

  • new approach with get_dir_entries took cca 30 seconds to finish
  • old approach with get_files - after more than 1 hour I stopped the script execution

Looking in details for a new approach:

  • the first step of preparing a file list took most of the time... around 29 seconds
  • to extract 10112 files - it took only a second.

Benchmark details (dbms output):

The code I used for testing:

    l_zip blob;
    l_files apex_zip.t_dir_entries;
    l_file blob;
    l_counter pls_integer := 1;
    l_index varchar2(32767);
    PROCEDURE p_out (p_text varchar2) IS
        dbms_output.put_line(to_char(systimestamp, 'hh24:mi:ssXff') || ' - ' || p_text);
    END p_out;
    SELECT blob_content 
    INTO l_zip
    FROM import_zip_xml 
    WHERE id = 2;
    dbms_output.put_line('ZIP file size: ' || dbms_lob.getLength(l_zip) );
    p_out('Getting dir entries list...');
    l_files := apex_zip.get_dir_entries (
        p_zipped_blob => l_zip,
        p_only_files => true
    p_out('Total files in the list: ' || l_files.count);
    p_out('Start extracting files...');

    --unzip files
    l_index := l_files.first;
        EXIT WHEN l_index is null;
        --p_out('Processing file ' || to_char(l_counter, 'fm00000') || ' ' || l_index || ': ');
        l_file := apex_zip.get_file_content (
            p_zipped_blob => l_zip,
            p_dir_entry => l_files( l_index ) 
        l_index := l_files.next(l_index);
        l_counter := l_counter + 1;

One more detail I noticed - a function get_dir_entries is returning not only a collection of filenames found in the ZIP file but for every file it returns an uncompressed file size too.

This is the record type and the collection it returns:

A Conclusion

If You have a newer version of APEX, >= 21.2, this new approach is definitely worth using.

Performances are blazing fast.

Plus, there are additional metadata provided for files, like an uncompressed file size for example.

Wednesday 27 March 2024

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:

    l_id number;
    l_c1 varchar2(100);
    l_c2 varchar2(100);
    l_c3 varchar2(100);
    l_c4 varchar2(100);
    l_c5 varchar2(100);

    SELECT *
    INTO l_id, l_c1, l_c2, l_c3, l_c4, l_c5
    FROM tica_test
    WHERE rownum = 1


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?!

FROM user_errors

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




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.

Friday 22 March 2024

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


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


The example of the usage is 

    l_zip blob;
    --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);


Tuesday 27 February 2024

Oracle APEX - DML on Collections


APEX Collections are introduced in the Oracle APEX since... forever.

They provide a really nice replacement for Global Temporary Tables and can be very useful in a wide range of scenarios and use cases.

Data stored in APEX collections can be read via APEX_COLLECTIONS view and can be manipulated via APEX_COLLECTION API. 

But unfortunately Oracle APEX doesn't provide a functionality to execute DML statements on APEX collections and this is something I've been missing since... forever 😊

This means You can not execute the following statement successfully:

UPDATE apex_collections
    c002 = c001,
    n002 = n002 * 2,
    d001 = trunc(sysdate + n001)
    collection_name = 'MY_COLL'
AND n001 > 50

You are going to get an error ORA-01031: insufficient privileges. 

In order to modify data in the collection You need to write a PL/SQL code instead, like this:

    FOR t IN (
        FROM apex_collections
            collection_name = 'MY_COLL'
        AND n001 > 50
    ) LOOP
        apex_collection.update_member (
            p_collection_name => 'MY_COLL',
            p_seq => t.seq_id,
            p_c002 => t.c001,
            p_n002 => t.n002 * 2,
            p_d001 => trunc(sysdate + t.n001)

But fortunately, I found a way on how to execute DML statements on APEX collections!

Solution scripts and examples can be found in the GIT Hub repository here:


The Idea behind the Solution

The idea behind the solution is to create a local updateable view in the target schema named "apex_collections_dml", on which You can execute DML statements.

This view contains an "instead of" trigger, which is triggered by DML operations and manages data in APEX collections via APEX_COLLECTION API.

The view is based on a pipelined function named "apex_collections_dml_pkg.get_coll_data", which simply returns data from APEX_COLLECTIONS view. The select statement / view source is 

    table (apex_collections_dml_pkg.get_coll_data)

This way a following UPDATE statement can be executed without ORA errors:

UPDATE apex_collections_dml
    c002 = c001,
    n002 = n002 * 2,
    d001 = trunc(sysdate + n001)
    collection_name = 'MY_COLL'
AND n001 > 50

Why this Approach with the pipelined Function?

Well... the first idea was to create an instead of trigger directly on the APEX_COLLECTIONS view. But I have no privileges to do so. Plus, this also means messing up with APEX objects, which is never a good option. Plus, if You are on the cloud You have no access to APEX objects.

Second idea was to create a local view APEX_COLLECTIONS_DML, which reads data directly from APEX_COLLECTIONS view, and to create an instead of trigger on the view. But the problem was that the database is checking privileges on underlying objects BEFORE executing the trigger and therefore I ended up with "ORA-01031: insufficient privileges" error.

In order to avoid this ORA error I needed to somehow separate APEX_COLLECTIONS view object from my local DML view... and pipelined function was the solution I was looking for.

INSERT statement vs apex_collection.create_collection_from_query

The collection can be created in both ways and the SELECT statement, which prepares data is the same.

But... the SELECT statement used with the procedure apex_collection.create_collection_from_query is executed and validated during the runtime. And if the data structure used in the SELECT statement changes (for example some table is dropped or some columns are renamed) You are going to get errors during the runtime only.

On the other hand INSERT statement is validated during the development and compilation phase and program units with INSERT statement are going to be invalidated... as a good sign that something is not right...

Performance Concerns

I noticed no significant performance issues with this approach on the range of few hundreds or thousands of collection members.

Monday 19 February 2024

Oracle APEX - beware of the "Quick Edit" button while editing the code in a popup editor!

I bumped into a problem with Oracle APEX App Builder while editing the page and been able to reproduce it on apex.oracle.com (current APEX version 23.2.4).

So, what happened was - the JavaScript code I was editing in the JavaScript popup editor overwrote item's label! This happened when I used the "Quick Edit" button in the running page and selected a page item with the editor still open.

Popup editors in the Page Designer are modal and this should prevent user from selecting another object while they are open... but with Quick Edit button this can be achieved and can lead to a problem if changes in the editor are accepted.

Other editors like PL/SQL editor or CSS editor behave in the similar way, but they are overwriting another item attributes... not necessarily the label. For example PL/SQL editor is overwriting text item's subtype, CSS editor is overwriting Template Options, File URL editor is is overwriting text item's Column attribute... HTML editor is overwriting the text item's label, similar to the JavaScript editor.

Beside those editors I mentioned, other editors might have the same behavior but I haven't tested them all.

Wednesday 14 February 2024

Oracle "Instead of" triggers - beware of privileges and grants!

Recently I bumped into a problem while dealing with "instead of" triggers in the Oracle database.

The scenario is the following.

In my main schema (let's call it SC1) I created a view named DEMO_STATES_V based on the table DEMO_STATES located in another schema (let's call it SC2).

SELECT st, state_name
FROM sc2.demo_states;

The user SC1 has SELECT privilege granted on the table SC2.DEMO_STATES.

I created an "instead of" trigger on the view DEMO_STATES_V to handle DML operations. 
Currently there is only a dbms output but it is enough to present the problem.

CREATE OR REPLACE TRIGGER my_demo_states_trg
ON my_demo_states
    dbms_output.put_line('The trigger executes!');

I tried to insert the record into the view and I'm expecting to get the dbms output written.

INSERT INTO my_demo_states (st, state_name)
VALUES ('SI', 'Slovenia');


Instead of the trigger execution and dbms output produced I got an error!

ORA-01031: insufficient privileges
Seems that the database checked insert privileges BEFORE executing the trigger!
And while user SC1 does not have the insert privilege granted... an error occurred.

Honestly, I haven't expected that. I mean, if I have the "instead of" trigger created, why would database check any DML privileges on the underlying table? The "instead of" trigger is going to handle eventual DML operations... and grants should be checked for the PL/SQL code in the trigger body.

Another but... there is an override. 💪

If You modify the SELECT statement for the view object and implement UNION ALL from DUAL with "1=2" condition... so that actually no record is added to the original result set from the first part of the select statement... then everything works! For example:

SELECT st, state_name
FROM dome_ms1.demo_states
SELECT null, null
FROM dual
WHERE 1=2;

No privileges are checked and the trigger executes flawlessly!

Monday 11 December 2023

Deferrable constraint in Oracle database - be aware of them!

Recently I bumped into an error during Oracle APEX application import and I started digging in the depths of the APEX trying to figure out what the problem was. The error message stated that one foreign key constraint failed and I hoped to find a procedure with DML statement, where this error happened.

The error back trace was pretty long... and it ended at the COMMIT statement 😐At the COMMIT statement? And not at the very DML operation? 😮 I was puzzled... staring blankly at the screen... checking three times if I traced the error correctly... but I did. 😵

And suddenly I remembered that there is a parameter related to table constraints, the parameter I never used in my whole life, which determines if the constraint is validated at the moment of DML statement execution OR later on during the transaction ending COMMIT statement. This parameter is called "deferrable". And indeed, it was a case here. In the problematic APEX table all constraints were initially deferrable.