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: