Recently, I had to set some Oracle APEX page elements to read-only, but only if certain conditions are met.

Each page in my APEX app had around 100 items, so I was looking for a dynamic way to achieve that.

I want to share some of my thoughts on that.

My example

I have two users in my application:

RAFAL - this user doesn’t need to have any restrictions

TEST - this user shouldn’t be able to change items :P2_BUDGET “Budget” and :P2_COST “Cost” (read-only)

Solution 1 - lazy and bad

One of many lazy and NOT recommended ways is just to set this for :P2_BUDGET and. :P2_COST:

Solution 2 - not lazy but could be better

Or you can go a more declarative way.

Create a table with values of page items that should be read-only for specific app users.

create table read_only_page_items (
    id               number generated by default on null as identity
                     constraint read_only_page_items_id_pk primary key,
    app_user         varchar2(4000 char),
    app_page_item    varchar2(4000 char)
);

And use this table in your query for :P2_COST and :P2_BUDGET items

This solution is almost nice, as you can define your read-only items using a table, create a management GUI, etc.

However, you must still explicitly add the hardcoded name of your item to each of your “Read-only” setting conditions.

Solution 3 - probably the best one :)

If you have ever heard of the :APP_COMPONENT_NAME global item - you might know that you can use it for dynamic authorization for your APEX apps - Loius Moreaux described it in his blog post How to implement a dynamic Authorization Scheme in Oracle APEX?

But, how can we leverage :APP_COMPONENT_NAME and avoid hardcoding item names each time in the “Read Only” setting?

Create an authorization scheme called, e.g. “is_item_read_only” :

scheme type “Exists SQL Query” (but you can easily use other types)

      select 1 
        from read_only_page_items
      where app_user = :APP_USER
        and app_page_item = :APP_COMPONENT_NAME;

💡

You can use :APP_COMPONENT_NAME to pass it as a parameter to a function or procedure and write your logic in PL/SQL packages, etc.

If the above query returns rows, authorization will return TRUE.
Use authorization scheme as a “Read-only” type.

We can not check directly for an authorization scheme as it’s not on the list of types.

That’s why we should use APEX_AUTHORIZATION.IS_AUTHORIZED official API.

return APEX_AUTHORIZATION.IS_AUTHORIZED (
       p_authorization_name => 'is_item_read_only');

Summary

I hope you liked 3rd solution and you will write great code leveraging :APP_COMPONENT_NAME global item.

Once again, I encourage you to read Louis's blog How to implement a dynamic Authorization Scheme in Oracle APEX? it also mentions the possibility of using the :APP_COMPONENT_TYPE or :APP_COMPONENT_ID global items.

That’s all for today.

PS Wondering about the photo used? It’s Zakopane, Poland.

How to dynamically set your Oracle APEX elements into the read-only state?

My example

Solution 1 - lazy and bad

Solution 2 - not lazy but could be better

Solution 3 - probably the best one :)

Summary