Some Notes on my small steps with Oracle APEX

In this article, I will share some of my personal findings and discoveries as I start out building a small APEX application. No experienced developer is likely to learn anything from this article – but first timers like me could perhaps benefit from my findings.

Some of the challenges I faced and managed to overcome:

• Use CSS styling for images shown in a grid layout – managed them down from their original size to a proper format
• Derive the default value for an item from a database table using the currently signed in user’s identification
• Set a CSS style on an image in a form-layout (using Advanced | Custom Attributes)
• Show a tooltip for cells showing one database column showing text derived from a different database column
• Replace the edit icon (pencil) in interactive report
• Dynamically derive page title – using data in page items
• Dynamically determine to which Page a Button should Navigate

Derive Default Value for an Item – based on a Query leveraging the current user’s Id

Challenge: I want to set the default value of an item when the user creates a new record and I want this default value to be derived using a SQL query that filters based on the user identity of the current user. I have a MEMBERS table that contains entries corresponding to all my application’s end users. This table contains a column USERNAME with values corresponding to the usernames with which my users log in.

Define default for page item of type PL/SQL Function Body, write a PL/SQL code block that returns value of the correct type. The bind variable :APP_USER can be used in the PL/SQL code to refer to the username of the current session’s user.. In the PL/SQL code, perform a query against the MEMBERS table, filter on username using :APP_USER and return whatever column value is needed to be used as default value.

Resources:

documentation on Session State Management,

Set CSS properties to impact the contents of Cells in a Table (in an interactive report template)

Challenge: I have a table that contains a column based on a BLOB column. I want the cells in this column to display an image based on the BLOB. However, the size of the image has to be contained. I can not directly define a CSS property for the column. So I need a workaround.

The steps:

• define a CSS class at page level
• define inline CSS at page level (for include this CSS in a file included in the application) to set the image size for images in table cells in the relevant column
• define static id at column level

Define a page level CSS class:

Define Inline CSS at page level

Here I specify that within the global selector aanbod  that was specified at page level (and applies to all elements in the current page) all table cells whose header attribute has the value of afbeelding should have the CSS property height set to 80px for any images (img elements) they happen to contain.

Define the Static ID at column level:

The result of this setting is that each cell (TD element) in the column has an attribute headers set to the value of the Static ID (afbeelding).

The result is that the image has the style property height set to 80px:

Some of the resources I used:

https://dsavenko.me/classic-report-interactive-report-interactive-grid-cell-style-based-on-data/

https://www.w3schools.com/css/css_attribute_selectors.asp

Define CSS property for an Image item (based on a BLOB column) in a Form layout

I want to display an image in the form layout. The item is based on a BLOB column. Some of the uploaded images are decently small, and others can be outrageously large. I want to have them all displayed in the same height of 400 pixels. I can achieve this by specifying a value for Custom Attributes in the Advanced section of the property palette for my image item. The value is set to style=”height:400px”

The result is a decently sized image:

Show a datadriven tooltip for cells in an Interactive Report

My interactive report’s table component contains a column called NAME. The underlying database table also contains a column called DESCRIPTION, a VARCHAR2(4000) field with additional descriptive content for each record. I would like the content of description to be shown as a tooltip for cells in the interactive report. When the mouse hovers over the name value, the description of the corresponding database record should be displayed as tooltip.

After some searching, it turns out that I can make use of the very powerful HTML Expression property. This property specifies for page items how they are to be rendered in the web page. In HTML expressions, I can use HTML tags, include calls to JavaScript functions, insert CSS and use placeholders for column values – for example #DESCRIPTION# and #NAME#, as shown below.

The result of this HTML expression is shown below:

Note: the #DESCRIPTION# placeholder can be used because the DESCRIPTION  page item exists – as hidden column.

Resources

https://community.oracle.com/thread/3537281

Docs: Managing Interactive Report Column Attributes https://docs.oracle.com/database/apex-18.1/HTMDB/managing-interactive-report-column-attributes.htm#HTMDB-GUID-D3275F83-328B-477F-AA35-8BC7DCD3DC1F 

Replace Edit Icon in Interactive Report (no pencil  but something else)

Instead of the standard pencil icon, I would like to use a different icon to drill down to the details page. Using a blog article, I have learned that I should edit the attributes of the Interactive Report template instance in the current page. The Link Icon attribute defines the icon that is shown. It is easily changed to a different icon.

The out of the box available icons are listed here: https://tedstruik-oracle.nl/ords/f?p=25384:1003:::NO::: 

The result looks like this:

Alternatively, Font Awesome’s collection of icons is available: https://fontawesome.com . An icon can be configured to be used as link icon can be configured as easily as <span class=”fa fa-bell”></span> 

See: https://www.apex-at-work.com/2016/07/interactive-report-with-font-awesome.html 

Dynamically Set Page Title

The title of the page should be derived dynamically, depending on the current page item values. I can use substitution variables in the title – referring to page or application level items. For example:

Details en Reactie voor Te Geef Aanbod  &P14_HANDOVER_OR_SHARE.

Note that the Page Item Reference starts with & and ends with . (a period).

I create a new page item, based on a PL/SQL Function Body. In this body, I derive the proper value for the page title.

I then define the page title using a substitution variable that refers to this page item.

Live in the application it resolves beautifully:

https://stackoverflow.com/questions/24659852/change-page-title-based-on-item-in-oracle-apex-4-0

Dynamically determine to which Page a Button should Navigate

I am using a single edit details page that is drilled down to from two pages. When on this page the Cancel or Save button is clicked, it should navigate to original page – which is one of two options.

How can I instruct APEX to evaluate the target page at runtime, based on session level attribute or a page item.

Add a Dynamic Action to the Cancel button:

Add an Action under the Dynamic Action to specify what should happen: Submit Page.

Finally create a processing branch under ‘After Submit’ to be executed whenever the submit page event has occurred:

The branch will perform navigation to the desired target page. It executed a PL/SQL Function block to determine the page it needs to navigate to – using APEX_PAGE.GET_URL, and using the page item HANDOVER_OR_SHARE that determines which page the navigation should take place to.

Resource:

How to create dynamic links in Oracle Application Express?

https://www.bi4all.pt/en/news/en-blog/how-to-create-dynamic-links-in-oracle-application-express/