OraCoolAPEX | Blog About Oracle Application Express (APEX): 2017

Wednesday, November 22, 2017

DateDropper and TimeDropper APEX Item Plug-Ins

I recently created my first set of APEX item plug-ins based on two jQuery plugins created by Felice Gattuso (DateDropper and TimeDropper).

The plug-ins work well on both desktop and mobile and support multiple languages. I included a set of themes to match the color palette of the current APEX 5.1 themes such as Vita, Vita-Dark, Vita-Slate, and Vita-Red.

Below is a preview of the plug-ins:

DateDropper

TimeDropper

You can try them in my Demo APEX Application (DateDropper and TimeDropper).

To download them, you could either go to apex.world or clone them from my GitHub repositories (DateDropper and TimeDropper), where you can also review the documentation with all the available options.

This is a first release and there is still a lot of room for improvements (e.g. they currently do not work on Interactive Grid). Please feel free to report any issues you may find or contribute to the projects on GitHub.

Friday, September 22, 2017

Show/Hide Update Button Based on Page Change

APEX 5.1 introduced a new JavaScript namespace called apex.page with some interesting functions.

In this post, I will be using the isChanged function in a Dynamic Action to hide the Apply Changes button in an Employee Form and show it only after some of the information on the page has changed.

Below is a short video that shows the final product. Notice this works with any type of items:

To implement this, create a Dynamic Action with the following properties:

Name: Show/Hide Apply Changes on Page Changed
Event: Change
Selection Type: JavaScript Expression
JavaScript Expression: apex.gPageContext$
Client-Side Condition Type: JavaScript Expression
JavaScript Expression: apex.page.isChanged()
True Action

Action: Show
Selection Type: Button
Button: SAVE

False Action

Action: Hide
Selection Type: Button
Button: SAVE

That’s it! Feel free to login into my Demo Application to try this (Username/Password = Demo/Demo).

It’s possible to apply the same concept based on changes of specific items. The JavaScript apex.item namespace has a similar function and the way to call it would be:

apex.item(‘Px_ITEM_NAME’).isChanged();

Until next time.

Friday, May 26, 2017

Inspecting Oracle APEX Packaged Applications (Part 3)

This is the third and last part of a series of blog post. You can access the first and second parts here:

In this last part we are going to create a "Display As" pill button with two options using a List of Values with a custom template to switch between the Cards View created on the previous article and a new Interactive Report.

Create a button under the new Movies Report region:

Button Name: ADD_MOVIE
Label: Add Movie
Button Position: Next
Hot: Yes
Under Template Options:
Spacing Left: Large
Action: Redirect to URL
URL: #0

Go to Shared Components, List of Values:

Click on Create
Create List of Values: From Scratch
Name: DISPLAY_AS_LOV
Type: Static
Display Value #1: Cards View
Return Value #1: CARDS
Display Value #2: Report View
Return Value #2: REPORT
Create List of Values

Edit the DISPLAY_AS_LOV list of values:

Edit the Cards View Display and add the following Template: #DISPLAY_VALUE#
Edit the Report View and add the following Template: #DISPLAY_VALUE#

Back on Page 4, create a sub region under the Movies Report region:

Title: Display As
Template: Blank with attributes

Create a page item under the Display As sub region:

Name: P4_DISPLAY_AS
Type: Radio Group
Number of Columns: 2
Template: Hidden
Under Template Options:

Size: Large
Radio Group Display: Display as Pill Button

List of Values Type: Shared Component
List of Values: DISPLAY_AS_LOV
Display Extra Values: No
Display Null Value: No
Default Type: Static value
Static Value: CARDS
Escape special characters: No

Create a new region:

Name: Report
Type: Interactive Report
SQL Query:

	select
	m.id,
	m.name,
	listagg(c.category, ', ') within group (order by c.category) as categories
	from
	movies m
	left outer join movies_categories mc on m.id = mc.movie_id
	left outer join categories c on mc.category_id = c.id
	where
	nvl(:P4_DISPLAY_AS,'X') = 'REPORT'
	group by
	m.id,
	m.name
	order by
	m.name asc

view raw page_4_movies_report.sql hosted with ❤ by GitHub

Page Items to Submit: P4_DISPLAY_AS
Under Template Options:
General: Show Maximize Button
Static ID: reportRegion
Custom Attributes: style="display: none;"
Region Display Selector: No

Create a Dynamic Action:

Name: Toggle Display As
Event: Change
Selection Type: Item(s)
Item(s): P4_DISPLAY_AS
True Action #1:

Action: Execute JavaScript
Code:

	if ($v('P4_DISPLAY_AS') === 'CARDS') {
	$x_Hide('reportRegion');
	$x_Show('cardsRegion');
	$x_Show('filterRegion');
	$('body').removeClass('t-PageBody--hideLeft').addClass('t-PageBody--showLeft');
	apex.region('cardsRegion').refresh();
	apex.region('filterRegion').refresh();
	} else if ($v('P4_DISPLAY_AS') === 'REPORT') {
	$x_Hide('cardsRegion');
	$x_Hide('filterRegion');
	$x_Show('reportRegion');
	$('body').removeClass('t-PageBody--showLeft').addClass('t-PageBody--hideLeft');
	apex.region('reportRegion').refresh();
	}

view raw page_4_toggle_display_as.js hosted with ❤ by GitHub

That's it. Below are two print screens of the final result:

Thursday, May 25, 2017

Inspecting Oracle APEX Packaged Applications (Part 2)

This is the second part of a series of blog post. You can access the first and third parts here:

In this part we are going to implement a classic report using the cards template with a faceted search left side bar. In case you are wondering what a "faceted search" is, here is the definition according to Wikipedia:

Faceted search, also called faceted navigation or faceted browsing, is a technique for accessing information organized according to a faceted classification system, allowing users to explore a collection of information by applying multiple filters. A faceted classification system classifies each information element along multiple explicit dimensions, called facets, enabling the classifications to be accessed and ordered in multiple ways rather than in a single, pre-determined, taxonomic order.

Assuming we are working on Page Number 4.

Modify the page properties:

Page Template: Left Side Column

Create a new region:

Title: Cards
Type: Classic Report
SQL Query:

	select
	null as card_link,
	upper(decode(instr(m.name,' '),0,substr(m.name,1,2),substr(m.name,1,1)\|\|substr(m.name,instr(m.name,' ')+1,1))) as card_initials,
	m.name as card_title,
	m.name as card_text,
	listagg(c.category, ', ') within group (order by c.category) as card_subtext
	from
	movies m
	left outer join movies_categories mc on m.id = mc.movie_id
	left outer join categories c on mc.category_id = c.id
	where
	(:P4_SEARCH is null or
	(
	instr(upper(m.name),upper(:P4_SEARCH)) > 0 or
	instr(upper(c.category),upper(:P4_SEARCH)) > 0
	)) and
	(:P4_CATEGORIES is null or
	instr(':'\|\|:P4_CATEGORIES\|\|':',c.id) > 0
	) and
	nvl(:P4_DISPLAY_AS,'X') = 'CARDS'
	group by
	m.id,
	m.name
	order by
	case when :P4_ORDER_BY = 'ASC' then lower(m.name) end asc,
	case when :P4_ORDER_BY = 'DESC' then lower(m.name) end desc

view raw page_4_movies_cards.sql hosted with ❤ by GitHub

Page Items to Submit: P4_SEARCH,P4_ORDER_BY,P4_CATEGORIES,P4_DISPLAY_AS
Template: Blank with Attributes
Static ID: cardsRegion
Under the Report Attributes:

Template: Cards
Under Template Options:

General: Apply Theme Colors
Style: Basic
Icons: Display Initials

Create a new region:

Title: Filter
Position: Left Column
Under Template Options:

Header: Hidden
Item Width: Stretch Form Fields

Static ID: filterRegion

Create a page item under the Filter region:

Name: P4_SEARCH
Type: Text Field
Template: Hidden
Under Template Options:

Size: Large

Icon CSS Classes: fa-search
Value Placeholder: Search Movies

Create another page item under the Filter region:

Name: P4_ORDER_BY
Type: Select List
Label: Order By
Template: Optional – Above
List of Values Type: Static Values
Static Values: STATIC2:Ascending;ASC,Descending;DESC
Display Extra Values: No
Null Display Value: - Select -

Create another page item under the Filter region:

Name: P4_CATEGORIES
Type: Checkbox
Template: Optional – Above
List of Values Type: SQL Query
SQL Query: select category d, id r from categories order by category asc
Display Extra Values: No
Display Null Value: No
Pre Text:

	<button onclick="$f_CheckAll('#CURRENT_FORM_ELEMENT#',true);" class="t-Button t-Button--success" type="button"><span class="t-Button-label">Check All</span></button>
	<button onclick="$f_CheckAll('#CURRENT_FORM_ELEMENT#',false);" class="t-Button t-Button--danger" type="button"><span class="t-Button-label">Uncheck All</span></button>

view raw page_4_check_uncheck_buttons.html hosted with ❤ by GitHub

Create a region button under the Filter region:

Button Name: RESET_FILTERS
Label: Reset Filters
Button Position: Below Region
Button Template: Text with Icon
Under Template Options:

Type: Warning
Width: Stretch

Icon CSS Classes: fa-repeat
Action: Defined by Dynamic Action

Create a dynamic action:

Name: Reset Search Filters
Event: Click
Selection Type: Button
Button: RESET_FILTERS
True Action #1:

Action: Clear
Selection Type: Item(s)
Item(s): P4_SEARCH,P4_ORDER_BY,P4_CATEGORIES

Create a dynamic action:

Name: Refresh Report on Items Change
Event: Change
Selection Type: Item(s)
Item(s): P4_ORDER_BY,P4_CATEGORIES
True Action #1:

Action: Refresh
Selection Type: Region
Region: Cards

Create a dynamic action:

Name: Refresh Report on Search Enter
Event: Key Down
Selection Type: Item(s)
Item(s): P4_SEARCH
Client-side Condition:

Type: JavaScript expression
JavaScript Expression: this.browserEvent.which === 13

True Action #1:

Action: Refresh
Selection Type: Region
Region: Cards

True Action #2:

Action: Cancel Event
Event: Refresh Report on Search Enter

That's it for this part. On the third and last part with are going to create a "Display As" pill button with two options to switch between the Cards View and an Interactive Report.

Wednesday, May 24, 2017

Inspecting Oracle APEX Packaged Applications (Part 1)

Oracle Application Express (APEX) provides a set of built-in Packaged Applications that you can install and use in your environment. There are two types of packaged applications: (1) Sample Applications that highlight certain APEX capabilities such as Charts, Dynamic Actions, and so on; and, (2) Productivity Applications that are fully supported by Oracle as long as you don't unlock them.

With every new release, the APEX Team put a lot of effort upgrading these applications to take advantage of new features introduced on that release. For that reason, every time Oracle releases a new APEX version, I like to spend some time exploring some of these built-in Packaged Applications looking for design and functional ideas that I can use in my own projects. Once I find something I like, it's very simple to inspect how they've done it (if it's one of the Productivity applications, it must be unlocked first before you can inspect it on the Page Designer).

For this article, I'll show the step-by-step on how to implement one design and functionality that caught my attention in the latest APEX 5.1, located on the Customer Tracker application, Page 59 - Customers.

Whether possible, I tried to use declarative methods, e.g. Dynamic Actions instead of writing pure JavaScript code, this way I keep things as simple as possible. This is the print screen of the page I'm referring to:

It has three features that I liked and I will explain each of them on separate posts due to the length of this implementation:

Fixed Page Title with Icon and a Description.

A Classic Report with Cards Template and a Faceted Search Left Side Bar.

"Display As" Pill Button with Three Options: Cards View, Logo View, Report View.

I setup a demo application with the final results, login using the credentials Demo/Demo. You can also go there and copy the script to generate the sample database objects and data If you want to follow along this demo.

Let's start with the fixed page title with icon and a description.

On your Application, create a new page:

Select a Page Type: Blank Page
Page Number: 4
Name: Movies
Breadcrumb: - don't use breadcrumbs on page -

On Page 4, create a new region:

Title: Movies Report
Text: Track and Manage Movies
Position: Breadcrumb Bar
Template: Hero
Icon CSS Classes: fa-film
Region Display Selector: No

Optionally, you could use one of the images located on the following directory inside the APEX installation folder: apex\images\apex_ui\img\icons_src\pkg-apps, e.g.

Icon CSS Classes: app-script-planner

For this to work, you'll have to go to your application's Shared Components, User Interface Attributes. Select the Desktop interface, under Cascading Style Sheets add the following File URL:
#IMAGE_PREFIX#pkgapp_ui/css/5.0#MIN#.css

That's it for this part. On the second part with are going to create a classic report using the cards template with a faceted search left side bar.

Monday, May 15, 2017

Working with Multi-Select Items on Oracle APEX

The following post was motivated by a recent thread in the Oracle Application Express Community about how to convert single select to multi select field.

The Problem

When working with items that allow multiple selections in Oracle APEX such as checkbox, list manager, radio group, or shuttle, these values are stored in a single colon-delimited string.

Storing the value in this format into the database does not comply with the database normalization forms and is considered a bad practice.

Below I explain my approach to solve this problem. I created a demo application where you can try the solution and/or copy the script to generate the required database objects in your own environment to follow along with this example. Login using the credentials Demo/Demo.

The Solution

In this example, let’s assume we have the following data model:

Since a movie can have one or more categories, and a category can be assigned to zero or more movies, we will use an associate table (MOVIES_CATEGORIES) to link the values between these two tables (MOVIES and CATEGORIES). Instead of storing CATEGORIES as a column on the MOVIES table.

The first step is to create a "Form on a Table with Report" based on the MOVIES table. Click on "Create Page", select "Form" and "Form on a Table with Report". Modify the following values and leave the rest as default:

Under Report Page

Implementation: Classic
Page Name: Movies Report

Under Data Source

Table / View Name: MOVIES

Under Form Page

Page Mode: Modal Dialog
Page Name: Movies Form
Primary Key Type: Select Primary Key Column(s)
Primary Key Column 1: ID (Number)
Select Columns: NAME (Varchar2)

You will end up with two pages, e.g.:

Page 2 – Movies Report
Page 3 – Movies Form

On the Movies Report, change the SQL query as follow:

	select
	m.id,
	m.name,
	listagg(c.category, ', ') within group (order by c.category) as categories
	from
	movies m
	left outer join movies_categories mc on m.id = mc.movie_id
	left outer join categories c on mc.category_id = c.id
	group by
	m.id,
	m.name
	order by
	m.name asc

view raw page_2_movies_report.sql hosted with ❤ by GitHub

On the Movies Form, go to Processes and select "Process Row of MOVIES". Under Settings, set the "Return Key Into Item" to your page item holding the Primary Key ID, e.g. P3_ID.

Create a new Page Item "P3_CATEGORIES". Make it a Shuttle. Under the List of Values properties, specify the following:
Type: SQL Query
SQL Query: select category d, id r from categories order by category

Create a new process "Process Categories", right after "Process Row of MOVIES" and before "reset page" with the following PL/SQL Code:

	declare
	l_vc_arr2 apex_application_global.vc_arr2;
	begin
	l_vc_arr2 := apex_util.string_to_table(:P3_CATEGORIES);

	delete from movies_categories
	where movie_id = :P3_ID;

	forall i in l_vc_arr2.first..l_vc_arr2.last
	insert into movies_categories (movie_id, category_id)
	values (:P3_ID, l_vc_arr2(i));
	end;

view raw page_3_process_categories.sql hosted with ❤ by GitHub

Finally, create a fetch process "Fetch Categories" on the Pre-Rendering section, right after "Fetch Row from MOVIES" to retrieve the category information for existing records. This process should have a server-side condition of "Item is NOT NULL" = P3_ID and the following PL/SQL Code:

	declare
	l_vc_arr2 apex_application_global.vc_arr2;
	begin
	select category_id
	bulk collect
	into l_vc_arr2
	from movies_categories
	where movie_id = :P3_ID
	order by 1;

	:P3_CATEGORIES := apex_util.table_to_string (
	p_table => l_vc_arr2,
	p_string => ':'
	);
	end;

view raw page_3_fetch_categories.sql hosted with ❤ by GitHub

That's it!

Final Words

This demo was designed with Oracle Database 11g and APEX 5.0 in mind. In Oracle Database 12c, you can take advantage of Identity Columns when creating new tables. Also, the latest APEX 5.1 comes with a new package APEX_STRING that includes two functions (SPLIT and JOIN) that can be used instead of the package APEX_UTIL and the functions STRING_TO_TABLE and TABLE_TO_STRING.

Regardless of the method you use, it’s important to always keep your database normalized to reduce data redundancy and improve data integrity.