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).

Use SELECT statement with inline PLSQL in APEX

If someone wants to use a SELECT statement which contains inline PLSQL as region source in APEX it will not work out-of-the-box.

Example of such a statement is:

WITH

    FUNCTION f_2x(p_number number) RETURN number IS

    BEGIN

        RETURN p_number * 2;

    END;

SELECT 

    level as c_level,

    f_2x(level) as c_x2

FROM dual

CONNECT BY level < 10

;

APEX validates it as a valid statement BUT when page is run it returns an error "Unsupported use of WITH clause":

There are 2 possible solutions:

1. to create a view and then use it in region source (preferably)
2. to use a WITH_PLSQL hint

Hint should be set as region property "Optimizer hint":

but once set... SELECT statement can not be validate any more!

Maybe this will be solved once in future versions of APEX... but until then I suggest to create and use database view for such a scenarios.

Database view creation example:

CREATE OR REPLACE VIEW v_tica_test AS

WITH

    FUNCTION f_2x(p_number number) RETURN number IS

    BEGIN

        RETURN p_number * 2;

    END;

SELECT 

    level as c_level,

    f_2x(level) as c_x2

FROM dual

CONNECT BY level < 10

;

/

or with outside wrapper (example of HINT usage):

CREATE OR REPLACE VIEW v_example AS

SELECT /*+ WITH_PLSQL */

    v.*

FROM 

    (

    WITH

        FUNCTION f_2x(p_number number) RETURN number IS

        BEGIN

            RETURN p_number * 2;

        END;

    SELECT 

        level,

        f_2x(level) as x2_value

    FROM dual

    CONNECT BY level < 10

    ) v

;

/

APEX Page Processing when buttons have similar names

When a button on page has a name which is contained in another button's name (for example button "SAVE" and another button "SAVE_AND_CONFIRM") be careful with defining Server-side condition for page process, computations or branches when Type is "Request is contained in Value".

If Value of Server-side condition is "SAVE_AND_CONFIRM" (so we want for example to execute page process when button "SAVE_AND_CONFIRM" is pressed) process will be executed also when button "SAVE" is pressed. I presume that this is happening because APEX is using LIKE operator to check if request is contained in value... and in our example SAVE is contained in both button names/requests.

My quick (and maybe dirty solution) was to rename button "SAVE_AND_CONFIRM" to "SV_AND_CONFIRM".

Oracle APEX - be careful not to duplicate APEX_ITEM ID for different items

When page gets complicated and there are a lot of regions, it is easy to forget which items created with APEX_ITEM package have which ID.

This IDs is later used in page processes and are referenced as APEX_APPLICATION.G_Fxx collections (where xx is ID defined in APEX_ITEM funtions call).


For example, if we have two SELECT statements as source for two different regions on page

SELECT
    level as nivo,
    apex_item.checkbox(1, level) as nivo_input
FROM dual
CONNECT BY level <= 10;

SELECT
    level + 30 as nivo,
    apex_item.text(1, level + 30) as nivo_input
FROM dual
CONNECT BY level <= 10;

There will be no error when page is generated and shown. Items are working as they are supposed to. You can mark checkboxes from first region and enter/alter values in second region.

BUT!!!!

If we have after submit process which loops through collection APEX_APPLICATION.G_F01 and do some work... we encounter a problem. LOOP takes data from both items and do the operation in process (deletes rows).

Example of process:

APEX_DEBUG.ENABLE;

FOR t IN 1 .. apex_application.g_f01.count LOOP
    apex_debug.message('how many: ' || apex_application.g_f01.count);
END LOOP;

Debug after processing:

We can see that process combined values from 2 items.

Using TABLE operator with apex_application.g_fxx collections

From Oracle version 12.1 it is possible to use TABLE operator with APEX apex_application.g_fxx collections to get Your data directly into SELECT statement as table.

Example:

• In interactive or classic report define one field as checkbox (APEX_ITEM package)
• Create new process which executes after submit
• Inside PLSQL code You can use following SELECT to get checked values

SELECT column_value as vl
FROM table(apex_application.g_f01)
WHERE column_value is not null

Examples and detailed explanation:

https://roelhartman.blogspot.com/2018/02/apexapplicationgf0x-array-processing-in.html

Oracle APEX - Conditions for Classic Report Gallery Row Template

When editing Classic Report template of style Gallery it is possible to define 4 different row templates, which are later used to render a page region correctly.

Interesting is that APEX will choose first one which meet desired conditions, similar to CASE or IF statement.

So, in upper example first template will be chosen when value exists for both CARD_LINK and CARD_LINK_TARGET. Every card in gallery will have link with target set.

Second template will be chosen only when CARD_LINK value exists. No target in link will be set.

Third template will be chosen when conditions for first two are not met.


It is possible to quickly edit template when You have it used on page. Find it on Page Shared Components:

On right side there is Edit Component Button: