Saturday, September 23, 2017

Multiple APEX Workspaces in one Oracle Exadata Express Cloud account now possible

Last night my Oracle Exadata Express Cloud account was updated to APEX 5.1.2, but there was more updated once I looked closer into the dashboard.

Before we could already create multiple Oracle schemas, but from now on within one Oracle Exadata Express account you can create multiple APEX Workspaces.

Go to Instance Administration under the Application Express setting in Manage:

Clicking on Manage Workspaces > Create Workspace

Just follow the wizard; you can attach the workspace to an Oracle database schema and once finished you will see.

Now when you login again in your Oracle Exadata Express account and go to the APEX App Builder it will ask you which workspace you want to login to:

The first time it will give you a welcome message and asks to set a password

As it's an Oracle cloud account with identity management, I don't really need to set a password, I just need to confirm my profile

That's it! You are now in your new Oracle Application Express (APEX) Workspace.

It's very nice to see Oracle Exadata Express getting better with every maintenance release. Thanks Oracle :)

Update 23-SEP-2017: another new feature is that you can export your Oracle schemas and data to the Oracle Storage Cloud Service.

Go to Export to Cloud Storage:

Add your Oracle Cloud storage details and select the schemas to be exported:

Wednesday, September 06, 2017

Adding the game, the importance of the APEX Advisor, a lot of JavaScript and adapting for mobile

This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

Over a year ago I developed the game to practice the multiplications. I quickly created an Oracle APEX page, added a bunch of JavaScript and that was about it. The last weeks you could read I've redeveloped the entire app from the ground up but I still have to add the game to it.

I thought to just copy the page I developed before with all the JavaScript in the new app.

How do you copy a page from one Oracle APEX environment to another? 

Export the app from the first environment and import the app in the second environment. Once both apps are in the same workspace, you can copy a page from another app by going to the Create button:

Click the Page as Copy option and follow the wizard:

So I followed the wizard to copy the page to my new app.

Now the tricky bit was that I changed page numbers, so all my references (in JavaScript, Processes etc.) to page items were wrong. To quickly identify the issues I used the APEX Advisor.

The APEX Advisor you find in App Builder > Utilities : Advisor

The Advisor checks for best practices, errors, security issues etc. I really like the Advisor as it will give your application some kind of quality control. And it's very fast to identify issues and navigate to them. Here's a screenshot what the Advisor found:

I would always run the APEX Advisor, even when you start from a Blueprint application as you will most likely modify the app, add pages, make changes etc. and it's easy to forget for example to put an authorization scheme.

A few years ago I also wrote an extension to the APEX Advisor that would check for our own standards. You can query an Oracle APEX application with SQL, so it's easy to check if naming conventions where followed, if deployments went fine or anything else you find important. Here's a screenshot of some of the checks in one of the projects:

In the community some other people did something similar, for example Oliver Lemm wrote a Quality Assurance plugin.

So back to my application - I fixed those issues and was able to play the game :)
(screenshot from the browser)

To build the table/grid of the game I programmatically generate the APEX items by using the API apex_item.

All the rest is build in JavaScript. I'm not going to put all the source code in this blog post, but if you right click on the page where the game is, and you look at the source, you see all the JavaScript that is used.

Whenever the Start button is clicked, a Dynamic Action is fired which calls some other JavaScript functions. The fireworks and stopwatch are libraries I found on the internet which I integrated in the APEX app. If you have any question on the JavaScript side, feel free to ask in the comments section.

A few weeks ago I released the app to test. Some people sent me an email with their feedback, but many also used the build-in feedback mechanism that came with our Blueprint app.

I typically leave a feedback mechanism in every app I build (yes, even in production), it's a great way to communicate with your users.

The result of the feedback was, it didn't run great yet on mobile devices. Except from using Universal Theme, I didn't really pay attention to mobile devices, as I thought it would be too small anyway, but apparently people were trying it on there. So I decided to make sure it would work on at least a tablet, for example I saw a use case for myself to let my son play on my iPad.

In the next section I will go in a bit of detail of things that I had to adapt to make it more user friendly on a mobile device and some other things that might be interesting to know in other applications.

So here's how the app looks like on an iPad:

Compare this image with the image before taking in the browser and you will see some differences.

I added some JavaScript library to check if we run on a mobile device and when so, we add some extra CSS to make the table fit on the screen. The breadcrumb will always be shrunk and the padding is less. Here's the code:

When starting the game the keyboard comes out. One of the first things I had to do, was to make the number keyboard the default and not the normal keyboard with the letters. The way you do that is in your input text item, you define a pattern and give it a type of numeric (see the PL/SQL code where we do the call to apex_item).

The keyboard that slides out hides a part of the table, so it's not ideal. I've an external keyboard for my iPad, so hooking that up, makes it a really nice experience:

Another issue on the iDevice was that the fireworks at the end of the game didn't work well. So I decided to add a setting, so you can choose what you want to see at the end when you finish the game and when you run on an iDevice, the Fireworks is hidden.

The other settings are based on feedback. Some people don't want to play all numbers yet, so you can now pick the numbers. Some others wanted to see the time or countdown, and for some others they got stressed by it. So I decided to make it customizable per player.

Those preferences are all normal APEX items, but the difference is the way they get stored. I only use one field (preferences column in the mtl_player table) to store all preferences. The way I do that was inspired by the APEX Multi-language translate plugin (JTL Item) from my friend Jorge Rimblas, who stored all translations in one field by using JSON. So, all the settings of the game are stored in one JSON object.

On page load I use following JavaScript to set the items:

There's also a save button in the settings section. There's a straight forward dynamic action to do the update. But once saved I show a notification which is done by adding a call to the APEX JavaScript API:

apex.message.showPageSuccess("Player settings saved.")

This results in:

While you play the game on every switch of the number, the timing is saved by calling an AJAX Callback process by apex.server.process (see the source on the page when you play the game). For now I've done it this way, but maybe in the future I might cache the results and only do a callback at the end.

The last thing I want to cover is the overlay you get when you end the game. You have the fireworks, an image or a message as on the below screenshot.

The overlay is done by adding a div on the page and some CSS.

By default it has display set to none, but once the game is complete it's set by some JavaScript.

Hopefully this gives you more insight how the game was created and some things I cover are also useful in your own project.

If you want to play the game, surf to

Happy playing!