Contents

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. In this article, I’ll 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” (they should be Read Only)

A screen showing APEX settings.

Solution 1 – lazy and bad

One of the many lazy and NOT recommended ways is to simply use the following settings for :P2_BUDGET and. :P2_COST.

A screen showing the settings.

Solution 2 – not lazy, but could be better

You can also use a more declarative method. 

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

A screen showing the settings.

2. Use this table in your query for :P2_COST and :P2_BUDGET items

A screen showing the settings.

A screen showing the settings.

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 article How to implement a dynamic Authorization Scheme in Oracle APEX?

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

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

A screen showing the settings.

Scheme type is “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;

Comment: 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.

2. If the above query returns rows, authorization will return TRUE

3. Use the authorization scheme as a “Read Only” type.

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

A screen showing the settings.

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

A screen showing the settings.

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

Summary

That’s all for today. I hope you liked the third solution, and you will write great code leveraging the :APP_COMPONENT_NAME global item. Once again, I encourage you to read Louis’s article 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. And of course, if you’re interested in APEX, you can also check some of my other articles on this blog:

  1. Oracle SQLcl Project:  The only CI/CD tool for APEX you need?
  2. Oracle APEX: Dynamic highlights for required fields
  3. APEX 24.1 Working Copies – An in-depth look at one of the platform’s coolest features 
  4. Liquibase tutorial 2024: What is this tool and how to start using it effectively?
  5. Track your DEV database changes and export them to UAT using SQLcl Liquibase

 

Share