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)
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.
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) );
2. 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 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”
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.
That’s why you should use APEX_AUTHORIZATION.IS_AUTHORIZED official API.
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:
- Oracle SQLcl Project: The only CI/CD tool for APEX you need?
- Oracle APEX: Dynamic highlights for required fields
- APEX 24.1 Working Copies – An in-depth look at one of the platform’s coolest features
- Liquibase tutorial 2024: What is this tool and how to start using it effectively?
- Track your DEV database changes and export them to UAT using SQLcl Liquibase