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:

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.

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

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;

 

Tuesday 27 February 2024

Oracle APEX - DML on Collections

Introduction

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
SET 
    c002 = c001,
    n002 = n002 * 2,
    d001 = trunc(sysdate + n001)
WHERE 
    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:

BEGIN
    FOR t IN (
        SELECT 
            seq_id,
            n001,
            c001,
            n002
        FROM apex_collections
        WHERE 
            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)
        );
    END LOOP;
END;


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:

https://github.com/zorantica/db_apex_utils


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 

SELECT * 
FROM 
    table (apex_collections_dml_pkg.get_coll_data)


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

UPDATE apex_collections_dml
SET 
    c002 = c001,
    n002 = n002 * 2,
    d001 = trunc(sysdate + n001)
WHERE 
    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).

CREATE OR REPLACE VIEW my_demo_states AS
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
INSTEAD OF INSERT OR UPDATE OR DELETE 
ON my_demo_states
FOR EACH ROW
BEGIN
    dbms_output.put_line('The trigger executes!');
END;
/

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');

But...

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:

CREATE OR REPLACE VIEW my_demo_states AS
SELECT st, state_name
FROM dome_ms1.demo_states
UNION ALL
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.

Friday 13 October 2023

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: