Moved blog...

 ...to the Hashnode. You can found it here https://apexbyg.hashnode.dev

APEX 22.2 Clear Active Tab

From APEX version 22.2 there's a changed behavior how active tabs or region display selectors (RDS) are stored in the browser session storage:

In versions before 22.2 they were stored without a prefix (session storage scope) and now they are stored with the prefix ORA_WWV_apex.apexTabs.

In some of my applications I've used a custom JS function to clear active tab:

	/**
	* Clear Active Tab
	* @param {number} pPageId - Page Number
	* @param {string} pRegionId - Region Static ID
	*/
	function clearActiveTab(pPageId,pRegionId){
	var vStorage;
	if (apex.storage.hasSessionStorageSupport()) {
	vStorage = apex.storage.getScopedSessionStorage({
	useAppId: true
	});
	vStorage.setItem(pPageId+'.'+pRegionId+'.activeTab', null)
	}
	}

view raw clear_active_tab_before_apex_22_2.js hosted with ❤ by GitHub

From APEX 22.2 you need to set prefix:

	/**
	* Clear Active Tab
	* @param {number} pPageId - Page Number
	* @param {string} pRegionId - Region Static ID
	*/
	function clearActiveTab(pPageId,pRegionId){
	var vStorage;
	if (apex.storage.hasSessionStorageSupport()) {
	vStorage = apex.storage.getScopedSessionStorage({
	prefix: 'ORA_WWV_apex.apexTabs',
	useAppId: true
	});
	vStorage.setItem(pPageId+'.'+pRegionId+'.activeTab', null)
	}
	}

view raw apex_clear_active_tab_from_apex_22_2.js hosted with ❤ by GitHub

Tested in APEX 22.2.0

Set Up Environment Banner

If you work on many different APEX environments, as I do, from APEX 21.2 you can easily set up a banner that will be visible on the left or the top of the APEX Builder to easily identify the environment.

Environment Bar on the left of the APEX Builder

You can do this on instance or workspace level. To modify this on the instance level you need to log in as instance admin (Manage Instance > Define Environment Banner):

You can also do this with the API:

	begin
	-- instance level
	apex_instance_admin.set_parameter('ENV_BANNER_ENABLE' , 'Y');
	apex_instance_admin.set_parameter('ENV_BANNER_LABEL' , 'Development');
	apex_instance_admin.set_parameter('ENV_BANNER_COLOR' , 'accent-3');
	apex_instance_admin.set_parameter('ENV_BANNER_POS' , 'LEFT');
	commit;
	end;
	/

view raw apex_instance_param_banner.sql hosted with ❤ by GitHub

On the Workspace level, you can do this in Manage Service > Define Environment Banner.

Unfortunately, API for setting Workspace params currently (APEX 21.2.0) doesn't work:

	begin
	-- workspace level
	apex_instance_admin.set_workspace_parameter(:WORKSPACE, 'ENV_BANNER_ENABLE' , 'Y');
	apex_instance_admin.set_workspace_parameter(:WORKSPACE, 'ENV_BANNER_LABEL' , 'Development');
	apex_instance_admin.set_workspace_parameter(:WORKSPACE, 'ENV_BANNER_COLOR' , 'accent-4');
	apex_instance_admin.set_workspace_parameter(:WORKSPACE, 'ENV_BANNER_POS' , 'LEFT');
	commit;
	end;
	/

view raw apex_workspace_param_banner.sql hosted with ❤ by GitHub

Tested in APEX 21.2.0

Full-width Page Layout on specific pages

If you set Body Content Max Width property in Theme Roller of UT theme to some fixed value (other than Auto) your body content will be centered on the screen. 

What if you want to reset this on specific pages and use the full width of the screen?

In this case, you should add the following line of the CSS to the page property Inline CSS: 

If you want to define it in the static CSS file you can also reference it as:

	.page-1 {
	--ut-body-content-max-width: auto;
	}

view raw apex_full_width_page_layout.css hosted with ❤ by GitHub

...or the declarative way - just set region position to Before Content Body and set the margins. 🙂

Tested in APEX 21.1.5

Manage APEX users from your app

I had one interesting task: to enable application end-users to manage users (to create new users from the app). Of course, I'm talking about APEX users and authentication.

Sounds simple, but is it?! I've done this 10 years ago...no problem...

To make it work I've used apex_util.create_user API:

	apex_util.create_user(
	p_user_name => p_username
	, p_web_password => p_password
	, p_first_name => p_first_name
	, p_last_name => p_last_name
	, p_developer_privs => 'ADMIN' -- you need to be WS ADMIN to be able to create users
	);

view raw 2020_11_manage_apex_users_from_app_01.sql hosted with ❤ by GitHub

As noted in a comment above, you need to add Workspace Admin privilege to the user you create so that this user can create other users. Sounds good...but you have one big problem. This user can login to the APEX Builder (of course, you can disable APEX builder access on test/production environments, but who does that in reality 😉).

The thing that worked before (I think last in APEX 5.1) was that you could create a new user that is locked by default:

	apex_util.create_user(
	p_user_name => p_username
	, p_web_password => p_password
	, p_first_name => p_first_name
	, p_last_name => p_last_name
	, p_developer_privs => 'ADMIN'
	, p_account_locked => 'Y' -- doesn't work any more
	);

view raw 2020_11_manage_apex_users_from_app_02.sql hosted with ❤ by GitHub

You can't login to the APEX builder, but unfortunately, you can't login to the app neither.

So I came to a new solution, and it's a simple one. 

By default I add new end-users to predefined User Group (don't forget to create user group before):

	apex_util.create_user(
	p_user_name => p_username
	, p_web_password => p_password
	, p_first_name => p_first_name
	, p_last_name => p_last_name
	, p_developer_privs => 'ADMIN'
	, p_group_ids => apex_util.get_group_id(p_group_name => 'APP_END_USERS')
	);

view raw 2020_11_manage_apex_users_from_app_03.sql hosted with ❤ by GitHub

After that, on the APEX workspace instance level (Manage Instance > Security > Development Environment Authentication Schemes > APEX Accounts), I've added Post-Authentication procedure that doesn't allow users from that group (in my case APP_END_USERS) to login to the APEX Builder:

...and it works. When you try to login to the APEX builder, you'll get something like this:

*Note: this is only a part of the code. Don't forget to add more security checks to your apps.

...and that's all folks! Stay safe & enjoy!

Tested in APEX 20.2.0.00.20

Reset APEX builder login authentication scheme

If you change Development Environment Authentication Schemes (aka the way you login to the APEX Builder) at the instance level and you don't read a confirm dialog carefully, as I didn't you may struggle to login as the instance admin next time:

In my case, I've changed it to the Database Accounts, and though that doesn't affect the instance admin account. But I was wrong. In my case, the solution was to create a database user with username admin.

After that, I've logged in as instance admin and changed the authentication back to the APEX Accounts.

If you change it to some other Authentication Scheme you may need to use PL/SQL API:

	begin
	-- documented here: https://docs.oracle.com/en/database/oracle/application-express/20.2/aeapi/Available-Parameter-Values.html#GUID-75DCF658-5A76-4E81-B12D-04E254A3D80A
	apex_instance_admin.set_parameter('APEX_BUILDER_AUTHENTICATION','APEX');
	commit;
	end;
	/

view raw builder_auth_scheme.sql hosted with ❤ by GitHub

Link in the documentation.

That's all! Stay safe & enjoy!

Tested in APEX 20.2.0.00.20

Bulk submit all region items to session state

Recently I came across one simple but effective solution so why not to share it... 

Colege and I were building a search page with lots of filters on the left side of the screen (similar to Faceted Search).  In the middle of the page, we've created an Interactive Report that uses a function that returns the SQL query. There's a lot of logic going in there and we used the v function to fetch item values (no worries, SQL query uses only bind variables). It was just easier and faster than to put everything into input parameters. Also, I'm not a fan of using functions to return SQL queries, but in this case, it was a perfect match. 

The only problem was that we didn't want to submit the page to get item values into the session state nor to put all the items into region property Page Items to Submit (it's hard to handle that with lots of items). So we needed a quick and easy solution to submit all items from the filter region to the session state and here it is:

First, I've created a dummy AJAX Callback process, that doesn't do anything, just returns empty JSON object:

After that, I've created a simple JS function

	function fSearch(){
	var vItemArr = [];
	// loop through all filter region items and push item IDs into JS Array
	$('#rgnFilter .apex-item-text,#rgnFilter .apex-item-radio, #rgnFilter .apex-item-select').each(function(){
	vItemArr.push($(this).attr('id'));
	});
	// call dummy AJAX process and send items array. on success, refresh report
	apex.server.process("SUBMIT_ITEMS"
	, {pageItems:vItemArr}
	, {success: function(data) {apex.region('rgnReport').refresh()}}
	);
	}

view raw bulk_submit_region_items.js hosted with ❤ by GitHub

Function loops through region items (didn't test it for all possible item types in APEX), adds them to the JS array, and with the use of the feature of apex.server.process that you can put an array of item names in property pageItems, it submits them to the session state.

On success, it refreshes the Interactive Report region.

That's all!

Stay safe & enjoy!

Tested in APEX 20.1.0.00.13