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.

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.

You may read more about instead of triggers in the official documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-triggers.html#GUID-9F06D45C-7C60-434E-A597-114A0C445671

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!

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.

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:

Where to download Oracle eCertificate and Badge for the passed exam?

So, You've just passed an Oracle exam, feeling happy and full of energy 💪 and now You want to download Your badge and eCertificate.

But where to find it? 😕

Maybe I'm a little confused guy... but honestly, it took me some time to click through the CertView portal to find the correct spot.

Oracle MERGE INTO statement behavior in concurrent sessions

A question - if we have 2 concurrent sessions executing exactly the same MERGE INTO statement on a table... what will happen? Are we going to get one row inserted, two rows inserted, one inserted and one updated... an exception... what will happen?

Well... let's see in following experiment 😏

Oracle APEX_DICTIONARY and DBA_DICTIONARY views

Oracle APEX is providing a dictionary view called APEX_DICTIONARY which is returning names and comments for all other dictionary vies.

For example:

SELECT *
FROM apex_dictionary
;

A result:

So, rows with column ID 0 contain a view name and other rows with numbers >= 1 contain column names.