Apex report with LEVEL indentation using CONNECT BY

November 18, 2015 at 11:12 am | Posted in Oracle Developement | Leave a comment

Christoph Ruepprich:

Nice trick to add indentation for hierarchical reports in APEX.

Originally posted on Welcome to Paul Flynn's Blog:

How to keep the indentation generated using the reserved work LEVEL in a query which uses CONNECT BY

View original

APEX 5: Adding an icon to a report column

November 11, 2015 at 3:35 pm | Posted in Oracle Developement | 1 Comment

On a report, I needed to display a delete icon, that changes colors on hover. Here is how I went about it:


In the report, I set the the Customer ID column to type Plain Text. And added the following code to the HTML Expression:

<span class=”delIcon fa-stack”>
<i class=”fa fa-square-o fa-stack-2x”></i>
<i class=”fa fa-remove fa-stack-1x” style=”line-height: 20px;”></i>

I got the code snippet from the FontAwesome website. It simply stacks two fontawesome icons on top of each other. I added the class dellIcon to the span, so I could control the color and the hover behavior.


The following CSS adds the color and the hover color to the icon:

.delIcon {
color: #EF9A9A;

.delIcon:hover {
color: #D32F2F;


That’s it.

APEX 5 Page Designer Plugin

November 11, 2015 at 2:28 pm | Posted in Oracle Developement | Leave a comment

Filip Van Vooren created browser extensions for Chrome and Firefox to allow you to customize the APEX 5 page designer.

Check out his blog post for details.


The xPlug Chrome extension allows you to pin the gird layout to the right.

APEX & Raspberry Pi Game

September 11, 2015 at 10:42 am | Posted in Oracle Developement | Leave a comment

This is a proof of concept for a multiplayer game in Oracle APEX. Players log into an APEX application and choose a team (Blue/Red). Then the game presents each player with a page and a randomly chosen math problem. The page however is “locked” by a modal page telling the player that the game is paused.

The button the the Raspberry Pi controls the game state: Stopped, Ready, Running. When the game is stopped or ready, the player’s screens are blocked with the modal window.

A breadboard with a LED and two buttons is connected to the Raspberry Pi (the blue button doesn’t do anything).

The red button cycles through the games states. When the game is in Running state, the modal window goes away, and the players can answer the math question. When the button is pressed again, the game goes into Stopped state, and the modal window pops up, preventing the players from answering more questions.

In addition, the LED also shows the game state: Off- game stopped, Blinking – ready, On – running.

The interaction between the Raspberry Pi and APEX is done through Node.js utilizing Node4ORDS and socket.io.

This video shows me cycling through the three game states. There are four players connected: Red team on phones, Blue team on separate browsers.
At first the game is Stopped (LED off, modal window up), the first button press puts the game in Ready state (LED blinks, modal window up). The second button press puts the game in running state (LED on, modal window disappears). The final button press puts the game back int Stopped state (LED off, modal window up).

Quick & Dirty: APEX 5 – Change size of Modal Dialog page

September 2, 2015 at 6:05 pm | Posted in Oracle Developement | 3 Comments

To change the size (height/width) of a modal dialog page in APEX 5, edit the modal page’s page attributes, and edit the attributes of the Dialog section:


Setting And Retrieving CLOB values in APEX with JavaScript

September 1, 2015 at 12:36 pm | Posted in Oracle Developement | Leave a comment
Tags: , , , ,

Here is a very helpful blog post by Andy Tulley on how to set/get CLOB values from an APEX collection.


Expert Oracle Application Express

August 13, 2015 at 9:53 am | Posted in Oracle Developement | 1 Comment

I guess I’m a published author now. :)


Today the new edition of Expert Oracle Application Express was released. My contribution is chapter 12: Map Integration, where I show how to integrate maps, such as Google, or Leaflet maps into an APEX application. Here is the introduction from the book:

Any database application that contains location data, such as addresses or geographic coordinates, may benefit from displaying this data on a map. It may be of further benefit, to allow users to interact with this data by associating it with other data, or even perform geometric calculations against it.

In this chapter we will explore some options to free the geographic data from its confines of the database tables, and render it in useful ways on interactive maps. To accomplish this we will look at a number of tools and techniques available to us in APEX, the Oracle database, and some publicly available APIs.

We will learn how to turn address data into geographic coordinates and how to use these coordinates to display addresses on a map. Further we will take a look at various mapping APIs, and how to use Oracle’s powerful spatial features to find relationships between geographic data points.

You can get the book from Amazon. If you do, please don’t forget to leave a comment.

APEX 5 Login Screen

July 23, 2015 at 11:48 am | Posted in Oracle Developement | 4 Comments
Tags: , , , ,

The login screens of some of the APEX 5 sample applications have a nice look and include a check mark when you enter text into the username/password fields. This article will demonstrate how to create your own login page to look like that of some of the sample applications.

Edit your Login page (101):

  • Change login region template to Login.
  • Changes to Username and Password fields:
    • Change Template to Hidden.
    • Change Value Placehoder to Username / Password respectively.
    • Change Value Required to Yes.
    • Change CSS classes:
      • Username: icon-login-username
      • Password: icon-login-password
    • Change Post Text for username and password to
      <span class=”t-Login-iconValidation a-Icon icon-check”></span>.
  • Change the Login button Button Position to Next.

Save the changes and run the page. The result should look like this:


To can add one of the application icons from the the packaged applications select on of the icons my previous blog post and make the following changes:

Navigate to Edit Application Definition -> User Interface, and edit the interface for your application.

Edit User Interface

Under Cascading Style Sheets add the this CSS file: #IMAGE_PREFIX#pkgapp_ui/css/5.0#MIN#.css

Add CSS file

On the Login page (101) click the Log In region and add the name of the icon class to Icon CSS Classes:


Save and run the log in page. It should look something like this:


Where is my schema?

July 17, 2015 at 5:17 pm | Posted in Oracle Developement | Leave a comment
Tags: , , ,

I just came accross a little nuisance: During attempt to load some spreadsheet data into a new table using the APEX Data Load wizard in the SQL Workshop, I saw that the Schema select list was empty:

The schema select list is empty.

The schema select list is empty.

The issue was that the parsing schema was granted CREATE ANY TABLE. However for this list to be populated, the parsing schema needs the CREATE TABLE privilege.

Use JavaScript to add/remove elements from a delimited list

July 7, 2015 at 6:30 pm | Posted in Oracle Developement | 2 Comments

In my APEX application I have a page item that stores a colon delimited list of items, and I want to use JavaScript to add or remove elements from that list.

P1_LIST =”Black:Red:Gold:Blue:White”

Adding An Element

To add an element to the list with JavaScript you could  simple concatenate a color preceded by a colon:
newColor = ‘Green’
$s(‘P1_LIST’, $v(‘P1_LIST’) + ‘:’ + newColor);

This is problematic, if P1_LIST were empty. Then you could end up with:
P1_LIST = “:Green”

To avoid this you can first split the list into an array, “push” the new color to the array, and then write it back as a delimited list:

newColor = 'Green';
arr = $v('P1_LIST').split(':');
$s('P1_LIST', arr.join(':'));

Removing an Element:

To remove a color from our list is a little trickier. But working with arrays helps. Let’s say we want to remove Blue. We’ll pull the delimited list back into an array (arr). We’ll find the array index of Blue, then loop through arr and push all elements except Blue into a new array (arr2). Lastly we’ll write the new array back to the page item:

arr = $v('P1_LIST').split(':'); //change list into an array
arr.splice(arr.indexOf('Blue'),1); //remove the color blue
$s('P1_LIST', arr.join(':')); //write array back to list

Thanks to Sentinel for the simplified version using splice();

Next Page »

Blog at WordPress.com. | The Pool Theme.
Entries and comments feeds.


Get every new post delivered to your Inbox.